The average looks comfortable while one caller waits far longer than another. Query Store can expose unstable queries by showing duration spread alongside the number of executions behind that average.
Verify History Exists Before Ranking Unstable Queries
Query Store records runtime summaries for captured queries and their plans. It stores intervals rather than every individual duration. That history helps distinguish persistent variability from one current execution that happened to be slow.
I check collection state before trusting an empty report. Query Store can be disabled, read-only, restricted by capture policy, or holding too little retained history. No rows in that situation do not establish a stable workload.
Query Store is available in SQL Server 2016 and later. It is enabled by default for newly created databases starting with SQL Server 2022. Check existing and upgraded databases instead of assuming that default applies to them.
SELECT actual_state_desc, desired_state_desc, readonly_reason,
current_storage_size_mb, max_storage_size_mb,
interval_length_minutes, query_capture_mode_desc
FROM sys.database_query_store_options;Run these queries in the database you intend to investigate with the required diagnostic permissions. Capture the database and instance identity with the output. A report from another database can look entirely plausible while answering the wrong question.
Use enough executions to make spread meaningful. A single execution has no useful distribution. Choose a minimum population and inspection window that fit your workload, and show those choices to the person reading the report.
Preserve the Weight of Each Summary
avg_duration, min_duration, max_duration, and stdev_duration are expressed in microseconds. Divide durations by 1000.0 to present milliseconds. Ratios such as spread divided by average remain unitless when both terms use the same unit.
Do not average averages without their execution counts. A summary covering many executions deserves more weight than one covering a few. Multiply each average by count_executions, sum those products, then divide by the summed count.
An active interval can contain more than one runtime-statistics row for a plan and execution type. Aggregate those rows rather than assuming one row equals one plan interval. Filter regular executions separately from aborted or failed executions.
The query below combines the selected summaries. It uses minimum and maximum for a simple spread ranking. That identifies candidates without pretending to reconstruct every individual duration from an aggregate.
DECLARE @Since datetimeoffset = DATEADD(day, -7, SYSDATETIMEOFFSET());
WITH Summary AS
(
SELECT p.query_id,
SUM(CONVERT(bigint, r.count_executions)) AS Executions,
SUM(r.avg_duration * r.count_executions)
/ NULLIF(SUM(CONVERT(bigint, r.count_executions)), 0) AS AverageUs,
MIN(r.min_duration) AS MinimumUs,
MAX(r.max_duration) AS MaximumUs,
COUNT(DISTINCT p.plan_id) AS PlanTotal
FROM sys.query_store_runtime_stats AS r
JOIN sys.query_store_runtime_stats_interval AS ri
ON ri.runtime_stats_interval_id = r.runtime_stats_interval_id
JOIN sys.query_store_plan AS p ON p.plan_id = r.plan_id
WHERE r.execution_type = 0 AND ri.start_time >= @Since
GROUP BY p.query_id
)
SELECT TOP (50) s.query_id, s.Executions, s.PlanTotal,
s.AverageUs / 1000.0 AS AverageMs,
s.MinimumUs / 1000.0 AS MinimumMs,
s.MaximumUs / 1000.0 AS MaximumMs,
(s.MaximumUs - s.MinimumUs) / NULLIF(s.AverageUs, 0) AS SpreadRatio,
qt.query_sql_text
FROM Summary AS s
JOIN sys.query_store_query AS q ON q.query_id = s.query_id
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
WHERE s.Executions >= 20 AND s.AverageUs > 0
ORDER BY SpreadRatio DESC, s.Executions DESC;The seven-day window and twenty-execution threshold are inspection choices. They are not observed workload facts or universal cutoffs. The interval filter includes whole intervals beginning inside that window, rather than reconstructing exact boundary executions.
Inspect Standard Deviation Without Inventing a Merge
Standard deviation describes how widely durations vary around the average within a summary. Its ratio to average duration helps compare relative variation between queries with different typical durations. Inspect it alongside the underlying execution count.
SELECT TOP (100) p.query_id, r.plan_id,
r.runtime_stats_interval_id, r.count_executions,
r.avg_duration / 1000.0 AS AverageMs,
r.stdev_duration / 1000.0 AS StandardDeviationMs,
r.min_duration / 1000.0 AS MinimumMs,
r.max_duration / 1000.0 AS MaximumMs,
r.stdev_duration / NULLIF(r.avg_duration, 0) AS VariationRatio
FROM sys.query_store_runtime_stats AS r
JOIN sys.query_store_plan AS p ON p.plan_id = r.plan_id
WHERE r.execution_type = 0 AND r.count_executions >= 20
ORDER BY VariationRatio DESC;These rows remain individual summaries. Their standard deviations must not be averaged and labeled as the combined query deviation. Properly combining distributions requires counts and their first and second moments, including differences between summary averages.
Large maximum duration with modest standard deviation can indicate a rare outlier. High deviation with substantial execution count suggests broader inconsistency. Query Store summaries identify the distinction to investigate, not the exact parameter of the slowest execution.
Compare Plans for Unstable Queries Before Changing Anything
Use a selected query_id to inspect its separate plans and runtime history. Keep plan_id in the result. Otherwise, variability caused by plan changes gets mixed together with variability inside one plan.
DECLARE @QueryID bigint = 1;
SELECT p.plan_id, p.is_forced_plan, ri.start_time, ri.end_time,
SUM(CONVERT(bigint, r.count_executions)) AS Executions,
SUM(r.avg_duration * r.count_executions)
/ NULLIF(SUM(CONVERT(bigint, r.count_executions)), 0) / 1000.0 AS AverageMs,
MIN(r.min_duration) / 1000.0 AS MinimumMs,
MAX(r.max_duration) / 1000.0 AS MaximumMs
FROM sys.query_store_plan AS p
JOIN sys.query_store_runtime_stats AS r ON r.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval AS ri
ON ri.runtime_stats_interval_id = r.runtime_stats_interval_id
WHERE p.query_id = @QueryID AND r.execution_type = 0
GROUP BY p.plan_id, p.is_forced_plan, ri.start_time, ri.end_time
ORDER BY ri.start_time, p.plan_id;Replace the sample identifier with the candidate you actually selected. Compare plans over similar periods and populations. A plan used during a quiet interval cannot be declared superior from its average alone.
Investigate One Plan With Different Experiences
The same plan can run with different parameter values, data volumes, blocking, memory pressure, or storage waits. Duration spread within one plan therefore does not prove parameter sniffing. Match the slow period with additional evidence.
I check wait information before blaming the optimizer for every wide duration range. A query waiting behind another transaction needs a different remedy from one reading too many rows. The average is diplomatic enough to hide both.
Which executions do users describe as slow? Collect their time window and input shape, then compare that period with Query Store intervals. Avoid inventing parameter histories that this runtime-summary view does not retain.
Turn a Candidate List Into a Diagnosis
Use the ranking to choose a manageable set for investigation. Read query text, plans, interval populations, and relevant waits together. A high ratio on a tiny fast query can deserve less attention than a lower ratio on expensive frequent work.
Preserve aborted and failed execution summaries for a separate review. Excluding them from the regular-duration calculation keeps the comparison coherent, but those failures can still explain user complaints. Do not let a clean ranking hide them.
Rank Unstable Queries by the Work Callers Feel
Relative spread needs an absolute duration beside it. A high ratio can describe a statement whose longest execution still meets the application's response requirement. Keep maximum duration and execution count visible when deciding which query deserves investigation first.
Also consider accumulated work. A frequent query with modest variation can consume more total time than an isolated dramatic outlier. Multiply weighted average duration by the execution population when you need a rough accumulated-duration comparison. Do not treat that total as wall-clock server occupancy, because executions can overlap.
Retained history defines what this report can see. Cleanup removes earlier intervals, and capture policy excludes some statements. State those limits before concluding that a workload contains no other unstable behavior. Repeated sampling over a known window supplies a more useful picture than one unexplained export.
Query identity also matters. Similar text can belong to separate Query Store entries because of context or other identifying attributes. Review the selected query's module and database context before treating its summary as every application call that looks similar.
Compare wait categories for the selected plan and intervals when Query Store wait collection is available. A duration increase accompanied by locking waits leads you toward contention. A change in resource waits needs a different investigation. Keep that comparison at the same interval granularity as the runtime statistics.
Finally, retain the candidate list before applying a change. Repeat the same calculation over a comparable workload afterward. An improvement needs reduced troublesome durations without shifting cost into another important query. A tidier average alone does not answer the original complaint about inconsistent waits. Unstable queries need representative executions rather than one isolated slow sample. Compare the important plans and intervals before choosing a remedy for unstable queries.
Related reading on this blog: Comparing a Good and a Bad Plan for One Query in Query Store and Query Store Wait Stats: Why a Query Was Slow, Not Just That It Was.
A comfortable average is not a stable query, it is one summary that needs its spread and population beside it.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
