Dynamic Management Views: sys.dm_os_wait_stats

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.

select wait_type
		,waiting_tasks_count
		,wait_time_ms
		,max_wait_time_ms
		,signal_wait_time_ms 
	from sys.dm_os_wait_stats
	order by wait_time_ms desc

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.

dbcc sqlperf ('sys.dm_os_wait_stats', clear)

For more information on specific wait types see the MSDN article on the DMV: sys.dm_os_wait_stats

About these ads

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s