SOS_Scheduler_Yield wait type

Question

I have a process that has a last_wait_type as SOS_Scheduler_Yield. It is inserting 100 records to a table. This process has been with this wait type forever with low CPU utilization %.

There are other processes executing the same query simultaneously without this wait type and executing in minutes. What could be the reason for this process hanging? It has been in runnable state with Last_wait_type as SOS_Scheduler_Yield for that long.

Answer 1

The simple occurrence of SOS_Scheduler_Yield don't mean very much. This can have some other causes.

Generally you can take a look at this DMV to clarify some things.

SELECT * 
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'SOS_SCHEDULER_YIELD'

Interpretation of dm_os_wait_stats:

• High counter values, low time means everything ok. This mainly just means that there are many queries which run in parallel and need to be scheduled to different threads.
• High counter values, high time can cause some problems. Beware you many intensive queries which may take long time to run (e.g. Aggregation on big tables).
• Low counter values, high time can indicate some intensive queries which take very long to complete. Beware this may be a CPU bottle neck which may be through bigger data loads or poor design (e.g. user defined functions).

Another good resource to look at may be the schedulers itself:

SELECT *
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
ORDER BY scheduler_id

It will provide you the status of each CPU. Basically description of this statement:

• parent_node_id represents the NUMA node of the core.
• scheduler_id and cpu_id are basically in many cases the same. They represent the CPU number.
• status will represent if the CPU is used for processing steps in the SQL Server instance (affinity_mask)
• is_idle represents the state of the cpu. 1 = it does nothing/not much and 0 = on load!
• *_switches_count represent the value of the switches between some states. High context_switches_count tends to show that the CPU cycles between different operations while idle_switches_count indicates if the CPU goes often idle and back to work or not. Generally low idle_switches_count indicates that your system is under permanent pressure.
• current_tasks_count shows how many tasks were assigned to this cpu.
• runnable_tasks_count shows how many tasks really run on the corresponding cpu.
• load_factor shows the pressure on this single core.

Hopefully this will clarify things for you.

Answer 2

I recently wrote the article about this and maybe someone can find it useful.

How to handle excessive SOS_SCHEDULER_YIELD wait type values in SQL Server

I tried to provide not only multiple aspects of troubleshooting SOS_SCHEDULER_YIELD wait type but also to give some details on SQL OS Scheduler functionality, as it is crucial for understanding SOS_SCHEDULER_YIELD waits

Answer 3

Without having high CPU utilization you can probably just ignore it but watch it when utilization goes high. This most likely means that 'your CPU is under sustained pressure and threads are being scheduled as normal'. SQL OS manages threads in the MS SQL Server app, not Windows OS. SQL OS uses a method where threads are released by the process on purpose so that a single thread doesn't get too greedy and start starving other actions. Your scheduler is a representative of that process.

Here is some more back ground:

http://sqlperformance.com/2014/02/sql-performance/knee-jerk-waits-sos-scheduler-yield

Link