Finding The Top Ten Cumulative Wait Events.

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',

'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