Detect SQL Server CPU Pressure

By:   |   Comments (5)   |   Related: 1 | 2 | 3 | 4 | > Dynamic Management Views and Functions


I'm trying to tune my SQL Server and I'm having a hard time deciding if my CPU is under a lot of stress. What's a quick and easy way to detect CPU pressure?  Check out this tip to learn more.


There are a few different ways to detect CPU pressure, for example, Performance Monitor or SQL Profiler, but in this tip we'll discuss the wait type SOS_SCHEDULER_YIELD and the DMV sys.dm_os_schedulers.

Some of you might have seen the wait type SOS_SCHEDULER_YIELD and wondered what in the world is this? Books Online (BOL) describes this wait type as:

Occurs when a task voluntarily yields the scheduler for other tasks to execute. During this wait the task is waiting for its quantum to be renewed.

SOS_SCHEDULER_YIELD is a fairly common wait type and occurs when there is CPU pressure. SQL Server runs multiple threads and tries to allow all threads to run without problems. However, if all the threads are busy on each scheduler and can't let other threads run, it will yield itself for another thread, which in turn creates the SOS_SCHEDULER_YIELD wait type.

You can see the top wait stats and the percentage of waits at the current point in time by running the following query by Tim Ford and Glenn Berry.

WITH Waits AS 
wait_time_ms / 1000. AS wait_time_s, 
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct, 
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn 
FROM sys.dm_os_wait_stats 
WHERE wait_type 
) -- filter out additional irrelevant waits 
SELECT W1.wait_type, 
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s, 
CAST(W1.pct AS DECIMAL(12, 2)) AS pct, 
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct 
FROM Waits AS W1 
INNER JOIN Waits AS W2 ON W2.rn <= W1.rn 
GROUP BY W1.rn, 
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold;

After running this query on one of my servers I can see that only 2.22% of my waits are due to CPU pressure so I can rule that out as a cause for poor performance. (This is only current since the last SQL Service restart):

SQL Server Wait Types and Associated Values

If you see a high percentage of this wait type then one way of troubleshooting this is to use the DMV sys.dm_os_schedulers. BOL describes sys.dm_os_schedulers as:

Returns one row per scheduler in SQL Server where each scheduler is mapped to an individual processor. Use this view to monitor the condition of a scheduler or to identify runaway tasks.

This DMV has several columns including number or workers, active tasks, and the status of each scheduler, etc., which can help troubleshoot certain problems, but the most important column is the one used for measuring queued tasks, the runnable_tasks_count column. This column displays the count of tasks yielding to other tasks resulting in a SOS_SCHEDULER_YIELD wait type. If this column is frequently greater than 0 then CPU pressure may be present and blocking may occur.

To see an average of current tasks and current waiting tasks you can use the following query:

SELECT AVG(current_tasks_count) AS [Avg Current Task], 
AVG(runnable_tasks_count) AS [Avg Wait Task]
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255

To see an average of current tasks and current waiting tasks you can use the following query

Another useful query that will display all statements being run and the associated scheduler that the statement is running on is:

a.scheduler_id ,
 (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 , 
      ( (CASE WHEN statement_end_offset = -1 
         THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) 
         ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement
FROM sys.dm_os_schedulers a 
INNER JOIN sys.dm_os_tasks b on a.active_worker_address = b.worker_address
INNER JOIN sys.dm_exec_requests c on b.task_address = c.task_address
CROSS APPLY sys.dm_exec_sql_text(c.sql_handle) AS s2 

Another useful query that will display all statements being ran

BOL mentions each scheduler is mapped to a processor so if we run the following query we can see how many processors SQL Server is using:

FROM sys.dm_os_schedulers 
AND is_online = 1

run the following query we can see how many processors SQL Server is using

While buying more CPU's is the easy answer to fixing these problems, it may not be the best answer. Tuning expensive queries can also help reduce the number of runnable_tasks_count. For example, if you have a poor performing query, adding more CPU's may not help at all.

The following query will show current tasks running, CPU time, wait type, etc.:

SELECT s2.text, 
FROM sys.dm_exec_requests a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS s2  
WHERE status <> 'background'

Once you have tuned your environment and you are still continuously seeing the runnable_tasks_count column greater than 0, you might have a good argument for adding more CPU's. Determining how many CPU's is a choice you as a DBA will have to make based on your baseline and the results captured since.

I, personally, prefer using DMV's over Performance Monitor and Profiler because it's faster to setup and easier for me to read. Running a trace can be difficult unless you know what the exact problem is and Performance Monitor has hundreds of counters to choose from. I know some DBA's have been using Performance Monitor and Profiler for years and will argue, but I think DMV's are the future and Microsoft seems to be focusing on adding more DMV's to each version release and less time on Performance Monitor and Profiler.

Next Steps
  • Check out Tim Ford's tip on "SQL Server sys.dm_os_wait_stats DMV Queries" where he talks more about the query used to see the top wait stats and the percentage of waits at the current point in time.
  • A good way to test you server before production is to use a stress tool that will simulate a normal workload and look for high SOS_SCHEDULER_YIELD wait types.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Brady Upton Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, August 9, 2016 - 1:42:30 PM - Megan Back To Top (43090)

 Is runnable_tasks_count actually running_pct column from first query?


Thursday, February 26, 2015 - 11:55:03 AM - JeremyH Back To Top (36361)

This is a great post. Just one suggestion - I think it would be helpful clarify the statement, "scheduler is mapped to a processor so if we run the following query we can see how many processors SQL Server is using," by changing processors to cores, and by making a note that this returns the number of logical cores, not physical cores. I realize that is what BOL says, but I believe they are using incorrect terminology in the post.

Monday, June 3, 2013 - 10:14:20 AM - Todd Back To Top (25253)


Great article and very useful DMV scripts.

A couple of items to add:

1. If your system contains a high percentage of complex/reporting queries and you are running on a multi-processor server, to optimize CPU utilization, you may also need to also adjust MAX DEGREE OF PARALLELISM. Today, it's very common to see 8 or 16 processors and depending on the applicaiton, SQL Server may not perform optimally with default degree of parallelism settings.

Microsoft article provides a good start/overview of MAX DEGREE OF PARALLELISM. Also, see and

2. If you have determined that CPU is an issue on your server, the best tool I found for identifying which specifc queries are responsible for high CPU is SQL Trace Analyzer ( )  SQL Profiler traces are difficult to work with, especially on busy systems. SQL Trace Analyzer taks a Proiler trace and generates a report of the most CPU intensive stores procedures and queries.


Tuesday, March 5, 2013 - 8:39:50 AM - Siva Back To Top (22570)

Good article for the DBA community. Thanks, Brady!

Friday, March 1, 2013 - 12:10:49 PM - Srinath Back To Top (22510)

Very nice article.. Thank you Brady. Keep uo the good work!!

get free sql tips
agree to terms