Duration Spread

Overview

Is this query’s worst case getting worse, and is its typical case moving with it?

Query Store keeps five numbers for every plan in every interval: how many times it ran, its average duration, the standard deviation, the fastest run and the slowest run. Almost every page reduces those to the average, and the average is exactly the number that does not move in the case this report is for.

A plan that suits fewer and fewer parameter values, a seek whose range is growing behind it, a lock that is starting to be contended, and a table that has outgrown the memory it is cached in all begin the same way: most executions are unaffected and a growing minority are not. The average barely moves. The distance between the average and the slowest run is what opens up, and this page shows it.

The Duration Spread report: the chart above the grid
The whole page on a database with a month of Query Store history.

Tail drift: a ratio of ratios

The window is cut in half, and each query’s typical case (its execution weighted average) and worst case (its slowest run) are compared between the halves.

worst case growth   = slowest run in the second half / slowest run in the first half
typical growth      = average in the second half / average in the first half
tail drift          = worst case growth / typical growth

Tail drift is 1 when the whole distribution moved together, and above 1 only when the distance between the typical run and the worst run opened up. Ranking by the growth of the slowest run alone would put every query that simply got slower at the top, which is a different question.

Each query gets one finding:

Finding Rule
Worst case drifting The slowest run grew by at least 100 ms, to at least 1.5 times what it was, and tail drift is at least 1.4.
Slower across the board The slowest run grew by at least 100 ms and the average grew by at least 1.3 times.
Worst case coming in The slowest run is two thirds or less of what it was.
Long tail, not moving The slowest run in the second half is at least 15 times the average.
Steady None of the above.

The tests run in that order: a worst case that tripled while the average doubled is drift, not a shift. The 100 ms floor is there because a tail that went from two milliseconds to six has tripled and nobody will ever notice.

A query has to have run in both halves to be graded. A query that only ran in the second half has no earlier self, and every ratio for it would be a division by nothing; those are counted in the footer.

Where to find it

In the tree, under a database, Real Time → Query Store → Duration Spread.

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 runtime statistics live inside Query Store.
VIEW DATABASE STATE To read the Query Store catalog views.

The toolbar

Control What it does
Tail drift / Slowest run growth / Widest spread / Most runs Which 40 queries the grid lists and in what order. The ranking decides which queries are read, so changing it reads Query Store again.
4 h / 12 h / 24 h / 3 d / 7 d The window, cut in half at its midpoint. 24 hours is the default.
Any avg / 1 ms+ / 10 ms+ / 100 ms+ Only queries averaging at least this long are considered. A query nobody can perceive has no tail worth watching. 1 ms is the default.
Turn Query Store on Only when Query Store is off and a setting would fix it.
Refresh Re-reads Query Store.

A query also needs at least 10 regular executions in the window.

Reading the chart

The Duration Spread chart
The chart on its own, from the same capture.

A verdict at the top names the worst drifting query, or the worst whole shift, or the widest long tail, or says that every spread moved with its average. Tiles:

Tile What it is
Queries compared Queries that qualified in both halves, their executions and the number of intervals in the window.
Tails drifting Queries whose worst case outran the typical run. Click to list only those.
Widest drift The largest tail drift and the slowest run now.
Whole shift Queries whose typical and worst cases both grew. Click to list only those.
Long tails, not moving Queries whose slowest run is over 15 times their average. Click to list only those.
Slowest single run The slowest execution of any listed query, with its average.
Tails coming in Shown only when some worst cases fell by a third. Click to list only those.

Click a filtering tile again to clear the filter.

Under the tiles, the selected query (the first row of the grid when nothing is selected), one mark per Query Store interval of the window:

  • The wick runs from the fastest execution in the interval to the slowest.
  • The block is the average plus and minus one standard deviation, trimmed to the fastest and slowest runs. It is not a quartile box: Query Store keeps no quartiles.
  • The line across the block is the average.
  • A filled block means the average rose from the previous interval the query ran in; hollow means it fell.
  • The color is the plan that ran most of the interval’s executions. A change of color along the row is a change of plan, and the key above the chart names the plans.
  • A dotted line is an interval the query did not run in. It is kept, so the axis stays evenly spaced in time and “did not run” is never confused with “ran and was fast”.
  • The dashed horizontal line is the query’s average over the whole window.
  • The dashed divider is where the second half begins.

