13 - Wait Stats Analysis

19 min read

Wait Stats Analysis

System Health Dashboard — First Check

Before diving into wait families, start with one health row that establishes the confidence boundary for every cumulative metric that follows.

Quick health snapshot — sys.dm_os_sys_info and sys.dm_os_performance_counters

WITH bchr AS (
    SELECT
        SUM(CASE WHEN counter_name = 'Buffer cache hit ratio' THEN cntr_value END) AS ratio_value,
        SUM(CASE WHEN counter_name = 'Buffer cache hit ratio base' THEN cntr_value END) AS base_value
    FROM sys.dm_os_performance_counters
    WHERE object_name LIKE '%Buffer Manager%'
      AND counter_name IN ('Buffer cache hit ratio', 'Buffer cache hit ratio base')
)
SELECT
    cpu_count AS logical_cpus,
    physical_memory_kb / 1024 AS physical_memory_mb,
    committed_kb / 1024 AS committed_memory_mb,
    DATEDIFF(MINUTE, sqlserver_start_time, GETDATE()) AS uptime_minutes,
    (SELECT cntr_value
     FROM sys.dm_os_performance_counters
     WHERE counter_name = 'Page life expectancy'
       AND object_name LIKE '%Buffer Manager%') AS page_life_expectancy_sec,
    CAST((
        SELECT CASE
            WHEN base_value > 0 THEN ratio_value * 100.0 / base_value
        END
        FROM bchr
    ) AS decimal(9,2)) AS buffer_cache_hit_ratio_pct,
    (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1) AS user_sessions,
    (SELECT COUNT(*) FROM sys.dm_exec_requests WHERE session_id > 50) AS active_requests
FROM sys.dm_os_sys_info;
logical_cpusphysical_memory_mbcommitted_memory_mbuptime_minutespage_life_expectancy_secbuffer_cache_hit_ratio_pctuser_sessionsactive_requests
1624732540436922165100.00312

The instance is not showing broad memory distress right now. PLE = 22165 and buffer_cache_hit_ratio_pct = 100.00 are both strong. The important caveat is uptime_minutes = 369: all cumulative waits, file latencies, and cached-query rankings are only a few hours old, so they are useful for current triage but not yet a full-cycle baseline.

ColumnValueWatchMeaningImplication
uptime_minutesLowDependsCumulative history is young.Use waits and file stats as incident-window evidence, not long-term trend proof.
page_life_expectancy_secVery high✅Pages are staying in memory a long time.No strong current signal of broad buffer-pool churn.
buffer_cache_hit_ratio_pctNear 100✅Logical reads are mostly satisfied from cache.Disk misses are not the first explanation to assume.
active_requestsNon-zeroDependsThere is live activity while you troubleshoot.Good time to correlate waits with current requests and Query Store.

Top Waits Query — The Primary Diagnostic

sys.dm_os_wait_stats is still the primary instance-level bottleneck surface. The key production skill is not memorizing every wait type. It is separating actionable waits from background engine housekeeping, then grouping the actionable waits into resource families.

sys.dm_os_wait_stats — top waits query with benign exclusions

