Published 31 January 2017
This common wait isn’t necessarily a problem, and may indicate that SQL Server CPU scheduling is working efficiently. SQL Server assigns a scheduler to each CPU core to manage threads. Threads can be in one of three states:
- RUNNING - only one thread at a time can be actively running on each CPU.
- SUSPENDED - the thread is waiting on the waiter list until resource is available.
- RUNNABLE - resource is available but a thread is already running, so the thread is in the runnable queue.
Each thread is given a 4 millisecond duration (or quantum) to use CPU without interruptions. If every scheduler is running a thread, those that have exhausted their quantum will voluntarily yield to give the next thread in the runnable queue its CPU time. Yielded threads don’t go on the waiter list because they’re not waiting for resource. Instead, they go to the bottom of the runnable queue and wait for their quantum to be renewed.
If the majority of waits are of this type and your system is experiencing performance issues, it suggests your system is suffering from CPU pressure.
Investigating CPU pressure
- Check whether the Signal wait time is greater than a few milliseconds per wait event. This can suggest that CPU pressure is causing the problem.
- Check the Analysis page for consistently high values for these metrics:
- Machine: processor time
- Avg. CPU queue length
Also see whether SQL Server is responsible for sustained CPU usage, or is being starved by another process that needs investigating by checking:
- SQL Server: processor time
- Query the sys.dm_os_scheduler DMV and check the runnable_task_count. See Detect SQL Server CPU Pressure.
Fixing CPU pressure problems
- If the runnable_tasks_count is high, try reducing it by tuning queries responsible for sustained CPU usage. See Optimizing SQL Server Query Performance (MSDN).
- Check whether SQL Server is suffering from spinlock contention. See SOS_SCHEDULER_YIELD waits and the LOCK_HASH spinlock.
- Consider splitting the workload for the most active databases to a different SQL Server or host machine. See Move a database from one server to another (MSDN).
- As a last resort and if you’re sure it’ll fix the problem, consider adding CPUs.