High signal waits (above 20%) are another indication of CPU pressure. The results from this query are cumulative since your SQL Server has been running.
— Total waits are wait_time_ms (high signal waits indicates CPU pressure)
,’%signal (cpu) waits’ = CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
,resource_wait_time_ms=SUM(wait_time_ms – signal_wait_time_ms)
,’%resource waits’= CAST(100.0 * SUM(wait_time_ms – signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
— Check SQL Server Schedulers to see if they are waiting on CPU
SELECT scheduler_id, current_tasks_count, runnable_tasks_count
WHERE scheduler_id < 255
If the runnable task count is above 10 for multiple schedulers, you are experiencing CPU pressure. This can be a much more reliable indicator than CPU Utilization or processor queue length.
Technorati Tag: SQL Server