(Republishing, or using this info in a commercial product/website, is prohibited without permission. All other uses are permitted. If in doubt, please ask.)
This wait type is when a thread was able to execute for its full thread quantum (4 milliseconds in all versions of SQL Server, unchangeable), and so voluntarily yielded the scheduler, moving to the bottom of the Runnable Queue in its scheduler. Although the thread is immediately in the RUNNABLE state, it does not go onto the Waiter List because it doesn’t have to wait for a resource. Even though the thread doesn’t need to wait, it must register a wait type when it context switches off the processor, and that wait type is SOS_SCHEDULER_YIELD.
(Books Online description: “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.”)
Questions/comments on this wait type? Click here to send Paul an email, especially if you have any information to add to this topic.
Added in SQL Server version:
Removed in SQL Server version:
Extended Events wait_type value:
The map_key value in sys.dm_xe_map_values is 120 in 2008 and 2008 R2, and 124 in 2012 and 2014 RTM. After 2014 RTM, you must check the DMV to get the latest value as some map_key values have changed in later builds.
There are various knee-jerk reactions to this wait type:
- “It must be spinlocks that are the problem” – no, spinlocks are not tracked by wait types
- “There must be CPU pressure” – no, CPU pressure is indicated by increasing signal-wait times and long Runnable Queues, not by the prevalence of SOS_SCHEDULER_YIELD waits
- “The query needs more CPU” – no, see #2
The most common cause of SOS_SCHEDULER_YIELD waits that I see is queries doing scans of pages that are in memory and aren’t changing, hence there’s no contention for page access and the scanning thread can run until it exhausts its thread quantum. This could be because a query plan is erroneously doing a table scan, or it could be a normal part of your workload. Just like CXPACKET waits, don’t jump to the conclusion that SOS_SCHEDULER_YIELD waits are bad.
I wrote a long article on understanding and troubleshooting SOS_SCHEDULER_YIELD waits on sqlperformance.com that explains more about thread scheduling and quantum exhaustion, plus troubleshooting. Basically this involves identifying the query that’s producing the SOS_SCHEDULER_YIELD waits and making sure the query plan looks correct (e.g. is there a missing nonclustered index causing an in-memory table scan?). Note that queries incurring SOS_SCHEDULER_YIELD waits don’t show up in sys.dm_os_waiting_tasks so you need a script that looks at sys.dm_exec_requests instead – and I have one in this blog post.
When the thread quantum expires, the thread *must* yield the processor. It has no knowledge of other threads on that scheduler and there is *always* a context switch when the thread goes to the bottom of the runnable queue, even if it’s the only thread on the scheduler. The thread cannot decide to just not yield. It’s the context switch that forces the registration of a wait type within SQLOS. If the context switch does not occur (because the thread fails to check whether the quantum has expired), that’s a non-yielding scheduler and you’ll see message 17883 in the error log.
SOS_SCHEDULER_YIELD waits always have a 0 resource wait component, because no resource wait occurs (which is why the thread doesn’t go onto the waiter list).
Known occurrences in SQL Server (list number matches call stack list):
- Yielding while scanning a table (in this case, as part of an ordered parallel table scan)
- Yielding while performing a sort (in this case, as part of running a nested-loop join)
- Yielding while scanning the LRU values of buffers in the buffer pool to populate the buffer pool free list with free buffers (in this case, while allocating a page in an index as part of a page split)
- Yielding while calculating cardinality estimates while compiling a query plan
- Yielding while scanning the the list of buffers for a database (in this case, while shutting down the database as part of a DROP DATABASE)
And many, many more similar call stacks from all across SQL Server.
Abbreviated call stacks (list number matches known occurrences list):