Plan Survival
Overview
Plan instability is the most common answer to “it was fast yesterday”, and most pages that look at plans look at what they cost. A query whose plan changes twice a day between two plans that happen to cost the same never shows up as a regression, and it is still a query that is recompiled, sniffed again, and one statistics update away from landing on a bad plan, twice a day, every day.
This report counts the changes rather than their cost. It answers two questions:
- How long does a plan last on this database before it is replaced?
- Which queries keep changing theirs, and which of those go back to a plan they had already stopped using?
Why a survival curve
Most plans have not ended. A plan still in use when its query last ran has only lasted at least as long as it was watched. There are two tempting ways to handle that and both are wrong:
| Shortcut | What goes wrong |
|---|---|
| Count only the plans that were replaced | Those are the short lived ones by construction, so every database looks unstable. |
| Treat the end of the window as the end of every plan | The chart invents a replacement at the moment you looked. |
The Kaplan-Meier (product limit) estimate uses a plan still in use for exactly what is known about it: it counts as watched up to the age it was last seen, and then it leaves the population without moving the curve. On the chart that difference is a tick on the curve rather than a step down it.
At each age at which a plan was replaced, the curve is multiplied by the share of plans still being watched at that age that were not replaced there. The same single replacement is a small step when hundreds of plans are watched and a large one when three are, which is why the number still watched is printed under the axis.
What counts as a plan and a replacement
A tenure is a run of the query’s own active intervals in which one plan shape was used every time. Three rules keep the count honest:
Plans running side by side are not a change. A cursor statement carries two plans that run together in every interval. Ordering them by their first execution makes them appear to alternate, and on a real database that reads as a query changing plan every hour. Here two shapes in use together are two tenures running at once.
A tenure ends as a replacement only when the query went on running without that plan. A plan still in use in the last interval its query ran in is censored (still alive), not replaced.
Plans removed by Query Store cleanup are not replacements. Size based cleanup, stale query cleanup and sp_query_store_remove_plan take a plan away, and when the query next runs Query Store captures the same plan again under a new plan_id. Tenures are built on query_plan_hash rather than plan_id, so a plan captured again is one tenure. Removing a plan also removes its runtime statistics, so the history before the removal simply disappears; it is never read as the plan ending.
Ages start no earlier than the window. A plan already in use when the window opens may have been in charge for months. Its age is counted from the later of when its shape was first compiled and when the window opened, so every age on the axis is one the window actually covered. A plan whose query first ran partway through the window enters the estimate at that age rather than at zero. The full age is kept for the grid’s notes.
Every execution type counts, aborted and failed ones included: the question is which plan ran, not what it cost.
Where to find it
In the tree, under a database, Real Time → Query Store → Plan Survival.
The Query Store folder is hidden below SQL Server 2016, and on master and tempdb, where Query Store cannot be turned on.
Requirements
| Requirement | Why |
|---|---|
| SQL Server 2016 or newer | Query Store arrived in SQL Server 2016. |
| Query Store on for the database | The plan history lives inside Query Store. |
VIEW DATABASE STATE |
To read the Query Store catalog views. |
The toolbar
| Control | What it does |
|---|---|
| By kind of query / Whole database | One curve for the whole database, or that curve plus one each for procedures and functions, parameterized statements and ad hoc statements. Redrawn without reading Query Store again. |
| 24 h / 3 d / 7 d / 14 d / 30 d | The window. A week is the default: long enough for a nightly statistics update and a weekly job to both show up. |
| 5+ runs / 20+ runs / 100+ runs / 500+ runs | How often a query has to run in the window to be watched. A query that ran twice cannot change its plan in any way that means anything, and ad hoc text with a literal in it is a new query every time. |
| Turn Query Store on | Only when Query Store is off and a setting would fix it. |
| Refresh | Re-reads Query Store. |
Reading the chart
A verdict at the top names the most important finding, in this order:
- A forced plan that is not holding. Query Store says plan 14 is forced and the query is running on plan 19, or the force has failed. A force that does not hold is worse than no force at all, because it is still believed to be the fix.
- Queries going back and forth. A query that returns to a plan it had stopped using has plans that each suit part of its workload.
- Half of the plans replaced within a day.
- A real share of plans replaced within a day.
- Plans staying put.
Six tiles carry the headline numbers:
| Tile | What it is |
|---|---|
| Recurring queries | Queries that ran at least the chosen number of times. |
| Changed plan | How many of them had at least one plan replaced. Click to list only those. |
| Half replaced within | The age at which the whole database curve crosses 50%, or “not reached” when more than half of the plans outlived the window. |
| In charge after a day | The curve’s reading at 24 hours, with how many plans were watched that long. |
| Going back and forth | Queries that went back to a plan they had dropped. Click to list only those. |
| Forces not holding | Forced plans that are not in use or have failed. Click to list only those. |
Click a filtering tile again to clear the filter.
Under the tiles, the survival curve:
- Across is how long a plan had been in charge, from when it took over or the window opened.
- Up is the share of plans still in charge at that age, always from 0% to 100%.
- A step down is one or more plans replaced at that age.
- A tick is a plan still in use when its query last ran.
- The shaded band is the 95% confidence interval of the first curve (Greenwood’s formula). It widens to the right because fewer plans are watched at greater ages.
- The dashed line to the axis marks the age at which half the plans had been replaced, when the curve gets there.
- The dotted rule marks one day.
- The table under the axis is how many plans were still being watched at each tick, one row per curve. A step cannot be read without it.
Each curve stops at the last age anything in it was watched, because past that age nothing is known.
Select a row in the grid and that query’s own curve is drawn over the database’s. Hover over a step or a tick for the query it belongs to; click it to select that query in the grid.
Reading the grid
The grid lists up to 50 queries, the ones with the most changes, worst finding first. Every recurring query is counted in the curve whether it is listed or not.
| Column | What it is |
|---|---|
| # | Rank: worst finding, then most changes, then most executions. |
| Finding | Force not holding, back and forth, several changes each to a new plan, changed once and stayed, forced and holding, one plan all window, or plans in use together. |
| Notes | Which plans it went back to, force failures and their reason, plans captured again after Query Store removed them, and how long a plan has been in charge since before the window. It takes the width the other columns leave, so it stays on screen at 1280×900. |
| Changes | The moments a plan stopped being used while the query went on running. A pair of plans replaced together is one change. |
| Longest run | The longest time the window saw one plan in use. |
| Typical run | The middle of the tenures that ended. Empty when none did. |
| Runs | Executions in the window. |
| Object | The procedure, function or trigger, when the statement belongs to one. |
| Query | The statement text, with the parameter list Query Store prefixes removed. |
| Plans | Distinct plan shapes in the window. |
| Current plan | The busiest plan still in use when the query last ran. |
| On it since (UTC) | When the current plan was first seen in the window. |
| Kind | Procedures and functions, parameterized, or ad hoc. |
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 its tenures beside it. Right-click for:
| Action | What it does |
|---|---|
| Explain this query’s plans | Every tenure in order: plan, shape, first and last run, runs, and whether it was replaced, came back or was captured again. |
| Show the statement | The statement in the query window. |
| Go to Plan Differences / Go to Plan Regressions | When the query has more than one plan. |
| Go to Parameter Sensitive Plans | When the query goes back and forth. |
| Go to Automatic Tuning | When the query has a forced plan. |
| Copy query text | The statement, without the parameter list. |
| Copy a script listing this query’s plans | A read only SELECT over sys.query_store_plan for this query. Nothing is run. |
| Copy the query behind this report | The whole batch, ready to run in SSMS. |
Where the data comes from
| Source | What it gives |
|---|---|
sys.query_store_runtime_stats |
Executions, first and last execution time per plan per interval. |
sys.query_store_runtime_stats_interval |
The window, and the query’s own active intervals. |
sys.query_store_plan |
query_plan_hash, compile time, is_forced_plan, force_failure_count and the failure reason. |
sys.query_store_query, ..._query_text |
The statement and what kind of query it is. |
sys.database_query_store_options |
The readiness banner. |
The window ends at the last completed interval, not at the clock, and both ends are on interval boundaries. The survival arithmetic is done in the report rather than in the batch.
When a plan was already in use before the window, the last run of any other shape before the window is read from the runtime statistics rather than from the plan’s own last_execution_time, which on a real database was found to be two weeks behind the statistics for a plan still running every hour.
Messages you may see
No query ran 20 times or more in the last 7 days. Choose a lower execution floor or a longer window. If the database was busy, look at Query Store Health.
N plans were removed by Query Store and captured again under a new plan_id. Those are matched on
query_plan_hashand not counted as replaced.
N queries ran fewer than 20 times and are not counted. The execution floor on the toolbar.
Query Store’s history starts after the window begins. The window is only partly covered.
Capture mode is AUTO, so cheap or infrequent queries are being discarded. Queries AUTO did not capture are not in the curve.
Related reports
| Report | Why you would go there |
|---|---|
| Plan Regressions | Which plan changes made a query slower, and the script to force the old plan. |
| Plan Differences | What is actually different inside two plans for one statement. |
| Parameter Sensitive Plans | Whether a query going back and forth is sensitive to its parameters. |
| Automatic Tuning | Every forced plan, with the failure reason Query Store recorded. |
| Duration Spread | Whether a query’s worst case is pulling away from its typical case. |
| Query Store Health | Whether Query Store is capturing what this page needs. |
Frequently asked questions
Half the plans are replaced within hours, but only a few queries changed plan. The curve counts plan tenures, not queries. A query that recompiles on every run and alternates between two plans produces a tenure every time it switches, and a handful of those can outnumber every stable plan on the database. The summary line says what share of the replacements belong to queries going back and forth, and the Changed plan tile counts queries.
A cursor shows two plans but no changes. A cursor statement runs two plans together. Two shapes in use in the same intervals are two tenures running at once, not a change.
Why does a plan compiled months ago start near zero on the axis? Ages are counted from when the plan took over or when the window opened, whichever was later, so the axis only covers time the window saw. The Notes column keeps the full age.
The grid says one plan, but sys.query_store_plan lists two plan ids for the query. Plans are compared by query_plan_hash. The same shape under a second plan_id, whether Query Store captured it again after removing it or recorded it for another reason, is not a change.
Does an aborted execution count? Yes. An aborted or failed execution still ran on a plan, and this page is about which plan ran.