Match what you're seeing to a card — each one gives the wait types that confirm it and the one query to run first. No clicking around: this is the cheat sheet.
Looks like: everything is slow but still moving; no single blocker; runnable queues stack up.
-- signal % of waits: >25% = CPU-bound SELECT CAST(100.0*SUM(signal_wait_time_ms)/SUM(wait_time_ms) AS decimal(5,1)) AS signal_pct FROM sys.dm_os_wait_stats;
Looks like: queries queue before they even start; grants pending; errors 701 / 8645.
-- who's queued for memory, who holds the big grants
SELECT session_id, requested_memory_kb/1024 AS req_mb,
granted_memory_kb/1024 AS granted_mb, wait_time_ms
FROM sys.dm_exec_query_memory_grants
ORDER BY requested_memory_kb DESC;Looks like: reads/writes crawl; avg read >20 ms = storage conversation; <10 ms but waits high = buffer pool too small.
-- per-file latency: is it the disks? SELECT DB_NAME(vfs.database_id) AS db, mf.type_desc, vfs.io_stall_read_ms/NULLIF(vfs.num_of_reads,0) AS avg_read_ms, vfs.io_stall_write_ms/NULLIF(vfs.num_of_writes,0) AS avg_write_ms FROM sys.dm_io_virtual_file_stats(NULL,NULL) vfs JOIN sys.master_files mf ON vfs.database_id=mf.database_id AND vfs.file_id=mf.file_id ORDER BY avg_read_ms DESC;
Looks like: sessions pile up behind one spid; app "hangs"; CPU is quiet.
-- find the LEAD blocker (blocks others, blocked by nobody)
SELECT r.session_id, r.blocking_session_id, r.wait_type,
r.wait_time/1000 AS wait_s, t.text
FROM sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0 OR r.session_id IN
(SELECT blocking_session_id FROM sys.dm_exec_requests);Looks like: secondary lags; failover "not ready"; send/redo queues growing instead of draining.
-- send queue = data not yet shipped (RPO);
-- redo queue = shipped, not yet replayed (RTO)
SELECT ar.replica_server_name, drs.synchronization_state_desc,
drs.log_send_queue_size, drs.redo_queue_size
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON drs.replica_id=ar.replica_id;Looks like: many spids briefly waiting on 2:x:x pages; bursts during heavy temp-table churn.
-- live tempdb page waits right now SELECT session_id, wait_type, resource_description FROM sys.dm_os_waiting_tasks WHERE resource_description LIKE '2:%';
Looks like: app times out but the instance looks bored — SQL finished; the app isn't consuming rows.
-- sessions waiting on the app to fetch rows SELECT session_id, wait_type, wait_time, host_name, program_name FROM sys.dm_exec_requests r JOIN sys.dm_exec_sessions s ON r.session_id=s.session_id WHERE wait_type='ASYNC_NETWORK_IO';
Looks like: writes fail with 9002; on an AG the usual cause is a secondary holding the log.
-- WHY can't the log clear? SELECT name, log_reuse_wait_desc FROM sys.databases ORDER BY log_reuse_wait_desc;
Every tile is a wait type with its plain-English meaning underneath; the stripe is the honest read: green = normal background noise, amber = watch it if it dominates, red = act when you see it climbing. Click a tile for the first check and its queries.
Pick the pieces, get the exact command plus its verification query. REVOKE removes a grant; DENY actively blocks (and wins over everything) — use DENY sparingly and on purpose. The Permissions chip above lists every permission-related error with its fix.
-- pick options above
/* server-level grants + role membership for one login */
SELECT p.state_desc, p.permission_name, p.class_desc
FROM sys.server_permissions p
JOIN sys.server_principals sp ON p.grantee_principal_id = sp.principal_id
WHERE sp.name = N'<login>';
SELECT r.name AS server_role
FROM sys.server_role_members rm
JOIN sys.server_principals r ON rm.role_principal_id = r.principal_id
JOIN sys.server_principals m ON rm.member_principal_id = m.principal_id
WHERE m.name = N'<login>';
/* database-level (run in the db) */
SELECT p.state_desc, p.permission_name, p.class_desc,
OBJECT_NAME(p.major_id) AS on_object
FROM sys.database_permissions p
JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
WHERE dp.name = N'<user>';
SELECT r.name AS db_role
FROM sys.database_role_members rm
JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
WHERE m.name = N'<user>';
/* effective permissions - the truth after all grants/denies */
EXECUTE AS LOGIN = N'<login>';
SELECT * FROM fn_my_permissions(NULL,'SERVER');
SELECT * FROM fn_my_permissions(N'<schema.object>','OBJECT');
REVERT;/* every DENY - these silently break apps after a DR rebuild */ SELECT sp.name AS grantee, p.permission_name, p.class_desc FROM sys.server_permissions p JOIN sys.server_principals sp ON p.grantee_principal_id = sp.principal_id WHERE p.state = 'D'; /* who is sysadmin / CONTROL SERVER (should be a short list) */ SELECT m.name, m.type_desc FROM sys.server_role_members rm JOIN sys.server_principals r ON rm.role_principal_id = r.principal_id JOIN sys.server_principals m ON rm.member_principal_id = m.principal_id WHERE r.name = 'sysadmin' UNION SELECT sp.name, sp.type_desc FROM sys.server_permissions p JOIN sys.server_principals sp ON p.grantee_principal_id = sp.principal_id WHERE p.permission_name = 'CONTROL SERVER' AND p.state = 'G';
The first-responder pack. Run top to bottom: who's running, who's blocking whom, what everyone is waiting on right now, then the historical/IO context. All read-only.
Every live request with its wait, blocker, runtime, and the exact statement. Your sp_WhoIsActive stand-in.
SELECT r.session_id, r.status, r.command,
r.wait_type, r.wait_time AS wait_ms,
r.blocking_session_id AS blocked_by,
DB_NAME(r.database_id) AS db,
r.cpu_time, r.total_elapsed_time/1000 AS elapsed_s,
r.percent_complete,
s.login_name, s.host_name, s.program_name,
SUBSTRING(t.text, r.statement_start_offset/2 + 1,
(CASE WHEN r.statement_end_offset = -1
THEN DATALENGTH(t.text)
ELSE r.statement_end_offset END
- r.statement_start_offset)/2 + 1) AS running_sql
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.is_user_process = 1 AND r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;Kill/pursue the HEAD of the chain, not the victims. Lead blockers block others but wait on nothing.
SELECT r.blocking_session_id AS lead_blocker,
COUNT(*) AS sessions_blocked,
MAX(r.wait_time)/1000 AS longest_wait_s,
s.login_name, s.host_name, s.program_name,
s.status AS blocker_status,
t.text AS blocker_last_sql
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s
ON s.session_id = r.blocking_session_id
LEFT JOIN sys.dm_exec_connections c
ON c.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE r.blocking_session_id <> 0
AND r.blocking_session_id NOT IN
(SELECT session_id FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0)
GROUP BY r.blocking_session_id, s.login_name, s.host_name,
s.program_name, s.status, t.text
ORDER BY sessions_blocked DESC;
-- sleeping blocker + open tran = someone forgot to COMMIT:
-- check s.status='sleeping' and DBCC OPENTRAN in that dbWhat user tasks are waiting on at this second. Map the top wait types against the board above.
SELECT wt.wait_type,
COUNT(*) AS waiting_tasks,
SUM(wt.wait_duration_ms) AS total_ms,
MAX(wt.wait_duration_ms) AS max_ms,
MIN(wt.session_id) AS example_spid
FROM sys.dm_os_waiting_tasks wt
JOIN sys.dm_exec_sessions s ON wt.session_id = s.session_id
WHERE s.is_user_process = 1
GROUP BY wt.wait_type
ORDER BY total_ms DESC;The long-term wait profile with benign waits removed, ranked with percentages (Paul Randal-style filter).
;WITH w AS (
SELECT wait_type, wait_time_ms, signal_wait_time_ms,
waiting_tasks_count,
100.0 * wait_time_ms / NULLIF(SUM(wait_time_ms) OVER(),0) AS pct
FROM sys.dm_os_wait_stats
WHERE waiting_tasks_count > 0 AND wait_type NOT IN (
'BROKER_EVENTHANDLER','BROKER_RECEIVE_WAITFOR','BROKER_TASK_STOP',
'BROKER_TO_FLUSH','BROKER_TRANSMITTER','CHECKPOINT_QUEUE',
'CHKPT','CLR_AUTO_EVENT','CLR_MANUAL_EVENT','CLR_SEMAPHORE',
'DBMIRROR_DBM_EVENT','DBMIRROR_EVENTS_QUEUE','DBMIRROR_WORKER_QUEUE',
'DBMIRRORING_CMD','DIRTY_PAGE_POLL','DISPATCHER_QUEUE_SEMAPHORE',
'EXECSYNC','FSAGENT','FT_IFTS_SCHEDULER_IDLE_WAIT','FT_IFTSHC_MUTEX',
'HADR_CLUSAPI_CALL','HADR_FILESTREAM_IOMGR_IOCOMPLETION',
'HADR_LOGCAPTURE_WAIT','HADR_NOTIFICATION_DEQUEUE',
'HADR_TIMER_TASK','HADR_WORK_QUEUE','KSOURCE_WAKEUP',
'LAZYWRITER_SLEEP','LOGMGR_QUEUE','MEMORY_ALLOCATION_EXT',
'ONDEMAND_TASK_QUEUE','PARALLEL_REDO_DRAIN_WORKER',
'PARALLEL_REDO_LOG_CACHE','PARALLEL_REDO_TRAN_LIST',
'PARALLEL_REDO_WORKER_SYNC','PARALLEL_REDO_WORKER_WAIT_WORK',
'PREEMPTIVE_OS_FLUSHFILEBUFFERS','PREEMPTIVE_XE_GETTARGETSTATE',
'PWAIT_ALL_COMPONENTS_INITIALIZED','PWAIT_DIRECTLOGCONSUMER_GETNEXT',
'QDS_ASYNC_QUEUE','QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP','QDS_SHUTDOWN_QUEUE',
'REDO_THREAD_PENDING_WORK','REQUEST_FOR_DEADLOCK_SEARCH',
'RESOURCE_QUEUE','SERVER_IDLE_CHECK','SLEEP_BPOOL_FLUSH',
'SLEEP_DBSTARTUP','SLEEP_DCOMSTARTUP','SLEEP_MASTERDBREADY',
'SLEEP_MASTERMDREADY','SLEEP_MASTERUPGRADED','SLEEP_MSDBSTARTUP',
'SLEEP_SYSTEMTASK','SLEEP_TASK','SLEEP_TEMPDBSTARTUP',
'SNI_HTTP_ACCEPT','SOS_WORK_DISPATCHER','SP_SERVER_DIAGNOSTICS_SLEEP',
'SQLTRACE_BUFFER_FLUSH','SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'SQLTRACE_WAIT_ENTRIES','WAIT_FOR_RESULTS','WAITFOR',
'WAITFOR_TASKSHUTDOWN','WAIT_XTP_HOST_WAIT',
'WAIT_XTP_OFFLINE_CKPT_NEW_LOG','WAIT_XTP_CKPT_CLOSE',
'XE_DISPATCHER_JOIN','XE_DISPATCHER_WAIT','XE_TIMER_EVENT')
)
SELECT TOP 20 wait_type,
CAST(pct AS decimal(5,2)) AS pct_of_all,
wait_time_ms/1000 AS total_s,
waiting_tasks_count,
wait_time_ms/NULLIF(waiting_tasks_count,0) AS avg_ms_per_wait,
100.0*signal_wait_time_ms/NULLIF(wait_time_ms,0) AS signal_pct
FROM w
ORDER BY wait_time_ms DESC;
-- signal_pct high across the board = CPU pressure,
-- regardless of which wait type it shows up underAvg ms per read/write per file. Rule of thumb: data <10ms good, 10–20 watch, >20 storage conversation. Log writes want <5ms.
SELECT DB_NAME(vfs.database_id) AS db, mf.type_desc,
vfs.num_of_reads,
vfs.io_stall_read_ms/NULLIF(vfs.num_of_reads,0) AS avg_read_ms,
vfs.num_of_writes,
vfs.io_stall_write_ms/NULLIF(vfs.num_of_writes,0) AS avg_write_ms,
mf.physical_name
FROM sys.dm_io_virtual_file_stats(NULL,NULL) vfs
JOIN sys.master_files mf
ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
ORDER BY vfs.io_stall_read_ms + vfs.io_stall_write_ms DESC;The DR-exercise favorite — how long until that restore finishes.
SELECT r.session_id, r.command,
CAST(r.percent_complete AS decimal(5,2)) AS pct,
DATEADD(ms, r.estimated_completion_time, GETDATE()) AS eta,
r.total_elapsed_time/60000 AS mins_running,
DB_NAME(r.database_id) AS db
FROM sys.dm_exec_requests r
WHERE r.command LIKE '%BACKUP%' OR r.command LIKE '%RESTORE%'
OR r.command LIKE 'DBCC%'
ORDER BY r.percent_complete DESC;Version store bloat (long snapshots / readable-secondary load) and who's eating tempdb.
SELECT SUM(user_object_reserved_page_count)*8/1024 AS user_obj_mb,
SUM(internal_object_reserved_page_count)*8/1024 AS internal_mb,
SUM(version_store_reserved_page_count)*8/1024 AS version_store_mb,
SUM(unallocated_extent_page_count)*8/1024 AS free_mb
FROM tempdb.sys.dm_db_file_space_usage;
SELECT TOP 10 su.session_id, s.login_name, s.program_name,
(su.user_objects_alloc_page_count
+ su.internal_objects_alloc_page_count)*8/1024 AS alloc_mb
FROM sys.dm_db_session_space_usage su
JOIN sys.dm_exec_sessions s ON su.session_id = s.session_id
ORDER BY alloc_mb DESC;Reference tool for personal decision support. Wait-type risk ratings are honest defaults, not absolutes — a "watch" wait dominating a slow system is your signal, a "red" wait at trivial volume is not. Defer to your site's runbook where it differs. Companion page: SQL HADR Triage.