Over the next few weeks we will be reviewing some of the common SQL Server Waits. We will explain what they mean and some basic things to resolve the waits.

This week we will review SOS_SCHEDULER_YEILD wait. This wait is associated to how SQL Server interacts with the server CPU. SQL Server dictates how threads are managed in the CPU. Each thread that is scheduled can have 3 different states:

Running: Thread is current running
Suspended: This state means the thread is waiting for resources
Runnable: Resources are available, but there is CPU contention.

When a thread exhausts its quantum (CPU time) we start seeing the SOS_SCHEDULER_YEILD wait type show up in reports. When I have a query moved into a runnable state, this is where I start seeing the waits.

Causes and Fixes for SOS_SCHEDULER_YIELD Wait:

So now that I have a basic understanding of the wait type, its time to review what could be some of the causes and fixes.

SOS_SCHEDULER_YIELD is a pretty common wait type that is seen. Often times the wait indicates some CPU pressure somewhere. This pressure can be queries using alot of CPU that need tuning or something similar.

I see a lot of systems with the MAXDOP setting set to 1. This setting may have fixed some CXPacket Waits (we will review CXPACKET soon) but it came at a cost in seeing an increase in the SCHEDULER wait. By reviewing this MAXDOP setting and making appropriate adjustments you can reduce the SOS_SCHEDULER_YIELD wait type in SQL Server.

To be honest, I am troubleshooting this wait only if there are issues reported. This wait could actually be an indication of a lot of threads being scheduled by the engine. With this example there is nothing to fix.

Other times you might actually have a problem if you have some CPU heavy queries that need tuning. Typically reviewing indexes, table scans, sorts are ways to reduce CPU usage for queries. Reducing the total CPU usage for queries will result in faster running queries and thus reducing the amount of SPIDS in the runnable queue.

The folks over at SQLShack.com have a well documented post on the wait and some examples of when the wait is seen. You can find the post here.

Written by:  Justin Figg a Sr. SQL Server DBA with over 10 years of IT experience with Windows OS, Windows Server and Microsoft SQL Server. Subscribe to receive notifications of newly posted blog entries.

Support VitaminDBA.com

The post Let’s talk SQL Server Waits: Topic 1 – SOS_SCHEDULER_YIELD appeared first on VitaminDBA.