In my last post, I talked about the sys.dm_exec_requests dynamic management view. That view is useful for finding currently executing queries on SQL server. In this post I’ll talk about another very useful dynamic management view sys.dm_os_wait_stats.
The sys.dm_os_wait_stats view is primarily used to determine where tasks in SQL server have spent their time waiting. It can be used determine general performance issues within a SQL server instance.
Usually, I’ve used this DMV in a test environment to determine if there are any bottlenecks inhibiting performance. I’ll usually order by wait_time_ms and take the top few wait types to determine if there is a problem based on the description in the MSDN article. Monitoring increases in wait times can also be useful to determine what’s inhibiting performance over certain time periods or intervals.
The unfortunate thing about this view is that it will give you a general inclination of a performance problem, but determining where specific performance problems originate from take more investigation. As such, it’s a good view to monitor to get an overall health of a SQL server instance.
Another thing to be aware of, there are certain wait types that do have a high wait time by design that don’t necessarily indicate a performance problem.
It is possible to reset the values in this view via a DBCC command. The only other time the stats will be reset is after a SQL server restart. This view is tracked server wide so remember, resetting this in an active system will affect others monitoring this view.
For more information on specific wait types see the MSDN article on the DMV: sys.dm_os_wait_stats