Latency Service Levels
Overview
Every ranked page reduces a query to one number, a total or an average, and sorts on it. Neither can be tested against a promise. Nobody was told the order screen would average two hundred milliseconds. They were told it would feel quick, and what breaks that is the one call in fifty that takes nine seconds. An average hides exactly that.
This report answers the question a service level is written in: is this database meeting its response time target, and which queries are missing it? You choose the target on the toolbar, for example 95% of executions within 500 ms, and the page draws the whole population of executions against it.
A curve, not a ranking
The chart is a cumulative curve. Along the bottom is a duration, on a logarithmic scale. Up the side is the share of all executions in the window that finished at or under that duration.
- Read across from a share to find the duration that covers it: the 95% line meets the curve at the duration 95% of executions finished within.
- Read up from a duration to find the share that finished within it: the target line meets the curve at the share that met the target.
The target and the goal make a corner on the chart. A curve that passes above and to the left of the corner kept the promise; one that passes below it did not.
Why there is a band around the curve
Query Store does not keep individual executions. For every plan in every interval it keeps the number of executions and their fastest, slowest and average duration. That is not the distribution, but it brackets it exactly:
| Curve | Built from | What it means |
|---|---|---|
| Best estimate (solid line) | Each interval’s average, weighted by its executions | The single best reading, and the one the tiles quote. |
| Band, left edge | Each interval’s fastest execution | The most the database could possibly have met the target. |
| Band, right edge | Each interval’s slowest execution | The least it could possibly have met the target. |
The true curve lies somewhere inside the band. How wide the band is says how much the Query Store collection interval costs this reading, not anything about the workload: a database collecting in 60 minute intervals gets a wide band, and a shorter INTERVAL_LENGTH_MINUTES narrows it.
The page says so on the chart: the curve is built from interval averages weighted by executions.
Where to find it
In the tree, under a database, Real Time → Query Store → Latency Service Levels.
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. |
VIEW DATABASE STATE |
To read the Query Store catalog views. |
The toolbar
| Control | What it does |
|---|---|
| All of it / Slowest 10% / Slowest 1% | Where the share axis starts. The tail views spend the chart’s height on the part of the curve a service level is about. This redraws without querying again. |
| 4 h / 24 h / 3 d / 7 d | The window. The same length of time immediately before it is drawn as a dashed comparison curve. |
| 10 ms / 50 ms / 100 ms / 250 ms / 500 ms / 1 s / 5 s | The target duration. Changing it counts the misses again. |
| 90% / 95% / 99% / 99.9% | The goal: the share of executions that should finish within the target. |
| Turn Query Store on | Only when Query Store is off and a setting would fix it. |
| Refresh | Re-reads Query Store. |
The target and the goal are remembered for this page between sessions, as the window and the view are.
Reading the chart
At the top, a verdict says whether the goal was met and how sure that is:
| Verdict | When |
|---|---|
| Every execution met the target | Nothing missed, even at each interval’s slowest execution. |
| The goal is met | Even counting every execution at its interval’s slowest, the goal holds. |
| Probably met, but the interval size cannot confirm it | The best estimate meets the goal, but the pessimistic edge of the band does not. |
| Probably short of the goal | The best estimate misses the goal, but the optimistic edge of the band would meet it. |
| Only N% met the target | Short of the goal however the intervals are read. The verdict then says whether a few queries own half the misses (fix them) or the misses are spread across the workload (look at the server). |
Six tiles carry the numbers:
| Tile | What it is |
|---|---|
| Met (target) | The best estimate of the share that met the target, with the two bounds under it. Colored against the goal. |
| Executions that missed | How many executions missed, and how many queries own half of them. |
| Typical execution | The median: half of everything finished faster. |
| (Goal) finished within | The duration the goal share finished within, and how many times the typical execution that is. |
| The window before | The share that met the target in the same length of time before the window. |
| Longest single run | The slowest regular execution Query Store recorded in the window. |
Under the tiles, the curve:
- The solid line is the best estimate for the window, drawn as a staircase: each step is the top of a duration bucket, because nothing more precise is known about the executions inside it.
- The shaded band is the space between the two bounds.
- The dashed gray line is the best estimate for the window before.
- The dashed orange line is the target, with everything slower tinted.
- The dashed line across the chart is the goal share, labeled with it.
- The dot on the target line is the share that met the target, counted exactly, green when it meets the goal and red when it does not.
- The small dots are the twelve queries at the top of the grid, each placed on the curve at its own average duration. Red dots average slower than the target.
Hover anywhere over the curve to read every curve at that duration. Hover over a dot for the query; click it to select the row in the grid.
Reading the grid
The grid ranks queries by executions that missed the target, not by total time. The query that owns the most of a total and the query that owns the most broken promises are usually not the same query, and the second is the one somebody is complaining about.
| Column | What it is |
|---|---|
| # | Rank by executions that missed the target. |
| Object | The procedure, function or trigger, when the statement belongs to one. |
| Query | The statement text, with the parameter list Query Store prefixes removed. |
| Runs | Regular executions in the window. |
| Missed | Executions in intervals whose average exceeded the target. |
| At least / At most | The two bounds on that count. Where they agree, the count is exact. |
| Share | This query’s share of every miss in the window. |
| Cumulative | The running total: the share of every miss owned by this query and every query above it. |
| Average | Duration per execution, weighted by executions. |
| Worst | The slowest single execution. |
| Plans | How many plans the query ran on in the window. |
| Before | Its misses in the window before, so a query new to the tail stands out. |
| Last run (UTC) | When it last ran. |
Queries with no misses stay in the list, ranked by executions, so a database that meets its target does not show an empty grid.
Double-click a row to see the statement with its misses explained beside it. Right-click for:
| Action | What it does |
|---|---|
| Explain this query’s misses | The counts, the bounds and what they mean for this query. |
| Show the statement | The statement in the query window. |
| Go to Parameter Sensitive Plans | Only when the query ran on more than one plan. |
| Go to Duration Spread | Whether the query’s slow runs are a spread that is widening. |
| Go to Timeouts and Failed Executions | Only when the window had aborted or failed executions. |
| 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, minimum and maximum duration per plan per interval. |
sys.query_store_runtime_stats_interval |
The window boundaries. |
sys.query_store_plan, ..._query, ..._query_text |
The query behind each plan. |
sys.database_query_store_options |
The readiness banner and the interval length. |
Durations are bucketed by the logarithm of the duration, ten buckets to each power of ten, which is a step of about 26%. That turns a week of rows into a few dozen without dropping a single execution.
Three things the query gets right
The window ends at the last completed interval, not at the clock, and is bounded at both ends. The interval still being filled has partial counts in it.
Only regular executions are on the curve. A query canceled by a client timeout records the timeout as its duration. Aborted and failed executions are counted in the footer and listed on Timeouts and Failed Executions.
Executions are weighted, not intervals. An interval that ran a query once weighs a thousandth of one that ran it a thousand times.
Messages you may see
Query Store recorded no completed executions in the window. Nothing ran, or Query Store was cleared or turned on since. If it recorded aborted or failed executions, the message says how many.
Probably … but the interval size cannot confirm it. The band straddles the goal. A shorter
INTERVAL_LENGTH_MINUTESmakes future windows decidable.
N aborted and N failed executions in the window are not on the curve. They are counted apart because their durations are not the query’s.
Query Store’s history starts after the window begins. The window is only partly covered.
Capture mode is AUTO, so cheap or infrequent queries are being discarded. The curve is missing whatever AUTO dropped, which is mostly fast executions, so the share meeting the target may be understated.
Related reports
| Report | Why you would go there |
|---|---|
| Timeouts and Failed Executions | The executions this curve leaves out: timeouts and errors. |
| Duration Spread | Whether a query’s slow runs are a spread that is widening. |
| Parameter Sensitive Plans | When one query is fast for some parameters and slow for others. |
| Slow Periods | When the misses cluster in particular hours. |
| Throughput and Latency Headroom | Whether the server is the ceiling when misses are spread widely. |
| Waits by Query | What slow queries are waiting on. |
Frequently asked questions
Why is the band so wide? Query Store keeps only the fastest, slowest and average execution of each interval. With 60 minute intervals one slow execution makes the whole interval’s slowest reading slow. The band is honest about that. Set INTERVAL_LENGTH_MINUTES lower to narrow it for future windows.
Why does a percentile land exactly on a round step? Durations are counted in buckets, and a percentile is reported at the top of the bucket it falls in. Nothing more precise is known.
The average for a query is under the target, but it still has misses. Some of its intervals averaged over the target and others did not. The average column is across the whole window.
Why are there executions on the curve faster than a microsecond? Query Store rounds very fast work down. Everything at or under a microsecond is counted in the first bucket rather than dropped.