Do you suspect you are encountering CPU pressure? Some of the more common signs of CPU pressure include:
- Sustained “% Processor Time” values (>75% for extended periods)
- High signal wait time percentage from sys.dm_os_wait_stats
- SOS_SCHEDULER_YIELD waits
One way to measure if you have several tasks waiting for CPU time is to query the sys.dm_os_schedulers DMV. This DMV has several interesting columns – however one in particular is useful for measuring queued tasks – the “runnable_tasks_count” column. The “runnable_tasks_count” value indicates the count of workers that have tasks assigned to them that are just waiting for their share of time on the scheduler (logical CPU). I checked various busy OLTP hosting SQL Server instances, and even though these systems are quite active, the value for runnable_tasks_count was very low (almost always showing zero values).
Yesterday I did encounter a SQL Server instance where the value of runnable_tasks_count averaged about 13 across all schedulers with a status of “VISIBLE ONLINE”. The value of runnable_tasks_count should be as low as possible – and if it shows ongoing non-zero values, you have further evidence of CPU pressure.