WITH waits AS (
    SELECT
        wait_type,
        wait_time_ms / 1000.0 AS wait_sec,
        (wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_wait_sec,
        signal_wait_time_ms / 1000.0 AS signal_wait_sec,
        waiting_tasks_count
    FROM sys.dm_os_wait_stats
    WHERE 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','PWAIT_EXTENSIBILITY_CLEANUP_TASK',
        '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_RECOVERY','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 (10)
    wait_type,
    wait_sec,
    resource_wait_sec,
    signal_wait_sec,
    waiting_tasks_count
FROM waits
WHERE wait_sec > 0
ORDER BY wait_sec DESC;
wait_typewait_secresource_wait_secsignal_wait_secwaiting_tasks_count
LCK_M_IX92.04800092.0480000.0000003
CXPACKET90.31000083.4830006.827000165388
LCK_M_SCH_S89.42500089.4250000.0000002
CXSYNC_PORT59.24300059.0950000.1480001366
LCK_M_U56.66200056.6620000.00000012
LATCH_EX32.87800030.7310002.14700051133
LCK_M_X22.32400022.3210000.003000157
RESERVED_MEMORY_ALLOCATION_EXT20.28800020.2880000.0000001534067
CXCONSUMER8.7850008.5410000.2440002939
PREEMPTIVE_OS_AUTHENTICATIONOPS5.7080005.7080000.0000005512

The current wait picture is not storage-led. The strongest actionable families are locking (LCK_M_*) and parallelism (CXPACKET, CXSYNC_PORT, CXCONSUMER). That means the next moves are blocking analysis and plan-review work, not disk expansion or blanket memory tuning.

Wait familyWatchWhat it usually meansFirst next step
LCK_M_*❌Sessions are blocked by other sessions.Inspect blockers and transaction scope.
CXPACKET, CXCONSUMER, CXSYNC_PORTDependsParallel plan coordination or skew.Check plan shape, MAXDOP, and cost threshold.
PAGEIOLATCH_*, WRITELOG❌ when dominantData-file or log-file I/O latency.Correlate with file-latency DMV output.
SOS_* with high signal waits❌CPU scheduler pressure.Validate CPU saturation and expensive queries.

signal_wait_ms vs resource_wait_ms — CPU pressure indicator

WITH waits AS (
    SELECT
        wait_time_ms,
        signal_wait_time_ms
    FROM sys.dm_os_wait_stats
    WHERE 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','PWAIT_EXTENSIBILITY_CLEANUP_TASK',
        '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_RECOVERY','WAIT_XTP_HOST_WAIT',
        'WAIT_XTP_OFFLINE_CKPT_NEW_LOG','WAIT_XTP_CKPT_CLOSE',
        'XE_DISPATCHER_JOIN','XE_DISPATCHER_WAIT','XE_TIMER_EVENT'
    )
)
SELECT
    CAST(SUM(signal_wait_time_ms) * 100.0 / NULLIF(SUM(wait_time_ms), 0) AS decimal(9,2)) AS signal_wait_pct,
    CAST((SUM(wait_time_ms) - SUM(signal_wait_time_ms)) * 100.0 / NULLIF(SUM(wait_time_ms), 0) AS decimal(9,2)) AS resource_wait_pct
FROM waits;
signal_wait_pctresource_wait_pct
2.2097.80

Only 2.20% of the current actionable wait profile is signal wait time. That means scheduler pressure is not the dominant story. The bottleneck is mostly in the resource families themselves, which matches the current lock-heavy wait list.

ColumnValueWatchMeaningImplication
signal_wait_pctBelow 10✅CPU scheduler delay is a small part of total actionable waits.Focus on the underlying wait families first.
signal_wait_pctAbove 10-15❌Sessions are ready to run but cannot get CPU time fast enough.Investigate CPU saturation and runnable pressure.
resource_wait_pctDominantDependsMost time is spent waiting on external resources or locks.Use wait-family meaning to choose the next diagnostic surface.

Wait Type Interpretation for Pipeline Workloads

Wait type or familyUsual meaningFirst question to askFirst linked note
LCK_M_*Blocking or lock serializationWho is the blocker and how long is the transaction open?
PAGEIOLATCH_*Data page had to be read from diskIs this storage latency or a cache-coverage problem?
WRITELOGCommit or log flush latencyIs the log file slow or is the workload committing too often?
CXPACKET, CXSYNC_PORT, CXCONSUMERParallel plan coordination and skewIs the plan going parallel for good reason, and is work balanced?
RESOURCE_SEMAPHORE, RESERVED_MEMORY_ALLOCATION_EXTMemory grant pressure or reservation pressureAre queries asking for large grants, or is memory capped badly?
PAGELATCH_* in TempDB contextsAllocation or metadata contentionAre TempDB files balanced, and is concurrency too allocation-heavy?
ASYNC_NETWORK_IOClient is consuming rows slowlyIs the consumer fetching too much or too slowly?

Top Resource-Consuming Queries

Wait families tell you which resource is hurting. The next step is to find which persisted query patterns are likely contributing to that pain.

On a short-uptime or admin-heavy instance, plan-cache top-N lists are often noisy. Query Store is a more stable surface because it persists runtime rows across cache eviction.

Persisted heavy business queries from Query Store

SELECT TOP (10)
    LEFT(REPLACE(REPLACE(qt.query_sql_text, CHAR(13), ' '), CHAR(10), ' '), 160) AS query_text,
    CAST(
        SUM(rs.avg_duration * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0)
        / 1000.0 AS decimal(18,3)
    ) AS weighted_avg_duration_ms,
    CAST(
        SUM(rs.avg_logical_io_reads * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0) AS decimal(18,2)
    ) AS weighted_avg_logical_reads,
    SUM(rs.count_executions) AS executions
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
  ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
  ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
  ON p.plan_id = rs.plan_id
WHERE (qt.query_sql_text LIKE '%silver.%' OR qt.query_sql_text LIKE '%gold.%')
  AND qt.query_sql_text NOT LIKE '%demo[_]%'
  AND qt.query_sql_text NOT LIKE '%sys.query_store_%'
GROUP BY qt.query_sql_text
ORDER BY weighted_avg_duration_ms DESC;
query_textweighted_avg_duration_msweighted_avg_logical_readsexecutions
SELECT * FROM silver.eurostoxx50_ohlcv ORDER BY symbol, date142.750797.001
WITH ranked AS ( SELECT symbol, date, [close], ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY date DESC)141.990631.605
SELECT TOP 15 d.symbol, d.short_name, t.date, t.volume, t.[close] FROM silver.index_dim d CROSS APPLY ( SELECT TOP 3 date, volume, [close] FROM132.265155290.001
SELECT * FROM silver.stoxxusa50_ohlcv ORDER BY symbol, date119.266764.001
SELECT symbol, date, [close], LAG([close]) OVER (PARTITION BY symbol ORDER BY date) AS prev FROM silver.eurostoxx50_ohlcv110.349836.673
SELECT symbol, date, [close], LAG([close]) OVER (PARTITION BY symbol ORDER BY date) FROM silver.eurostoxx50_ohlcv102.517858.002
WITH ranked AS ( SELECT symbol, date, [close], ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY date DESC)80.8012079.005
SELECT symbol, date, [open], high, low, [close], volume FROM silver.eurostoxx50_ohlcv71.514761.005
WITH ranked AS ( SELECT symbol, date, [close], ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY date DESC)63.1972021.605
WITH ranked AS ( SELECT symbol, date, [close], ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY date DESC)61.8282022.005

The current persisted heavy-query surface is dominated by wide ordered scans and window-function patterns over the OHLCV tables. One cross-apply pattern against silver.index_dim stands out for logical reads: 155290.00 reads per execution is a real plan-review candidate even though it executed only once.

ColumnValueWatchMeaningImplication
weighted_avg_duration_msHighDependsAverage elapsed time for the persisted pattern.Prioritize when combined with meaningful execution count or user impact.
weighted_avg_logical_readsVery high❌Query is touching many buffer-pool pages.Good candidate for plan review, indexing, or shape reduction.
executionsLowDependsQuery ran only a few times in the retained window.High cost may still matter for batch jobs, but not all low-frequency statements need indexing.

Database File I/O Latency

If waits suggest storage pressure, the next step is to verify file-level latency directly instead of assuming the disk layer is guilty.

sys.dm_io_virtual_file_stats — per-database file I/O query

SELECT TOP (10)
    DB_NAME(fs.database_id) AS database_name,
    f.name AS file_name,
    f.type_desc,
    f.physical_name,
    fs.num_of_reads,
    fs.num_of_writes,
    CAST(fs.io_stall_read_ms / NULLIF(fs.num_of_reads, 0) AS decimal(18,2)) AS avg_read_latency_ms,
    CAST(fs.io_stall_write_ms / NULLIF(fs.num_of_writes, 0) AS decimal(18,2)) AS avg_write_latency_ms,
    CAST(fs.num_of_bytes_read / 1024.0 / 1024 AS decimal(18,2)) AS total_read_mb,
    CAST(fs.num_of_bytes_written / 1024.0 / 1024 AS decimal(18,2)) AS total_write_mb
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
JOIN sys.master_files AS f
  ON fs.database_id = f.database_id
 AND fs.file_id = f.file_id
ORDER BY (fs.io_stall_read_ms + fs.io_stall_write_ms) DESC;
database_namefile_nametype_descphysical_namenum_of_readsnum_of_writesavg_read_latency_msavg_write_latency_mstotal_read_mbtotal_write_mb
tempdbtempdev2ROWS/var/opt/mssql/data/tempdb2.ndf84293581.003.0051.73584.41
tempdbtempdev7ROWS/var/opt/mssql/data/tempdb7.ndf79883951.003.0049.05524.16
tempdbtempdev4ROWS/var/opt/mssql/data/tempdb4.ndf84183821.002.0051.63523.36
tempdbtempdev8ROWS/var/opt/mssql/data/tempdb8.ndf84392231.002.0051.85575.88
tempdbtempdevROWS/var/opt/mssql/data/tempdb.mdf82992800.002.0051.37578.95
tempdbtempdev3ROWS/var/opt/mssql/data/tempdb3.ndf103299960.001.0063.78624.16
stoxxstoxx_logLOG/var/opt/mssql/data/stoxx_log.ldf1722975740.000.00271.525023.94
tempdbtempdev6ROWS/var/opt/mssql/data/tempdb6.ndf80983931.002.0049.45524.03
tempdbtempdev5ROWS/var/opt/mssql/data/tempdb5.ndf81283971.001.0049.92524.29
stoxxstoxxROWS/var/opt/mssql/data/stoxx.mdf169593870.001.0029.382985.34

The current file-latency surface does not support a storage-blame hypothesis. The stoxx data and log files are both sub-millisecond, and even the busiest TempDB files are still in the low single-digit millisecond range. If users complain of slowness right now, the stronger explanation is concurrency or query shape, not raw disk latency.

ColumnValueWatchMeaningImplication
avg_read_latency_ms < 5Fast reads✅Read path is healthy for general SSD-backed workloads.Storage is unlikely to be the first bottleneck.
avg_write_latency_ms < 2 on logFast commits✅Log flushes are fast.WRITELOG would be less likely to dominate.
avg_write_latency_ms > 5 on log❌Slow commit path.Investigate log placement, flush rate, and storage health.

TempDB Contention Detection

TempDB waits are not only about raw disk speed. They are also about whether concurrency is piling onto the same allocation paths.

TempDB per-file I/O distribution — check for uneven load

SELECT
    f.name AS file_name,
    f.physical_name,
    fs.num_of_reads,
    fs.num_of_writes,
    CAST(fs.num_of_bytes_read / 1024.0 / 1024 AS decimal(18,2)) AS read_mb,
    CAST(fs.num_of_bytes_written / 1024.0 / 1024 AS decimal(18,2)) AS write_mb,
    CAST(fs.io_stall_read_ms / NULLIF(fs.num_of_reads, 0) AS decimal(18,2)) AS avg_read_stall_ms,
    CAST(fs.io_stall_write_ms / NULLIF(fs.num_of_writes, 0) AS decimal(18,2)) AS avg_write_stall_ms
FROM sys.dm_io_virtual_file_stats(2, NULL) AS fs
JOIN tempdb.sys.database_files AS f
  ON fs.file_id = f.file_id
ORDER BY fs.num_of_writes DESC;
file_namephysical_namenum_of_readsnum_of_writesread_mbwrite_mbavg_read_stall_msavg_write_stall_ms
tempdev3/var/opt/mssql/data/tempdb3.ndf1032999663.78624.160.001.00
tempdev2/var/opt/mssql/data/tempdb2.ndf842935851.73584.411.003.00
tempdev/var/opt/mssql/data/tempdb.mdf829928051.37578.950.002.00
tempdev8/var/opt/mssql/data/tempdb8.ndf843922351.85575.881.002.00
tempdev5/var/opt/mssql/data/tempdb5.ndf812839749.92524.291.001.00
tempdev7/var/opt/mssql/data/tempdb7.ndf798839549.05524.161.003.00
tempdev6/var/opt/mssql/data/tempdb6.ndf809839349.45524.031.002.00
tempdev4/var/opt/mssql/data/tempdb4.ndf841838251.63523.361.002.00
templog/var/opt/mssql/data/templog.ldf1111070.9860.660.001.00

TempDB activity is reasonably balanced across the eight data files. tempdev3 is somewhat busier than the others, but the write distribution is still close enough that there is no obvious single-file hotspot. Combined with the low file-latency numbers, this is not a current TempDB emergency signal.

ColumnValueWatchMeaningImplication
num_of_writesSimilar across data files✅Allocation and spill activity is spreading across files.TempDB file layout is behaving reasonably.
num_of_writesOne file far above the rest❌Possible skew or unequal file sizing.Review TempDB file sizes and allocation pattern.
avg_write_stall_msLow single digits✅TempDB write latency is healthy.TempDB wait complaints are more likely concurrency-related than storage-related.

Query Store — Regression Detection

Wait families tell you what hurts. Query Store helps answer whether a changed plan is part of why it started hurting.

Query Store regression candidates

WITH recent_plans AS (
    SELECT
        q.query_id,
        qt.query_sql_text,
        p.plan_id,
        CAST(
            SUM(rs.avg_duration * rs.count_executions)
            / NULLIF(SUM(rs.count_executions), 0)
            / 1000.0 AS decimal(18,3)
        ) AS weighted_avg_duration_ms
    FROM sys.query_store_query_text AS qt
    JOIN sys.query_store_query AS q
      ON qt.query_text_id = q.query_text_id
    JOIN sys.query_store_plan AS p
      ON q.query_id = p.query_id
    JOIN sys.query_store_runtime_stats AS rs
      ON p.plan_id = rs.plan_id
    WHERE qt.query_sql_text NOT LIKE '%demo[_]%'
      AND qt.query_sql_text NOT LIKE '%qs_force_demo%'
    GROUP BY q.query_id, qt.query_sql_text, p.plan_id
),
multi_plan AS (
    SELECT
        query_id,
        MIN(weighted_avg_duration_ms) AS best_avg_ms,
        MAX(weighted_avg_duration_ms) AS worst_avg_ms,
        COUNT(*) AS plan_count
    FROM recent_plans
    GROUP BY query_id
    HAVING COUNT(*) > 1
)
SELECT TOP (10)
    m.query_id,
    m.plan_count,
    m.best_avg_ms,
    m.worst_avg_ms,
    CAST(m.worst_avg_ms / NULLIF(m.best_avg_ms, 0) AS decimal(18,2)) AS regression_factor,
    LEFT(REPLACE(REPLACE(MIN(r.query_sql_text), CHAR(13), ' '), CHAR(10), ' '), 140) AS sample_query_text
FROM multi_plan AS m
JOIN recent_plans AS r
  ON m.query_id = r.query_id
GROUP BY m.query_id, m.plan_count, m.best_avg_ms, m.worst_avg_ms
ORDER BY regression_factor DESC, m.worst_avg_ms DESC;
query_idplan_countbest_avg_msworst_avg_msregression_factorsample_query_text
24920.3263.48410.69DELETE b FROM bronze.stoxxusa50_ohlcv b INNER JOIN ( SELECT symbol, MAX(date) AS max_date FROM bronze.stoxxu
23520.4433.1757.17DELETE b FROM bronze.stoxxasia50_ohlcv b INNER JOIN ( SELECT symbol, MAX(date) AS max_date FROM bronze.stoxx
22820.4483.1246.97DELETE b FROM bronze.eurostoxx50_ohlcv b INNER JOIN ( SELECT symbol, MAX(date) AS max_date FROM bronze.euros
276620.2650.7632.88(@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000))SELECT clmns.name AS
188157.29513.6411.87UPDATE STATISTICS [silver].[eurostoxx50_ohlcv]
1879410.19015.7231.54UPDATE STATISTICS [silver].[stoxxasia50_ohlcv]
1880410.21514.9221.46UPDATE STATISTICS [silver].[stoxxusa50_ohlcv]
54922.5012.7931.12(@P1 nvarchar(6))SELECT date FROM bronze.trading_calendar WHERE exchange_code = @P1 AND is_trading_day = 1

There are real multi-plan candidates in the live Query Store history. The strongest ones are the bronze OHLCV cleanup deletes, where the worst persisted plan is about 7x to 11x slower than the best one. That does not prove the current wait profile is caused by plan regression, but it is strong enough to justify a pivot into the dedicated Query Store regression note if those statements are part of the incident window.

ColumnValueWatchMeaningImplication
plan_count > 1Multiple persisted plansDependsThe query has experienced plan variation.Regression is possible, not guaranteed.
regression_factor >= 2Material spread❌Worst persisted plan is at least twice as slow as the best one.Strong candidate for plan comparison and forcing review.
regression_factor near 1Low spread✅ or neutralPlans are similar in average duration.Focus elsewhere unless other evidence contradicts it.

Common Wait Types — Quick Reference

Wait typeWhat it usually meansFirst thing to verify
LCK_M_X, LCK_M_U, LCK_M_SCH_S, LCK_M_IXBlocking or metadata serializationBlocking chain and transaction scope
CXPACKET, CXCONSUMER, CXSYNC_PORTParallel-plan coordination or skewActual plan shape and parallelism settings
PAGEIOLATCH_*Data pages not already in cacheFile latency and memory pressure
WRITELOGCommit or log flush delayLog latency and commit frequency
PAGELATCH_*In-memory latch contention, often TempDB allocationTempDB file layout and concurrency pattern
RESOURCE_SEMAPHOREQueries waiting for memory grantsMemory grants and max server memory
ASYNC_NETWORK_IOClient fetching too slowlyConsumer-side fetch and result handling

SQL Server Wait Stats Analysis References

Graph View

Backlinks