What does high wait in SOS_SCHEDULER_YIELD mean?

Share this Post

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?

High Count
Low 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.
High Count
High Wait
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
High Wait
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.

Share this Post

2 thoughts on “What does high wait in SOS_SCHEDULER_YIELD mean?”

  1. I cannot get the table in your posting to render correctly in either IE8 or Firefox.  The fourth column is truncated – maybe half the text is missing.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.