SQL SERVER: SOS_SCHEDULER _YIELD waits

By Luke
11th July 2019

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

 

Luke Salter Senior SQL Server DBA
Luke Salter Senior SQL Server DBA

 

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

Share

<< Back to resources

Join Our Newsletter

Contact Email  *
First Name 
Last Name 
*Required Fields
I agree to the  Privacy Policy and  Terms of Use 

Here's what other people think

Google Rating
5.0
WellData provide an excellent level of support and their team are very knowledgeable and always go the extra mile. I would wholeheartedly recommend them.read more
Stefan Parrott
Stefan Parrott
13:12 19 Sep 17
Having worked with the team at Welldata for approaching 17 years, my company and I have always found them professional, reliable and generally a great firm to work with.read more
John Lynes
John Lynes
14:00 04 Oct 17
Our partnership with WellData has enabled us to resolve critical database problems. Their attitude and service makes them a natural extension to our own technology team, which extends our capability in a critical area of our business.read more
Steve Fenton
Steve Fenton
10:56 05 Nov 18
Really know their stuff and their response times are very good.
Andy Cole
Andy Cole
11:45 06 Nov 18
We use Welldata for SQL support and are very happy with their service.
David Sadler
David Sadler
09:40 15 Nov 18
A very dedicated team of extremely professional and knowledgeable experts
Giulia Iannucci
Giulia Iannucci
14:08 16 Nov 18
I have worked with the WellData team for many years now and they have always delivered. I would highly recommend them to any business looking for best-value outsourced Expert DBA’s. Being able to offer a rolling contract on the basis that if they don’t deliver you can leave shows their confidence and commitment. Their professionalism and knowledge is second to none.read more
Petro Bartoszyk
Petro Bartoszyk
08:51 18 Oct 19