July 20, 2012 at 4:17 am
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
July 20, 2012 at 5:06 am
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
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
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
😎
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
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply