Check if SQL Server is Under CPU Pressure
May 1, 2012 Leave a comment
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.
Here is an example of the output of this query:
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:
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.