SOS_Scheduler_yield – Wait types of SQL Server

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.

Declare @Waits Table (
    WaitID int identity(1, 1) not null primary key,
    wait_type nvarchar(60),
    wait_time_s decimal(12, 2));    

WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
    100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
    ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN( 'SLEEP_TASK', 'BROKER_TASK_STOP',
  'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
  'LAZYWRITER_SLEEP')) -- filter out additional irrelevant waits
Insert Into @Waits (wait_type, wait_time_s)
SELECT W1.wait_type,
  CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold

WaitFor Delay '0:01:00';

WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
    100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
    ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN( 'SLEEP_TASK', 'BROKER_TASK_STOP',
  'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
  'LAZYWRITER_SLEEP')) -- filter out additional irrelevant waits
Insert Into @Waits (wait_type, wait_time_s)
SELECT W1.wait_type,
  CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold

Select wait_type, MAX(wait_time_s) - MIN(wait_time_s) WaitDelta
From @Waits
Group By wait_Type
Order By WaitDelta Desc

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”

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

select * from sys.dm_exec_requests where wait_type ='SOS_SCHEDULER_YIELD' 
order by wait_time desc

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