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
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.
More SQL Server scripts: Performance
- Top 20 statements by CPUCached statements ranked by total CPU since they entered the plan cache. Compare avg_cpu_ms with execution_count to separate one heavy query from a…