Plan Resource Profile
Overview
A statement running on two plans is two different pieces of software running under one name. Plan Regressions tells you that a plan changed and what the change cost on the clock. It does not tell you what the dearer plan is doing differently, and that is what decides what to do next.
The answer is not in the duration, because duration is the symptom. It is in the other measures, and Query Store has been keeping all of them per plan the whole time:
- A plan that reads eleven times as many pages for the same processor time lost an index seek and is scanning.
- A plan that goes through tempdb where its sibling does not was given less memory than it needed, or is spooling.
- A plan that reads and computes exactly as much as its sibling and takes eight times as long is not a worse plan at all. It ran while something else was in the way, and forcing a plan will not fix it.
This report lists the statements with more than one plan and draws the plans of the selected statement as outlines on a radar, one spoke per resource.
Plans differ, or were asked different things?
Before calling a plan worse, the report checks what the plans were asked for. Rows returned is read for every plan and is deliberately not one of the eight spokes. A plan that read ten times as many pages and returned ten times as many rows was handed a different parameter value, and forcing a plan would pin one parameter’s plan onto the other’s workload.
Every statement is filed as one of four readings:
| Reading | What it means |
|---|---|
| Plans differ | The plans are at least 2x apart in a resource other than duration, and the rows they returned do not explain it. This is a plan fault. |
| Different rows | They are far apart, and the rows returned differ at least half as much. That is a parameter rather than a plan. |
| Same work | Duration is 2x apart and no resource is. Those executions were waiting, not working. |
| Settled | Nothing is as much as 2x apart. Several plans for one statement is normal. |
Where to find it
In the tree, under a database, Real Time → Query Store → Plan Resource Profile.
The report needs SQL Server 2017 or newer, because it reads the per plan wait statistics, tempdb and log columns that arrived in 2017. On SQL Server 2016 the page says so instead of loading.
Requirements
| Requirement | Why |
|---|---|
| SQL Server 2017 or newer | sys.query_store_wait_stats, avg_tempdb_space_used and avg_log_bytes_used arrived in 2017. |
| Query Store on for the database | The history lives inside Query Store. |
WAIT_STATS_CAPTURE_MODE = ON |
For the waiting spoke. When it is off the page says so and offers a button to turn it on. |
VIEW DATABASE STATE |
To read the Query Store catalog views. |
The toolbar
| Control | What it does |
|---|---|
| Total duration / Duration spread / CPU time / Executions / Plan count | How the 50 statements on the page are chosen. |
| 24 h / 3 d / 7 d / 30 d | The window. |
| 1+ runs / 5+ runs / 20+ runs / 100+ runs | How many executions a plan needs before it is compared. The default is 5, because the cheapest average in the store is very often a plan that ran twice on a Sunday. |
| Turn Query Store on, Turn wait capture on | Only when one of those is off and a setting would fix it. |
| Refresh | Re-reads Query Store. |
Reading the chart
At the top, a verdict names the statement whose plans are furthest apart in a resource, with the two readings and the page to go to next. Six tiles carry the totals:
| Tile | What it is |
|---|---|
| On more than one plan | Statements in the window with at least two plans that cleared the floor, out of all statements that ran. |
| Plans compared | How many plans those statements have, and their share of all duration. |
| Widest gap | The largest resource gap on the page. Click to show only the statements whose plans differ. |
| They differ most in | The resource that separates plans on the most statements. Click to filter to them. |
| Asked different things | Statements whose gap the rows returned explain. Click to filter. |
| Forced plans | Plans pinned with plan forcing. Click to filter. |
Click a filtering tile again to clear the filter.
Under the tiles, the radar for one statement: the one selected in the grid, or the first row when nothing is selected.
- Each spoke is one resource per execution, clockwise from the top: duration, CPU time, waiting, logical reads, physical reads, memory grant, tempdb used, and log bytes.
- Each spoke is scaled to the dearest plan on it. A vertex on the rim is the worst of these plans on that resource, and a vertex at the center is nothing at all. The largest reading is printed beside each spoke; “none” means no plan used any.
- One outline per plan, up to six, the busiest first. The heavier outline is the plan that ran most often. Two outlines on top of each other are two plans that cost the same; one outline pushed out along a single spoke is the finding.
- The area inside an outline is not a quantity. The spokes are always in the same order so that two statements look comparable, but eight unrelated units multiplied together mean nothing.
When the chart is wide enough, a table beside the radar prints every reading, with the largest on each row in bold, and the rows returned and runs for each plan. Hover over an outline for its readings.
Reading the grid
| Column | What it is |
|---|---|
| # | The statement’s position in the ranking chosen on the toolbar. The grid is ordered plans differ first, then same work, then different rows, then settled, widest gap first. |
| Object | The procedure, function or trigger, when the statement belongs to one. |
| Query | The statement text, with the parameter list Query Store prefixes removed. |
| Gap | The widest gap: the largest reading over the smallest, on the resource named in In. For Same work it is duration. |
| In | The resource the plans are furthest apart on. |
| Reading | The two readings behind the gap, per execution. |
| Verdict | Plans differ, Different rows, Same work or Settled. |
| Best / run and Worst / run | The cheapest and dearest plan’s average duration. |
| Execs | Regular executions across the compared plans. |
| Total time | Their total duration in the window. |
| Last run (UTC) | The most recent execution. |
| Plans | Plans that cleared the execution floor. |
| Rows differ | How far apart the plans are in rows returned, shown when it is 2x or more. |
| Forced | Forced plans among them. |
The Query column takes the width the others leave, so it grows with the window. Every column up to Last run (UTC) stays on screen at 1200×900 with the Query column at about 150 pixels or more. When there is not room for the last three columns (Plans, Rows differ and Forced) as well, they sit past the right hand edge; scroll right for them, or hover over a row, whose tooltip lists every column along with the full statement.
Double-click a row to see the statement with every plan written out. Right-click for:
| Action | What it does |
|---|---|
| Explain these plans | Every plan on all eight measures, and the comparison in words. |
| Show the statement | The statement in the query window. |
| Go to Plan Differences | Which operators changed between the plans. |
| Go to Waits by Query | When the gap is in waiting or duration only. |
| Go to Parameter Sensitive Plans | When the rows returned explain the gap. |
| Go to Plan Regressions | Where a plan can be forced. |
| Copy query text | The statement, without the parameter list. |
| 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, duration, CPU, reads, memory grant, tempdb, log bytes and rows per plan per interval. |
sys.query_store_wait_stats |
Waiting per plan, in milliseconds. |
sys.query_store_runtime_stats_interval |
The window boundaries. |
sys.query_store_plan, ..._query, ..._query_text |
Plan flags, compile counts and the statement behind each plan. |
How the numbers are made
Per plan, per execution. A plan that ran four hundred thousand times and a plan that ran twice can only be compared per execution, and every figure is a total over the window divided by that plan’s executions, never an average of averages.
Regular executions only. A query canceled by a client timeout records the timeout as its duration. Aborted and failed executions are counted in the footer instead, and they are left out of waiting too.
The window ends at the last completed interval and is bounded at both ends.
Waiting leaves out Idle, User Wait, Tracing and Network IO. A client slow to fetch its rows is not a plan being held up.
Floors per measure. A gap only counts when the larger reading is worth mentioning: 1 ms of duration, CPU or waiting, 100 logical reads, 10 physical reads, 16 pages of memory grant, 10 pages of tempdb, or 4 KB of log. When one plan uses none of something, the floor stands in for the nothing, so the ratio stays finite and the sentence says “uses none at all”.
Waiting on a parallel plan is not compared as a cause. Query Store adds up the waits of every worker, so a plan scanning on eight workers can record several times its own duration as waiting. On a test database a parallel scan at 58 ms a run recorded 464 ms of waiting. The spoke still draws it; the verdict names the reads instead.
Messages you may see
Every statement here is running on one plan. Nothing in the window has a second plan that cleared the execution floor. A lower floor on the toolbar compares plans that ran less.
One statement’s plans do the same work and take different lengths of time. No resource separates the plans, only the clock. Look at Waits by Query rather than forcing a plan.
Every statement here has plans that were asked different questions. The rows returned explain every gap. That is parameter sensitivity, not a plan fault.
The Plan Resource Profile report requires SQL Server 2017 or newer. The per plan wait statistics are not there on this instance.
WAIT_STATS_CAPTURE_MODE is OFF on this database. The waiting spoke will read nothing until wait capture is turned on.
Related reports
| Report | Why you would go there |
|---|---|
| Plan Differences | Which operators changed between two plans of one statement. |
| Plan Regressions | When a plan change cost time and you want to force the older plan. |
| Parameter Sensitive Plans | When the rows returned explain the gap. |
| Waits by Query | When the plans do the same work and one of them waits. |
| Missing Indexes | When the gap is in logical reads. |
| Key Lookups | When the dearer plan fetches the rest of each row one row at a time. |
| Plan Survival | How long plans last before the optimizer changes its mind. |
Frequently asked questions
Why does a plan sit at the center of every spoke? Each spoke is scaled to the dearest plan on it, so a plan that reads 259 pages against another’s 13,180 is drawn at two percent of the way out. The table beside the radar has the real numbers.
Why is the memory grant 0 KB for one plan? Query Store records no grant for a plan that needed none, such as a seek with no sort, hash or parallelism. A sibling with a sort or a parallel operator gets one.
Why is waiting larger than duration? The plan runs in parallel and Query Store adds up the waits of every worker. The page does not treat that as the cause of the gap.
Why only six outlines? Eight outlines over eight spokes is unreadable, and the plans past six are always the ones that hardly ran. They are still in Explain these plans.