World Search

Friday, September 10, 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
WHERE t1.state = 'RUNNABLE' AND
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