The duration axis is logarithmic, so “the slowest run doubled” is the same distance wherever it happens. Times on the axis are UTC.

A wick that lengthens across many intervals while the block stays put is the pattern this page exists to find. One tall wick in one interval is a single event. Hover over a mark for the interval’s figures; select another row to redraw the chart.

Reading the grid

The Duration Spread grid
The first rows of the grid, from the same capture.
Column What it is
# Rank under the chosen ranking.
Object The procedure, function or trigger, when the statement belongs to one.
Query The statement text, with the parameter list Query Store prefixes removed.
Finding Worst case drifting, slower across the board, long tail not moving, worst case coming in, or steady.
Runs Regular executions in the window.
Avg then / Avg now The typical case: the average duration in the first and second halves.
Max then / Max now The worst case: the slowest run in the first and second halves.
Tail drift Worst case growth divided by typical growth.
Spread The slowest run in the second half as a multiple of its average.
Plans Plans the query used in the window.
Last run (UTC) The last execution Query Store recorded.

The standard deviation of every execution in the window, pooled across plans and intervals, and the number of intervals the query ran in are not grid columns; both are in the double-click text and in Explain this query’s spread.

Double-click a row to see the statement with the two halves and every interval beside it. Right-click for:

Action What it does
Explain this query’s spread The two halves, the movement, and every interval’s fastest, average, standard deviation and slowest run.
Show the statement The statement in the query window.
Go to Plan Regressions When the query used more than one plan.
Go to Parameter Sensitive Plans When it used one plan throughout, so there is no better plan to force.
Go to Performance Baselines When the whole distribution shifted.
Go to Waits by Query Whether the slow runs were working or waiting.
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, average, standard deviation, minimum and maximum duration per plan per interval.
sys.query_store_runtime_stats_interval The window, its midpoint, and every interval for the gaps.
sys.query_store_plan, ..._query, ..._query_text The query behind each plan.
sys.database_query_store_options The readiness banner.

How the standard deviation is combined

Standard deviations do not add. When two plans ran in one interval, or when the whole window is summarized, the deviation is pooled through the second moment:

mean      = sum(average x runs) / sum(runs)
variance  = sum((deviation squared + average squared) x runs) / sum(runs) - mean squared

This is exact because Query Store’s stdev_duration is the population standard deviation (at two executions it is exactly half the range). Averaging the per interval deviations instead understates the spread of exactly the queries this report is for: four runs averaging 100 with deviation 10 and one run of 300 have a pooled deviation of 80, not 8.

Three things the query gets right

The window ends at the last completed interval, not at the clock, and both halves are closed on interval boundaries. The interval still being filled would make the second half look quieter.

Only regular executions are read. An aborted execution’s duration is the client’s timeout, and a timeout in the second half would read as exactly the tail this page hunts for. Aborted and failed executions are counted in the footer.

Averages are weighted by executions, never an average of averages.

Messages you may see

There was nothing here to compare against itself. No query ran in both halves at least 10 times above the floor. Choose a longer window or a lower floor. On a database with an hourly collection interval, a short window is also a small number of marks.

N queries ran in only one half and could not be compared. New or retired queries have no earlier self.

N executions were aborted or ended in an exception. Counted, and kept out of every figure on the page.

Query Store’s history starts after the window begins. The first half is only partly covered.

Report Why you would go there
Performance Baselines Whether a whole shift is larger than the query’s own ordinary variation.
Parameter Sensitive Plans Whether one plan is producing both the fast runs and the slow ones.
Plan Regressions When the query used more than one plan and one of them is the slow one.
Waits by Query Whether the slow executions were working or queueing.
Change Significance Whether a change in the average is larger than noise.
Latency Service Levels How many executions miss a duration target.

Frequently asked questions

The verdict says the worst case grew 1,667 times. Is that real? The slowest single run is the least stable figure Query Store keeps: it is one execution. Select the row and look at the chart. One tall wick in one interval is a single event; wicks that lengthen across many intervals are a trend.

Why is the block sometimes cut off at the bottom? The block is trimmed to the fastest run in the interval. Query Store’s minimum and maximum are not always consistent with its average and deviation, and a block reaching below the fastest run would claim runs that never happened.

Why does a query that got much slower show as drifting rather than slower across the board? Its worst case outran even its slower average. The verdict says when the typical case grew as well.

Why are there no quartiles? Query Store does not record them. The block is one standard deviation either side of the average and is labeled that way.