SQL Server wait statistics

Dear All,

When users fire workloads and the response does not come in timely manner, it is perceived as a performance problem. Just like in life, either you are busy or free. Similarly, every request that goes to SQL Server is either executing or waiting. And it will be nice to know what makes a request wait; since knowing the waits can help you drill down to the exact root cause of a performance problem. At least, you can start your performance tuning journey from there.

Whenever a request is waiting, SQL Server assigns that request a wait type. There are more than 400 different wait types. You can use sys.dm_os_wait_stats DMV to simply list all the wait types.

select * from sys.dm_os_wait_stats

This DMV shows you a cumulative figure for all the wait types. Watch out for wait_time_ms column that shows you the total time in milliseconds that requests have spent waiting. Whenever a particular wait type occurs, the metrics get added up here. Which means that this DMV shows you aggregated data at server level. To being with, you can run the following query to see all the wait types in descending order of their cumulative wait times.

FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0
ORDER BY wait_time_ms DESC

Well, with so many wait types, a lot more needs to be explained as to what you really need to watch out for. And I shall do that in my next posts. To give a hint, we first need to differentiate between internal waits (about which you cannot do anything as they are bound to happen as part of SQL Server internal processes) and resource wait types (what you really need to look for).



Rahul Sharma

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook


Leave a Reply

Your email address will not be published.