Skip to main content

Calculates signal waits and resource waits as a percentage of the overall wait time, in order to diagnose potential CPU pressure.

/********************************************************************
    Filename:   CPU_Pressure.sql
    Author:     https://technet.microsoft.com/en-us/magazine/dn383732.aspx
    Date:       09/09/15
    Comments:   Calculates signal waits and resource waits as a
                percentage of the overall wait time, in order to
                diagnose potential CPU pressure.
********************************************************************/

set nocount on;
set transaction isolation level read uncommitted;

select '% Signal (CPU) Waits' = CAST(100.0 * SUM(signal_wait_time_ms) / SUM(wait_time_ms) as numeric(20, 2)),
    '% Resource Waits' = CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM(wait_time_ms) as numeric(20, 2))
from sys.dm_os_wait_stats;