Another DMV a Day

High signal waits (above 20%) are another indication of CPU pressure. The results from this query are cumulative since your SQL Server has been running.

— Total waits are wait_time_ms (high signal waits indicates CPU pressure)
SELECT signal_wait_time_ms=SUM(signal_wait_time_ms)
,’%signal (cpu) waits’ = CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
,resource_wait_time_ms=SUM(wait_time_ms – signal_wait_time_ms)
,’%resource waits’= CAST(100.0 * SUM(wait_time_ms – signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
FROM sys.dm_os_wait_stats

 

— Check SQL Server Schedulers to see if they are waiting on CPU
SELECT scheduler_id, current_tasks_count, runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255

If the runnable task count is above 10 for multiple schedulers, you are experiencing CPU pressure. This can be a much more reliable indicator than CPU Utilization or processor queue length.

 

Technorati Tag: SQL Server

This entry was posted in SQL Server 2005. 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