Deployment Impact
Overview
“We deployed on Tuesday. What did it cost?” Most comparisons split a window somewhere nothing happened: this week against last week, the recent half against the earlier half. A change made at four on Tuesday afternoon then has Tuesday morning on both sides of it.
This report splits at the moment each object changed. sys.objects.modify_date records when a procedure, function or trigger was last created or altered, and Query Store attributes every statement back to its module through object_id. For each changed module the report compares what its statements cost in the intervals wholly before the change against the intervals wholly after it.
It does not claim the change caused what came after. A release goes out on the evening the month end batch starts, and both are true. What it shows is the run up beside the aftermath, which is what separates a procedure that stepped the moment it was altered from one that was already climbing for two days.
Rates, not totals
An object altered two hours ago has two hours of history after its change and days before it, so its totals after the change are smaller whatever happened. Every figure that crosses the change is a rate, and the toolbar chooses which:
| Basis | What it answers |
|---|---|
| Per run | What one execution costs now against before. The developer’s question. |
| Per hour | What the object costs the database per hour of clock time now against before. |
They do not always agree. A procedure rewritten to do in one call what used to take forty is slower per run and cheaper per hour.
Added a day is always worked out from the hourly rates on each side, so it is the same on both bases.
Where to find it
In the tree, under a database, Real Time → Query Store → Deployment Impact.
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, before the change | The before side has to have been recorded. |
VIEW DATABASE STATE |
To read the Query Store catalog views. |
VIEW DEFINITION on the modules |
To show a module’s text. Without it the measurements still appear. |
The toolbar
| Control | What it does |
|---|---|
| Duration / CPU / Reads | The measure compared. Changing it redraws without reading Query Store again. |
| 24 h / 3 d / 7 d / 14 d / 30 d | How far back to look for changes. |
| Per run / Per hour | The basis, described above. |
| 5 runs / 20 runs / 100 runs | How many executions a change needs on each side before it is graded. |
| 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 names the change that costs the most since it happened, or says that none of the changes can be measured yet, or that nothing is costing more. The tiles:
| Tile | What it is |
|---|---|
| Objects changed | Modules created or altered in the window, and how many have runs on both sides. |
| Added a day | What the changes that cost more add to a day of the database, on the chosen measure. |
| Slower after the change | How many changes are graded slower, and the worst. Click to filter the grid. |
| Better after the change | How many are graded better, and the best. Click to filter. |
| Last change | The most recent change, in UTC. |
| Ran, then dropped | Only when present: object ids Query Store recorded running in the window that no longer exist, which is what a release that drops and recreates its procedures leaves behind. |
The event study
The chart draws up to eight changed objects, from the top of the grid, as one line each.
- The horizontal axis is time from each object’s own change, not the clock. Every line is slid along so that its change sits on the dashed rule in the middle, so a Tuesday change and a Thursday change overlay directly. Both sides share one time scale: two hours of evidence after a recent change is drawn two hours wide, next to however many days came before it.
- The vertical axis is the object’s cost against its own level before its change, on a log scale that is symmetric about “same”. Half and double are the same distance from the line.
- The shaded half is before the change.
- A line breaks where there were no readings, and it breaks at the rule itself, because the last reading before a change and the first after it are two versions of the code.
- A reading beyond 32 times the before level is drawn on the edge with a small chevron rather than dropped.
- The axis never zooms in closer than 1.25x, so a change that moved nothing does not look dramatic.
| Color | Reading |
|---|---|
| Red, drawn over the others | Slower since the change |
| Green | Better since the change |
| Gray | The change did not move it |
| Orange | Too little to grade |
Hover over a line for its before level, its latest reading and the finding. Click a line to select its grid row. Readings are grouped into buckets of 30 minutes for a 24 hour window up to 6 hours for 30 days, never finer than Query Store’s own interval.
Reading the grid
One row per changed object, the changes that added the most to a day first, then the ones that cannot be measured yet, most recent first.
| Column | What it is |
|---|---|
| # | Rank. |
| Object | The procedure, function or trigger. |
| Finding | See below. |
| Notes | When it changed, the parent table of a trigger, and anything else worth knowing about the row. |
| Change | After divided by before. |
| Added a day | The difference in the hourly rate, times 24. Negative is a saving. |
| Before / After | The measure per run or per hour, on each side. |
| Failed after | Executions after the change that were aborted or ended in an exception. |
| Runs before / Runs after | Regular executions in the intervals wholly before and wholly after the change. |
| Kind | Procedure, scalar function, inline function, table function or trigger. |
| Changed (UTC) | modify_date, converted to UTC. |
| Evidence | Hours of Query Store history on each side. |
| Statements | How the module’s statements compare across the change, matched on query_hash: same, edited, new and gone. |
The Notes column takes the width the others leave, so it grows with the window. The last columns (Kind, Changed (UTC), Evidence and Statements) sit past the edge of a 1280 pixel screen; scroll right for them, or hover over a row, whose tooltip lists every column along with the full finding and notes.
| Finding | Meaning |
|---|---|
| Slower since the change | At least 1.25x on the chosen basis, and adding at least a second a day (100,000 pages for reads). |
| Better since the change | 0.8x or less on the chosen basis. |
| The change did not move it | Graded, and in between. |
| New, so there is nothing before it | Created in the window, or no runs before the change. |
| Changed, and nothing has run it since | No executions after the change. |
| Too little either side to compare yet | Fewer runs on one side than the toolbar asks for. |
Double-click a row to see the module’s current definition with the before and after figures at the top. Right-click for:
| Action | What it does |
|---|---|
| Show the definition and what changed | The same as double-click. |
| Copy the definition | The module’s current text. |
| Copy object name | The schema qualified name. |
| Go to Statement Hot Spots | Which statement inside a module the time is going to. |
| Go to Change Significance | Whether a difference is larger than the object’s normal variation. |
| Copy the query behind this report | The whole batch, ready to run in SSMS. |
SQL Server keeps no earlier version of a module, so the definition shown is what the change produced, not what it replaced.
Where the data comes from
| Source | What it gives |
|---|---|
sys.objects |
The modules of types P, FN, IF, TF and TR, with create_date and modify_date. |
sys.query_store_query |
Each statement’s object_id and query_hash. |
sys.query_store_runtime_stats |
Executions and averages per plan per interval. |
sys.query_store_runtime_stats_interval |
Interval boundaries, and where Query Store’s history begins. |
sys.database_query_store_options |
The interval length. |
sys.sql_modules |
The current definition. |
Five things the query gets right
The interval the change landed in is counted on neither side. Counted before, it would carry the new code into the old average; counted after, the old code into the new one. The hours of evidence on each side stop at that interval’s edges too.
Statements are matched on query_hash, not query_id. An ALTER that leaves a statement’s text untouched keeps its query_id. One that changes only a literal gives the statement a new query_id with the same query_hash. A rewritten statement gets a new hash as well. Matching on the hash is what lets the Statements column tell an edited statement from a new one.
The clock is converted. Query Store intervals are UTC and sys.objects dates are the server’s local time. modify_date is converted to UTC at the server’s current offset, which the footer names. A change made on the other side of a daylight saving switch is placed an hour out.
The before side starts where Query Store’s history starts when that is later than the window, so a rate is never divided by hours nobody recorded.
Only regular executions are averaged. Aborted and failed executions are counted separately, so a change that introduced timeouts is not reported as a speed up.
What the report cannot see
Tables, views and indexes. Query Store attributes time to the module a statement came from and has nothing to attribute to a table, so a release made entirely of schema changes is invisible here. A module dropped and recreated rather than altered arrives as a new object with no history.
Messages you may see
Nothing in this database was created or altered in the window. The ordinary case on a stable system. Widen the window to find an older release.
N objects were changed, and none of them can be measured yet. Measuring a change needs a workload on both sides of it. A module that has not been called at all since it changed is either unreached code or a release that did not take.
None of the changed objects has run on both sides of its change. The grid lists them; there is no before level to draw a line against.
Changed in the interval still being collected The change is newer than the last completed interval. Refresh after the interval closes.
Related reports
| Report | Why you would go there |
|---|---|
| Statement Hot Spots | Which statement inside the changed module the time goes to. |
| Change Significance | Whether the difference is larger than the object’s own variation. |
| Cost Drift | When a query’s price changed, for changes sys.objects does not record. |
| Plan Regressions | Whether a statement’s plan changed around the same time. |
| Workload Change | What changed across the whole workload between two windows. |
Frequently asked questions
Why does a procedure I did not touch show as changed? modify_date moves for any ALTER of the module, including one that re-ran the same text, which is what many deployment scripts do for every object. The Statements column shows same for every statement when the body did not change.
Why is the Change column empty? The row cannot be graded: too few runs on one side, no runs after the change, or no history before it.
The change time is an hour out. The server’s offset from UTC changed between the modify date and now, because of daylight saving.
Can it compare the old and new source? No. SQL Server stores only the current definition. Source control is where the old version lives.