DESCRIPTION
If Signal Waits Percent metric is delivering false positives, this may be a better alternative to use as a gauge for CPU pressure over time.
METRIC DEFINITION
To create the custom metric, click on Options > Custom Resource Metrics and configure the metric similar to this:
Database Type: SQL Server
Database Versions: 9.0 and above
Display Name: Signal Wait Time
Description: <add your own description>
Category: CPU
Units: ms
Metric Type: Delta
Frequency: 60
Timeout: 20
SQL Statement:
SELECT SUM(signal_wait_time_ms) AS [SignalWaitTime]
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE',
'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES',
'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK', 'SP_SERVER_DIAGNOSTICS_SLEEP',
'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'DIRTY_PAGE_POLL',
'HADR_FILESTREAM_IOMGR_IOCOMPLETION', 'XE_LIVE_TARGET_TVF')
AND wait_time_ms <> 0
OPTION (RECOMPILE)
