Post reply

Performance Troubleshooting - Jonathan Kehayias and Ted Krueger Troubleshooting SQL Server

  • July 20, 2012 at 4:17 am

    #276555

    Hi All

    I'm using the following script to investigate wait statistics on my SQL Server, I got the script from the

    Jonathan Kehayias and Ted Krueger Troubleshooting SQL Server book - GREAT BOOK!

    SELECT TOP 10

    wait_type ,

    max_wait_time_ms wait_time_ms ,

    signal_wait_time_ms ,

    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,

    100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( )

    AS percent_total_waits ,

    100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( )

    AS percent_total_signal_waits ,

    100.0 * ( wait_time_ms - signal_wait_time_ms )

    / SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits

    FROM sys.dm_os_wait_stats

    WHERE wait_time_ms > 0 -- remove zero wait_time

    AND wait_type NOT IN -- filter out additional irrelevant waits

    ( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',

    'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',

    'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',

    'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',

    'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',

    'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',

    'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',

    'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',

    'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',

    'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS',

    'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',

    'RESOURCE_QUEUE' )

    ORDER BY wait_time_ms DESC

    I'm struggling to understand the signal_wait_time_ms values

    What does this column indicate?

    Thanks

  • July 20, 2012 at 4:54 am

    #1515423

    http://sqlserverpedia.com/wiki/Wait_Events_-_Sys.dm_os_wait_stats

    Steve Hall
    Linkedin
    Blog Site

  • July 20, 2012 at 5:06 am

    #1515431

    The signal wait is just time waiting on the processor. It's an indication that for any given type of wait, you may, depending on what percentage the signal wait takes up of the total wait, be looking at CPU issues.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • July 20, 2012 at 5:09 am

    #1515433

    Grant Fritchey (7/20/2012)

    The signal wait is just time waiting on the processor. It's an indication that for any given type of wait, you may, depending on what percentage the signal wait takes up of the total wait, be looking at CPU issues.

    Thanks

    I'm seeing a huge signal_wait_time for the CX_Packet wait type

    Any Ideas?

    Thanks

  • July 20, 2012 at 5:25 am

    #1515446

    SQLSACT (7/20/2012)

    Grant Fritchey (7/20/2012)

    The signal wait is just time waiting on the processor. It's an indication that for any given type of wait, you may, depending on what percentage the signal wait takes up of the total wait, be looking at CPU issues.

    Thanks

    I'm seeing a huge signal_wait_time for the CX_Packet wait type

    Any Ideas?

    Thanks

    Well, the CX_PACKET wait is the wait for context switching within the CPU, so I would expect that wait to also have a very high signal wait time. It's all CPU oriented.

    It might be an indication of excessive parallelism in your queries, but it's not automatic. Jimmy May's suggestion is if you're seeing more than 5% of all waits as CX_PACKET waits, you might be suffering from excessive parallelism. That's not a bad number to start with. My one suggestion to addressing it is to start by changing the cost threshold for parallelism, not adjusting the max degree of parallelism, not at the start anyway. Personally, I'd put it up as high as 50 for an OLTP system or up to 25 for a reporting system, and then adjust other settings as needed from there.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • July 20, 2012 at 5:28 am

    #1515450

    😎

    Grant Fritchey (7/20/2012)

    SQLSACT (7/20/2012)

    Grant Fritchey (7/20/2012)

    The signal wait is just time waiting on the processor. It's an indication that for any given type of wait, you may, depending on what percentage the signal wait takes up of the total wait, be looking at CPU issues.

    Thanks

    I'm seeing a huge signal_wait_time for the CX_Packet wait type

    Any Ideas?

    Thanks

    Well, the CX_PACKET wait is the wait for context switching within the CPU, so I would expect that wait to also have a very high signal wait time. It's all CPU oriented.

    It might be an indication of excessive parallelism in your queries, but it's not automatic. Jimmy May's suggestion is if you're seeing more than 5% of all waits as CX_PACKET waits, you might be suffering from excessive parallelism. That's not a bad number to start with. My one suggestion to addressing it is to start by changing the cost threshold for parallelism, not adjusting the max degree of parallelism, not at the start anyway. Personally, I'd put it up as high as 50 for an OLTP system or up to 25 for a reporting system, and then adjust other settings as needed from there.

    Thanks for this!

  • July 20, 2012 at 6:19 am

    #1515476

    See chapter 6 in the same book.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply