SQL Server Blog

Wait Stats

Show me Wait Stats

Cumulative Waits on instance to Date Top X Waits for server instance This is since the last restart or having statistics manually cleared --Clearing Stats on Server DBCC SQLPERF('sys.dm_os_wait_stats',clear) --Clean Cache DBCC FREEPROCCACHE --Clean Buffers DBCC DROPCLEANBUFFERS

                
                    WITH WaitStats
AS (
	SELECT [wait_type], [waiting_tasks_count]
		,[wait_time_ms]/1000. AS [wait_time_secs]
		,100.*[wait_time_ms]/SUM([wait_time_ms])OVER()	AS [pct]
		,ROW_NUMBER()OVER(ORDER BY [wait_time_ms] DESC) AS [Rnum]
	FROM sys.dm_os_wait_stats
	WHERE [wait_type] NOT IN 
		('BROKER_EVENTHANDLER', 'BROKER_RECIEVE_WAITFOR', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH', 'BROKER_TRANSMITTER'
		,'CHECKPOINT_QUEUE', 'CHKPT', 'CLR_AUTO_EVENT','CLR_MANUAL_EVENT','CLR_SEMAPHORE', 'DISPATCHER_QUEUE_SEMAPHORE'
		,'FT_IFTS_SCHEDULER_IDLE_WAIT', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP', 'LAZYWRITER_SLEEP'
		,'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'RESOURCE_QUEUE', 'SERVER_IDLE_CHECK'
		,'SLEEP', 'SLEEP_BPOOL_FLUSH', 'SLEEP_DBSTARTUP', 'SLEEP_DCOMSTARTUP', 'SLEEPMSDBSTARTUP'
		,'SLEEP_SYSTEMTASK', 'SLEEP_TASK', 'SLEEP_TEMPDBSTARTUP', 'SNI_HTTP_ACCEPT', 'SQLTRACE_BUFFER_FLUSH'
		,'WAITFOR_TASKSHUTDOWN' ,'XE_DISPATCHER_JOIN', 'XE_DISPATCHER_WAIT', 'XE_TIMER_EVENT'
		-----Newly Background Wait types added in 2012
		,'DIRTY_PAGE_POLL', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION', 'SP_SERVER_DIAGNOSTICS_SLEEP'
		,'SLEEP_MASTERDBREADY', 'FFT_RECOVERY', 'PWAIT_ALL_COMPONENTS_INITIALIZED'
		)
	AND [wait_type] NOT LIKE 'PREEMPTIVE_%'
	)
SELECT 
	WS_1.waiting_tasks_count
	,WS_1.wait_type AS [wait_type]
	,CAST(WS_1.[wait_time_secs] AS DECIMAL(12,2))	AS [wait_type_secs]
	,CAST(WS_1.pct AS DECIMAL(12,2)) AS [Pct]
	,CAST(SUM(WS_2.pct) AS DECIMAL(12,2)) AS [RunPct]
FROM WaitStats AS WS_1 INNER JOIN WaitStats AS WS_2 ON WS_2.Rnum <= WS_1.Rnum
GROUP BY WS_1.Rnum
		 ,WS_1.waiting_tasks_count
		 ,WS_1.wait_type
		 ,WS_1.wait_time_secs
		 ,WS_1.pct
HAVING SUM(WS_2.pct) - WS_1.pct < 95 
OPTION (RECOMPILE); --pct threshold		 
        
        

Glen Berry Version: Isolate top waits for server instance since last restart or statistics clear


WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN'))
SELECT W1.wait_type, 
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold
        

Wait Stats - Explanation

Common Significant Wait Types with BOL Explanantions
ASYNC_IO_COMPLETION Occurs when a task is waiting for I/Os to finish
IO_COMPLETION Occurs while waiting for I/O operations to complete.
This wait type generally represents non-data page I/Os.
Data page I/O completion waits appear as PAGEIOLATCH_* waits
PAGEIOLATCH_SH Occurs when a task is waiting on a latch for a buffer that is in an I/O request.
The latch request is in Shared mode.
Long waits may indicate problems with the disk subsystem.
PAGEIOLATCH_EX Occurs when a task is waiting on a latch for a buffer that is in an I/O request.
The latch request is in Exclusive mode.
Long waits may indicate problems with the disk subsystem.
WRITELOG Occurs while waiting for a log flush to complete.
Common operations that cause log flushes are checkpoints and transaction commits.
PAGELATCH_EX Occurs when a task is waiting on a latch for a buffer that is not in an I/O request.
The latch request is in Exclusive mode.
BACKUPIO Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data.