Check if SQL Server is Under CPU Pressure

Last fall, I wrote about how to check wait conditions in SQL Server.  As I pointed out in that post, if a high percentage of your server’s wait time is due to waits of the type SOS_SCHEDULER_YIELD, you may be experiencing CPU pressure on your server.

You can use the following query to further investigate if your server is waiting on CPU resources.

SELECT scheduler_id, current_tasks_count, runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255

Here is an example of the output of this query:

scheduler_id current_tasks_count runnable_tasks_count
———— ——————- ——————–
0            8                   0
1            11                  0

If you run this query frequently and see the runnable_tasks_count consistenty above zero, that is an indication that the server is in fact CPU-bound.  If you see runnable_tasks_count in double digits for any length of time, that is cause for immediate concern.

The sys.dm_os_schedulers dynamic management view which is used in this query gives information on the SQL Server schedulers.  Schedulers are responsible for allocating system resources (such as CPU time) to SQL Server processes.  sys.dm_os_schedulers contains one row per scheduler for schedulers mapped to individual processors.  “Active worker” schedulers are those dedicated to regular user-controlled tasks; these are assigned IDs less than 255.  Schedulers with IDs above 255 are called “hidden”schedulers; these are used by system processes.  You should be more concerned with the “active worker” schedulers, and that’s why the query explicitly excludes schedulers 255 and up.

Optionally, instead of checking for particular scheduler_id values, you could modify the query to check the scheduler status, as follows:

SELECT scheduler_id, current_tasks_count, runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE status = ‘VISIBLE ONLINE’

The WHERE clause (status = ‘VISIBLE ONLINE’) will limit the result set to only those schedulers that are available for user processes.

These queries should work in SQL Server 2005, 2008, 2008 R2, and 2012.

One final point… before running out to upgrade your server, you should ensure that your queries and stored procedures are using the correct plans. An incorrect or corrupt plan can cause SQL Server to consume far more of the CPU resources than should normally be necessary.  In that case, adding physical resources to your server is not the answer.

Advertisements

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

%d bloggers like this: