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.
