Cost Drift
Overview
The database is using more CPU than it did, and here is the query at the top of the list. Is it running more often, or has each run gotten more expensive? Those are opposite problems. The first is fixed in the application by calling the query less; the second is fixed in the database by looking at the plan. A total cannot tell them apart, because a total is how often the query ran multiplied by what each run cost.
The second question is when. Comparing this week with last week dilutes a Tuesday change with the ordinary days after it, and the reader has to guess a window before knowing what to look for.
This report answers both without a baseline or a split of the window. For each of the busiest queries it keeps two running totals through the window, in the order the intervals happened, and reads the answer off the shape they make.
Two running totals
For each query, at every collection interval:
- Across is how much of the query’s executions had arrived by then.
- Up is how much of its cost those executions had spent.
Both are shares of that query’s own totals, so every line runs from the bottom left corner to the top right corner. The slope of the line is the price of one execution.
- A query whose price never changed spends at exactly the rate its executions arrive, and draws the diagonal.
- A query that grew dearer part way through spent less than its share early and more of it late, so its line sags below the diagonal. The deepest point of the sag is when the price changed.
- A query that grew cheaper arches above the diagonal.
Accumulating is what makes a modest change visible. A third added to an hourly average disappears into the noise of hourly averages. The same third, carried along by every interval after it, is a bend you can see from across the room.
The same arithmetic run over clock time and executions dates a change in how often the query is called. That is how a query that simply ran more is told apart from one that got dearer.
Where to find it
In the tree, under a database, Real Time → Query Store → Cost Drift.
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 history lives inside Query Store. |
VIEW DATABASE STATE |
To read the Query Store catalog views. |
The toolbar
| Control | What it does |
|---|---|
| CPU / Duration / Reads | The measure a price is taken over. |
| 12 h / 24 h / 3 d / 7 d / 14 d / 30 d | The window. |
| At 1.25x / At 1.5x / At 2x / At 3x | How much dearer or cheaper one execution has to get before the page names a change. The same multiple is used in both directions. Changing it redraws without reading Query Store again. |
| Real work / All queries | Real work leaves out queries that came to less than 5 seconds of CPU or duration (50,000 pages of reads) over the window. |
| Turn Query Store on | Only when Query Store is off and a setting would fix it. |
| Refresh | Re-reads Query Store. |
Reading the chart
At the top, the verdict says which of four situations the database is in: a query that changed price, queries that never settled on one, queries simply called more often, or nothing changed. The tiles:
| Tile | What it is |
|---|---|
| Queries followed | How many queries are on the page, of all that ran in the window. |
| Grew dearer | Queries whose price rose by the toolbar multiple and stayed there. Click to filter the grid. |
| Cost added | What those changes have cost since they happened. |
| Grew cheaper | Queries whose price fell by the multiple, and what that has saved. Click to filter. |
| Simply ran more | Queries whose price held while their calls rose by the multiple. Click to filter. |
| Biggest change | Only when something grew dearer: the largest multiple, and the query. |
The mass curve
The plot is always square, because the reading is a distance from a diagonal, and a diagonal at any other angle exaggerates that distance one way.
- Faint gray lines are queries the page is not naming. How wide that bundle is says whether the whole database drifts or one query does.
- Colored lines are the eight changes that cost or saved the most, drawn over the bundle.
- Dashed lines from the corners to the marker are the two straight runs the page reads the line as. Their slopes are the price before and after.
- A filled marker is a change the page is naming, with its date and multiple beside it. A hollow marker is the deepest bend of a line the page will not name a change on.
- Ticks across a colored line put time back on the picture. Time is not an axis here: it runs along the line, so ticks far apart are a busy stretch and ticks bunched together are a quiet one. The legend under the chart says what one tick stands for.
| Color | Finding |
|---|---|
| Orange | Grew dearer |
| Purple | Never settled |
| Blue | Ran more |
| Green | Grew cheaper |
The colors say what happened rather than how bad it is: a query that got cheaper and one that got dearer are the same kind of finding read two ways.
Hover over a line for the query, its price before and after, its total, and the finding. Click a line to select its grid row.
Reading the grid
One row per query followed, the changes that cost the most first, then the queries that did not change, then the ones that got cheaper.
| Column | What it is |
|---|---|
| # | Rank. |
| Object | The procedure, function or trigger, when the statement belongs to one. |
| Query | The statement text, with the parameter list Query Store prefixes removed. |
| What changed | Grew dearer, Grew cheaper, Ran more, Never settled or Held steady. |
| Was / Now | The price of one execution before and after the deepest bend. |
| Change | Now divided by was. Empty on rows the page is not naming. |
| When (UTC) | When the change happened: the price change, or for Ran more the change in calls. |
| Added | What the change has cost since: everything spent after the bend, less what the same executions would have cost at the old price. Negative is a saving. |
| Ran | Calls after the arrival bend divided by calls before it. |
| Executions | Regular executions in the window. |
| Share | The query’s total on the chosen measure as a share of the whole window. |
Whether a price change came all at once or gradually, the query’s total, and how many collection intervals it ran in are on the chart label and in the double-click text rather than in columns of their own, so the grid fits a 1280×900 window.
Double-click a row to see the statement with its figures beside it. Right-click for:
| Action | What it does |
|---|---|
| Explain this query’s curve | The figures and what they mean, in words. |
| Show the statement | The statement in the query window. |
| Go to Plan Regressions | For a query that grew dearer or never settled. |
| Go to Deployment Impact | For a statement that belongs to a module. |
| Copy query text | The statement, without the parameter list. |
| Copy the query behind this report | The whole batch, ready to run in SSMS. |
How a change is found and graded
The bend is the point furthest from the diagonal that has at least a tenth of the query’s executions on each side of it. Without that floor, one interval at either end would decide a price.
At once or gradually. A price that changed on a day follows the two straight runs through the bend exactly. One that drifted from one level to another departs from them by about a quarter of how deep the bend is. Under 12% of the depth is At once.
Never settled. Two shapes are refused a named change:
- A line on both sides of the diagonal by a comparable amount (at least 4 share points each way) has no single change in it.
- A line the two straight runs do not describe (departing from them by more than 60% of the bend’s depth). One catastrophic hour at fifty times the usual price lifts the average of everything after it, so the arithmetic alone would report a query that grew dearer and stayed dearer. It did not.
What a query needs to be followed: at least 8 collection intervals, at least 50 executions, and with Real work, at least 5 seconds of the measure. The sixty largest that qualify are followed, and how many were left out for too few intervals is said under the summary.
Where the data comes from
| Source | What it gives |
|---|---|
sys.query_store_runtime_stats |
Executions and the average of the measure per plan per interval. |
sys.query_store_runtime_stats_interval |
The window, and the time of each reading. |
sys.query_store_plan, sys.query_store_query, sys.query_store_query_text |
The query behind each plan, its module and its text. |
What the query gets right
Executions are summed across plans per interval, because a query that changed plan is exactly the case this report is for, and splitting it by plan would hide the change inside two short lines.
Intervals a query did not run in are not invented as zeroes. Both running totals stand still there, which is a point the line already has.
The window ends at the last completed interval, not at the clock.
Only regular executions are measured. A timed out execution’s duration is how long the client waited, so a query that started timing out would otherwise look cheaper. Aborted and failed executions are counted in the footer.
The query is followed by query_id. A statement whose text changed, for example in an altered procedure, is a new query from that moment, so its line starts there. Deployment Impact compares across that kind of change.
Messages you may see
Nothing on this database has run in enough intervals to follow. No statement appeared in 8 or more collection intervals. A longer window is the usual fix.
No query here is large enough to be worth following. Nothing cleared the floor of 50 executions and 5 seconds. Choose All queries, or a longer window.
No query has settled on a new price, and N of them have never had a steady one. See Never settled above: a bad afternoon, or a query with more than one price, such as a parameter sensitive one.
Nothing changed price. N queries are simply being called more often. A fact about the application rather than the database.
Related reports
| Report | Why you would go there |
|---|---|
| Plan Regressions | Whether the plan changed on the day the price did. |
| Plan Differences | What separates a query’s plans. |
| Deployment Impact | Whether a module was altered around the date. |
| Slow Periods | For a query that never settled because of one bad stretch. |
| Parameter Sensitive Plans | For a query that never settled because it has more than one price. |
| Workload Change | How much the whole workload changed between two windows. |
| CPU by Query | What the database spends its time on, changed or not. |
Frequently asked questions
Why is the date a little before the change I made? The date is the start of the last collection interval before the bend, so it can be up to one interval early. With 60 minute intervals, a change at 14:20 shows as 14:00 or 13:00.
A query doubled in cost but is shown as Held steady. The price has to change by the toolbar multiple, and hold, with a tenth of the executions on each side. A query that doubled for its last few executions has too little evidence after the bend yet.
Why is a query that got slower shown as Never settled? Its line leaves the diagonal and comes back, or goes both ways. Naming one date would describe a change that did not last.
The Added column is empty on most rows. It is only filled on rows the page names as a change. Every line has some small difference either side of its deepest point, and showing it would read as a finding.