Many SQL Server performance incidents are over before a database administrator can connect. A report stalls at 9:05, the application pool recycles at 9:20, and by 10:00 the query is fast again. The plan cache alone is a weak historical record because entries can be evicted, recompiled, or lost at restart. Query Store provides a durable history of query text, plans, and aggregated runtime behavior inside the database.
What Query Store can answer
Query Store helps determine which queries consumed resources during a specific interval, whether a query changed plans, and how performance differed between those plans. It can reveal a regression after a deployment, a plan that works for one parameter pattern but not another, or a workload that gradually became more expensive without a plan change.
It is not a complete monitoring platform. Runtime values are aggregated into intervals, and the feature does not replace session-level evidence for a live blocking chain. It also cannot explain an application timeout that never reached SQL Server. Use it alongside job history, application telemetry, and operating-system resource data.
Configure it as an operational system
Turning Query Store on with defaults and forgetting it is not enough. Review the capture policy, maximum storage, cleanup behavior, runtime statistics interval, and retention period against the database workload. An ad hoc system generating large amounts of unique SQL text needs different settings from an application using stable parameterized statements.
- Retain enough history to cover month-end, weekly processing, and other meaningful business cycles.
- Monitor actual Query Store size and read-only state so collection does not stop unnoticed.
- Exclude or reduce capture of low-value, one-time statements when they crowd out important history.
- Confirm that restores, replicas, and support procedures treat Query Store data as intended.
Configuration should be tested under the real workload. Collection has overhead, though sensible capture and retention settings usually make it manageable. The objective is useful evidence, not indefinite storage of every statement ever submitted.
Start with the incident window
When investigating, define the affected operation, database, and UTC time window before sorting any report. Broad lists of the highest total CPU queries often surface routine background work rather than the incident. Compare the bad interval with a known good interval and examine duration, CPU, logical reads, execution count, row count, and waits where the SQL Server version exposes them.
Total resource use and per-execution resource use answer different questions. A moderately expensive query executed very frequently can consume the server. A single query with extreme duration may explain one user complaint while having little system-wide impact. Keep both perspectives visible.
Compare plan history carefully
A plan change close to the regression is strong evidence, but timing alone is not proof. Open the competing plans and compare estimates, actual behavior if separately captured, join algorithms, access methods, memory grants, spills, parallelism, and lookup volume. Then relate those differences to parameter values, statistics changes, indexes, and data distribution.
| Observation | Next check |
|---|---|
| One plan is consistently slower | Statistics, indexes, optimizer choice, and safe plan correction |
| Each plan wins for different inputs | Parameter sensitivity and workload segmentation |
| The plan is unchanged but reads increased | Data growth, predicate selectivity, and result volume |
| Duration rose while CPU stayed similar | Blocking, storage latency, memory grants, or external waits |
Use plan forcing as a stabilizer
Query Store can force a known plan, which is valuable when a severe regression needs quick containment. Treat forcing as a controlled operational change. Document the reason, owner, expected benefit, validation measurements, and review date. Watch for force failures and for workload changes that make the selected plan unsuitable.
A forced plan does not repair stale statistics, a missing access path, parameter-sensitive design, or a query that requests unnecessary data. Once the incident is stable, diagnose and correct the underlying cause. Then test whether forcing can be removed. Query Store hints can influence behavior without changing application text, but they deserve the same change control and follow-up.
Build a repeatable regression workflow
- Record the user-visible symptom and exact time range.
- Find queries whose runtime behavior changed in that range.
- Separate execution-count changes from per-execution regressions.
- Compare plans and relevant wait categories.
- Apply the least risky stabilizing action.
- Validate against normal and edge-case parameters.
- Implement the durable query, statistics, index, or application correction.
Query Store is most useful when it turns a vague report that the database was slow into a named query, a time interval, a plan history, and a testable cause.
Review regressions routinely instead of waiting for a crisis. A weekly examination of newly expensive or highly variable queries can expose a release problem before it becomes an outage. Preserve screenshots or exported facts in the incident record, but keep Query Store itself maintained so the next investigation begins with evidence rather than recollection.