Sql-server – SOS_Scheduler_Yield wait type

performancesql server

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.

Best Answer

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.

Related Question