Plan Regressions

Overview

Query Store has been in the box since SQL Server 2016 and is on by default in SQL Server 2022. It is the only source on the instance that knows a query used to be faster. This report reads it.

The page answers one question: which queries got slower, and how much time comes back if the old plan does. Everything on it is arranged around that ranking.

Why not percentage

The obvious ranking is percentage, and it is the ranking the SSMS Regressed Queries report reaches for by default. It is wrong in a way that makes the report useless rather than merely imprecise:

• A query that went from 2 ms to 8 ms is a 300 percent regression and is worth nothing at all.
• A query that went from 200 ms to 260 ms and runs four million times a day is the outage.

Percentage puts the first at the top and buries the second. Every ranking here is (now - was) x executions per day, which is a duration somebody can be asked to approve a change for.

Where to find it

In the tree, under a database, Real Time → Plan Regressions.

The node is hidden on SQL Server 2015 and older because the Query Store catalog views do not exist there. Reaching the page another way – go back history, a favourite, the command palette – runs the same probe and produces a message instead.

Requirements

The readiness banner

The banner is not decoration and it leads for a reason.

Query Store fails silently and specifically. It fills its size quota, flips itself to READ_ONLY, and from that moment collects nothing while continuing to look enabled. Every number under a READ_ONLY banner is history from before it stopped, and a page that did not say so would be presenting an artifact as current.

The banner reads actual_state_desc against desired_state_desc first, and then:

• readonly_reason, decoded. This is the column nobody reads and it is the whole diagnosis. Four of the eight bits are properties of the database – read only, single user, emergency, secondary replica – and the other four are Query Store running out of room in four different ways, each with a different fix.
• The quota, when it is above 90 percent full. At 100 percent it flips to READ_ONLY.
• query_capture_mode. An AUTO instance is discarding the cheap but frequent queries, which are exactly the ones whose individual cost is trivial and whose total is not. A ranking by total time is missing whatever AUTO dropped, and the banner says so.

Reading the chart

One row per query. Two bars, stacked:

• The upper bar, in grey, is what the query used to cost per day in the baseline window.
• The lower bar, in the severity colour, is what it costs per day now.

The gap between the two is the time that comes back. Both bars are on one shared scale, so the top row is longer than the rest and the ranking survives the split.

The bars are stacked rather than laid end to end in one track on purpose. Two adjacent segments in one track read as parts of a total, and these two are alternatives – adding them would produce a number that does not exist.

The severity bands

Nothing below 30 seconds a day reaches the page at all, and nothing that got less than 20 percent slower. Without those two floors the top of the list is whatever ran twice this morning and once last week.

The three views

Regressions

The default. Baseline window against comparison window, one row per query, ranked by time recovered.

Plan history

Every plan Query Store holds for one query, with when it first compiled, when it last ran, and what it averaged. This is where two very different situations are told apart:

• Several plans inside one window is the parameter sniffing signature: the same statement compiled for different parameter values, with the cache handing out whichever was compiled last.
• One clean switch is a statistics update, a schema change or a recompile hint, and it is fixed somewhere else entirely.

Reach it by right-clicking a regression and choosing Show every plan for this query, or by clicking the button with nothing selected, which opens the worst regression.

The window here is the whole comparison range rather than the last day, because a plan that stopped being used is exactly what somebody opening this view is looking for.

Forced plans

Every plan currently forced in the database, with no window at all. Forced plans are set once during an incident and then forgotten for three years until an upgrade makes them fail.

last_force_failure_reason_desc is the column that matters here. A forced plan that can no longer be produced fails silently: the query still runs, on whatever plan the optimizer picks, and the only evidence is a counter and a reason string on a catalog view.

The toolbar

The window choice is remembered between sessions. It is disabled on the forced plans view, which has no window.

Reading the grid

Double-click a row to open the statement with its plan document attached, which is what makes the Plan Analysis button on that dialog work.

The four things this query has to get right

These are why the numbers here differ from a version of this query copied off the internet.

The window is closed at both ends and never runs up to now. The runtime stats interval currently being filled holds partial counts. An upper bound of SYSUTCDATETIME() includes it and makes the freshest hour of any workload look like a collapse – which is exactly the shape this report hunts for, so the mistake manufactures findings rather than hiding them. The bound here is the end of the last completed interval, and the footer says when that was.

Aborted executions are counted, never averaged. A query cancelled by a client timeout records a duration equal to the timeout. A batch of those produces a regression that never happened. execution_type 0 is Regular; 3 is Aborted and 4 is Exception. The aborted count is on the page because a rise in client timeouts is itself a finding.

Every aggregate is weighted by execution count. Query Store stores an average per plan per interval. Averaging those averages across intervals is wrong whenever the execution count varies between them, which is always.

One row per query_id. That is per statement per set of SET options: the same text under a different QUOTED_IDENTIFIER is a different query_id and stays a separate row. The query_hash is carried into the copied script so the two can be seen to be siblings.

Right-click actions in the grid

When Query Store is not collecting, two more items appear at the bottom of the menu whatever is selected, because a page reporting that Query Store is off has no rows to right-click on:

The capture mode item appears on the same menu whenever Query Store is collecting and is still discarding queries:

