Plan regressions

A query that took 40 ms yesterday and takes 400 ms today has usually not changed — it got a different execution plan. Query Store keeps that history on the monitored instance: per query, per plan, with runtimes.

That is the difference from the plan cache the rest of this application reads. The cache forgets: on every restart, under memory pressure, on every recompile. Query Store does not — and it keeps the old plan, which is what makes "which plan was it before" a question that can be answered at all.

What the page shows

Performance → Query Store lists one row per query whose current plan is measurably slower than another plan of the same query observed in the same window.

The order is by extra cost per hour, not by factor. A query that goes from 30 ms to 90 ms and runs three times a day is arithmetic; one that goes from 200 ms to 500 ms and runs ten thousand times an hour is why the server is slow. The factor is shown next to it, but it does not decide the order.

Why some queries are missing

Four thresholds keep the list readable. They are deliberately set so that something is missing rather than the list becoming unusable:

• At least 10 executions per plan. Two executions of a cold plan measure the buffer cache, not the plan.
• At least 20 ms on the current plan. Going from 0.4 ms to 4 ms is a tenfold regression and still meaningless.
• At least a factor of 2.
• At least one second of extra cost per hour.

A comparison plan is only proposed if it has enough executions of its own. Forcing a plan on the strength of three executions is how a monitoring tool makes things worse than it found them.

Reading the coverage

Above the table is how many databases were read and how many were skipped. That is not decoration: in most estates Query Store is not enabled everywhere, and an empty table then does not mean "all clear" — it means "nobody looked here". The badges name the reason: switched off, read-only, no data yet, not permitted, SQL Server too old.

Read-only is the one not to miss: Query Store is on, but it has reached its storage limit and is recording nothing new. The badge carries the cause SQL Server itself reports.

Turning Query Store on

Per database, once:

ALTER DATABASE [MyDatabase] SET QUERY_STORE = ON;
ALTER DATABASE [MyDatabase] SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

Available from SQL Server 2016, and on by default for new databases from SQL Server 2022. In Azure SQL Database it is on regardless.

Forcing a plan

Selected rows produce a script using sp_query_store_force_plan — and a matching one to undo it.

The application never runs that itself. A forced plan overrides the optimizer indefinitely, survives restarts, and is exactly the kind of decision that should carry a human name. The script is there to be read, adjusted and run in your own maintenance window — the same rule as the index actions.

Where SQL Server has already failed to force a plan, the row is still listed but the suggestion is withheld: a plan that could not be applied once will not start working because we proposed it again.

Alerts

Where Query Store covers a server it is also the source for the alert "System: Plan Regression Detected" — the older plan-cache analysis stands down for that server. One rule, one cooldown, one notification; only the finding comes from the better source. Servers without Query Store keep the plan cache as their basis.

Alerting is far more reserved than listing: from 10 seconds of extra cost per hour, and at most five alerts per run. A statistics update can flip dozens of plans at once — without a cap that is a mailbox full of individually justified alerts, which is exactly how an alerting channel earns a filter rule. How many findings the cap held back is stated in the alert.

The same query on the same pair of plans alerts once within the collection window. If it moves on to a third plan, that is a new finding.

Collection

A background service reads every server hourly by default, over a 24-hour window. Unlike change tracking this is not a retention knob: Query Store keeps its own history, so a longer interval only means looking less often. Collect now triggers a pass by hand (Administrator role, because it opens connections to every database on the instance).