denrepo

SQL ServerPerformance

Top wait types

Where the instance spends its time waiting since the last restart, with benign background waits filtered out. High signal wait share points at CPU pressure.

Not yet verified. How scripts are tested

b-mssql-waits.sql
1SELECT TOP (15) wait_type,2       wait_time_ms / 1000.0 AS wait_sec,3       (wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_wait_sec,4       signal_wait_time_ms / 1000.0 AS signal_wait_sec,5       waiting_tasks_count,6       CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER () AS DECIMAL(5,2)) AS pct7FROM sys.dm_os_wait_stats8WHERE wait_time_ms > 09  AND wait_type NOT IN (10    'SLEEP_TASK','BROKER_TASK_STOP','BROKER_TO_FLUSH','SQLTRACE_BUFFER_FLUSH',11    'CLR_AUTO_EVENT','CLR_MANUAL_EVENT','LAZYWRITER_SLEEP','CHECKPOINT_QUEUE',12    'WAITFOR','XE_TIMER_EVENT','XE_DISPATCHER_WAIT','FT_IFTS_SCHEDULER_IDLE_WAIT',13    'LOGMGR_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','SLEEP_SYSTEMTASK',14    'BROKER_EVENTHANDLER','DIRTY_PAGE_POLL','HADR_FILESTREAM_IOMGR_IOCOMPLETION',15    'SP_SERVER_DIAGNOSTICS_SLEEP','QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',16    'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP','SOS_WORK_DISPATCHER',17    'BROKER_RECEIVE_WAITFOR')18ORDER BY wait_time_ms DESC;

Paste it into your query tool.

Open in denrepo

More SQL Server scripts: Performance