Plan Differences
Overview
Plan Regressions says a statement got slower and that its plan changed. Plan Resource Profile says which resource the dearer plan spends more of. Neither can say what is different inside the two plans, because neither reads the plans. The answer you usually want at that point is one sentence: the new plan is scanning a table the old one was seeking.
This report reads both showplan documents for every statement that ran on two plans and matches them operator against operator. The chart draws the match: the older plan down the left, the plan in use down the right, one lane per place in the match.
- Operators on both sides of a lane are the same place in both plans. A diamond in the middle means something about that operator changed.
- An operator with blank paper on the other side exists in only one plan. That blank is the finding.
- The lane a seek became a scan is drawn in red, and a scan that became a seek in green.
Which two plans
The two plans compared are the two most recently used, not the best and the worst. The question here is what changed, and a plan that last ran eleven days ago is not what changed. The more recent of the two is called the plan in use, the other the older plan.
A plan needs at least 5 regular executions in the window (a toolbar setting) before it can be either half of the pair, and the statement needs at least 20 executions and an average of 1 ms or more.
A difference only in the optimizer’s estimates, or in how the cost is spread across the operators, is not a change of shape. A plan recompiled against fresher statistics has a different number on every operator while being the same plan in every respect you could act on. Those statements are counted on the Same shape, new numbers tile.
Where to find it
In the tree, under a database, Real Time → Query Store → Plan Differences.
The report works on SQL Server 2016 and newer. The Query Store folder is hidden on older versions and on master and tempdb.
Requirements
| Requirement | Why |
|---|---|
| SQL Server 2016 or newer | Query Store arrived in SQL Server 2016. |
| Query Store on for the database | The plans and their history live inside Query Store. |
VIEW DATABASE STATE |
To read the Query Store catalog views. |
The toolbar
| Control | What it does |
|---|---|
| Time added / Widest swing / Total time / Executions | How the 25 statements on the page are chosen. |
| 24 h / 3 d / 7 d / 30 d | The window. |
| 2+ runs / 5+ runs / 20+ runs / 100+ runs | How many executions a plan needs before it can be one of the two compared. |
| Turn Query Store on | Only when Query Store is off and a setting would fix it. |
| Refresh | Re-reads Query Store. |
The page holds 25 statements rather than 50 because every row carries two showplan documents.
Reading the chart
At the top, a verdict names the most expensive finding. Five tiles carry the totals:
| Tile | What it is |
|---|---|
| Statements on two plans | Statements whose two most recent plans both cleared the floors, out of all that ran. |
| Time the newer plans added | The extra time every statement spent on its newer plan, against what a run on its older plan cost. Click to show the statements that cost more. |
| Now scanning what it sought | Statements whose plan in use scans where the older plan sought, and which cost more for it. Click to filter to them. |
| Widest swing | The largest ratio between the two plans’ cost per run, in either direction. Click to select that statement. |
| Same shape, new numbers | Statements whose plans differ only in estimates or cost shares. Click to filter to them. |
Under the tiles, the alignment for one statement, the one selected in the grid or the first row:
- A line above the key names the statement and counts the lanes: held, changed, added, gone.
- The two columns are the two plans. Each keeps its own indentation, so each still reads as a tree, and the connectors run down through lanes the other plan’s insertions opened up.
- The marks in the middle gutter say what happened, as shapes rather than colors: a plain rail for an operator that did not change, a diamond for one that changed, a hollow triangle pointing back for an operator only the older plan has, and a solid triangle pointing forward for one only the plan in use has.
- The short bar on the rail shows which way the operator’s share of its plan’s estimated cost moved. It is a ratio drawn on a log scale, so a doubling and a halving are the same length in opposite directions. A notch at the end means it ran past ten times.
- The bars on the outer edges are each operator’s share of its own plan’s estimated cost, on one scale for both sides.
- A red tick on an operator means the plan carries a warning there, such as a spill to tempdb.
Hover over a lane for both operators, their objects, their cost shares and what changed.
Query Store sometimes keeps a plan document with no operators in it. When that happens the chart draws the side it can read and says which document is empty; nothing is matched, and the run times in the grid are still real.
Reading the grid
| Column | What it is |
|---|---|
| # | The statement’s position in the ranking chosen on the toolbar. The grid is ordered by finding: seek became scan, costs more, improved, then the rest. |
| Finding | Scanning what it used to seek, the newer plan costs more, the newer plan is faster, or the same price. |
| What changed | The headline of the match, the counts, forcing, and how many plans the statement had. It takes the width the other columns leave, so it stays on screen at 1280×900. |
| Change | The plan in use against the older plan, as a multiple. A minus sign means faster. |
| Time added | (now, a run – then, a run) x runs on the plan in use. Negative is time saved. |
| Then, a run / Now, a run | Average duration on each plan. |
| Object | The procedure, function or trigger, when the statement belongs to one. |
| Query | The statement text, with the parameter list Query Store prefixes removed. |
| Runs | Regular executions of the statement in the window. |
| Older plan / Plan in use | The two plan ids. |
| Moved | Operators added, removed or changed in a structural way. |
Hover over a row to see every column in full, including the ones past the right edge of a narrow window.
Double-click a row to see the statement with the whole match written out, lane by lane. Right-click for:
| Action | What it does |
|---|---|
| Explain what changed | Both plans, their run times and every lane of the match, as text. |
| Show the statement | The statement in the query window, with the plan in use attached for plan analysis. |
| Copy the script that forces the older plan | sp_query_store_force_plan for the older plan, with the unforce statement commented beneath it. Copied, never run. |
| Copy the older plan (showplan XML) | The document, to save as a .sqlplan file and open in SSMS. |
| Copy the plan in use (showplan XML) | The same for the other plan. |
| Go to Plan Resource Profile | Which resource the dearer plan spends more of. |
| Go to Plan Regressions | The same statements ranked by the time a change added. |
| Copy query text | The statement, without the parameter list. |
| Copy the query behind this report | The whole batch, ready to run in SSMS. |
Nothing on this page changes the database. Forcing a plan is right when the two plans differ because of the parameter values they were compiled for, and wrong when the data has grown into a shape the older plan no longer suits. Read both plans first.
Where the data comes from
| Source | What it gives |
|---|---|
sys.query_store_runtime_stats |
Executions and duration per plan per interval. |
sys.query_store_runtime_stats_interval |
The window boundaries. |
sys.query_store_plan |
Both showplan documents, and whether a plan is forced. |
sys.query_store_query, ..._query_text |
The statement behind the plans. |
How the plans are read
In the application, not in the query. Query Store keeps query_plan as text. A plan nested more than 128 levels deep stores fine but cannot be shredded with XQuery, which would fail the whole query for every statement on the page. The documents are read with an XML reader in Database Health Monitor, by element name, so a new showplan version does not stop them.
In reading order. The two operator lists are aligned as sequences rather than by node id or level by level, so a sort added on top of a plan is one operator added rather than a whole tree replaced.
By likeness, not equality. Two operators reading the same table count for more than two operators of the same kind, so a seek that became a scan on the same table lines up as one changed lane.
Regular executions only, and every average is a total divided by executions. Aborted and failed executions are counted in the footer.
The plan documents are the ones Query Store captured when each plan was first compiled. Estimates inside them can be old; the shape is what matters here.
Messages you may see
dbo.Proc is scanning a table its older plan was seeking. The plan in use has a scan where the older plan had a seek, and the statement costs at least a quarter more a run and a second more over the window.
N statements cost more on the plan they are running now. The newer plans cost more without a seek becoming a scan. The verdict says whether the shapes differ.
No statement is worse off on the plan it is running now. Plans changed, and none of the changes cost anything.
Every statement in this window ran on a single plan. There are no two plans to match. A longer window or a lower floor finds more.
The document of the plan in use holds no operators. Query Store kept an empty document for one of the plans, so nothing can be matched.
Related reports
| Report | Why you would go there |
|---|---|
| Plan Regressions | To rank plan changes by the time they added and force a plan. |
| Plan Resource Profile | To see which resource the dearer plan spends more of. |
| Parameter Sensitive Plans | When the plans differ because of the parameter they were compiled for. |
| Statistics | When a statistics update is the likely reason the optimizer changed its mind. |
| Missing Indexes | When the plan in use scans a table because no index covers the query. |
| Plan Warnings | For bad plans of statements that only ever had one plan. |
Frequently asked questions
The finding says the plans have the same shape, but the lanes are marked changed. The changes are in estimates or in cost shares only. The optimizer arrived at the same plan with fresher numbers, which is not a change you can act on.
Why is a Key Lookup paired with a scan rather than the index seek? The matcher pairs the operators most alike, and a lookup and a scan of the same clustered index are more alike than a seek on a different index. The headline names what the scan replaced: a seek and key lookup.
Why are some plans missing their operators? Query Store keeps a document for every plan, and for some statements the document has an empty Statements element. The page says so rather than calling the plans the same.
Why only the two most recent plans? The question is what changed. The best and the worst plan of a statement is a different question, and Plan Regressions answers it.