A query that became slow overnight needs a before-and-after comparison, not an immediate server-wide tuning change. Query Store can retain plans and execution statistics across time, allowing a DBA to ask whether a different plan coincided with the regression and whether the work performed was actually comparable.1
Start by confirming that the required history was captured. Then compare the same query, workload interval and execution outcome. A different plan is evidence to investigate; it does not by itself prove why the user experience worsened.
The reference environment is SQL Server 2022. Examples are source-checked and untested. Plan forcing, capture-setting changes and production workload replay are not performed or authorised by this article.
Establish what changed for the reader
Write the complaint as an observable operation: for example, an order search now takes substantially longer for a particular class of customer. Record when it was last acceptable, when degradation began, the application release and the database serving the request. Preserve the request shape without placing customer parameters in unrestricted logs.
Distinguish query execution time from the full application response. A pool wait, network delay or slow rendering can make a page slow even if its SQL execution is unchanged. Query Store contributes database evidence; the application trace establishes how much of the response that evidence explains.
Also distinguish per-execution cost from total load. A query executed many more times can dominate server resources without becoming slower on each execution. Conversely, a rare but expensive parameter combination can be important to users while having a small effect on an overall average.
Verify that Query Store has the relevant history
Query Store is enabled by default for new SQL Server 2022 databases, but that is not proof of its current state in an existing or upgraded database. Earlier SQL Server releases have different defaults. Inspect the actual database rather than assuming a feature is collecting because the engine supports it.1
This read-only example uses the documented options view. Run it in the intended database through an authorised diagnostic connection:
SELECT actual_state_desc,
desired_state_desc,
readonly_reason,
current_storage_size_mb,
max_storage_size_mb,
interval_length_minutes,
stale_query_threshold_days,
size_based_cleanup_mode_desc,
query_capture_mode_desc
FROM sys.database_query_store_options;
For SQL Server 2022, the options reference specifies VIEW DATABASE PERFORMANCE STATE or a higher applicable permission. Runtime-statistics access also has a version-specific performance-state requirement.3, 4 Do not broaden an application's permissions merely to collect this incident record.
Compare desired and actual state. Query Store can be read-only despite a desired read-write state, including when it reaches a configured size limit. The reason field is a bitmap; interpret its documented flags rather than treating every read-only state as the same failure.4
Also check retention and capture, even when the actual state is read-write. stale_query_threshold_days controls time-based retention; zero disables that retention policy. With size_based_cleanup_mode_desc = AUTO, cleanup begins near 90% of max_storage_size_mb and removes older and less costly queries until usage is approximately 80%. Compare current_storage_size_mb with the limit, but do not infer historical utilisation from today's size alone.4
AUTO selects queries by execution count and resource use; CUSTOM uses the configured capture policy. NONE stops capturing new queries while continuing statistics for queries already captured. An absent older query or plan may therefore have been excluded or removed. It is missing evidence, not proof that no earlier plan or regression existed. Preserve the settings and retained interval coverage before drawing a baseline comparison.4
If the incident interval was not captured, say so. Enabling capture now cannot recreate missing plan history. Preserve whatever application and monitoring evidence remains and set up an approved future observation window. Do not clear Query Store to solve a capacity warning before preserving the historical evidence needed for this investigation.
Choose comparable intervals
In Management Studio, Query Store's Regressed Queries view supports selecting metrics and inspecting or comparing retained plans.1 Choose a known-good interval and an affected interval that reflect comparable business activity. Record the time zone, interval boundaries and whether either interval is still collecting data.
Inspect execution count, average and maximum duration, CPU, reads and relevant wait categories together. Query Store wait categories group related waits; they are not a replacement for an exact live wait/resource investigation.1
The runtime-statistics view reports duration and CPU in microseconds. That is different from a display labelled milliseconds. It also separates regular, client-aborted and exception-aborted execution types. Keep those outcomes distinct when comparing results; otherwise an apparent improvement can hide a rise in aborted requests.3
If building a custom report, aggregate correctly. Microsoft documents that the active interval can have multiple rows for the same plan and execution type because statistics exist in memory and on disk. Group by plan, execution type and interval. To combine averages across intervals, weight each average by its execution count rather than averaging the averages.3 The weighting recommendation follows directly from what those counts and averages represent.
For example, an interval containing one slow execution should not receive the same weight as an interval containing thousands of short executions when estimating the combined average. Preserve the rare slow result separately, because it may still describe the complaint that triggered the investigation.
Compare the plans and the work
Keep query identity, plan identity and application operation together. Similar-looking SQL text may have different database context or settings, and one application action can execute several statements. Do not select a plan solely because its graphical shape looks familiar.
Compare access paths, join choices, cardinality estimates and memory-intensive operators. Ask which difference could explain the measured change in CPU, reads, duration or waiting. An index scan is not automatically a regression, and an index seek is not automatically efficient. The amount and distribution of qualifying data matters.
Then examine changes around the transition: application deployment, statistics, indexes, schema, compatibility level and workload mix. Query Store's historical plans help locate the transition, but the release and maintenance records are needed to explain its context.1
Use the following decision distinctions:
| Observation | What to investigate next |
|---|---|
| New plan, comparable workload, much higher reads and CPU | Plan choice and estimates are plausible contributors. |
| Same plan, much more qualifying data | Workload or data growth may explain the cost. |
| Similar CPU and reads, longer elapsed time | Investigate contention and external resource waiting. |
| Fast for one parameter range, slow for another | Examine parameter sensitivity before selecting one plan for all calls. |
| Missing baseline or capture gap | State the evidence limit and collect a new controlled baseline. |
These are diagnostic directions, not verdicts produced by the table alone.
Account for parameter-sensitive behaviour
SQL Server 2022 introduced Parameter Sensitive Plan optimisation for eligible parameterised queries. At compatibility level 160, the engine can use a dispatcher and multiple variants for different cardinality ranges. Availability of the feature does not mean every query is eligible or that every parameter-related problem is solved.2
Record compatibility level and relevant scoped settings. Do not transfer enhancements documented for SQL Server 2025 and level 170 into a SQL Server 2022 explanation. In particular, the source describes additional statement and predicate support in the later release.2
When a query has variants, inspect their relationship rather than treating every additional plan as accidental instability. A plan that performs well for a small result set may be unsuitable for a broad search. Preserve representative parameter categories in the test design without publishing sensitive values.
Choose a bounded response
Plan forcing is a supported option for directing a query towards a retained plan and can address a plan regression.1 It should follow identification of the exact database, query and plan, and evidence that the chosen plan is acceptable for the required workload range.
Before approving it, record the current forcing state, the proposed plan, expected benefit, known limitations and removal criteria. A DBA should verify the operation's result and subsequent execution evidence; a successful request to force a plan is not a measured performance result. Monitor for forcing failures and for regressions in parameter ranges not represented by the original complaint.
An alternative may be a scoped query, statistics or index correction. Change one explanation at a time where practical. Clearing the entire plan cache, forcing every historical plan or changing server memory simultaneously destroys the ability to attribute improvement and may affect unrelated workloads.
Verify recovery and preserve a return path
Define acceptance before changing anything: business completion, latency distribution, resource use, execution failures and the parameter ranges that matter. Test a proposed query or schema correction in an authorised disposable environment first. No replay or performance measurement was carried out for this article.
After an approved production change, compare like-for-like intervals and the actual application outcome. Keep the previous forcing/configuration state and normal application rollback route documented. Removing a forced plan returns plan choice to the optimiser; it does not guarantee the next plan will equal the original baseline.
Retain a short incident conclusion that distinguishes demonstrated facts from interpretation. For example, an observed plan change and increased reads can support a plan-regression explanation; the claim that a particular statistics event caused it needs its own evidence.
Learning notes
- Query Store history is useful only for intervals and queries actually captured.
- Execution counts, units and aborted outcomes affect the comparison.
- A successful response must fit the workload range, not just one convenient parameter.
Acceptance checklist
- [ ] Capture mode, cleanup policy, retained intervals and permission limits are recorded.
- [ ] Baseline and incident comparisons use consistent units, execution types and weighted counts.
- [ ] Plan variants and representative parameter ranges are included.
- [ ] Any approved response meets business and resource criteria, with forcing failures and rollback limits recorded.
References
- Monitor Performance by Using the Query Store - SQL Server | Microsoft Learn. Accessed 2026-11-01.
- Parameter Sensitive Plan Optimization - SQL Server | Microsoft Learn. Accessed 2026-11-01.
- sys.query_store_runtime_stats (Transact-SQL) - SQL Server | Microsoft Learn. Accessed 2026-11-01.
- sys.database_query_store_options (Transact-SQL) - SQL Server | Microsoft Learn. Accessed 2026-11-01.