SQL Server CPU Waits

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
FROM sys.dm_os_schedulers
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.

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: