[DMV] Wait Resources List

--Simple

SELECT top 10 *

FROM sys.dm_os_wait_stats

WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK','WAITFOR')

ORDER BY wait_time_ms DESC

GO

 

--Detail

declare @totalWait bigint

declare @totalSignalWait bigint

declare @totalResourceWait bigint

 

SELECT

             @totalWait = sum([wait_time_ms]),

             @totalSignalWait = sum([signal_wait_time_ms])

FROM

             sys.dm_os_wait_stats

WHERE

             [wait_type] NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK','WAITFOR')

 

SELECT @totalResourceWait = @totalWait - @totalSignalWait

 

SELECT top 10

             wait_type,

             waiting_tasks_count,

             wait_time_ms-signal_wait_time_ms AS resource_wait_ms,

             cast(100*(wait_time_ms - signal_wait_time_ms) / @totalResourceWait AS numeric(20,1)) AS resource_wait_percent,

             signal_wait_time_ms,

             cast(100*signal_wait_time_ms / @totalSignalWait AS numeric(20,1)) AS signal_wait_percent,

             wait_time_ms,

             cast(100*wait_time_ms / @totalwait AS numeric(20,1)) AS wait_time_percent

FROM

             sys.dm_os_wait_stats

WHERE

             [wait_type] NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK','WAITFOR')

ORDER BY 4 DESC

GO