Knee-Jerk Wait Statistics : SOS_SCHEDULER_YIELD

In my previous post, I discussed LCK_M_XX, ASYNC_NETWORK_IO, and OLEDB waits and the knee-jerk reactions to them. In this post I’m going to continue with the wait statistics theme and discuss the SOS_SCHEDULER_YIELD wait.

When SOS_SCHEDULER_YIELD is the most prevalent on a server, it’s common to see sustained, high CPU usage. The knee-jerk reaction here is that the server must be under CPU pressure, or that a spinlock is the problem.

We need a bit of background here to understand these two reactions.

Thread Scheduling

Thread scheduling in SQL Server is managed by SQL Server itself, not by Windows (i.e. it’s non-preemptive). The SQL OS portion of the Storage Engine provides scheduling functionality and threads transition from running on a Processor (where the thread state is RUNNING) to being on the Waiter List waiting for a resource to become available (state is SUSPENDED) to being on the Runnable Queue once the resource becomes available (state is RUNNABLE) waiting to get to the top of the queue and back onto the Processor again (back to state being RUNNING). I’ve capitalized Processor, Waiter List, and Runnable Queue to identify them as parts of a scheduler.

Whenever a thread needs a resource that it can’t immediately acquire, it becomes suspended and waits on the Waiter List to be told (signaled) that its resource is available. The time spent on the Waiter List is the resource wait time and the time spent on the Runnable Queue is the signal wait time. Together they combine to be the overall wait time. SQL OS keeps track of the wait time and the signal wait time so we have to do some math on the output from sys.dm_os_wait_stats to derive the resource wait time (see my script here).

The Waiter List is unordered (any thread on it can be signaled at any time and move to the Runnable Queue) and the Runnable Queue is First-In-First-Out (FIFO) almost 100% of the time. The only exception to the Runnable Queue being FIFO is where multiple Resource Governor workload groups have been configured in the same resource pool and they have different priorities relative to each other. I’ve never seen this used successfully in production so I won’t discuss it further.

There is another reason why a thread may need to move off the Processor – it exhausts its quantum. The thread quantum in SQL OS is fixed at 4 milliseconds. The thread itself is responsible for determining that its quantum has been exhausted (by calling helper routines in SQL OS) and voluntarily giving up the processor (known as yielding). When this occurs, the thread moves directly to the bottom of the Runnable Queue, as there is nothing for it to wait for. SQL OS must register a wait type for this transition off the Processor though, and registers SOS_SCHEDULER_YIELD.

This behavior is often mistaken for CPU pressure, but it’s not – it’s just sustained CPU usage. CPU pressure, and recognizing it, is a whole other topic for a future post. As far as this post is concerned, as long as the average signal wait time is low (0-0.1-0.2ms), it’s a pretty safe bet that CPU pressure isn’t an issue.

Spinlocks

A spinlock is a very low-level synchronization primitive that is used to provide thread-safe access to data structures in SQL Server that are extremely hot (very volatile and accessed and changed incredibly frequently by multiple threads). Examples of such structures are the buffer free list in each portion of the buffer pool and the proportional-fill weightings array for the data files in a filegroup.

When a thread needs to acquire a spinlock, it looks to see if the spinlock is free and if so immediately acquires it (using an interlocked assembly-language primitive like ‘test bit clear and set’). If the spinlock can’t be acquired, the thread immediately tries to acquire it again, and again, and again, for up to a thousand iterations, until it backs off (sleeps for a bit). This doesn't register as any wait type, as the thread simply calls the Windows sleep() function, but can make other threads that are waiting have large (10-20ms+) signal wait times as the sleeping thread stays on the processor until it gets the spinlock.

Why am I talking about spinlocks? Because they can also be a cause of high CPU usage, and there's a misconception that spinlocks are a cause of SOS_SCHEDULER_YIELD waits. They're not.

SOS_SCHEDULER_YIELD Causes

So there is one cause for SOS_SCHEDULER_YIELD: a thread exhausting its scheduling quantum and heavily recurring instances can lead to SOS_SCHEDULER_YIELD being the most prevalent wait along with high CPU usage.

You won't see SOS_SCHEDULER_YIELD waits show up in the output from sys.dm_os_waiting_tasks, as the thread isn't waiting. You can see which query is generating the SOS_SCHEDULER_YIELD waits by querying sys.dm_exec_requests and filtering on the last_wait_type column.

This also means that when you see SOS_SCHEDULER_YIELD in the output of sys.dm_os_wait_stats, the resource wait will be zero, because it didn't actually wait. But remember that each of these 'waits' equals 4ms of CPU time accrued for the query.

The only way to prove what's causing SOS_SCHEDULER_YIELD waits is to capture SQL Server call stacks when that wait type occurs, using Extended Events and debug symbols from Microsoft. I have a blog post that describes and shows how to perform that investigation, and there’s a great whitepaper about spinlocks and spinlock investigations that is worth reading if you’re interested in that depth of internals.

For the case of quantum exhaustion, that’s not the root cause. It’s a further symptom. Now we need to consider why a thread may be exhausting its quantum repeatedly.

A thread can only exhaust its quantum when it can continue processing SQL Server code for 4ms without needing a resource that another thread owns – no waiting for locks, page latches, data file pages to be read from disk, memory allocations, file growths, logging, or the myriad other resources that a thread might need.

The most common piece of code where quantum exhaustion can occur and rack up large amounts of SOS_SCHEDULER_YIELD waits is scanning an index/table where all the necessary data files pages are in memory and there is no contention for access to those pages, and so that’s what I encourage you to look for in query plans when you see SOS_SCHEDULER_YIELD as the top wait type – large and/or repeated index/table scans.

This doesn’t mean I’m saying that large scans are bad, as it could be that the most efficient way to process your workload is through a scan. However, if the SOS_SCHEDULER_YIELD waits are new and unusual, and are caused by large scans, you should investigate why the query plans are using scans. Maybe someone dropped a critical nonclustered index, or statistics are out-of-date and so an incorrect query plan was chosen, or maybe an unusual parameter value was passed to a stored procedure and the query plan called for a scan, or a code change occurred without supporting index additions.

Summary

Just as with other wait types, understanding exactly what SOS_SCHEDULER_YIELD means is key to understanding how to troubleshoot it, and whether the behavior is expected because of the workload being processed.

As far as general wait statistics are concerned, you can find more information about using them for performance troubleshooting in:

• My SQLskills blog post series, starting with Wait statistics, or please tell me where it hurts
• My Wait Types and Latch Classes library here
• My Pluralsight online training course SQL Server: Performance Troubleshooting Using Wait Statistics
• SQL Sentry Performance Advisor

In the next article in the series, I’ll discuss another wait type that is a common cause of knee-jerk reactions. Until then, happy troubleshooting!