SQL Server CPU Waits
October 13, 2012 Leave a comment
About a year ago, I wrote about wait conditions on SQL Server, and how they can be used to diagnose server performance problems.
In my experience, a few wait conditions are more common than others. One of these, SOS_SCHEDULER_YIELD, generally indicates that the server is under CPU pressure.
SQL Server schedulers allocate processor time to running threads. The amount of time that schedulers spend waiting for processors to become available is measured by the SOS_SCHEDULER_YIELD wait condition. If that wait condition is accounting for a high percentage of the total wait time on a server, that server is very likely under CPU pressure. An article at SQLServerCentral.com contains much more information about the SQL Server schedulers and the SQL OS.
The sys.dm_os_schedulers dynamic management view “returns one row per scheduler in SQL Server where each scheduler is mapped to an individual processor”. SO, the following query can be used to monitor whether the schedulers are waiting on CPU resources:
SELECT scheduler_id, current_tasks_count, runnable_tasks_count
WHERE scheduler_id < 255
In general, you want to see values of zero in the runnable_tasks_count column. Values greater than zero indicate possible problems, and values in double digits for any length of time almost surely indicate that the server is experiencing CPU resource contention.
Notice that schedulers with IDs greater than or equal to 255 are used by system processes, so that’s why the query explicitly excludes them.
Note that this doesn’t tell you anything about WHY the CPU usage is high, but it does give you a hint about where to focus your investigations into performance problems.
Credit goes to this thread on the Microsoft forums for pointing out this simple check for CPU pressure.