An Introduction to SQL Server Wait Types and Stats

Ok, so in this post, I wanted to go over SQL Server wait types and statistics  – what they are and how to check them.

My idea is then to start a series of posts which go into more detail about individual SQL Server wait types, their causes and how to improve them. At the time of writing this post, I do not know about all of the wait types in SQL Server and there are many of them.

Some of them I have experience with, some of them I haven’t so this is a partially a learning experience for me, as well as hopefully providing you with informative content so that you can better understand your own SQL Server wait types.

So let us begin…. 🙂

What are SQL Server Wait Types?

When troubleshooting performance issues in SQL Server, it is difficult to know where to start as there is so much to look at.

The best way is to look at your SQL Server at a high level and then drill down to the root causes. As well as capturing hardware/OS and SQL Server related counters using Perfmon, looking at the wait types on your SQL Server is essential to this process.

Ultimately this is more efficient than just assuming that something is, when it may not actually be.

If you, the DBA are more efficient, management gets more for their money and we all know that time is money. SQL Server wait stats provide you with that high level information which you need to succeed.

Whenever a process inside SQL Server has to wait for something, the time spent on that is tracked. So for example, the wait could be tracked because of delays reading data, writing data or some external process.

The waiting process is assigned a wait type. The wait types are not terribly descriptive and require translation into something more meaningful which I will attempt to do in later posts.

If you can reduce waits in SQL Server, you will achieve better overall performance.

How to view your SQL Server wait types and statistics

There is a DMV which you can use to return an aggregated view of system waits in milliseconds.

[sourcecode language=’sql’]
SELECT * FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC
[/sourcecode]

The results will show many waits, some of them however are system waits and are not useful for performance tuning.

I prefer to use this version which was written by SQL Server expert Glenn Berry. It sorts the “wheat from the chaff” and returns the really useful wait stats from your SQL Server instance, with the worst offenders at the top of the list.

[sourcecode language=’sql’]
— Isolate top waits for server instance since last restart or wait 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’,’SQLTRACE_INCREMENTAL_FLUSH_SLEEP’))
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 OPTION (RECOMPILE); -- percentage threshold GO[/sourcecode] If you want to view the current waits as they are occurring, you can do this too. Here I am applying the same filters as in Glenn's script above: [sourcecode language='sql'] SELECT * FROM sys.dm_os_waiting_tasks 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','SQLTRACE_INCREMENTAL_FLUSH_SLEEP') ORDER BY wait_duration_ms DESC; [/sourcecode] Something to note is that if SQL Server is restarted, then all wait statistics are reset. Additionally, you can run this to reset them: [sourcecode language='sql']DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);[/sourcecode] Permissions needed….

The user must have VIEW SERVER STATE permission