What is it?
According to Microsoft, SOS_SCHEDULER_YIELD 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 wait types are typically understood to be an indication of CPU Pressure; however, they might also be caused by other underlying problems or, on the opposite side, they could actually be a normal behaviour for your SQL Servers. Knowing what the cause is and if you need to worry about it will help you address the issue.
Let’s describe the process a little
SQL Server has its own scheduling mechanism that is implemented as part of what we know as SQLOS.
A small unit of time, called ‘time quantum’, is assigned to the thread in SQLOS. When the time quantum expires, the CPU (Or Scheduler) is switched to another process in what is known as a context switch:
1. A quantum (4ms) of CPU time is assigned to every thread
2. A thread it is sent to a queue: the Waiter List or SUSPENDED State
3. Suspended threads wait until resource becomes available to enter the RUNNABLE State.
4. Those threads from the top of the RUNNABLE Queue are granted execution and enter the RUNNING State
A thread will yield its position on the CPU and move to the bottom on the runnable queue when it runs for the duration of the 4ms quantum. It is during this context switch that an SOS_SCHEDULER_YIELD wait type is registered.
As mentioned above. SOS_SCHEDULER_YIELD could well be normal behaviour and nothing to worry about. However; there are two other possible causes for a SOS_SCHEDULER_YIELD:
1) Incorrectly configured sockets and cores
In this case the SOS_SCHEDULER_YIELD is most likely due by the Edition of your SQL Server, as there is limit for the number of Sockets and CPU’s that can be utilised in both Express Edition and Standard Edition. These limits can be found on the Microsoft website.
The easiest way of seeing if SQL Server cannot utilise all the cores assigned to it is to run the following query:
Select * from sys.dm_os_schedulers
This will show you online and offline schedulers. For instance; if you have a Standard Edition SQL Server and have 8 sockets assigned to it (Standard can only use 4) you will see 4 online schedulers and 4 offline schedulers. The wait type in this instance presents when the server is running at 50% CPU but registering the wait type because SQL Server cannot use more CPU.
2) Over Subscribed Virtual CPU’s at Host level
In this scenario, an executing thread could have been prevented from running because the thread itself might have exhausted its quantum without getting the 4ms of CPU time. In this instance a context switch would occur and a SOS_SCHEDULER_YIELD wait type would be registered.
If a thread is waiting for a resource, and this thread has to wait to be scheduled to execute by the hypervisor due to the host being oversubscribed with vCPUs based on the hardware pCPUs, then the actual resource wait time noted in SQL Server will include that time that the VM was unable to run, and so the wait time will appear to be longer than it would have been had the VM not been delayed.
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.
While these are some of the causes for SOS_SCHEDULER_YIELD wait, if you are still worried about your SQL Server database our Expert DBA’s are here to help
This article originally appeared on the blog of WellData’s Senior SQL DBA Luke Salter
About the Author
Luke Salter is a Senior SQL Server DBA at WellData, providing technical expertise from SQL Server 2000 through to SQL Server 2017 and AWS Cloud Technologies. He is also a keen Pet & Wildlife Photographer who volunteers at his local animal rescue organisation
<< Back to Articles
