A stored procedure that returned in 40 milliseconds on Friday takes 9 seconds on Monday. Nobody deployed. Nobody changed an index. The plan cache has rolled over twice, so sys.dm_exec_query_stats shows the current disaster and nothing about the version that worked. Query Store is where you find the answer to that one question: what changed, and when.
Most write-ups stop at "turn it on and open the Top Resource Consuming Queries report." That gets you a dashboard, not a Query Store that survives eight weeks of production traffic without dropping into read-only, and it says nothing about what to do when a forced plan stops working without telling you.
Turning it on, with the options that matter
Query Store is the second stop, not the first: start from wait statistics before you open Query Store, and come back here when the numbers say a plan moved rather than that the server is busy. The defaults changed between versions, and the version you inherited decides how much trouble you are in.
| Option | 2016 / 2017 | 2019 | 2022 |
|---|---|---|---|
| Query Store enabled | Off | Off | On for new databases |
MAX_STORAGE_SIZE_MB |
100 | 1000 | 1000 |
QUERY_CAPTURE_MODE |
ALL | AUTO | AUTO |
SIZE_BASED_CLEANUP_MODE |
AUTO | AUTO | AUTO |
STALE_QUERY_THRESHOLD_DAYS |
30 | 30 | 30 |
INTERVAL_LENGTH_MINUTES |
60 | 60 | 60 |
DATA_FLUSH_INTERVAL_SECONDS |
900 | 900 | 900 |
MAX_PLANS_PER_QUERY |
200 | 200 | 200 |
SQL Server 2022 turns Query Store on for newly created databases in every edition. A database restored from an older instance, or carried through an in-place upgrade, keeps whatever settings it already had. A 2022 instance hosting a restored 2016 database can be running at a 100MB cap with QUERY_CAPTURE_MODE = ALL, which is the worst combination the feature offers.
The configuration I put on a new production database on 2019 or later:
ALTER DATABASE [YourDatabase]
SET QUERY_STORE = ON
(
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 60 ),
DATA_FLUSH_INTERVAL_SECONDS = 900,
MAX_STORAGE_SIZE_MB = 2048,
INTERVAL_LENGTH_MINUTES = 30,
SIZE_BASED_CLEANUP_MODE = AUTO,
QUERY_CAPTURE_MODE = AUTO,
MAX_PLANS_PER_QUERY = 200,
WAIT_STATS_CAPTURE_MODE = ON
);
What each one buys you:
MAX_STORAGE_SIZE_MB = 2048 because the 2019 default of 1000 covers a moderate OLTP database and nothing bigger. Query Store keeps its data inside the user database, so this space comes out of your data file and travels with your backups. Microsoft's guidance is that a workload wanting more than 10GB should first be re-examined for plan reuse, or have its Query Store configuration tightened.
STALE_QUERY_THRESHOLD_DAYS = 60 covers two month-end cycles. At 30 the January close falls out before you can compare it to February.
INTERVAL_LENGTH_MINUTES = 30 halves the granularity blur. Runtime statistics aggregate into one row per plan per interval, so at 60 minutes a regression starting at 10:50 gets averaged with fifty good minutes. Arbitrary values are rejected: the allowed set is 1, 5, 10, 15, 30, 60, and 1440.
DATA_FLUSH_INTERVAL_SECONDS = 900 stays at the default. It is both how long data sits in memory before it hits disk and the cadence at which Query Store checks its own size.
MAX_PLANS_PER_QUERY = 200 suits parameterized code. Once a query reaches the limit, Query Store stops capturing new plans for it. The sys.database_query_store_options page documents 0 as removing the limitation, which on a query with unstable parameterization means unbounded plan growth, so raise it to a number instead.
WAIT_STATS_CAPTURE_MODE = ON, available from 2017, gives you per-query wait categories: the difference between "the query got slower" and "the query spent its time on PAGEIOLATCH_SH."
OPERATION_MODE = READ_WRITE is the only mode that collects. READ_ONLY still serves the reports, so a Query Store stuck there looks alive in SSMS while collecting nothing new.
QUERY_CAPTURE_MODE decides whether this works at all
Four values, and the one you inherit is the one most likely to fill the store.
ALL captures every query, and it is the default on 2016 and 2017. Where the application concatenates predicates into the SQL text, every distinct text becomes a new query_id with its own plan. A reporting front end that builds filter clauses from checkboxes produces tens of thousands of distinct texts in a day, which against a 100MB cap fills the store before the first afternoon. After that the store churns: cleanup deleting rows, capture writing new ones, neither winning. Microsoft's diagnostic is a ratio. Run SELECT COUNT(*), COUNT(DISTINCT query_hash) FROM sys.query_store_query; and if the two numbers are close, nearly every captured entry has a distinct shape, which is what an ad hoc workload looks like from inside the store.
AUTO is the default from 2019 and the right answer for most servers. It drops ad hoc queries that miss all three thresholds over the evaluation window: 30 executions, 1000ms of total compile CPU, 100ms of total execution CPU. An ad hoc statement that ran once for 40ms never enters the store. The thresholds do not apply to everything: cursors, statements inside stored procedures, and natively compiled queries are always captured under ALL, AUTO and CUSTOM alike, so AUTO will not thin out a procedure-driven workload.
NONE stops capturing new queries and keeps collecting runtime statistics for the ones already captured. Two honest uses: a benchmark with a fixed query set, and a vendor shipping a configuration tuned to their own workload. Everywhere else it means you stop seeing the query that ships with the next release.
CUSTOM arrived in SQL Server 2019 and moves the AUTO thresholds. Use it when AUTO still captures more than your storage budget allows:
ALTER DATABASE [YourDatabase]
SET QUERY_STORE = ON
(
OPERATION_MODE = READ_WRITE,
QUERY_CAPTURE_MODE = CUSTOM,
QUERY_CAPTURE_POLICY = (
STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
EXECUTION_COUNT = 30,
TOTAL_COMPILE_CPU_TIME_MS = 1000,
TOTAL_EXECUTION_CPU_TIME_MS = 100
)
);
Raise EXECUTION_COUNT and TOTAL_EXECUTION_CPU_TIME_MS in small steps, checking current_storage_size_mb after each change. Every increment is a class of query you have chosen to stop seeing.
The READ_ONLY failure mode, stated correctly
A claim you will read often: Query Store flips to READ_ONLY when it hits MAX_STORAGE_SIZE_MB, so raise the cap. That is half the mechanism, and it produces the wrong fix.
SIZE_BASED_CLEANUP_MODE = AUTO is the default. It triggers cleanup at 90% of MAX_STORAGE_SIZE_MB, deletes the least expensive and oldest queries first, and stops near 80%. With cleanup on and a workload that fits, the store never reaches the limit. The cap is also not checked on every write: Query Store checks its size when it flushes to disk, on the DATA_FLUSH_INTERVAL_SECONDS cadence. Breach it between two of those checks and the store goes read-only, cleanup fires in the same pass, and the store returns to read-write once cleanup frees enough room.
A database that sits in READ_ONLY for days has one of two problems. Either SIZE_BASED_CLEANUP_MODE is OFF, in which case turn it on. Or cleanup cannot keep up, because the workload writes new query texts faster than cleanup deletes old ones, which is the ad hoc problem again. Raising MAX_STORAGE_SIZE_MB there buys a few more days and a bigger store to scan.
Size is one of eight reasons. readonly_reason is a bitmap:
| Value | Cause |
|---|---|
| 1 | Database is read-only |
| 2 | Database is in single-user mode |
| 4 | Database is in emergency mode |
| 8 | Database is a secondary replica |
| 65536 | Reached max_storage_size_mb |
| 131072 | Distinct statement count hit an internal memory limit |
| 262144 | In-memory items awaiting persistence hit a memory limit (temporary) |
| 524288 | Database reached its disk size limit |
It is a bitmap, so a database can return 65544 for two causes at once. Test for the bit rather than for equality: readonly_reason & 65536. Bit 8 on a readable secondary is expected. Bit 524288 means the data file is full. Bit 65536 is the only one that raising the cap addresses.
Finding the regression
This is the query that earns Query Store its keep. It aggregates runtime statistics for every plan of every query across two windows, recent and baseline, and returns only queries with more than one plan.
Three details keep it honest. avg_duration is in microseconds, so divide by 1000 to get milliseconds. start_time on the interval view is datetimeoffset, and it holds UTC in practice, so compare against SYSUTCDATETIME() rather than GETDATE() or you skew every window by the server's offset. Microsoft documents the type and says nothing about the zone, so confirm it on your instance if a window looks shifted. And for the interval still open, a plan can have several rows, some flushed to disk and some in memory, so sum across rows instead of reading one row per plan per interval.
USE [YourDatabase];
GO
DECLARE @recent_hours int = 4;
DECLARE @baseline_days int = 14;
WITH raw_stats AS (
SELECT rs.plan_id,
CASE WHEN rsi.start_time >= DATEADD(HOUR, -@recent_hours, SYSUTCDATETIME())
THEN 1 ELSE 0 END AS is_recent,
rs.count_executions,
rs.avg_duration,
rs.avg_logical_io_reads
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_runtime_stats_interval AS rsi
ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE rs.execution_type = 0
AND rsi.start_time >= DATEADD(DAY, -@baseline_days, SYSUTCDATETIME())
),
agg AS (
SELECT p.query_id,
s.plan_id,
p.is_forced_plan,
s.is_recent,
SUM(s.count_executions) AS executions,
SUM(s.avg_duration * s.count_executions)
/ NULLIF(SUM(s.count_executions), 0) / 1000.0 AS avg_duration_ms,
SUM(s.avg_logical_io_reads * s.count_executions)
/ NULLIF(SUM(s.count_executions), 0) AS avg_logical_reads
FROM raw_stats AS s
JOIN sys.query_store_plan AS p
ON p.plan_id = s.plan_id
GROUP BY p.query_id, s.plan_id, p.is_forced_plan, s.is_recent
)
SELECT a.query_id,
a.plan_id,
a.is_forced_plan,
CASE a.is_recent WHEN 1 THEN 'recent' ELSE 'baseline' END AS window_label,
a.executions,
CAST(a.avg_duration_ms AS decimal(18, 2)) AS avg_duration_ms,
CAST(a.avg_logical_reads AS decimal(18, 1)) AS avg_logical_reads,
qt.query_sql_text
FROM agg AS a
JOIN sys.query_store_query AS q
ON q.query_id = a.query_id
JOIN sys.query_store_query_text AS qt
ON qt.query_text_id = q.query_text_id
WHERE a.query_id IN (
SELECT query_id
FROM agg
GROUP BY query_id
HAVING COUNT(DISTINCT plan_id) > 1
)
ORDER BY a.query_id, a.is_recent DESC, a.avg_duration_ms DESC;
A result shape I have seen more than once, from an order-history procedure on a 2019 instance:
| query_id | plan_id | window_label | executions | avg_duration_ms | avg_logical_reads |
|---|---|---|---|---|---|
| 8842 | 19104 | baseline | 61,230 | 38.11 | 412.0 |
| 8842 | 22871 | recent | 2,904 | 8,907.44 | 1,910,338.0 |
Plan 22871 is the nested loop the optimizer chose after a statistics update, when a recompile landed on a customer with three orders. Plan 19104 is the hash join that serves the customer with 2.1 million rows. Both suit the parameter that compiled them, and only one survives the whole workload. The reads column is the tell: 1.9 million against 412 is a different access path, not a tuning difference.
Forcing a plan, and the failure it does not tell you about
EXECUTE sys.sp_query_store_force_plan
@query_id = 8842,
@plan_id = 19104;
Forcing does not replay a stored plan. It compiles the query again and steers the optimizer toward the shape of the plan you named. Microsoft's wording is that the result is the same or similar, and that performance between the two can differ.
The part that costs people weeks: when SQL Server cannot produce the forced plan it fires an Extended Event, optimizes the query the normal way, and leaves the forcing in place. Nothing clears, and the application sees no error. Six months later somebody drops the index the plan referenced, forcing fails on every recompile, and the query goes back to whatever the optimizer picks. is_forced_plan still reads 1.
Audit it on a schedule:
SELECT p.plan_id,
p.query_id,
q.object_id,
OBJECT_NAME(CAST(q.object_id AS int)) AS containing_object,
p.plan_forcing_type_desc,
p.force_failure_count,
p.last_force_failure_reason_desc,
p.last_execution_time,
qt.query_sql_text
FROM sys.query_store_plan AS p
JOIN sys.query_store_query AS q
ON q.query_id = p.query_id
JOIN sys.query_store_query_text AS qt
ON qt.query_text_id = q.query_text_id
WHERE p.is_forced_plan = 1
ORDER BY p.force_failure_count DESC, p.last_execution_time DESC;
force_failure_count increments on recompile, not on every execution, so a small number on a seldom-recompiled query still means the forcing is broken. last_force_failure_reason_desc names the cause: NO_INDEX for an index the plan needs and no longer finds, NO_PLAN when the optimizer cannot verify the forced plan as valid for the query, HINT_CONFLICT when someone added a hint the plan contradicts, NO_DB after a database rename, which breaks every forced plan at once because plans reference objects by database.schema.object. plan_forcing_type_desc (2017 and later) separates MANUAL from AUTO, so you can tell your forcing from automatic plan correction's. Add the query_store_plan_forcing_failed Extended Event for a live view.
Forcing pins a plan. It does not repair what made the optimizer choose the other one. When the plan flipped because a histogram went stale, the forced plan is a lid on the estimate problem a forced plan only hides, and it will hold until the data shifts far enough that your pinned plan is the wrong one. Force to stop the bleeding, then go fix the estimate.
Remove it with EXECUTE sys.sp_query_store_unforce_plan @query_id = 8842, @plan_id = 19104;
Query Store hints, for code you cannot edit
Query Store hints landed in SQL Server 2022 and run in every edition, including Express. They attach a query hint to a statement by query_id and leave the text alone, which for a vendor application whose support contract forbids changing the SQL is the difference between a fix and a ticket.
EXECUTE sys.sp_query_store_set_hints
@query_id = 8842,
@query_hints = N'OPTION (MAXDOP 1, MAX_GRANT_PERCENT = 15)';
Query Store hints override hard-coded statement hints and plan guides, and hints you create by hand are exempt from Query Store cleanup. Supported: RECOMPILE, MAXDOP, MAX_GRANT_PERCENT, MIN_GRANT_PERCENT, FORCE ORDER, the join and union hints, OPTIMIZE FOR UNKNOWN, PARAMETERIZATION SIMPLE | FORCED, NO_PERFORMANCE_SPOOL, and USE HINT. Note the shape of that list: OPTIMIZE FOR UNKNOWN works, OPTIMIZE FOR (@var = value) does not. Also out are MAXRECURSION, USE PLAN, and every table hint, so no FORCESEEK and no INDEX.
Double the inner single quotes when you pass USE HINT:
EXECUTE sys.sp_query_store_set_hints
@query_id = 8842,
@query_hints = N'OPTION (USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';
Hints fail the same way forced plans do. sys.query_store_query_hints carries query_hint_failure_count and last_query_hint_failure_reason_desc, so audit it alongside the forced plans. Two limits before you promise a fix: hints do not apply to queries that qualify for simple parameterization, and RECOMPILE conflicts with database-level forced parameterization, which raises warning 12461.
Clear one with EXECUTE sys.sp_query_store_clear_hints @query_id = 8842;
Automatic plan correction, and who can use it
FORCE_LAST_GOOD_PLAN arrived in SQL Server 2017. It watches for plan choice regressions, forces the previous plan, verifies the forcing helped, and unforces if it did not.
ALTER DATABASE [YourDatabase]
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );
Automatic tuning is Enterprise Edition only, in both the 2022 and 2025 editions matrices. Query Store itself and Query Store hints run on every edition including Express. On Standard, plan forcing is a manual job and the audit query above is not optional. It is worth knowing which side of that line you are on before you plan the work, because automatic plan correction and the edition it needs is one of several tuning features that stop at Standard.
On Enterprise the recommendations are there to read even with FORCE_LAST_GOOD_PLAN off. Microsoft does not document whether the DMV populates on Standard, and Kendra Little's test on a Standard instance produced no recommendations at all, so verify it on your own box before you build a process around it:
SELECT reason,
score,
script = JSON_VALUE(details, '$.implementationDetails.script'),
d.query_id,
d.regressedPlanId,
d.recommendedPlanId,
estimated_gain = (d.regressedPlanExecutionCount + d.recommendedPlanExecutionCount)
* (d.regressedPlanCpuTimeAverage - d.recommendedPlanCpuTimeAverage) / 1000000
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON (details, '$.planForceDetails')
WITH ( [query_id] int '$.queryId',
regressedPlanId int '$.regressedPlanId',
recommendedPlanId int '$.recommendedPlanId',
regressedPlanExecutionCount int,
regressedPlanCpuTimeAverage float,
recommendedPlanExecutionCount int,
recommendedPlanCpuTimeAverage float
) AS d;
estimated_gain is in seconds, and Microsoft's threshold for acting is above 10. This DMV does not survive an engine restart, so a recommendation you meant to read on Monday is gone if the instance bounced over the weekend.
Query Store on secondary replicas
Query Store for readable secondary replicas is in preview across every SQL Database Engine platform. SQL Server 2025 is the first release where it is available, off by default per database. SQL Server 2022 has it in limited preview behind trace flag 12606, which Microsoft states is not for production, and both the 2022 and 2025 editions matrices list the feature as Enterprise only.
On 2025 you enable it from the primary:
ALTER DATABASE [YourDatabase]
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
ALTER DATABASE [YourDatabase]
FOR SECONDARY
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
Secondary replicas stream execution data to the primary, where it persists and becomes visible from every replica. Connect to the secondary and actual_state_desc should read READ_CAPTURE_SECONDARY with readonly_reason of 8. sys.query_store_replicas gives the replica names, sys.query_store_plan_forcing_locations shows which plans are forced where, and sp_query_store_force_plan takes a @replica_group_id to scope the forcing. An Always On availability group has to exist first, and SSMS before version 21 flags the valid FOR SECONDARY syntax as an IntelliSense error.
Checking on Query Store itself
Put this on a schedule and alert on it. A Query Store that stopped collecting looks like a working one until you go looking for last Tuesday.
SELECT DB_NAME() AS database_name,
desired_state_desc,
actual_state_desc,
readonly_reason,
current_storage_size_mb,
max_storage_size_mb,
CAST(current_storage_size_mb * 100.0
/ NULLIF(max_storage_size_mb, 0) AS decimal(5, 1)) AS pct_used,
interval_length_minutes,
stale_query_threshold_days,
size_based_cleanup_mode_desc,
query_capture_mode_desc
FROM sys.database_query_store_options;
desired_state_desc is what you asked for and actual_state_desc is what you have. A gap means the engine changed the mode on its own, and readonly_reason names the cause.
actual_state of 3 is the ERROR state, which points at on-disk corruption. Set read-write again and re-read the view. If it stays in error, 2017 and later give you a repair path, and Query Store has to be off before you run it:
USE [YourDatabase];
GO
ALTER DATABASE [YourDatabase] SET QUERY_STORE = OFF;
EXECUTE sp_query_store_consistency_check;
ALTER DATABASE [YourDatabase] SET QUERY_STORE = ON;
ALTER DATABASE [YourDatabase] SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
On 2016 the only route is SET QUERY_STORE CLEAR followed by setting read-write again, and the history is gone.
A store that stayed read-write through a whole maintenance cycle is also the cheapest evidence you have for proving a maintenance window changed anything. Capture the runtime statistics for your top queries before the window and read them after, and the argument about whether the job was worth the log volume stops being an argument.
What to stop doing
Stop leaving QUERY_CAPTURE_MODE = ALL on an inherited 2016 or 2017 database. It was the default and it is wrong for any workload with ad hoc volume. Move it to AUTO.
Stop treating a bigger MAX_STORAGE_SIZE_MB as the fix for READ_ONLY. Read readonly_reason first. Only 65536 is a size problem, and the cause there is often plan reuse.
Stop forcing a plan and walking away. Forcing fails without a sound and stays in place. If nothing in your monitoring reads force_failure_count, you do not know whether your fixes are live.
Stop turning Query Store off after an incident. The instinct to disable it under load dates to the 2016 and 2017 era, and Microsoft shipped scalability fixes for exactly that: 2016 SP2 CU2 for spinlock contention, and 2016 SP2 CU15, 2017 CU23, and 2019 CU9 for memory use under heavy ad hoc load. Patch first, then reconsider.
The order to work in when a plan has gone bad
- Run the health query against every user database. Find the ones off, in
READ_ONLY, or in ERROR. - Fix the capture mode and the cap before you collect anything. A churning store gives you a truncated history.
- Let it run for a full business cycle. A week covers weekly jobs, a month covers the close.
- Run the regression query, 4-hour recent window against a 14-day baseline. It orders by duration, so re-sort the output by the gap in logical reads, because reads separate a plan change from a busy server.
- Open both plans for the query with the largest gap and confirm the shape difference before forcing anything.
- Force the good plan, and record the
query_idandplan_idwhere your team will find them. - Add the forced-plan and hint audits to monitoring. Alert when
force_failure_countorquery_hint_failure_countmoves. - On Enterprise, once you trust the baseline, enable
FORCE_LAST_GOOD_PLANand keep readingsys.dm_db_tuning_recommendations.
If you would rather hand this off, it is the kind of work I do. See SQL Server DBA services, review client results, or get in touch.