Where the state is one no ALTER DATABASE would fix – SQL Server 2015 or older, a read only or single user database, a secondary replica, or the ERROR state – the menu carries the reason as a greyed line instead of an action that could not work.

Turning Query Store on from the report

This is the one thing in this report that changes anything on the server, and it is here because the alternative was a page whose only content was a message saying it could not help.

There are two ways to reach it, and the toolbar button is the one that is always visible. The button appears only when the state is one a setting would fix, so a page with no button is a page where turning something on is not the answer.

The confirmation dialog shows the exact statement before it runs. What it sets, and why:

Copy the script instead if you want different values.

Nothing is deleted. The read-only recovery raises the quota and does not CLEAR. Clearing is the other half of the advice usually given with it, and it throws away every plan and every runtime statistic – which on a store that filled up is the only record of what happened. It stays in the copied script, with a warning, and is never a single click.

Query Store starts empty. This report will have nothing to say until it has been collecting for a window.

Capture mode, and the queries you never see

This is the quiet failure. Query Store is on, the banner is green, the grid has rows in it, and the ranking is still incomplete because query_capture_mode decided part of the workload was not worth recording. AUTO is the default on SQL Server 2019 and newer, and CUSTOM is whatever somebody set it to.

What AUTO drops is the worst possible thing to drop from a page ranked by time per day: the queries whose individual cost is trivial and whose total is not. A statement running four million times a day at 3 ms is an hour of CPU a day, and AUTO can decide it is not interesting.

The banner says so when it applies, and the Capture mode to ALL button runs one statement:

ALTER DATABASE [YourDatabase] SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL);

Nothing else on the store is touched. Two things this costs, both in the confirmation dialog:

1. ALL is the more expensive of the two on a high frequency OLTP workload, because every trivial query now gets written down.
2. The quota fills faster, and a quota that fills is what puts the store into READ_ONLY where it collects nothing at all. Check SIZE_BASED_CLEANUP_MODE is AUTO before leaving it.

Nothing already discarded comes back. Only the intervals collected after the change are complete, so a window that straddles the change is still missing whatever AUTO dropped before it.

Forcing a plan

Nothing on this page runs anything, and the force script arrives commented out with three costs stated above it rather than below:

1. A forced plan is frozen against a data distribution that keeps moving. It is a tourniquet, not a fix, and it needs a date to be reviewed on.
2. Forcing fails silently. After an index change, a schema change or an upgrade the plan may no longer be producible, and the query runs on whatever the optimizer gives it.
3. The cause is usually elsewhere – statistics, parameter sniffing, or an implicit conversion feeding a bad estimate. Forcing hides all three.

Where the plan did not change, no EXEC is offered at all. Forcing the plan that is already in use would change nothing, and offering it anyway is how a report teaches people that its advice does not need reading.

Where the data comes from

The catalog views are local reads and cost the monitored instance almost nothing.

Messages you may see

Query Store is not available on this database. SQL Server 2015 or older, or tempdb, which can never have it.

Query Store is off on this database. Nothing has been collected. Right-click the grid to turn it on, or to copy the script and run it yourself.

Query Store is READ_ONLY and is collecting nothing. Everything below the banner is history from before it stopped. The banner names the reason.

Query Store has not completed a statistics interval yet, so there is nothing to compare. It has been turned on within the current interval. Come back after one has closed.

No query got measurably slower between the two windows. A real and common result on a healthy database.

Capture mode is AUTO, so cheap or infrequent queries are being discarded rather than recorded. Query Store is healthy and the ranking below is incomplete. The Capture mode to ALL button sets QUERY_CAPTURE_MODE = ALL, and only the intervals after that are complete.

Related reports

Frequently asked questions

Why is a query I know got slower not on this page? Four filters can drop it: fewer than five executions in the comparison window, less than 20 percent slower, less than 30 seconds a day of extra time, or a query_capture_mode of AUTO that never recorded it at all. The banner says when the last one applies.

Why do the numbers change when I change the window? Because both windows move. The 6 hour setting compares the last six hours against the seven days before those six hours, not against the same baseline the 24 hour setting used.

The same query appears twice. Two query_id values with different SET options, which the optimizer treats as two queries because it can compile them differently. The copied script carries the query_hash for both.

Why is the aborted count not in the averages? An aborted execution records the client’s timeout as its duration. Including it would make a query look slower every time an application gave up on it.

Can I force a plan from here? No. The script is copied to the clipboard with the consequences above it. Forcing a plan is a production change and belongs in a change window.

Why can Query Store be turned on from the report when nothing else here runs anything? Because the alternative was a dead end. Everything else the report can suggest – forcing a plan, changing a setting – is a decision about a workload, and those stay copied scripts. Turning Query Store on is a decision about whether the instance is recording one at all, it is reversible in a single statement, and without it the page had nothing to offer the reader it had just turned away.

I turned it on and the page is still empty. Query Store starts empty and aggregates on an interval. There is nothing to compare until an interval has closed, and nothing worth comparing until there is a baseline behind it.

What does “Force failing” mean on the forced plans view? force_failure_count is above zero: SQL Server tried to use the forced plan and could not. The query is running on whatever the optimizer produces, and has been since forcing started failing.