A DMV a Day – Day 14

The DMV for Day 14 is sys.dm_os_wait_stats, which is described by BOL as:

Returns information about all the waits encountered by threads that executed. You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches.

There are several very useful queries that use sys.dm_os_wait_stats, but I will only be talking about a simple one today. This DMV works with SQL Server 2005, 2008, and 2008 R2. It requires VIEW SERVER STATE permission.

-- Total waits are wait_time_ms (high signal waits indicates CPU pressure)
SELECT CAST(100.0 * SUM(signal_wait_time_ms)/ SUM (wait_time_ms)AS NUMERIC(20,2)) 
AS [%signal (cpu) waits],
CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) 
AS [%resource waits]
FROM sys.dm_os_wait_stats; 

This query is useful to help confirm CPU pressure. Signal waits are time waiting for a CPU to service a thread. Seeing total signal waits above roughly 10-15% is a pretty good indicator of CPU pressure, although you should be aware of what your baseline value for signal waits is, and watch the trend over time. You should also remember that these wait stats are cumulative since SQL Server was last restarted or since the wait statistics were cleared with this command:

-- Clear Wait Stats 
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

If your SQL Server instance has been running for quite a while, and you make a significant change (such as adding an important new index), you should think about clearing the old wait stats with the DBCC SQLPERF command shown above. Otherwise, the old cumulative wait stats will mask what is currently going on since your change.

About these ads
This entry was posted in SQL Server 2008. Bookmark the permalink.

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