The deploy went out at 09:20 on a Tuesday. By 09:24 the checkout API's p95 had climbed from 40 milliseconds to 2.8 seconds, and CPU on the SQL Server box was pinned at ninety percent. Nothing in the release touched the database — no migration, no index change — but one hot query had flipped its execution plan from an index seek into a clustered index scan over 60 million rows. By 09:35 I had Query Store's regressed queries report open in front of me, both plans side by side, and at 09:41 I forced the old one. Latency dropped back inside a minute. The actual fix — a stale statistics problem the deploy had merely exposed — shipped on Thursday. Query Store bought us two days of normal.
That is the deal this feature offers: it records every plan your queries ran and how each one performed, so a plan change stops being a rumor and becomes a row in a table you can diff. Everything here applies to SQL Server 2016 through 2022 and Azure SQL Database, where Query Store has been on by default for years. If you are coming from the PostgreSQL world, think of it as pg_stat_statements plus a plan history plus a hint system in one box — the closest cousin on that side is the plan analysis I described in my production health check routine.
What Query Store caught was a plan_id change with a cost delta attached. Query Store persists three things per query: the statement text, every distinct plan the optimizer produced for it, and runtime statistics bucketed into fixed collection intervals — fifteen minutes by default. So when the checkout query compiled a new plan at 09:20:47, the 09:15–09:30 interval ended up holding runtime stats for both plan 388 (the seek, 40 ms average duration, 1.2 logical reads per execution) and plan 391 (the scan, 2.8 seconds average, 900,000 logical reads per execution). Two rows, same query, one interval, and the regression is arithmetic rather than anecdote.
The important part is what did not happen: nobody had to reproduce the problem, capture an actual execution plan at exactly the right second, or guess which query out of four hundred active ones was the culprit. The evidence was already on disk because the engine had been quietly writing it down since we enabled the feature a year earlier. That is the entire argument for turning Query Store on before you need it — the day of the incident is too late to start collecting the data that exonerates or convicts.
How do I enable and size Query Store without it going read-only?
You enable it per database with ALTER DATABASE, set the capture mode to AUTO, and turn on size-based cleanup with an honest size budget. The defaults are almost right, which is exactly the trap: they work for a dev box and quietly fail in production six months later.
ALTER DATABASE CURRENT SET QUERY_STORE = ON
(
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
DATA_FLUSH_INTERVAL_SECONDS = 900,
MAX_STORAGE_SIZE_MB = 1024,
INTERVAL_LENGTH_MINUTES = 15,
SIZE_BASED_CLEANUP_MODE = AUTO,
QUERY_CAPTURE_MODE = AUTO,
MAX_PLANS_PER_QUERY = 200
);Two settings deserve the scrutiny. First, QUERY_CAPTURE_MODE. ALL records every query including one-off ad-hoc noise, which fills the store with garbage you will never query; AUTO skips queries the engine judges insignificant — tiny, compile-only, or run-once statements — and is the right default for almost everyone. Second, SIZE_BASED_CLEANUP_MODE. This is the gotcha that bites people: when Query Store hits MAX_STORAGE_SIZE_MB, it does not warn you nicely. With cleanup OFF, it flips itself to read-only — sys.database_query_store_options shows actual_state_desc = READ_ONLY and readonly_reason set — and from that moment it records nothing until you free space manually. Your flight recorder stops recording, usually without anyone noticing, because queries keep executing fine; only the history dies. With cleanup AUTO, the engine deletes the oldest and cheapest data to stay under the ceiling, which is exactly what you want. Check readonly_reason on every instance you inherit — I have found Query Stores that had been silently read-only for months on more than one occasion.
How do I find regressed queries without the GUI?
SSMS has a regressed queries report under the Query Store node, and it is genuinely good, but the same data lives in sys.query_store_runtime_stats and you can compare any two intervals yourself — which is what you want for automation and for instances where you only have a query window. The query below compares the last completed hour against the same hour yesterday and ranks by the change in average CPU.
WITH recent AS
(
SELECT rs.plan_id, rs.query_id,
AVG(rs.avg_duration) AS avg_duration_now,
AVG(rs.avg_cpu_time) AS avg_cpu_now,
SUM(rs.count_executions) AS execs_now
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_runtime_stats_interval AS i
ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE i.start_time >= DATEADD(hour, -1, SYSDATETIME())
GROUP BY rs.plan_id, rs.query_id
),
baseline AS
(
SELECT rs.plan_id, rs.query_id,
AVG(rs.avg_duration) AS avg_duration_before,
AVG(rs.avg_cpu_time) AS avg_cpu_before,
SUM(rs.count_executions) AS execs_before
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_runtime_stats_interval AS i
ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE i.start_time >= DATEADD(hour, -25, SYSDATETIME())
AND i.start_time < DATEADD(hour, -24, SYSDATETIME())
GROUP BY rs.plan_id, rs.query_id
)
SELECT r.query_id,
q.query_hash,
r.avg_cpu_now,
b.avg_cpu_before,
r.execs_now,
b.execs_before
FROM recent AS r
JOIN baseline AS b ON b.query_id = r.query_id
JOIN sys.query_store_query AS q ON q.query_id = r.query_id
WHERE r.avg_cpu_now > 4 * b.avg_cpu_before
ORDER BY (r.avg_cpu_now - b.avg_cpu_before) * r.execs_now DESC;The multiplication at the end is deliberate: a query that got ten times slower but runs twice a day ranks below a query that got four times slower and runs eight thousand times an hour. Total added cost, not the ratio alone, is what the incident feels like. One caveat on interval comparisons: pick baseline and comparison windows of equal length, and beware comparing a quiet window against a busy one — a plan that did nothing overnight looks infinitely regressed against an empty baseline. Execution counts in both windows are the sanity check.
What does forcing a plan actually do?
sp_query_store_force_plan takes a query_id and a plan_id, and on the next compile it steers the optimizer toward that plan's shape. The mechanics matter, because the common mental model — that forcing pins the plan in place forever — is wrong, and the wrong model leads to bad decisions. Internally, forcing behaves like applying the forced plan as a USE PLAN hint: the optimizer still runs, still has to produce a valid plan, and is told to match the forced shape if it can. If it can, you get your old plan back. If it cannot — because an index the plan used was dropped, a column changed, the schema moved — it compiles whatever plan it can, increments a failure counter, and your query runs on the unforced plan while the force record sits there doing nothing.
EXEC sys.sp_query_store_force_plan @query_id = 118, @plan_id = 388;
-- when the real fix ships:
EXEC sys.sp_query_store_unforce_plan @query_id = 118, @plan_id = 388;So a force is a strong suggestion with a fallback, not a lock. That is mostly good news: forcing cannot make a query fail to execute, and a dropped index does not take your forced plan down with it — the query keeps running on a fresh compile. The bad news is the mirror image: you can believe you are protected by a force that silently stopped applying weeks ago. sys.query_store_plan tells the truth here — is_forced_plan, force_failure_count, and last_force_failure_reason_desc, which reports why the optimizer declined (a missing index is the classic one). More on watching that below.
One more scoping note: the force survives service restarts because it lives in the database, not in plan cache. That persistence is the point — it is what made our 09:41 force hold through the Tuesday night patching window — but it also means every force you issue is a piece of permanent configuration that someone has to remember to remove.
When is forcing the right call, and when should I fix the statistics or indexes instead?
Forcing is the right call when a known-good plan exists in history, the regression is costing you money right now, and the root-cause fix needs more than an hour. It is a tourniquet: apply it fast, write down when and why, and treat it as evidence of a wound rather than a cure. The two-day window our force held was spent figuring out why the optimizer had chosen the scan in the first place — and the answer was statistics. A stats update with FULLSCAN on the skewed column made the seek the optimizer's own first choice again, at which point the force came off and the query stayed fast on its own merits.
Forcing is the wrong call in three situations I keep seeing. First, when there is no good plan in history — forcing a bad plan because it is the only one you have just freezes the badness. Second, when the real problem is a missing index: build the index and the optimizer picks the good plan by itself, permanently, for every query that benefits, not just the one you forced. Third, as a substitute for understanding. A server accumulating dozens of forced plans is a server whose statistics maintenance, indexing, or parameter sniffing problems — the sniffing variant has its own playbook, and the waiting-on-locks variant of plan pain is a different animal entirely, covered in my blocking chain notes — are being wallpapered over one incident at a time. My rule of thumb: every force gets a ticket and an expiry review. Forces older than ninety days get re-examined, and the answer is usually that they can come off.
How do I monitor forced plans that stop applying?
Poll sys.query_store_plan for forced plans with a rising failure count, and alert on it like you would alert on replication lag. The query is small enough to run anywhere:
SELECT q.query_id,
p.plan_id,
p.force_failure_count,
p.last_force_failure_reason_desc,
p.last_execution_time
FROM sys.query_store_plan AS p
JOIN sys.query_store_query AS q ON q.query_id = p.query_id
WHERE p.is_forced_plan = 1
ORDER BY p.force_failure_count DESC;Any row where force_failure_count is climbing means your safety net has a hole in it and the query is already running unforced — find out whether the current plan is the good one or the bad one before the next deploy does it for you. The second thing to monitor is the drift side: a forced plan whose forced shape is still applying but whose runtime stats are slowly degrading as data grows. The seek we forced at 09:41 was perfect for the table as it stood that Tuesday; a year and 200 million rows later, the right plan might be something else entirely, and the force would be holding the query to yesterday's answer. Forced plans deserve the same periodic review as indexes — they are performance debt with a date on it.
Plan regression hunting with MonPG when SQL Server support lands
The signals worth graphing here are plan_id churn per query, average duration and CPU per plan across intervals, Query Store's own actual_state and readonly_reason so the recorder going read-only pages someone, and the forced-plan inventory with its failure counters. MonPG monitors PostgreSQL in production today; SQL Server support is on the roadmap and in active development, and the SQL Server monitoring (coming soon) page carries the honest status of that work. Until it ships, Query Store itself is the monitoring stack for this failure mode — which is precisely why it should be enabled, sized, and checked for that read-only flag on every database you own, long before the 09:20 deploy that needs it.