What does high wait in SOS_SCHEDULER_YIELD mean?
Microsoft SQL Server Escalation Services
I was teaching a class this weekend at http://www.sqlsaturday.com/60/eventhome.aspx on using Change Data Capture to pull changed data from SQL Server. I complete my class so I headed over to Thomas LaRock http://thomaslarock.com/ class on WAITS AND QUEUES. It was a great class and I always enjoy listening to Thomas speak. He has a great natural way of engaging his audience.
This question came up during the discussion. Does high wait in SOS_SCHEDULER_YIELD as shown in sys.dm_os_wait_stats indicate an underlying CPU performance problem? Answer: it depends if high count or high wait?
Okay Lot of threads working, waiting in short burst to check on other threads, no one thread dominating CPUs. Look else ware for performance problems.
Worth Investigating Lot of CPU intense queries completing for CPU resource. High Wait occurs when one thread yielded, but then took a while for Scheduler to get back to that thread Look at TSQL and/or execution plans for unexpected CPU calculations such as intense data conversion or user defined functions. Low Count
Worth Investigating High wait again indicates thread yielded, but took a long time to get CPU time back. The low count indicates not too many threads waiting. This usually indicates system not CPU bound. More often indicates there is a non-yielding or preemptive thread hogging the CPU. An example may be a filter driver (e.g. Antivirus), or IO driver not releasing control back to the executing SQL thread.
To keep a single executing thread from taking 100% CPU, our code automatically relinquishes control back to the Scheduler. The Scheduler looks to see if there is another thread/query ready to run (runnable). If yes, it will start that thread/query, run it for a short while, then switch back. If no one else is ready to run, we switch back to the first thread. While the first thread is WAITING for the Scheduler to check for other runnable threads and while those threads are running, we clock SOS_SCHEDULER_YIELD wait.
Thanks to Bob Ward for details on waits.