Browse cheat sheets · SQL Server
Query Store Quick Reference
Use SQL Server Query Store to find regressions, compare plans, analyze runtime history, force or unforce plans safely, and understand when Query Store data can mislead you.
On this page
- Problem
- Quick answer
- Code / commands
- 1. Check whether Query Store is enabled
- 2. Find top queries by average duration
- 3. Find top queries by total CPU
- 4. Find top queries by logical reads
- 5. Show all plans for one query
- 6. Compare runtime by plan
- 7. Compare a query before and after a change window
- 8. Force a known-good plan
- 9. Unforce a plan
- 10. Find queries with multiple plans
- 11. Show recent runtime history for one query
- What the result means
- What to look for
- Same query, different plan, major runtime difference
- Duration increases but CPU does not
- CPU and logical reads both increase
- One plan dominates only after a deployment
- High average duration with very low execution count
- Production notes
- Query Store is historical, but not infinite history
- Query Store timestamps are stored in UTC-oriented metadata
- Runtime stats are aggregated
- Weighted averages are better than averaging averages
- Plan forcing is operationally powerful
- Query Store can be read-only
- Common mistakes
- 1. Averaging avg_duration across intervals directly
- 2. Assuming multiple plans means parameter sniffing
- 3. Forcing the fastest-looking plan without context
- 4. Treating plan forcing as the final fix
- 5. Ignoring server-wide conditions
- 6. Comparing UTC timestamps to local times without conversion
- 7. Assuming Query Store has complete history
- Next step
- Related deep dives
Problem
A query was fast yesterday and slow today, a deployment changed performance, or an intermittent issue has already disappeared before you could inspect the live workload.
sys.dm_exec_requests only shows what is running now. Query Store helps you answer historical questions:
- Which queries consumed the most CPU or duration over time?
- Which query regressed?
- Did SQL Server switch execution plans?
- Which plan was faster?
- When did the regression begin?
- Can a known-good plan be forced temporarily?
- Is the problem isolated to one query or part of a broader workload change?
Query Store is most useful when you need history, plan comparison, and regression analysis.
Quick answer
Start with Query Store enabled and use the built-in views to find expensive queries by recent runtime.
SELECT TOP (20)
q.query_id,
qt.query_sql_text,
p.plan_id,
rs.count_executions,
CAST(rs.avg_duration / 1000.0 AS decimal(18,2)) AS avg_duration_ms,
CAST(rs.avg_cpu_time / 1000.0 AS decimal(18,2)) AS avg_cpu_ms,
rs.avg_logical_io_reads,
rsi.start_time,
rsi.end_time
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
ON rs.plan_id = p.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi
ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE
rsi.end_time > DATEADD(hour, -1, SYSUTCDATETIME())
ORDER BY
rs.avg_duration DESC;
Use this as a starting point, then group by query and plan to compare historical behavior.
Code / commands
1. Check whether Query Store is enabled
SELECT
actual_state_desc,
desired_state_desc,
readonly_reason,
current_storage_size_mb,
max_storage_size_mb,
query_capture_mode_desc,
size_based_cleanup_mode_desc,
stale_query_threshold_days
FROM sys.database_query_store_options;
What you usually want:
actual_state_desc = READ_WRITE- enough free Query Store storage
- a capture mode appropriate for the workload
If Query Store is READ_ONLY, do not assume collection is working normally.
2. Find top queries by average duration
SELECT TOP (20)
q.query_id,
qt.query_sql_text,
SUM(rs.count_executions) AS executions,
CAST(
SUM(rs.avg_duration * rs.count_executions)
/ NULLIF(SUM(rs.count_executions), 0)
/ 1000.0
AS decimal(18,2)
) AS weighted_avg_duration_ms
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
ON rs.plan_id = p.plan_id
GROUP BY
q.query_id,
qt.query_sql_text
ORDER BY
weighted_avg_duration_ms DESC;
Weighting by execution count is important.
A plain average of averages can misrepresent workload when runtime intervals contain very different execution counts.
3. Find top queries by total CPU
SELECT TOP (20)
q.query_id,
qt.query_sql_text,
SUM(rs.count_executions) AS executions,
CAST(
SUM(rs.avg_cpu_time * rs.count_executions)
/ 1000.0
AS decimal(18,2)
) AS total_cpu_ms
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
ON rs.plan_id = p.plan_id
GROUP BY
q.query_id,
qt.query_sql_text
ORDER BY
total_cpu_ms DESC;
This is often more useful than average CPU when you want to know which query consumed the most CPU overall.
4. Find top queries by logical reads
SELECT TOP (20)
q.query_id,
qt.query_sql_text,
SUM(rs.count_executions) AS executions,
CAST(
SUM(rs.avg_logical_io_reads * rs.count_executions)
AS decimal(18,2)
) AS total_logical_reads
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
ON rs.plan_id = p.plan_id
GROUP BY
q.query_id,
qt.query_sql_text
ORDER BY
total_logical_reads DESC;
High logical reads can reveal workload that is expensive even when duration looks acceptable because data is already cached.
5. Show all plans for one query
DECLARE @query_id bigint = 123;
SELECT
p.query_id,
p.plan_id,
p.is_forced_plan,
p.force_failure_count,
p.last_force_failure_reason_desc,
p.last_execution_time,
p.query_plan
FROM sys.query_store_plan AS p
WHERE
p.query_id = @query_id
ORDER BY
p.plan_id;
Use this when you suspect plan regression.
A query with multiple plans is not automatically a problem.
The important question is whether different plans correlate with materially different runtime behavior.
6. Compare runtime by plan
DECLARE @query_id bigint = 123;
SELECT
p.plan_id,
p.is_forced_plan,
SUM(rs.count_executions) AS executions,
CAST(
SUM(rs.avg_duration * rs.count_executions)
/ NULLIF(SUM(rs.count_executions), 0)
/ 1000.0
AS decimal(18,2)
) AS weighted_avg_duration_ms,
CAST(
SUM(rs.avg_cpu_time * rs.count_executions)
/ NULLIF(SUM(rs.count_executions), 0)
/ 1000.0
AS decimal(18,2)
) AS weighted_avg_cpu_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,
MIN(rsi.start_time) AS first_interval,
MAX(rsi.end_time) AS last_interval
FROM sys.query_store_plan AS p
INNER JOIN sys.query_store_runtime_stats AS rs
ON rs.plan_id = p.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi
ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE
p.query_id = @query_id
GROUP BY
p.plan_id,
p.is_forced_plan
ORDER BY
weighted_avg_duration_ms DESC;
This is one of the most useful Query Store queries for plan-regression analysis.
7. Compare a query before and after a change window
DECLARE @query_id bigint = 123;
DECLARE @change_time datetimeoffset = '2026-10-04T14:00:00-04:00';
SELECT
CASE
WHEN rsi.end_time <= @change_time THEN 'Before'
ELSE 'After'
END AS period,
p.plan_id,
SUM(rs.count_executions) AS executions,
CAST(
SUM(rs.avg_duration * rs.count_executions)
/ NULLIF(SUM(rs.count_executions), 0)
/ 1000.0
AS decimal(18,2)
) AS weighted_avg_duration_ms,
CAST(
SUM(rs.avg_cpu_time * rs.count_executions)
/ NULLIF(SUM(rs.count_executions), 0)
/ 1000.0
AS decimal(18,2)
) AS weighted_avg_cpu_ms
FROM sys.query_store_plan AS p
INNER JOIN sys.query_store_runtime_stats AS rs
ON rs.plan_id = p.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi
ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE
p.query_id = @query_id
AND (
rsi.end_time <= @change_time
OR rsi.start_time >= @change_time
)
GROUP BY
CASE
WHEN rsi.end_time <= @change_time THEN 'Before'
ELSE 'After'
END,
p.plan_id
ORDER BY
period,
p.plan_id;
Use this after:
- compatibility-level changes
- deployments
- index changes
- statistics changes
- configuration changes
Be careful with intervals that straddle the change timestamp. The filter above excludes those mixed intervals.
8. Force a known-good plan
Only do this after you have identified a specific query and validated the plan.
EXEC sys.sp_query_store_force_plan
@query_id = 123,
@plan_id = 456;
Then verify:
SELECT
query_id,
plan_id,
is_forced_plan,
force_failure_count,
last_force_failure_reason_desc
FROM sys.query_store_plan
WHERE
query_id = 123;
Plan forcing is a mitigation, not necessarily the final fix.
9. Unforce a plan
EXEC sys.sp_query_store_unforce_plan
@query_id = 123,
@plan_id = 456;
Unforce when:
- the root cause is fixed
- the forced plan no longer fits the workload
- forcing causes regressions
- schema or data distribution changed enough that the old plan is no longer appropriate
10. Find queries with multiple plans
SELECT
q.query_id,
COUNT(*) AS plan_count
FROM sys.query_store_query AS q
INNER JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
GROUP BY
q.query_id
HAVING
COUNT(*) > 1
ORDER BY
plan_count DESC;
This is a discovery query only.
Multiple plans can be normal.
Use runtime evidence to decide whether variation is harmful.
11. Show recent runtime history for one query
DECLARE @query_id bigint = 123;
SELECT
rsi.start_time,
rsi.end_time,
p.plan_id,
rs.count_executions,
CAST(rs.avg_duration / 1000.0 AS decimal(18,2)) AS avg_duration_ms,
CAST(rs.avg_cpu_time / 1000.0 AS decimal(18,2)) AS avg_cpu_ms,
rs.avg_logical_io_reads
FROM sys.query_store_plan AS p
INNER JOIN sys.query_store_runtime_stats AS rs
ON rs.plan_id = p.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi
ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE
p.query_id = @query_id
ORDER BY
rsi.start_time DESC,
p.plan_id;
This is useful for seeing exactly when a query became slower and whether a plan change lines up with the regression.
What the result means
| Field | Why it matters |
|---|---|
query_id |
Stable Query Store identifier for the query entry. |
plan_id |
Query Store identifier for a captured execution plan. |
count_executions |
Number of executions represented by a runtime-stat row. |
avg_duration |
Average duration for executions in that runtime-stat entry. |
avg_cpu_time |
Average CPU time for executions in the entry. |
avg_logical_io_reads |
Average logical reads associated with the entry. |
is_forced_plan |
Indicates whether Query Store plan forcing is currently active. |
force_failure_count |
Number of times SQL Server failed to force the plan. |
last_force_failure_reason_desc |
Reason for the most recent plan-forcing failure. |
runtime_stats_interval_id |
Groups runtime stats into configured Query Store intervals. |
start_time / end_time |
Boundaries of the runtime interval. |
What to look for
Same query, different plan, major runtime difference
This is classic plan-regression evidence.
Validate:
- the slower period aligns with the new plan
- execution count is sufficient to compare
- parameter values/workload are comparable
- the regression is not caused by blocking or server-wide pressure
Duration increases but CPU does not
Possible causes include:
- blocking
- I/O waits
- memory waits
- concurrency
- external resource pressure
Do not assume the plan is the only variable.
CPU and logical reads both increase
This often points toward a less efficient access path.
Inspect the plan for:
- larger scans
- different join strategy
- row-estimate issues
- lookup amplification
- missing/changed indexes
One plan dominates only after a deployment
Good candidate for before/after analysis.
Compare:
- compatibility level
- parameterization
- statistics
- indexes
- query text
- optimizer behavior
High average duration with very low execution count
Do not over-prioritize a one-off query without understanding business impact.
Frequency and importance matter.
Production notes
Query Store is historical, but not infinite history
Retention depends on:
- max storage size
- stale-query cleanup
- capture mode
- read/write state
If the store fills or becomes read-only, collection can stop or degrade.
Query Store timestamps are stored in UTC-oriented metadata
Be careful when comparing Query Store interval timestamps with local deployment or incident times.
Normalize time zones before drawing conclusions.
Runtime stats are aggregated
Query Store does not store every execution as an individual row in the runtime stats views.
It stores aggregated statistics by interval.
That means:
- outliers can be hidden by averages
- very different executions can be grouped
- execution-count weighting matters
Weighted averages are better than averaging averages
When combining intervals, weight by count_executions.
Otherwise, one interval with 1 execution can influence your result as much as another interval with 10,000 executions.
Plan forcing is operationally powerful
Plan forcing can stabilize a regression quickly, but it should be monitored.
A forced plan can later become inappropriate because of:
- data growth
- schema changes
- index changes
- parameter distribution shifts
- optimizer changes
Query Store can be read-only
Common causes include reaching configured storage limits or other state transitions.
Always check sys.database_query_store_options before assuming new data is being captured.
Common mistakes
1. Averaging avg_duration across intervals directly
This produces a mathematically misleading result when intervals contain different execution counts.
Use weighted averages.
2. Assuming multiple plans means parameter sniffing
Multiple plans can result from many things.
Treat plan variation as evidence, not a diagnosis.
3. Forcing the fastest-looking plan without context
A plan that is fast for one parameter set may be poor for another.
Validate workload shape before forcing.
4. Treating plan forcing as the final fix
Forcing can be an excellent mitigation.
But you still need to investigate:
- statistics
- indexes
- cardinality estimation
- query design
- parameter sensitivity
- compatibility changes
5. Ignoring server-wide conditions
A slow Query Store interval may coincide with:
- CPU saturation
- blocking
- storage latency
- memory pressure
Do not attribute every regression to the plan.
6. Comparing UTC timestamps to local times without conversion
This can shift your incident correlation by hours.
Normalize first.
7. Assuming Query Store has complete history
Retention, cleanup, capture mode, and storage limits all affect what remains available.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.