A Tuesday deploy doubled our p95 latency by Wednesday morning, and every developer swore their diff was innocent. It took twenty minutes in Query Store to find the truth: one query, running two hundred times a minute, had flipped from a 3-millisecond seek plan to a 400-millisecond scan plan at 14:07, fourteen minutes after the migration that added a column. Nobody's code was slow. A plan choice was. That is the job Query Store was born to do, and it is the first SQL Server feature in years that I consider malpractice to leave off.
Query Store is a flight recorder inside the database. It persists query text, plans, and per-interval runtime statistics, so "what changed?" stops being an argument and becomes a lookup. But it only pays off if you configured it before the incident, know how to read it during one, and resist the folk remedies that quietly destroy its value.
What are sane Query Store settings for production?
Turn it on in read-write mode, capture with AUTO so trivia stays out, give it real disk budget, and let size-based cleanup manage retention. The defaults from 2016 were conservative to the point of being dangerous: a 100 MB size cap on older builds is easy to outgrow in a busy week, and a database that fills its Query Store stops recording right when things get interesting. This is the shape I deploy on 2019 and 2022:
ALTER DATABASE [AppDb] SET QUERY_STORE = ON (
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
DATA_FLUSH_INTERVAL_SECONDS = 900,
INTERVAL_LENGTH_MINUTES = 15,
MAX_STORAGE_SIZE_MB = 2048,
QUERY_CAPTURE_MODE = AUTO,
SIZE_BASED_CLEANUP_MODE = AUTO
);Two choices there earn their keep. QUERY_CAPTURE_MODE = AUTO tells the engine to skip queries it judges insignificant, the millions of cheap single-row lookups that would otherwise drown the store in noise, while keeping everything with real resource cost. And INTERVAL_LENGTH_MINUTES at 15 instead of the default 60 gives you finer time resolution for regression hunting; runtime stats are aggregated per interval, and a one-hour bucket can hide a forty-minute incident inside its average. The tradeoff is more rows and slightly more flush work, which is why the size budget matters. On 2022, Query Store is on by default for new databases, which removes the most common failure mode of all: never having turned it on.
Why did my Query Store flip to READ_ONLY?
Because it hit MAX_STORAGE_SIZE_MB and your cleanup mode could not keep up, so the engine protected itself by stopping collection. The nasty part is that it does this quietly. Queries keep running, plans keep compiling, and your flight recorder has tape over the lens. Check the actual state, not the one you configured:
SELECT actual_state_desc, desired_state_desc,
current_storage_size_mb, max_storage_size_mb,
readonly_reason
FROM sys.database_query_store_options;A readonly_reason of 65536 means the size quota did it. With SIZE_BASED_CLEANUP_MODE = AUTO the engine is supposed to purge the oldest data as it approaches the cap, but under an ad-hoc query storm, where every execution has unique text, cleanup can lag behind the flood. The recovery is to raise the cap, let or force cleanup to run, and if you live on an ad-hoc-heavy workload, look at the 2019-and-later custom capture policy to filter by execution count and cost instead of hoping AUTO guesses right. If your monitoring does not alert on actual_state_desc changing, add that alert today. A Query Store that has been read-only for three weeks is a regression hunt you will lose.
How do I find the query that regressed after a deploy?
Compare aggregated runtime statistics for the same query across two windows, before and after the deploy, and sort by the delta. That is what the GUI's Regressed Queries report does, and doing it by hand in sys.query_store_runtime_stats gives you the same answer with more control. Note that durations are stored in microseconds, which surprises everyone once:
WITH before_stats AS (
SELECT p.query_id,
SUM(rs.avg_duration * rs.count_executions) /
SUM(rs.count_executions) AS avg_duration_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
JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
WHERE i.start_time >= DATEADD(day, -8, SYSUTCDATETIME())
AND i.start_time < DATEADD(day, -1, SYSUTCDATETIME())
GROUP BY p.query_id
),
after_stats AS (
SELECT p.query_id,
SUM(rs.avg_duration * rs.count_executions) /
SUM(rs.count_executions) AS avg_duration_after,
SUM(rs.count_executions) AS executions_after
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
JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
WHERE i.start_time >= DATEADD(day, -1, SYSUTCDATETIME())
GROUP BY p.query_id
)
SELECT TOP 20
a.query_id, qt.query_sql_text,
b.avg_duration_before / 1000.0 AS avg_ms_before,
a.avg_duration_after / 1000.0 AS avg_ms_after,
a.executions_after
FROM after_stats AS a
JOIN before_stats AS b ON b.query_id = a.query_id
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.avg_duration_after > b.avg_duration_before * 2
ORDER BY a.avg_duration_after - b.avg_duration_before DESC;When a query_id shows up with two plans, sys.query_store_plan will carry both, and comparing their is_forced_plan, count of compiles, and the interval when the new plan appeared usually closes the case. In my Wednesday incident the new plan's first execution timestamped fourteen minutes after the migration, the old plan had vanished from cache, and the regression window matched the deploy to the minute. Argument over.
When is forcing a plan the right move, and when is it self-deception?
sp_query_store_force_plan is a tourniquet: it stops the bleeding tonight by pinning the known-good plan, and it buys you the calm to do real surgery, which is usually a statistics or indexing fix. It is not the cure, and treating it as one is how you end up with a graveyard of forty forced plans that nobody dares unforce. Forcing is one command, and unforcing is one command:
EXEC sp_query_store_force_plan @query_id = 42, @plan_id = 7;
-- later, when the real fix ships:
EXEC sp_query_store_unforce_plan @query_id = 42, @plan_id = 7;The self-deception risk is that a forced plan can quietly stop being applied. If someone drops the index the forced plan depends on, SQL Server does not error your queries; it compiles something else, keeps the force recorded, and increments a failure counter. You think the tourniquet is on. It is on the floor. Monitor it:
SELECT p.query_id, p.plan_id, p.is_forced_plan,
p.force_failure_count, p.last_force_failure_reason_desc
FROM sys.query_store_plan AS p
WHERE p.is_forced_plan = 1
ORDER BY p.force_failure_count DESC;Any forced plan with a rising failure count is a promise your database is no longer keeping. Review that list monthly, unforce what the real fix has made unnecessary, and write down why each survivor exists.
What rests on Query Store in SQL Server 2022?
Half of the optimizer's self-correction features. Query Store hints let you pin a hint like MAXDOP or RECOMPILE onto a query through sp_query_store_set_hints without touching application code, which is the difference between a five-minute mitigation and an emergency release. Memory grant feedback persistence, degree-of-parallelism feedback, and cardinality-estimation feedback all store what they learn in Query Store, so disabling or constantly clearing it amputates those features. Query Store on readable secondaries, introduced in 2022 as a trace-flag-gated preview that stayed outside production support until SQL Server 2025, extends the recorder to your read-scale replicas instead of leaving them blind. The pattern is clear: Microsoft is building the intelligent query processing stack on top of this store, and treating it as optional overhead is a 2016 habit that makes less sense every release.
Why is clearing Query Store monthly such bad advice?
Because you are deleting the flight recorder's tape on a schedule to save a drive that was never going to fill, and the day you need a baseline is the day after the tape was erased. The folklore comes from early-adopter pain with runaway growth on 2016 defaults, but size-based cleanup already purges old data by design, and a size cap plus AUTO cleanup does the housekeeping continuously. A monthly CLEAR throws away the before picture every regression hunt needs. If space genuinely pressures you, shorten STALE_QUERY_THRESHOLD_DAYS or narrow the capture policy. Keep the tape.
Where MonPG's SQL Server support stands
MonPG is a PostgreSQL monitoring product today, and SQL Server monitoring is in active development and on the roadmap, not shipped. The Query Store data model is a natural fit for what we already graph on the PostgreSQL side with pg_stat_statements: per-query latency trends, plan-change events flagged on the timeline, and regression windows you can point at instead of argue about. The SQL Server monitoring (coming soon) page is where progress lands. Until then, the queries above plus the discipline to check actual_state_desc weekly will carry you further than most commercial dashboards.