Hi there,

In previous blog we came to know what is wait and what exactly is sos_scheduler_yield wait is, now let us start troubleshooting problems associated around it. The vital step in troubleshooting is identifying the culprit, there are multiple ways out there to troubleshoot but we will do with wait types. I always use the following script if I run in to performance issues, this script does a delta of waits for given time (here it is one minute) and will give us the output of the cumulative waits descending. With this we narrow to the problematic resources and thus can elevate the issue.

The output for the above on any busy server looks something like below and most of the times the above wait tops it and the reason is “there would be processes in runnable queue, waiting for processes to yield”

wait_type WaitDelta

But if there are processes continuously waiting with high wait times then that should be a matter of doubt,

In most cases the reason would be Non_yielding _scheduler where there would be a scheduler which is not yielding after its quantum.  Ideally, this condition would be addressed by SQL OS. A background thread is dedicated to check the health of schedulers and would result in dump if it is not yielding. We can discuss more on why non yielding scheduler occurs in another blog.

There would be another situation where there will be a single process which will wait with sos_scheduler_yield with less wait time but many number of times in its execution. This happens because that process requires more cpu cycles, then we may want to jump and see what is that operator that is taking huge CPU and address it. Below are some of the operators which could possibly cause high CPU.

  • Huge aggregate functions
  • Huge executions on a clustered index seek
  • Hash Join

Also, spinlocks, which is the synchronization mechanism to protect data structures can cause  high SOS waits. A process acquires spinlock while it is consuming CPU cycles and if it  doesn’t get spinlock it will backoff and sleeps on CPU, during backoff we see sos-scheduler_yield.  We will discuss more on how to tune spinlock contention in later blogs.

The above stated are few reasons that i have seen / learned where this particular wait can occur , please share your experiences and feedback.  

Happy Learning,
Aditya Badramraju