A clarification on the waitstat: SOS_SCHEDULER_YIELD

Table of Contents

Are you one of the people that says “we have a CPU related issue” whenever it sees SOS_SCHEDULER_YIELD popping up? Let me explain why you’re wrong.

In the books online , SOS_SCHEDULER_YIELD is defined simply as:

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.

Well, since the SQL Server scheduler is not preemptive, this means:

The thread executed for 4ms straight, didn’t have to wait for anything and it’s the end of its quantum; it’s releasing the CPU and going back to the runnable thread where it will wait for its quantum to be renewed and start running again.

In this description, nothing says CPU pressure ¯\_(ツ)_/¯

If you have a lot of this wait, you’ll have to search for queries that are running for a long time and aren’t waiting for resources to be available.

As an example, a scan of a big table with all the pages in memory will raise a lot of yielding events as it doesn’t have to wait on anything except going through the whole table page by page; A huge amount of SOS_SCHEDULER_YIELD will appear, is it normal? Absolutely. What is or isn’t ok is a table scan occurring, is the query supposed to act like that? There is a missing index? Investigate.

So, whenever SOS_SCHEDULER_YIELD comes up, don’t worry about the CPU, but start searching for queries that execute for a long time not due to resource contention, but due to the plan shape.

comments powered by Disqus

Related Posts

The SQL Diagnostic (jupyter) Book

Welcome to 2020! I wanted to start this year by giving to all my fellow consultants another way to troubleshoot our beloved SQL Servers; I’ve already talked about diagnostic notebooks in the past, and now, since Azure Data Studio has implemented the feature, I wanted to group them into a Diagnostic Book.

Read More

DeadLock Art

I love when it happens

Read More

How to update a SQL Server container on Docker for Windows (spoiler - there is a catch)

With the 1st CU for SQL 2019 released just yesterday, and Microsoft updating the docker image right away, the only natural response for me was to update the docker instance that I showed you how to deploy a few months back.

Read More