August 8, 2012 at 5:00 am
Hi All
I'm using the following script to track wait stats on my SQL Instance
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 trying to understand the Signal_Time
Am I right in that signal_time is purely CPU wait time and the original wait that the process did is irrelevant?
Thanks
August 8, 2012 at 2:13 pm
the signal wait indicates the time the thread has been waiting just for CPU but the wait_time_ms is the time the process has been waiting for CPU PLUS the wait time for any resource(a lock for example) to be available
Pooyan
August 10, 2012 at 4:44 am
pooyan_pdm (8/8/2012)
the signal wait indicates the time the thread has been waiting just for CPU but the wait_time_ms is the time the process has been waiting for CPU PLUS the wait time for any resource(a lock for example) to be available
What I'm trying to figure out is if I have a high signal_wait_time, it doesn't matter what the original wait was?
Thanks
August 10, 2012 at 5:13 pm
As a general guideline if the sum(signal_wait_time_ms)/sum(wait_time_ms) is more than 20% it could be a good indication of CPU bottleneck if that's what your looking for.The signal_wait_time_ms shows how much time in general all the processes of a specific type have been waiting for CPU .
Pooyan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply