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.
SELECT * 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).
Regards
Rahul Sharma
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
