09 April,2011 by Jack Vamvas
SQL Server Books online defines SOS_SCHEDULER_YIELD 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 “
What is happening when a SOS_SCHEDULER_YIELD is measured? The SQLOS worker has voluntarily yielded the CPU to another worker. This happens regularly and is part of the SQL Server CPU management system . At the point the thread yields it is SUSPENDED.
The point to become concerned is if the SOS_SCHEDULER_YIELD is a high percentage of overall wait time. This points to multiple concurrent requests on the CPU
A few issues to consider and approached to minimizing SOS_SCHEDULER_YIELD
1)A high percentage SOS_SCHEDULER_YIELD does not always mean CPU pressure. Use perfmon counters to cross-check.
2)Use the DMV – sys.dm_exec_query_stats by worker time
3)Check for high levels of recompilations.Look to Query Tuning
4)Consider redistributing database files onto other servers, to decrease CPU pressure
5)Assess response from sys.dm_os_schedulers . From BOL “Returns one row per scheduler in SQL Server where each scheduler is mapped to an individual processor. Use this view to monitor the condition of a scheduler or to identify runaway tasks”
SELECT scheduler_id, current_tasks_count, runnable_tasks_countFROM sys.dm_os_schedulersWHERE scheduler_id < 255