Slow Periods
Overview
Most pages answer a question about a subject: what this query costs, which plan regressed. The question a DBA is actually handed is about a moment: it was slow at nine o’clock on Tuesday; what was running then that is not usually running?
This report cuts the window by how slow the database was, and then asks something no ranking can: which queries were unusually heavy in those intervals and not otherwise.
- A query heavy in nine of the twelve worst hours and in four of the other three hundred is telling you something.
- A query heavy in nine of the twelve and in two hundred of the other three hundred is telling you nothing. It is usually the biggest query on the database, which is why a ranked list puts it first on every page.
How slow is decided
Not by total CPU, and not by the average duration across all queries. Both of those find the hour the nightly batch ran, which is not a slow hour, and neither finds the hour when every lookup took two seconds, which is.
Instead, every query in an interval is compared against its own usual cost per execution (its median across the window). The ratios are combined into one number for the interval:
- combined in logarithms, so one query fifty times slower cannot own the reading;
- weighted by each query’s executions, so the number is what the typical execution went through.
2x means the typical execution took twice as long as it usually does. An interval at or above the threshold on the toolbar (1.5x by default) is a slow period.
Three rules keep that honest:
- A query needs at least 5 intervals of its own history before its ratio counts.
- An interval in which no query could be compared is never called slow; a missing reading is not a good one either.
- At most a quarter of the window can be slow. If more intervals clear the threshold, only the worst are kept, because a baseline made mostly of slow periods is not a baseline. The footer says so.
The window needs at least 12 completed intervals before any of this can be done.
How heavy is decided
A query is heavy in an interval when it spent at least twice the duration of its own median ordinary interval there. The median is taken over the intervals that were not slow, because a baseline that includes the incident is one the incident has already moved. A query that never ran outside the slow periods has no baseline at all, and is marked as such rather than scored as ordinary.
Cause or victim
Being heavy in the slow periods does not make a query the cause of them. The page separates the cases it can tell apart:
| What it did | Meaning |
|---|---|
| Arrived more | It ran at least 1.5 times as often per second inside the slow periods. It brought work with it: a candidate cause. |
| More and slower | Both at once, which is what a runaway job looks like from the outside. |
| Only then | It never runs outside the slow periods, so there is nothing to compare. Usually a job. |
| Slowed down | It ran as often as usual and each execution took at least 1.5 times as long. It was held up along with everything else: a victim, and tuning it is a week spent on the wrong thing. |
| No clear change | Neither, or not by enough to say. |
Where to find it
In the tree, under a database, Real Time → Query Store → Slow Periods.
The Query Store folder is hidden below SQL Server 2016, and on master and tempdb, where Query Store cannot be turned on. Reaching the page another way, from history or the command palette, runs the same checks and produces a message instead.
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. |
| At least 12 completed Query Store intervals in the window | An interval can only be called slow against the ones around it. |
VIEW DATABASE STATE |
To read the Query Store catalog views. |
The toolbar
| Control | What it does |
|---|---|
| Real work only / Everything | Real work only leaves out queries that spent under 5 seconds in the whole window, so the plot is not a crowd of statements that ran twice. The counts on the tiles cover every query either way. |
| 24 h / 3 d / 7 d / 14 d | The window. Longer windows give the comparison more periods to work with. |
| 1.25x / 1.5x / 2x / 3x slower | How much slower than usual counts as slow. Remembered for this page between sessions. |
| 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, a verdict says which of these pictures you are looking at:
| Verdict | When |
|---|---|
| (Query) was unusually heavy in N of the M slow periods | A distinct coincidence that arrived more, did both, or only runs then. The first thing to look at. |
| The slow periods are real and nothing in the workload arrived to cause them | Every distinct coincidence slowed down without running more: victims. Look at what the database was waiting on. |
| N periods ran slower than usual, and no query stands out | No coincidence is larger than counting error. With few slow periods, a longer window helps. |
| Nothing in this window was slower than this database’s usual | No interval reached the threshold. A query somebody complained about was as slow at a quiet moment as at a busy one, so the reason is in the query. |
| There is not enough history to compare intervals | Fewer than 12 completed intervals in the window. |
| N periods, and nothing to attribute them to | The slowdown is real, but no query cleared the Real work only floor. |
Six tiles carry the numbers:
| Tile | What it is |
|---|---|
| Slow periods | How many intervals were called slow, of the intervals that could be measured. |
| Typical slowdown | How much slower the typical execution was across the slow periods, combined in logarithms. |
| Worst period | The slowest interval and when it started (UTC). |
| Time above usual | Time spent inside the slow periods above what the same number of seconds ordinarily holds: what the incident cost. |
| Surged with them | Queries heavy in a share of the slow periods at least 50 percentage points above their share of the ordinary periods, and the share of the time spent in the slow periods that went on them. |
| First to look at / Strongest coincidence | The query the verdict names, and how many slow periods it was heavy in. |
The association plot
Under the tiles, every query in the grid is a mark on a square plot:
- Across is the share of the ordinary periods it was heavy in.
- Up is the share of the slow periods it was heavy in.
- The diagonal is where a query that is heavy just as often either way lands. Height above it is the finding; where along it a mark sits only says how common the behavior is.
- The shaded lens around the diagonal is how far apart two rates counted over this many periods can drift by chance. It is widest in the middle and never thinner than one period. With a single slow period it covers the whole square, because one period is not evidence of anything.
- A hollow mark is inside the lens: the counting cannot tell it from chance.
- A mark’s size is the time the query spent inside the slow periods.
- A mark’s color is what it did: arrived more, more and slower, only then, slowed down, or no clear change. Color is never a grading, because a query that slowed down along with everything else is not a fault.
- Marks on the dotted left edge never ran outside the slow periods.
Rates counted over a few periods take only a few values, so marks often land on the same spot. Where they do, the one ranked first in the grid is drawn on top and answers the click; selecting any row in the grid rings its mark wherever it is.
When the page is wide enough, the stripe beside the square lists the slow periods, worst first, with their slowdown and executions.
Hover over a mark for its counts and what it did; click it to select the row in the grid.
Reading the grid
Rows are ordered by coincidence, strongest first.
| Column | What it is |
|---|---|
| # | Rank by coincidence, then by time spent inside the slow periods. |
| 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 it did | Arrived more, more and slower, only then, slowed down, or no clear change. |
| In slow | Share of the slow periods it was heavy in. |
| Outside | Share of the other periods it was heavy in, or “never ran”. |
| Coincidence | The difference between the two, in percentage points. |
| Beyond chance | Whether that difference is outside the lens, and whether it is large enough to name. |
| Ran | Executions per second inside the slow periods against outside them. |
| Per run | Duration per execution inside the slow periods against outside them. |
| Time in | Time it spent inside the slow periods. |
| Share | That time as a share of all time spent inside the slow periods. |
| Runs | Regular executions in the window. |
The time each query spent inside the slow periods above its own ordinary rate is in Explain this query (Above its usual) rather than in the grid, so the row fits a 1280×900 window.
Double-click a row to see the statement with its comparison beside it. Right-click for:
| Action | What it does |
|---|---|
| Explain this query | The counts, ratios and what they mean for this query. |
| Show the statement | The statement in the query window. |
| Go to Performance Baselines | The query against its own history. |
| Go to Waits by Query | For a query that slowed down: what it waited on. |
| Go to Load Sensitivity | Whether queries slow down with how busy the server was. |
| Copy query text | The statement, without the parameter list. |
| Copy the query behind this report | Both batches, ready to run in SSMS. |
Where the data comes from
| Source | What it gives |
|---|---|
sys.query_store_runtime_stats |
Executions, duration and CPU per plan per interval. |
sys.query_store_runtime_stats_interval |
The intervals being compared. |
sys.query_store_plan, ..._query, ..._query_text |
The query behind each plan. |
sys.database_query_store_options |
The readiness banner. |
The page runs two batches. The first measures every completed interval in the window. The page then decides which intervals are slow, and the second batch measures every query against that list, over exactly the same window as the first.
The window ends at the last completed interval, not at the clock. Only regular executions are counted: an aborted execution’s duration is the client’s timeout, and a page that counted them would report an incident that timed everything out as an improvement. Those executions are on Timeouts and Failed Executions. Totals are weighted by executions: every duration is the average multiplied by the executions.
Messages you may see
Query Store has not collected enough intervals to compare them. Choose a longer window, or shorten
INTERVAL_LENGTH_MINUTESso future windows hold more intervals.
N intervals were at or above the threshold; only the worst are called slow. More than a quarter of the window cleared the threshold. Raise the threshold to see the worst periods alone.
N intervals had no query with 5 intervals of its own history to compare. Those intervals were not judged either way.
Capture mode is AUTO, so cheap or infrequent queries are being discarded. Queries that are not captured cannot be measured against the slow periods.
Related reports
| Report | Why you would go there |
|---|---|
| Performance Baselines | Unusual intervals one query at a time, against its own history. |
| Load Sensitivity | Whether queries slow down when the server is busy, rather than at particular times. |
| Hourly Drift | Whether a slow hour is simply a time of day. |
| Waits by Query | What the victims were waiting on. |
| Timeouts and Failed Executions | Executions that were abandoned during the slow periods. |
| Active Queries | Whether sessions are blocking each other now. |
Frequently asked questions
Why does the lens cover the whole plot? There is only one slow period. A rate counted over one period is either 0% or 100%, which is not evidence of anything. Lower the threshold or lengthen the window to get more periods.
The query that caused the incident is not on the chart. A session that holds locks while doing nothing records no work in Query Store; only the queries it blocked do, and they show as slowed down. When every coincidence is a victim, look at what they waited on.
Why is my biggest query not named? If it is heavy just as often outside the slow periods, it sits on the diagonal, however large it is.
Why did the slow periods change when I changed the window? Each query’s usual cost is its median over the window, so a different window is a different baseline.