Performance Baselines
Overview
The question this page answers is is this query outside its own normal range right now?
Other pages can say what is expensive and what changed. None of them can say whether a change is larger than the query’s own ordinary wobble, and without that a change is hard to act on. A query that went from 300 ms to 500 ms is a shrug if it has spent all week wandering between 200 and 600 ms, and an incident if it has never once left 295 to 305.
So this page keeps each query’s history and draws the judgment. For each of the busiest queries it takes one reading per period (normally one per Query Store interval), works out from the earlier part of the window where the query normally sits and how far it normally strays, and tests the recent part against that. The distance is measured in standard deviations, which compares across queries: four sigma on a lookup and four sigma on a report are the same strength of evidence, even though one moved a millisecond and the other a minute.
Being outside the limits is evidence, not a diagnosis. The reason is usually on Plan Regressions (the plan changed) or Waits by Query (the time went on queueing).
How the limits are drawn
- The center is the mean of the query’s baseline readings, one reading per period, each the average cost of one execution in that period. Periods are not weighted against each other: the question is whether the database behaved differently at four in the morning, and an hour with nine executions is as much evidence of that as an hour with nine thousand.
- One sigma is the average difference between consecutive baseline readings divided by 1.128. A plain standard deviation would count any shift inside the baseline as noise, and a query that got slower halfway through the week would come out with limits wide enough to swallow the shift that made them wide.
- The limits are three sigma either side of the center. A lower limit below zero is not drawn.
The limits come from the baseline only, so the period under test cannot move the limits it is tested against.
What counts as a signal
| Rule | What it means |
|---|---|
| Past three sigma | One reading that does not belong. The strongest evidence. |
| Two of three past two sigma on the same side | A spike with a shoulder rather than one odd reading. |
| Eight in a row on one side of the center | Not a spike at all: the query has settled somewhere new. Invisible to every threshold alert. |
Each query is then graded:
| Grade | When |
|---|---|
| Slower than its baseline | A reading under test broke a rule, and most of the signals are above the center. |
| Faster than its baseline | The signals are mostly below the center. |
| Within its limits | No reading under test broke a rule. |
| No usable baseline | Fewer than eight baseline readings, every baseline reading identical, or more than a fifth of the baseline broke its own limits. |
Two refinements matter on real hourly Query Store data:
- The direction comes from the signals, not the average. A query that spiked past its upper limit once on an otherwise quiet day is graded slower, even though its average for the day was lower.
- Runs that are routine for a query do not count against it. Most queries follow the working day, so eight readings in a row on one side of the center happen in their own baseline every day. When runs fill more than a fifth of a query’s baseline, only the two limit rules grade its period under test, rather than the whole query being set aside.
Where to find it
In the tree, under a database, Real Time → Query Store → Performance Baselines.
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 runs the same checks and produces a message instead.
Requirements
| Requirement | Why |
|---|---|
| SQL Server 2016 or newer | Query Store arrived in SQL Server 2016. Nothing newer is used. |
| Query Store on for the database | The history lives inside Query Store. |
| At least eight readings of history for a query | Fewer and the limits are an accident of which readings happened to be neighbors. |
VIEW DATABASE STATE |
To read the Query Store catalog views. |
The toolbar
| Control | What it does |
|---|---|
| Duration / CPU / Reads | What a reading measures, per execution. Execution counts are not offered: they describe the callers, and a baseline of them would grade the application’s traffic as a fault. |
| 24 h / 3 d / 7 d / 14 d / 28 d | The whole window: baseline plus the period under test. |
| Test 1 h / 4 h / 12 h / 24 h | The period under test, at the end of the window. |
| Turn Query Store on | Only when Query Store is off and a setting would fix it. |
| Refresh | Re-reads Query Store. |
The period under test is cut to half the window when it would leave too little baseline (a day of testing on a one day window), and rounded to whole readings. The footer says when that happened and gives the baseline and test periods on this computer’s clock.
A reading is one Query Store interval, unless the interval is short against the window: a query gets at most 400 readings, so a one minute interval over a week becomes 30 minute readings, and the default 60 minute interval over four weeks becomes two hour readings.
Reading the chart
At the top, a verdict names the clearest query outside its range, or says that nothing is. Five tiles count the grades:
| Tile | What it is |
|---|---|
| Slower than baseline | Queries graded slower, and the time they cost above what their baselines predicted. Click to filter. |
| Faster than baseline | Queries graded faster, and the strongest move. Never green: a query that got faster is usually tuning that worked and occasionally a query that stopped doing part of its work. Click to filter. |
| Within their limits | Queries that broke no rule under test. Click to filter. |
| No usable baseline | Queries the page would not grade. Click to filter. |
| Shown here | How many queries are listed out of all that ran in the window, and why the rest are not: they did not run in the period under test, or have too little history. |
Under the tiles, the control chart for one query at a time: the first row of the grid, or whichever row you select.
- One mark per reading, in order. The bottom axis is the sequence, not the clock, because the rules count consecutive readings; a handful of times along it anchor it to the clock.
- The blue line is the center. The dashed red lines are the control limits, labeled at the right.
- The shaded bands are one, two and three sigma. The region past the limits is tinted red.
- Red marks broke a limit. Amber marks are two of three past two sigma, or part of a run of eight.
- The dashed vertical rule divides the baseline from the period under test.
- A query with no usable limits shows its readings and center with the reason in place of the band.
Hover over the chart for the reading nearest the pointer: its time, value, distance from the center in sigma, executions, which side of the divider it is on, and the rule it broke.
Reading the grid
| Column | What it is |
|---|---|
| # | Rank: slower first, then faster, within limits and no usable baseline, strongest signal first inside each. |
| Object | The procedure, function or trigger, when the statement belongs to one. |
| Query | The statement text, with the parameter list Query Store prefixes removed. |
| Baseline | The center: the query’s normal cost per execution. |
| Recent | The mean of its readings under test. |
| Change | Recent against baseline, as a percentage. |
| Sigmas | How far the recent mean sits from the center, in standard deviations. |
| Cost of it | What the period under test cost above or below what the baseline predicted. |
| Signals | Readings under test that broke a rule. |
| Verdict | The grade, with the reason when there is no usable baseline: too few readings, never settled, or no spread. |
| Readings | Baseline readings plus readings under test. |
The grid sorts by any column. Double-click a row to see the statement with its limits and every reading that broke a rule. Right-click for:
| Action | What it does |
|---|---|
| Explain this baseline | The center, sigma, limits, recent mean, and each reading that broke a rule with its time. |
| Show the statement | The statement in the query window. |
| Go to Plan Regressions | For a slower or faster query: whether its plan changed. |
| Go to Waits by Query | For a slower or faster query: what it waited on. |
| 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 and the average duration, CPU or reads per plan per interval. |
sys.query_store_runtime_stats_interval |
The interval each row belongs to. |
sys.query_store_plan, ..._query, ..._query_text |
The query behind each plan. |
sys.database_query_store_options |
The readiness banner and the interval length. |
The 40 queries with the highest total of the measure that have at least eight baseline readings and at least one reading under test are graded.
Things the query gets right
The window ends at the last completed interval. The interval still being collected is left out, and the footer says where the window ends.
Readings sit on a fixed grid. A reading’s start is counted from a fixed midnight, the window end is floored onto that grid, and the window start and the split between baseline and test are whole readings before it. Counting from the window start instead puts intervals one reading late, and the interval straddling the split lands on the wrong side.
Only regular executions make a reading. A query canceled by a client timeout records the timeout as its duration, and a burst of them would be a signal that is really the application giving up. Aborted and failed executions are counted in the footer instead.
Inside a reading, executions are weighted properly across plans and intervals, so a reading is the honest mean of that period’s executions.
Messages you may see
No query has enough history over the last 7 days to have a baseline. No query ran in at least eight readings before the period under test and again inside it. Widen the window, or shorten the period under test.
There is not enough history here to say what normal looks like. Every listed query has too few baseline readings or a baseline that never settled.
The period under test was fitted to the last 12 hours. The chosen period would have left too little baseline, or did not start on a whole reading.
Query Store’s history starts inside the window, so the baseline is shorter than the window.
Related reports
| Report | Why you would go there |
|---|---|
| Plan Regressions | Whether a query outside its range changed plan, and the script to force the old one. |
| Waits by Query | What a query outside its range waited on when its plan did not change. |
| Change Significance | Whether a change between two windows is larger than the query’s variation. |
| Workload Change | Which queries moved the workload’s total, and whether they ran more or cost more. |
| Hourly Drift | Whether an hour of the day, rather than a query, is getting worse. |
| CPU by Query | The expensive queries right now, when nothing is outside its range but the workload feels slow. |
Frequently asked questions
Why is a query graded slower when its recent average went down? One of its readings under test broke a limit on the high side. The Change column shows the average and the chart shows the spike, so both readings are on screen.
Why do so many queries have no usable baseline? On a busy database whose load changes sharply through the day, a query’s own readings can break its limits more than a fifth of the time. Its limits would then describe a process that was never steady, so the page does not grade it. A longer interval between readings, or the Hourly Drift page, is a better view of such a query.
Is three sigma a threshold somebody chose? No threshold on this page is about the query’s cost. The limits describe what the query has been doing, which is why the same step is a signal on one query and inside the band on another.
Why does the chart show only one query? Every query has its own limits, and two sets of limits on one chart cannot both be read. Select a row to chart it.