Udostępnij za pośrednictwem

Should you worry about SOS_SCHEDULER_YIELD?

  • Artykuł

Today I witnessed a performance load test against a 16 proc 64-bit SQL Server instance. During the first run we saw a very high value for CXPACKET, followed up with SOS_SCHEDULER_YIELD. After disabling parallelism and clearing the wait stats, CXPACKET disappeared and was replaced with a significant percentage of SOS_SCHEDULER_YIELD (representing > 80% of the total accumulated wait time). The load test was driving all 16 CPUs quite high during the test, almost pegging them at 100%.

So here are two questions to ask in this situation:

· What does the wait type mean (in this case SOS_SCHEDULER_YIELD)?

· Is it actionable?

In answer to the first question, SOS_SCHEDULER_YIELD represents a SQLOS worker (thread) that has yielded the CPU, presumably to another worker. My favorite white paper, SQL Server 2005 Waits and Queues, defines SOS_SCHEDULER_YIELD as occurring “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 over-simplified explanation of a quantum is the amount of time a thread is scheduled to run. The thread that yielded the scheduler (CPU) is now waiting for its turn again to run on the processor. 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. Threads will voluntarily yield their time so that other tasks can execute. On a busy, highly concurrent SQL Server instance, this is a good thing.

Now is this wait type actionable? If this wait type represents a high percentage of overall wait time accumulated for a workload or important period of time, we can surmise that SOS_SCHEDULER_YIELD is a symptom of CPU pressure. 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).

Hope this helps,

Joe

Comments