Wait Statistics

-- Step 1: Snapshot before the workload window
SELECT wait_type,
       waiting_tasks_count,
       wait_time_ms,
       signal_wait_time_ms,
       GETDATE() AS snapshot_time
INTO #WaitBaseline
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'SLEEP_TASK','SLEEP_SYSTEMTASK','LAZYWRITER_SLEEP',
    'SQLTRACE_BUFFER_FLUSH','CHECKPOINT_QUEUE',
    'BROKER_TO_FLUSH','BROKER_TASK_STOP',
    'WAITFOR','DISPATCHER_QUEUE_SEMAPHORE',
    'XE_DISPATCHER_WAIT','XE_TIMER_EVENT','CXCONSUMER'
);

-- ... wait for the representative window ...

-- Step 2: Delta — shows what consumed wait time during the window
SELECT
    c.wait_type,
    c.waiting_tasks_count - b.waiting_tasks_count       AS delta_tasks,
    c.wait_time_ms - b.wait_time_ms                     AS delta_wait_ms,
    c.signal_wait_time_ms - b.signal_wait_time_ms       AS delta_signal_ms,
    CAST(100.0 * (c.wait_time_ms - b.wait_time_ms)
        / NULLIF(SUM(c.wait_time_ms - b.wait_time_ms) OVER (), 0)
        AS DECIMAL(5,2))                                 AS pct_of_total
FROM sys.dm_os_wait_stats c
JOIN #WaitBaseline b ON b.wait_type = c.wait_type
WHERE c.wait_time_ms > b.wait_time_ms
ORDER BY delta_wait_ms DESC;

DROP TABLE #WaitBaseline;

WHERE wait_type NOT IN (
    'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SLEEP_DBSTARTUP',
    'SLEEP_DCOMSTARTUP', 'SLEEP_MASTERMDREADY', 'SLEEP_MASTERUPGRADED',
    'SLEEP_MSDBSTARTUP', 'SLEEP_TEMPDBSTARTUP', 'SLEEP_MASTERSTART',
    'WAITFOR',
    'LAZYWRITER_SLEEP',
    'SQLTRACE_BUFFER_FLUSH', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
    'CHECKPOINT_QUEUE',
    'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'BROKER_EVENTHANDLER',
    'DISPATCHER_QUEUE_SEMAPHORE',
    'FT_IFTS_SCHEDULER_IDLE_WAIT',
    'XE_DISPATCHER_WAIT', 'XE_TIMER_EVENT',
    'CXCONSUMER',                    -- always pair with CXPACKET
    'SP_SERVER_DIAGNOSTICS_SLEEP',
    'RESOURCE_QUEUE', 'SERVER_IDLE_CHECK',
    'REQUEST_FOR_DEADLOCK_SEARCH',
    'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE', 'KSOURCE_WAKEUP',
    'SQLTRACE_WAIT_ENTRIES',
    'DBMIRROR_EVENTS_QUEUE'
)
AND wait_type NOT LIKE 'SLEEP_%'
AND wait_type NOT LIKE 'HADR_%'     -- remove if diagnosing AG issues

SELECT r.session_id,
       r.wait_type,
       r.wait_time / 1000.0      AS wait_sec,
       r.wait_resource,
       SUBSTRING(st.text, (r.statement_start_offset/2)+1, 200) AS stmt
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.wait_type IS NOT NULL
  AND r.session_id > 50   -- exclude system sessions
ORDER BY r.wait_time DESC;

-- Check current tempdb file sizes (all should match)
SELECT name, type_desc,
       size * 8 / 1024      AS size_mb,
       growth * 8 / 1024    AS growth_mb,
       is_percent_growth
FROM tempdb.sys.database_files
ORDER BY type_desc, file_id;

-- Equalize sizes (run for each file that is smaller)
ALTER DATABASE tempdb
MODIFY FILE (NAME = 'tempdev2', SIZE = 4096 MB, FILEGROWTH = 512 MB);

-- Check current setting
SELECT name, value_in_use FROM sys.configurations
WHERE name IN ('max server memory (MB)', 'min server memory (MB)');

-- Set appropriately (example: 28 GB on a 32 GB server)
EXEC sp_configure 'max server memory (MB)', 28672;
RECONFIGURE;

-- Wait breakdown for a specific plan (requires WAIT_STATS_CAPTURE_MODE = ON)
SELECT qsws.wait_category_desc,
       SUM(qsws.total_query_wait_time_ms)  AS total_wait_ms,
       AVG(qsws.avg_query_wait_time_ms)    AS avg_wait_ms
FROM sys.query_store_wait_stats           qsws
JOIN sys.query_store_runtime_stats_interval qsrsi
    ON qsrsi.runtime_stats_interval_id = qsws.runtime_stats_interval_id
WHERE qsws.plan_id = @plan_id
  AND qsrsi.start_time >= DATEADD(HOUR, -24, GETUTCDATE())
GROUP BY qsws.wait_category_desc
ORDER BY total_wait_ms DESC;

-- Top 10 queries by lock wait time (last 24 hours)
SELECT TOP 10
    qsqt.query_sql_text,
    SUM(qsws.total_query_wait_time_ms) AS lock_wait_ms
FROM sys.query_store_query_text          qsqt
JOIN sys.query_store_query               qsq  ON qsq.query_text_id          = qsqt.query_text_id
JOIN sys.query_store_plan                qsp  ON qsp.query_id               = qsq.query_id
JOIN sys.query_store_wait_stats          qsws ON qsws.plan_id               = qsp.plan_id
JOIN sys.query_store_runtime_stats_interval qsrsi
     ON qsrsi.runtime_stats_interval_id = qsws.runtime_stats_interval_id
WHERE qsws.wait_category_desc = 'Lock'
  AND qsrsi.start_time >= DATEADD(HOUR, -24, GETUTCDATE())
GROUP BY qsqt.query_sql_text
ORDER BY lock_wait_ms DESC;