Query Store Explained: Why, How, and Its Trade-offs

Why Query Store Exists

Before Query Store shipped in SQL Server 2016, performance history lived in the plan cache, and the plan cache was never designed to be a history. It shows what is currently compiled, nothing more. A service restart, a memory pressure eviction, or a simple DBCC FREEPROCCACHE and the evidence for "why did this get slow" is gone. Third-party monitoring tools filled part of the gap by polling and storing snapshots externally, but that meant another moving part, another agent, and data that started only from the day you installed it.

Query Store closes that gap from inside the engine itself. It persists plan and runtime history to disk, per database, so the data survives restarts, cache evictions, and failovers, and it needs nothing external to collect it. The motivating question was always the same one DBAs kept asking after every deployment: "it was fine yesterday, what changed?" Query Store is Microsoft's answer: keep the receipts automatically, so that question has a factual answer instead of a guess.

How It Actually Works

Enabling Query Store is a database-level setting:

ALTER DATABASE [YourDatabase] SET QUERY_STORE = ON;

Once on, three things happen for every query that executes against that database:

The query text is captured and normalized. Literal values are stripped out so the same query shape, run with different parameters, is tracked as a single entity rather than a thousand near-duplicates.
Every distinct execution plan for that query is stored, not just the latest one. If the optimizer compiles three different plans for the same query over a month, all three stay on record with their own identity.
Runtime statistics are aggregated per plan, per time interval. Duration, CPU time, logical reads, memory grant, execution count: bucketed into windows that default to one hour, controlled by INTERVAL_LENGTH_MINUTES.

This is a separate write path from the plan cache. The plan cache still exists and still drives execution; Query Store is a durable record sitting alongside it, populated asynchronously so it does not sit directly in the critical path of every query. That asynchronous write is itself tunable: DATA_FLUSH_INTERVAL_SECONDS controls how often in-memory Query Store data is flushed to disk, trading a small window of potential data loss on a crash against fewer disk writes.

The Capture Modes

Not every query needs to be tracked forever, so Query Store has a capture policy:

Mode	Behavior
`ALL`	Every query is captured, including one-off ad hoc statements. Most complete picture, most overhead and storage.
`AUTO` (default)	Infrequent or cheap queries are ignored until they cross an internal resource threshold; then they start being tracked. Good default for most workloads.
`CUSTOM`	You define the thresholds yourself via `QUERY_CAPTURE_POLICY` settings (execution count, CPU time, etc.), for workloads where `AUTO` is either too noisy or misses something you care about.
`NONE`	Stop capturing new queries, but keep tracking runtime stats and forced plans for what is already there.

Catching a Plan Regression

This is the piece that makes plan regressions provable instead of anecdotal: you can show, with actual numbers, that a query's plan averaged 40 ms before Tuesday's deployment and a different plan for the same query has averaged 4 seconds since.

The built-in "Regressed Queries" report (SSMS, under the database's Query Store node) plots exactly this: duration per plan over time, sorted by how much worse things got. No custom monitoring, no waiting for the next complaint, the data was already being collected.

-- Find the plan and query IDs from the regression report, or query directly:
SELECT q.query_id, p.plan_id, p.is_forced_plan, rs.avg_duration
FROM sys.query_store_query      AS q
JOIN sys.query_store_plan       AS p  ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id
WHERE q.query_id = @QueryId
ORDER BY rs.avg_duration DESC;

-- Force the known-good plan back
EXEC sp_query_store_force_plan @query_id = @QueryId, @plan_id = @GoodPlanId;

-- Later, once the underlying cause is fixed, release it
EXEC sp_query_store_unforce_plan @query_id = @QueryId, @plan_id = @GoodPlanId;

Forcing a plan is immediate and requires no code deployment, no application restart, just the plan ID. This is exactly the mechanism referenced as a fix in Parameter Sniffing: When Good Plans Go Bad, once you know which cached plan behaves acceptably across your actual data distribution, Query Store is what lets you pin it there.

Advantages

History survives what used to erase it. Restarts, failovers, and cache evictions no longer take the evidence with them.
Regressions become provable, not anecdotal. Duration per plan, per time bucket, with numbers, not a feeling that "it seems slower since the deployment."
Plan forcing needs no code deployment. sp_query_store_force_plan pins a known-good plan back in seconds, independent of application release cycles.
It is built in. No agent to install, no external database to maintain, no license to buy; the data lives in the user database itself and moves with it on backup/restore.
It feeds other tooling. Query Store data underpins Automatic Plan Correction (Enterprise Edition) and the SSMS regression reports out of the box, and is queryable directly for custom dashboards.

Disadvantages

It is not free. Capturing plans and runtime stats has real, if usually small, CPU and I/O overhead. On extremely high-throughput OLTP systems with huge numbers of distinct query shapes, that overhead is worth measuring, not assuming away.
It can fill up and go read-only. If MAX_STORAGE_SIZE_MB is reached and cleanup cannot keep pace, Query Store flips to read-only mode and stops recording new data, silently, until space is freed. This is the single most common Query Store surprise in production.
Forced plans are not guaranteed forever. A dropped index or a schema change a forced plan depended on makes the force fail; SQL Server falls back to compiling fresh, and unless someone is watching is_forced_plan and the failure reason column, that fallback goes unnoticed.
It is not retroactive. History only starts the moment QUERY_STORE = ON is set. Turn it on after the incident and there is nothing to look back at.
Query text capture has a privacy angle. Query Store stores literal-free query text and plan XML, which can still include object and column names; on databases with contractual or regulatory data-handling requirements, that is worth a line in the review before enabling it broadly.

Sizing and Retention, Briefly

ALTER DATABASE [YourDatabase] SET QUERY_STORE
(
    OPERATION_MODE = READ_WRITE,
    MAX_STORAGE_SIZE_MB = 2000,
    CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 30 ),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    QUERY_CAPTURE_MODE = AUTO,
    SIZE_BASED_CLEANUP_MODE = AUTO
);

SIZE_BASED_CLEANUP_MODE = AUTO lets Query Store age out the oldest data itself once it nears the storage cap, rather than flipping to read-only. Setting a generous MAX_STORAGE_SIZE_MB up front and checking sys.database_query_store_options for actual_state periodically catches the read-only surprise before it costs you the exact history you turned Query Store on to keep.

The Bottom Line

Query Store trades a small, measurable, ongoing cost for a durable answer to "what changed and when." For the vast majority of databases that overhead is worth it the first time a deployment regression takes two minutes to diagnose instead of a war room. The feature earns its keep by being on before you need it; turned on mid-incident, it has nothing yet to show you.

← Back to Blog