10 September 2010

Detect CPU pressure

if you have a large number of workers in RUNNABLE state, it is symptom of CPU bottleneck. On the other hand, if your workers are spending most time in SUSPENDED state, it is indicative of excessive blocking in your SQL Server.

Detect CPU pressure
SELECT COUNT(*) AS workers_waiting_for_cpu, t2.Scheduler_id
FROM sys.dm_os_workers AS t1, sys.dm_os_schedulers AS t2
t1.scheduler_address = t2.scheduler_address AND
t2.scheduler_id < 255
GROUP BY t2.scheduler_id

Time spent by workers in RUNNABLE state:
SELECT SUM(signal_wait_time_ms)
FROM sys.dm_os_wait_stats