SOS_SCHEDULER_YIELD Wait class

According to BOL, SOS_Scheduler_Yield refers to the following:

  • 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.

The general consensus is that if you are seeing a lot of high SOS_SCHEDULER_YIELD waits, then there is a CPU bottleneck. In essence, the CPU(s) is having to switch tasks to accommodate requests. SOS_SCHEDULER_YIELD represents a SQLOS worker (thread) that has yielded the CPU, presumably to another worker.

If you are seeing lots of SOS_SCHEDULER_YIELD in your Wait States, that is a very stong indicator of CPU pressure.

You can run the DMV query to confirm that:

— Check SQL Server Schedulers to see if they are waiting on CPU
SELECT scheduler_id, current_tasks_count, runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255

If you see the runnable tasks count above zero, that is cause for concern, and if you see it in double digits for any length of time, that is cause for extreme concern.

If you see a high value for SOS_SCHEDULER_YIELD you should check perfmon counters to see if you also see sustained high values for % Processor Time and Processor Queue Length. You should also validate that it is indeed the SQL Server process that is causing the CPU pressure using the Process: % Processor Time counter, checking specifically for the SQL Server instance (or other processes responsible). You may also want to validate the number of workers in a runnable state in sys.dm_os_workers and sys.dm_os_schedulers, revealing if a significant number of tasks are waiting in line for their chance to run on a scheduler.

If you determine that SQL Server is responsible for the sustained CPU usage, then you should follow the standard CPU pressure troubleshooting path ( for example – query sys.dm_exec_query_stats ordering by total_worker_time, check for high compilations and recompilations, optimization time spent with sys.dm_exec_query_optimizer_info, and more).

SOS_SCHEDULER_YIELD is not necessarily a bad thing..on a busy, highly concurrent SQL Server instance, this is a good thing, as threads will voluntarily yield their time so that other tasks can execute.

SQL Server runs cooperatively (non-preemptive mode) for many activities, which means that SQL Server decides when a thread will yield – not the Operating System. With thread yielding being voluntary – if the thread was greedy, we could see a risk of the thread running until it was complete – however that is not the case. The SQLOS was designed in such a way that active threads should not starve out other runnable threads.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: