Change Significance
Overview
Is this change real, or is the query just varying the way it always does?
Every comparison page ranks by the size of a difference. A query whose average went from 40 ms to 90 ms looks the same on all of them whether it ran two million times either side of the line or four times, and in the second case there is nothing there. An afternoon spent tuning a query that did not change is an afternoon spent tuning nothing.
This report puts the amount of evidence on the picture. It is the filter to put in front of Plan Regressions, Workload Change and Rank Movement: they say what moved the most, and this says which of those moves are larger than noise.
The test
The window is cut in half. For every query with at least two regular executions in each half:
| Figure | How it is worked out |
|---|---|
| Change | Average in the second half divided by the average in the first. 1 is no change, 2 is twice as slow, 0.5 twice as fast. |
| Evidence | The two execution counts combined harmonically: n1 x n2 / (n1 + n2). Ten thousand executions before and four after is a comparison resting on four. |
| Run to run spread | Each half’s standard deviation divided by its average, combined as the root mean square of the two. Relative, because 100 ms of scatter on a two second query and on a 110 ms one are different queries. |
| Noise floor | The median run to run spread of every compared query that recorded one. A transactional database where everything takes the same four milliseconds gets a low floor; a reporting database gets a high one. |
| Standard errors out | The size of the change on a log scale divided by (spread / square root of evidence), using the query’s own spread or the noise floor, whichever is larger. |
A change 3 or more standard errors out is a real change: a real regression if the query got slower, a real improvement if it got faster. Three is the same rule Performance Baselines uses for an abnormal interval; with hundreds of queries compared, two would hand back a dozen changes a quiet database produced by chance.
A change of 1.5 times or more (or two thirds or less) that is not proven is large, unproven: it would lead any ranking by percentage, and it cannot be told apart from the query being itself.
Each query’s spread is floored at the noise floor. A query quieter than typical is tested as though it were typical, so nothing drawn inside the band can ever be called proven. The relationship is deliberately one way: not everything outside the band is proven, because a query noisier than typical has to move further.
Where to find it
In the tree, under a database, Real Time → Query Store → Change Significance.
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, and its per interval standard deviations, arrived in SQL Server 2016. |
| Query Store on for the database | The runtime statistics live inside Query Store. |
VIEW DATABASE STATE |
To read the Query Store catalog views. |
The toolbar
| Control | What it does |
|---|---|
| Duration / CPU | The measure compared: avg_duration with stdev_duration, or avg_cpu_time with stdev_cpu_time. |
| 6 h / 12 h / 24 h / 3 d / 7 d | The window, cut in half at its midpoint. 24 hours is the default. |
| Real changes / All compared | Whether the grid lists only the proven changes or every compared query. The chart always draws every compared query. |
| 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 which situation the database is in:
- A serious share of recent time is a real regression (10% or more of the second half’s total).
- Some queries really got slower, and what share of the time they are.
- Nothing got slower by more than its own noise, though some queries moved a long way without it being provable.
- Nothing measurable got slower.
- Query Store recorded no run to run spread, so nothing can be tested. This happens when each query ran once per collection interval.
Tiles:
| Tile | What it is |
|---|---|
| Queries compared | Queries with at least two executions in each half, and how many could not be compared. |
| Noise floor | The database’s typical run to run spread. |
| Proven regressions | Real regressions and their share of the second half’s time. Click to list only those. |
| Proven improvements | Real improvements. Click to list only those. |
| Large but unproven | Changes of half again or more that are not proven. Click to list only those. |
Click a filtering tile again to clear the filter.
Under the tiles, the precision funnel:
- Across is the evidence, on a logarithmic scale.
- Up is the change as a multiple, on a logarithmic scale centered on no change, so twice as slow and twice as fast are the same distance from the middle.
- The band is how far a query with the noise floor’s spread could drift on chance alone with that much evidence: the outer contour at 3 standard errors, the inner at 2. It narrows to the right, because more executions leave less room for chance.
- A filled dot is a real change: red slower, green faster.
- A hollow dot is outside the band and not proven: a query too erratic to be sure about. That is a finding about the query rather than about the change.
- A faint dot is inside the band: not a change, whatever its percentage says.
- The size of a dot is the time the change was worth, so a large move on something nobody runs is visibly worth nothing.
Hover over a dot for the query, its change, the evidence, how far noise reaches and how many standard errors out it is. Click a dot to select its row.
Reading the grid
The grid lists the real changes by default, ordered by the time the change added, largest first. Every column sorts.
| Column | What it is |
|---|---|
| # | Rank by time added across every compared query. |
| Object | The procedure, function or trigger, when the statement belongs to one. |
| Query | The statement text, with the parameter list Query Store prefixes removed. |
| Change | The change as a percentage, or as a multiple beyond three times. |
| Verdict | Real regression, real improvement, large unproven, or within the noise. |
| Sigmas | How many standard errors outside the noise the change is. 3 or more is proven. |
| Noise allows | How far this query could have drifted by chance at its tested spread and evidence. |
| Runs before / Runs after | Regular executions in each half. |
| Before / After | The execution weighted average in each half. |
| Added | The time the change added: (after minus before) times the runs after. Negative is time saved. |
| Spread | The query’s own run to run relative spread. |
The standard deviation of each half, pooled across plans and intervals, is in Explain this test rather than the grid, so the row fits a 1280×900 window. Double-click a row to see the statement with the test written out beside it. Right-click for:
| Action | What it does |
|---|---|
| Explain this test | Both halves, the spread, the evidence, how far noise reaches and the verdict, in words. |
| Show the statement | The statement in the query window. |
| Go to Plan Regressions | For a real regression. |
| Go to Duration Spread | For a large unproven change, whose finding is the variability. |
| 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, averages and standard deviations of duration and CPU per plan per interval. |
sys.query_store_runtime_stats_interval |
The window and its midpoint. |
sys.query_store_plan, ..._query, ..._query_text |
The query behind each plan. |
sys.database_query_store_options |
The readiness banner. |
The batch returns moments rather than answers: for each half, the executions, the sum of average times executions, and the sum of (deviation squared plus average squared) times executions. The variance of each half is recovered from those in the report, because standard deviations do not add, and the test is done in the report because the noise floor is not known until every query has been read. Query Store’s stdev_duration is the population standard deviation, which is what makes the pooling exact.
Three things the query gets right
The window ends at the last completed interval, and both halves are closed on interval boundaries. The interval still being filled has a partial count and a partial average in it.
Only regular executions are compared. An aborted execution’s duration is how long somebody waited before giving up, and a half with more of them would read as a half in which the query got faster. They are counted in the footer; the Timeouts and Failed Executions page is about them.
Averages are weighted by executions, never an average of averages.
Messages you may see
Nothing ran in both halves of this window, so nothing can be compared. A longer window, or a shorter one if Query Store was recently cleared, gives both halves something to hold.
Query Store recorded no run to run spread, so nothing here can be tested. Each query ran once per interval. A longer window or a busier period gives the intervals something to measure.
N ran only in the recent half, N only in the earlier half and N fewer than 2 times in one of them. None of those can be compared.
The N least run are not drawn. The chart draws the 4,000 most executed compared queries.
Related reports
| Report | Why you would go there |
|---|---|
| Plan Regressions | The plan in use either side of a real regression, and the script to force the old one. |
| Workload Change | Which queries moved the database’s total, split into running more and costing more. |
| Rank Movement | Which queries are climbing the list over several days. |
| Duration Spread | Whether a query’s worst case is pulling away from its typical case. |
| Parameter Sensitive Plans | A query whose two behaviors are both real. |
| Waits by Query | Whether added time was work or waiting. |
Frequently asked questions
A query got 2% slower and the page calls it a real regression. It ran tens of thousands of times, so even 2% is far outside what its scatter could produce. Real is not the same as important: look at the Time added column and the Proven regressions tile’s share of recent time.
A query doubled and the page says it is within the noise or unproven. It ran a handful of times, or it varies a great deal from run to run. Widen the window until there is enough of it to test.
Why is the noise floor different for Duration and CPU? They vary differently. Duration includes waiting, which is usually noisier than CPU time.
Why is a hollow dot outside the band not proven? The band is drawn at the database’s typical spread. That query varies more than typical, so it has to move further than the band shows before its own test is passed.