Catch SQL Server Plan Regressions Early with Query Store

A query that ran in 200 milliseconds yesterday now takes eight seconds. Nothing in the application changed, but the execution plan did. Without history, you are left comparing vague memories, clearing the plan cache, and hoping the problem does not return during the next business day. Query Store changes that by keeping a persistent, per-database record of query text, execution plans, and runtime statistics so you can see exactly when performance shifted and which plan the optimizer chose.

Query Store is not a performance silver bullet. It is closer to a flight recorder: it gives you evidence, but you still have to interpret it. Used deliberately, it turns plan regressions from guesswork into a short investigation.

The plan regression you cannot see

An execution plan is the set of steps SQL Server uses to retrieve data. The optimizer picks a plan based on statistics, indexes, and cardinality estimates. When those inputs change—after a large data load, a statistics update, or a parameter sniffing event—the optimizer may choose a different plan that is worse for your workload.

The plan cache is volatile. It clears on restart, under memory pressure, or when you run DBCC FREEPROCCACHE. If the slow plan appears and then disappears, you have no way to compare it with the fast plan from yesterday. Query Store solves this by writing the plans and their runtime measurements to internal tables inside the same user database, so the history survives restarts and cache clears.

What Query Store captures and where it stores it

When enabled, Query Store automatically collects four things for every query executed in the database:

• Query text and a normalized query identifier.
• Execution plans, including the XML representation and compile-time details.
• Runtime statistics such as CPU time, duration, logical reads, and writes, aggregated over a configurable interval.
• Wait statistics that show where queries spent time, such as PAGEIOLATCH_SH or LCK_M_S.

All of this lives in internal tables within the database itself. That means the data travels with database backups and is replicated to Always On Availability Group secondaries, though with an important restriction covered later.

Version assumptions

Query Store first appeared in SQL Server 2016 (13.x). Later releases added management options that matter for production sizing.

Release	Relevant Query Store capability
SQL Server 2016 (13.x)	Initial capture of queries, plans, and runtime statistics; plan forcing available.
SQL Server 2019 (15.x)	Read-only operation mode, size-based cleanup, and custom retention policies.
SQL Server 2022 (16.x)	Further enhancements to capture behavior and management options.

Enabling Query Store: commands and settings

Enable Query Store with a single ALTER DATABASE statement. Run it in the target database context or in master; it requires ALTER permission on the database.

ALTER DATABASE [SalesDB] SET QUERY_STORE = ON;

Immediately verify the configuration by querying the options view in the same database:

SELECT operation_mode,
       max_storage_size_mb,
       current_storage_size_mb,
       interval_length_minutes,
       size_based_cleanup_mode
FROM sys.database_query_store_options;

Three settings deserve attention before you leave the default. MAX_STORAGE_SIZE_MB caps the space Query Store may consume; when the limit is reached, older data is purged. INTERVAL_LENGTH_MINUTES controls how runtime statistics are aggregated—smaller intervals give finer granularity but more rows. SIZE_BASED_CLEANUP_MODE, available from SQL Server 2019 onward, lets the engine automatically delete the least expensive data first when space runs low.

Worked example: from regression to forced plan

Suppose a reporting query in SalesDB suddenly slows down after a statistics update. After enabling Query Store and letting a representative workload run, confirm that capture is working. You should see rows for your query:

SELECT q.query_id,
       p.plan_id,
       p.is_forced_plan,
       p.last_compile_start_time
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p
  ON q.query_id = p.query_id
WHERE q.query_sql_text LIKE '%MonthlySalesSummary%';

Compare the runtime statistics across captured plans using sys.query_store_runtime_stats or the built-in SQL Server Management Studio reports. If you identify a previously fast plan and a newly compiled slow plan, you can force the good plan while you investigate the root cause. Run this in SalesDB; it requires ALTER permission on the database.

EXEC sp_query_store_force_plan
    @query_id = 12,
    @plan_id = 34;

Verify the force took effect:

SELECT plan_id, is_forced_plan
FROM sys.query_store_plan
WHERE plan_id = 34;

Expected check: is_forced_plan returns 1. Monitor the query for at least one full business cycle. If the underlying data distribution has changed, the forced plan may now be the wrong choice. Remove the force when the underlying issue is fixed:

EXEC sp_query_store_unforce_plan
    @query_id = 12,
    @plan_id = 34;

Trade-offs: storage, forced plans, and replicas

Query Store is not free. Typical overhead is low—often cited as under five percent—but that depends on query volume and retention. A busy OLTP database with hundreds of distinct queries per second can generate significant internal table growth if retention is generous. Watch current_storage_size_mb against max_storage_size_mb and adjust the interval or cleanup policy before the store fills.

Plan forcing is a bridge, not a destination. A forced plan tells the optimizer to stop adapting. If the data volume or distribution changes, the forced plan can become the bottleneck. Review forced plans regularly and remove them once the statistics, indexes, or query code are corrected.

In an Always On Availability Group, Query Store on a readable secondary is read-only. You cannot change settings or force plans on the secondary; those operations must run on the primary replica.

What to do next

Pick one critical production database and enable Query Store this week. Set an explicit MAX_STORAGE_SIZE_MB rather than relying on defaults, then check sys.database_query_store_options after the first week to see actual consumption. Treat any forced plan as temporary scaffolding, and document why it was applied so the next engineer does not inherit a mystery.