An Introduction to SQL Server Wait Types and Stats

sql server wait typesOk, 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.

SELECT * FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC

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.

-- Isolate top waits for server instance since last restart or wait statistics clear
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN
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
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

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:

SELECT * FROM sys.dm_os_waiting_tasks
WHERE wait_type NOT IN
ORDER BY wait_duration_ms DESC;

Something to note is that if SQL Server is restarted, then all wait statistics are reset.

Additionally, you can run this to reset them:

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

Permissions needed….

The user must have VIEW SERVER STATE permission

It's only fair to share...Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInPin on PinterestBuffer this pageShare on RedditShare on StumbleUpon
About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on


  1. Hi Andy,

    This is khazi from india working as a SQL dba (3 Year EXP) in one of the MNC bangalore.
    I have reads all your blogs and the scenario which you have discussed really very much helpful for us.

    i have some doubt about the issue which is related to performamnce,

    SQL server 2008 r2 (ENTR EDITION) is not releasing memory back to os i am facing this usually. can you give some tips to fix it paramanently( without restarting services.) even i have set max memory as welll.

Speak Your Mind