SQL Server
How to Find SQL Server Performance Regressions with Query Store
A practical Query Store workflow for SQL Server regressions: compare time windows, find queries that slowed down, inspect plan changes, force plans safely and undo them.
On this page
“It was fast last week” is one of the most common SQL Server complaints, and one of the hardest to answer from live DMVs alone. By the time someone looks, the slow query may have finished, the plan may have left the cache, and the server may have restarted. What you need is history: which queries ran, with which plans, and how they performed before and after the change.
Query Store keeps that history inside the database. This article is a practical workflow for using it to investigate a performance regression, from the first symptom to a validated fix:
symptom → evidence → regression → candidate plan → validation → action
The examples use the documented Query Store catalog views and stored procedures, and work on SQL Server 2017 and later, Azure SQL Database and Azure SQL Managed Instance unless noted.
What a performance regression is
A regression is a query, or a workload, that performs worse than it did before for comparable work. That last part matters. A report that takes longer because it now reads ten times more data has not regressed; its workload changed. A regression is a change in efficiency, not just in duration.
Common causes:
- Plan changes. The optimizer chooses a different plan after a statistics update, a recompile, an index change, a parameter-sensitive compile, a compatibility-level change or an upgrade.
- Data growth or distribution changes. The same plan is now poor for the data it meets.
- Code changes. A deployment changed the query text, which Query Store treats as a new query.
- Resource contention. Blocking, memory pressure or I/O latency make an unchanged plan slower.
Query Store is strongest at the first two, and it helps separate them from the last two.
Why Query Store is the right tool
Query Store captures, per database:
- Queries and their text (
sys.query_store_query,sys.query_store_query_text). - Every plan each query has used (
sys.query_store_plan), including the showplan XML, compile times, the engine version and compatibility level that produced it. - Runtime statistics per plan per time interval (
sys.query_store_runtime_statswithsys.query_store_runtime_stats): executions, duration, CPU, logical reads, writes and more._interval - Wait statistics per plan and interval (
sys.query_store_wait_stats), when wait capture is enabled.
Durations and CPU times are stored in microseconds, and logical reads in 8 KB pages. Statistics are aggregated per interval (60 minutes by default), so you compare intervals, not individual executions.
The current-state view of active requests and sessions tells you what is happening now. Query Store tells you what changed.
Step 0: make sure Query Store is collecting
Query Store is enabled by default for new Azure SQL databases and, since SQL Server 2022, for new SQL Server databases. Older databases, and databases upgraded from earlier versions, may not have it on. Check before you rely on it:
SELECT
actual_state_desc,
desired_state_desc,
readonly_reason,
current_storage_size_mb,
max_storage_size_mb,
interval_length_minutes,
stale_query_threshold_days,
query_capture_mode_desc,
size_based_cleanup_mode_desc
FROM sys.database_query_store_options;The result you want is READ_WRITE in both state columns. If actual_state_desc is READ_ONLY while desired_state_desc is READ_WRITE, Query Store stopped collecting, most often because it reached its maximum size. You then have history up to that point and nothing after it, which can make a regression invisible.
A reasonable production configuration:
ALTER DATABASE CURRENT
SET QUERY_STORE = ON
(
OPERATION_MODE = READ_WRITE,
QUERY_CAPTURE_MODE = AUTO,
MAX_STORAGE_SIZE_MB = 2048,
SIZE_BASED_CLEANUP_MODE = AUTO
);QUERY_CAPTURE_MODE = AUTO skips insignificant ad hoc queries, which keeps the store focused. Size the storage for the history you want to keep, and keep SIZE_BASED_CLEANUP_MODE = AUTO so cleanup runs before the store fills. Retention is controlled by the stale query threshold; make sure it covers the comparison windows you need, such as “same day last week”.
Step 1: define the symptom and the windows
Before querying anything, write down:
- What is slow: a report, an API endpoint, a batch job, or “everything”.
- When it started: a deployment, an index rebuild, a statistics job, an upgrade, a data load.
- A baseline window: a period of comparable load when things were normal, ideally the same weekday and hours.
- An incident window: the same length, after the problem started.
Comparing a quiet Sunday with a busy Monday finds load changes, not regressions. Equal-length windows with similar business activity are what make the comparison meaningful. If the problem is happening right now, start with the SQL Server troubleshooting workflow and active request checks to rule out blocking and saturation first.
Step 2: find queries that became slower
This query compares two windows per query. It weights each interval’s averages by its execution count, so a busy interval counts more than a quiet one, and it reports aborted and failed executions separately. Client timeouts are recorded as aborted executions, so a regression that ends in timeouts can look like fewer slow executions rather than more.
DECLARE @baseline_start datetimeoffset = '2026-09-28T08:00:00+00:00';
DECLARE @baseline_end datetimeoffset = '2026-09-28T12:00:00+00:00';
DECLARE @incident_start datetimeoffset = '2026-10-05T08:00:00+00:00';
DECLARE @incident_end datetimeoffset = '2026-10-05T12:00:00+00:00';
WITH runtime AS (
SELECT
p.query_id,
CASE
WHEN rsi.start_time >= @incident_start AND rsi.start_time < @incident_end
THEN 'incident'
ELSE 'baseline'
END AS period,
rs.execution_type,
rs.count_executions,
rs.avg_duration,
rs.avg_cpu_time,
rs.avg_logical_io_reads
FROM sys.query_store_runtime_stats AS rs
INNER JOIN sys.query_store_runtime_stats_interval AS rsi
ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
INNER JOIN sys.query_store_plan AS p
ON p.plan_id = rs.plan_id
WHERE (rsi.start_time >= @baseline_start AND rsi.start_time < @baseline_end)
OR (rsi.start_time >= @incident_start AND rsi.start_time < @incident_end)
),
per_period AS (
SELECT
query_id,
period,
SUM(CASE WHEN execution_type = 0 THEN count_executions ELSE 0 END) AS regular_executions,
SUM(CASE WHEN execution_type <> 0 THEN count_executions ELSE 0 END) AS aborted_or_failed,
SUM(CASE WHEN execution_type = 0 THEN avg_duration * count_executions END)
/ NULLIF(SUM(CASE WHEN execution_type = 0 THEN count_executions END), 0) / 1000.0 AS avg_duration_ms,
SUM(CASE WHEN execution_type = 0 THEN avg_cpu_time * count_executions END)
/ NULLIF(SUM(CASE WHEN execution_type = 0 THEN count_executions END), 0) / 1000.0 AS avg_cpu_ms,
SUM(CASE WHEN execution_type = 0 THEN avg_logical_io_reads * count_executions END)
/ NULLIF(SUM(CASE WHEN execution_type = 0 THEN count_executions END), 0) AS avg_logical_reads
FROM runtime
GROUP BY query_id, period
)
SELECT TOP (25)
i.query_id,
b.regular_executions AS baseline_executions,
i.regular_executions AS incident_executions,
i.aborted_or_failed AS incident_aborted_or_failed,
CAST(b.avg_duration_ms AS decimal(18, 2)) AS baseline_avg_ms,
CAST(i.avg_duration_ms AS decimal(18, 2)) AS incident_avg_ms,
CAST(b.avg_cpu_ms AS decimal(18, 2)) AS baseline_cpu_ms,
CAST(i.avg_cpu_ms AS decimal(18, 2)) AS incident_cpu_ms,
CAST(b.avg_logical_reads AS decimal(18, 0)) AS baseline_reads,
CAST(i.avg_logical_reads AS decimal(18, 0)) AS incident_reads,
CAST((i.avg_duration_ms - b.avg_duration_ms) * i.regular_executions / 1000.0 AS decimal(18, 1))
AS additional_seconds,
LEFT(qt.query_sql_text, 200) AS query_text_start
FROM per_period AS i
INNER JOIN per_period AS b
ON b.query_id = i.query_id
AND b.period = 'baseline'
INNER JOIN sys.query_store_query AS q
ON q.query_id = i.query_id
INNER JOIN sys.query_store_query_text AS qt
ON qt.query_text_id = q.query_text_id
WHERE i.period = 'incident'
AND i.avg_duration_ms > 1.5 * b.avg_duration_ms
ORDER BY additional_seconds DESC;Notes
- lines 1–4The two windows to compare. Give them the same length and time of day.
- lines 33–34Query Store stores durations in microseconds: dividing by 1000.0 gives milliseconds.
- lines 53–54The extra time the slowdown cost during the incident window: the list is ranked by it.
- line 65Keeps queries at least 50% slower on average. Change 1.5 to widen or narrow the list.
Read the output in this order:
additional_secondsranks queries by the extra time they added to the incident window. A query that got 3× slower but runs twice a day matters less than one that got 30% slower and runs ten thousand times an hour.- Duration vs CPU vs reads. If CPU and reads rose with duration, the query is doing more work, which suggests a plan or data change. If duration rose but CPU and reads did not, the query is waiting, which points at blocking, I/O or memory rather than the plan.
- Executions. A large change in execution count is a workload change. Treat it separately from per-execution regressions.
- Aborted or failed. A rise here can mean timeouts. Check whether the slow executions were cut off by the client.
Queries that appear only in the incident window are not in this result, because they have no baseline. They are often new code or new query text after a deployment; list them separately if the deployment is a suspect.
Step 3: check whether the plan changed
For each candidate query, look at its plans and their performance over time:
DECLARE @query_id bigint = 4711;
SELECT
p.plan_id,
p.is_forced_plan,
p.compatibility_level,
p.engine_version,
p.last_compile_start_time,
rsi.start_time AS interval_start,
rs.execution_type_desc,
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,
CAST(rs.avg_logical_io_reads AS decimal(18, 0)) AS avg_logical_reads,
CAST(rs.max_duration / 1000.0 AS decimal(18, 2)) AS max_duration_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.start_time >= DATEADD(day, -14, SYSUTCDATETIME())
ORDER BY rsi.start_time, p.plan_id;Notes
- line 1Replace 4711 with a query_id from the previous result.
Three patterns are common:
- One plan before, another plan after, and the new plan is consistently worse. This is a classic plan regression, and the best candidate for plan forcing.
- The same plan before and after, but it got slower. The plan did not change; the data or the environment did. Look at table growth, statistics, blocking and resource pressure. Table sizes and the performance tuning checklist are the next stops.
- Several plans alternating, each good for some executions and bad for others. That usually means parameter sensitivity: a plan compiled for one parameter value is reused for very different values. Forcing a single plan may help some callers and hurt others.
On SQL Server 2022 and later, parameter sensitive plan optimization can store several variant plans for one parameterized query. sys.query_store_plan.plan_type_desc distinguishes dispatcher and variant plans, and sys.query_store_query_variant links them. Take that into account before concluding that a query has “too many plans”.
Step 4: compare the candidate plans
Pull the plans and compare them in SQL Server Management Studio or another plan viewer:
SELECT
plan_id,
query_id,
last_compile_start_time,
TRY_CONVERT(xml, query_plan) AS query_plan_xml
FROM sys.query_store_plan
WHERE plan_id IN (82, 97);TRY_CONVERT returns NULL for the rare plans too deeply nested to convert to xml; in that case save the query_plan text with a .sqlplan extension and open it directly.
When comparing, look for the operator that changed the cost profile: a scan where there used to be a seek, a nested loop over a large estimated input, a different join order, a missing parallelism, or a large gap between estimated and actual rows if you also have an actual plan. Note the compile time and compatibility level of each plan; a plan compiled right after an upgrade or a compatibility-level change is a strong clue.
The built-in Regressed Queries and Queries With Forced Plans reports in Management Studio show the same data visually. They are a good way to confirm what the T-SQL told you.
Step 5: decide whether to force a plan
If a previous plan was consistently better for the same workload, forcing it is a fast, reversible mitigation:
EXEC sys.sp_query_store_force_plan @query_id = 4711, @plan_id = 82;Forcing tells the optimizer to use that plan for the query. It does not change the query text, and it takes effect on the next compilation.
Do not force a plan when:
- The plan did not change. Forcing the same plan fixes nothing; the cause is elsewhere.
- The old plan was only better for some parameter values. You trade one group of slow callers for another.
- The old plan depends on an index or object that has since changed. Forcing can fail, and the optimizer falls back to normal compilation.
- You have not compared equivalent workloads. A plan that looked better on a quiet day may not be better under load.
- The real fix is cheap and available, such as correcting a statistics problem or an obvious missing index. Forcing should not hide a cause you can remove.
Forcing is a mitigation. Record why you forced the plan, who did it, and when it will be reviewed.
Step 6: validate the result
After forcing, wait for the query to recompile and run under representative load, then repeat the Step 2 comparison with a new “after” window. You want to see lower duration, CPU and reads per execution, with comparable execution counts and no rise in aborted executions.
Also confirm that forcing actually succeeds. A forced plan is not guaranteed to be used; if the optimizer cannot produce it (for example because an index it uses was dropped), forcing fails and the query compiles normally:
SELECT
p.plan_id,
p.query_id,
q.object_id AS containing_object_id,
p.plan_forcing_type_desc,
p.force_failure_count,
p.last_force_failure_reason_desc
FROM sys.query_store_plan AS p
INNER JOIN sys.query_store_query AS q
ON q.query_id = p.query_id
WHERE p.is_forced_plan = 1;force_failure_count increases only when the query is recompiled, not on every execution. Check this list regularly, not only after an incident. Renaming the database, dropping an index the plan uses, or schema changes to referenced objects can all break forcing silently.
Step 7: undo or replace the forced plan
Unforce a plan when it stops helping, when a durable fix is in place, or when validation shows it made things worse:
EXEC sys.sp_query_store_unforce_plan @query_id = 4711, @plan_id = 82;Plan forcing is a holding pattern. The durable fix is usually one of: better statistics maintenance, an index that supports the predicate, a query rewrite, separating genuinely different workload shapes, or a targeted hint. On SQL Server 2022 and later and in Azure SQL, Query Store hints (sys.sp_query_store_set_hints and sys.sp_query_store_clear_hints) can apply a query hint to a query without changing application code, which is often a better long-term control than forcing a whole plan.
Automatic plan correction
SQL Server 2017 and later can do the plan-regression part of this workflow automatically. With automatic tuning enabled, the engine detects plan choice regressions, forces the last known good plan, verifies that it helps, and reverts it if it does not:
ALTER DATABASE CURRENT
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);Even without enabling it, you can read the recommendations it would make:
SELECT
name,
reason,
score,
JSON_VALUE(state, '$.currentValue') AS current_state,
JSON_VALUE(details, '$.implementationDetails.script') AS script
FROM sys.dm_db_tuning_recommendations;sys.dm_db_tuning_recommendations is not persisted across restarts, and automatic correction handles only plan choice regressions. It does not fix data growth, blocking or resource pressure. Treat it as a helpful first responder, and keep the manual workflow for everything else.
Storage and capture considerations
- Size it for the history you need. A store that fills up switches to read-only and stops recording exactly when you need it.
- Capture mode.
AUTOis the practical default.ALLcaptures every query and can bloat the store on ad hoc workloads;CUSTOMlets you set thresholds. - Non-parameterized workloads create a new query for every literal value, which fills the store and makes comparison hard. Parameterize at the application, or consider forced parameterization where appropriate.
- Interval length trades detail for space. Shorter intervals help pinpoint when a change happened; 60 minutes is a sensible default for most systems.
- Read access. Query text can contain sensitive values. Control who can read Query Store views in production.
Common mistakes
- Comparing windows with different load, then calling a workload change a regression.
- Using unweighted averages across intervals, so a quiet interval counts as much as a busy one.
- Forcing a plan without checking that the plan changed.
- Forcing a plan for a parameter-sensitive query and moving the pain to other callers.
- Never checking forced plans again, so failures go unnoticed for months.
- Ignoring aborted executions, which hides timeouts.
- Letting Query Store go read-only and investigating a period it never recorded.
Recommendation
Make Query Store part of normal operations, not an emergency tool: keep it READ_WRITE, size it for at least a few weeks of history, and know how to compare two windows before you need to.
When a regression appears, follow the evidence: confirm comparable windows, rank queries by the extra time they added, check whether the plan changed, compare candidate plans, force only when one plan is clearly better for the same workload, validate the result, and schedule the durable fix. Return to the performance tuning workflow for the full investigation order, use real-time monitoring for current activity, and keep the Query Store quick reference nearby.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Continue learning
- Cheat sheetQuery Store Quick ReferenceUse 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.Open cheat sheet
- RoadmapSQL Server Troubleshooting & DiagnosticsA practical Query Store workflow for SQL Server regressions: compare time windows, find queries that slowed down, inspect plan changes, force plans safely and undo them.View roadmap
What should I build next?
I'm building Javi Builds around practical problems data engineers actually face. Tell me what you'd like to see next.
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.