Throughput and Latency Headroom
Overview
Every performance page measures the database at a moment: what was slow, what waited, what cost the most. None of them answers the question a capacity decision turns on, which is what happens next. A database that runs at 40 ms a call today either has room for twice the traffic or is a fortnight of growth away from a queue, and nothing on a ranked page tells the two apart.
This page takes the clock off the bottom of the chart and puts load there instead. Each completed Query Store interval is one reading of the database under a known demand. The intervals are sorted by that demand, grouped into levels holding equal numbers of them, and each level is drawn at what an execution cost under it.
- A flat curve is a database that is not load bound at any level it has been shown.
- A curve that turns upwards is one that is, and the load where it passes twice the quiet baseline, the knee, is the number to plan growth against.
Holding the workload still
Average duration across a whole database measures what was asked as much as how fast it was answered. On most databases the mix at 3am and at 3pm are different workloads, and a database whose nightly reports take four minutes each looks catastrophically slow at its quietest hour.
So by default the latency is measured over the recurring queries: up to 25 queries, by execution count, that ran in at least half of the intervals. Holding the set of queries still, the way a price index holds its basket still, leaves a reading much closer to a queue. Everything on the toolbar measures every query instead. When no query ran in half the intervals, the page falls back to everything and says so.
Where to find it
In the tree, under a database, Real Time → Query Store → Throughput and Latency Headroom.
The page needs SQL Server 2016 or newer. The queueing figures need SQL Server 2017 or newer, where Query Store records waits; on SQL Server 2016 they show as unavailable and the rest of the page works.
Requirements
| Requirement | Why |
|---|---|
| SQL Server 2016 or newer | Query Store arrived in SQL Server 2016. |
| SQL Server 2017 or newer for queueing | sys.query_store_wait_stats arrived in SQL Server 2017. |
| Query Store on for the database | The history lives inside Query Store. |
VIEW DATABASE STATE |
To read the Query Store catalog views. |
VIEW SERVER STATE (optional) |
For the instance’s worker thread count on the Requests in flight tile. |
The toolbar
| Control | What it does |
|---|---|
| Executions per minute / CPU cores busy | How load is measured along the bottom: every execution per minute, or CPU seconds per second across the database. |
| 24 h / 3 d / 7 d | The window. Three days by default: on hourly intervals a single day is one working day and one night. |
| Recurring queries / Everything | What the cost per execution is measured over. |
| Load levels / Queries | What the grid lists: the levels on the chart, or the queries whose cost rose most past the knee. |
| Refresh | Reads Query Store again. |
Reading the chart
The verdict says what the shape of the curve means, in the order the answers change what somebody does:
- Not enough history, or no range of load. When the busiest level ran at less than 1.5 times the load of the quietest, everything on the chart is one operating point, and the page says so rather than drawing a confident flat line.
- Spends much of its time past the knee. A quarter or more of the intervals ran at or above the load where cost per execution doubled.
- Room for about N x its typical load. There is a knee, and only the busiest intervals reach it. The multiple is the knee against the median interval’s load.
- Slowest when quietest. Cost falls as load rises, which is a different workload at quiet times (batches, maintenance, reports), not something load does.
- Latency holds steady across every level of load the window saw.
| Tile | What it is |
|---|---|
| Busiest interval | The highest load in the window, and when it was. |
| At the busiest level | Cost per execution at the busiest level, as a multiple of the quiet baseline. |
| Queueing at that level | The share of elapsed time spent waiting at the busiest level, against the quietest. |
| Requests in flight | Requests running at once on average at the busiest level, against the instance’s worker threads. |
| Curve leaves the baseline | The knee: the load where cost per execution passes twice the baseline. |
| Headroom | The knee as a multiple of the typical interval’s load, and the share of intervals already past it. |
The response curve
- Each mark is a level of load. Every level holds the same number of intervals, so every mark carries the same weight of evidence. A mark’s size is the executions behind it.
- The line joins the levels in load order, with straight segments: a smoothed curve would move the knee.
- The shaded band runs from the fastest to the slowest interval in each level, so a curve through noise cannot be mistaken for a trend.
- The green dashed line is the quiet baseline: what the lowest level of load cost per execution.
- The amber dashed line is the knee, and everything to the right of it is tinted. Marks past the knee are amber.
Both axes start at zero, so twice the baseline is twice the height.
Hover over a mark for its intervals, requests in flight, CPU busy, cost, band and queueing. Click it to select its row.
Reading the grid
Load levels
| Column | What it is |
|---|---|
| # | The level, quietest first. |
| Load | The mean load of the intervals in the level. |
| Intervals | How many intervals the level holds. |
| Per execution | Total duration over total executions for the level, never a mean of means. |
| vs quiet | Cost per execution as a multiple of the quiet baseline. |
| What this level says | The level in words. It takes the width the other columns leave, at least about 250 px. |
| Fastest / Slowest | The cheapest and dearest interval in the level. |
| In flight | Requests running at once on average: total duration over wall clock. |
| CPU busy | CPU cores busy on average. |
| Queueing | Wait time against elapsed time (SQL Server 2017 and newer). Waits are per thread, so parallel work can pass 100%. |
| Executions | Every execution in the level. |
| Busiest interval (UTC) | When the level’s busiest interval started. |
The level in words comes right after the figures that decide it so it is on screen without scrolling. Text that is wider than its column is cut off, and hovering over a row shows every column in full.
Double-click a level, or right-click and choose List the intervals in this level, for every interval in it. Show the recurring queries lists the basket the latency is measured on.
Queries
The queries whose cost per execution rose most past the knee, splitting every interval at the knee’s load. When there is no knee, the split is at the lowest load in the busiest level. A query needs at least two regular executions on each side.
| Column | What it is |
|---|---|
| # | Rank by the time added past the split. |
| Object / Query | The procedure and the statement. |
| Below the knee / Past the knee | Cost per execution on each side of the split. |
| Change | Past as a multiple of below. |
| Runs below / Runs past | Regular executions on each side. |
| Added past | The extra time over the below speed, across the executions past the split. |
| Plans | Plans used in the window. |
| Recurring | Whether the query is one of the recurring queries the curve is measured on. |
Double-click a query for the statement. Right-click for Show the statement, Go to Load Sensitivity, Go to Plan Regressions (when it used more than one plan), and Copy query text.
Where the data comes from
| Source | What it gives |
|---|---|
sys.query_store_runtime_stats_interval |
The completed intervals in the window, with their measured length. |
sys.query_store_runtime_stats |
Executions, duration and CPU per query per interval. |
sys.query_store_wait_stats |
Waits per interval, without Idle, Tracing and User Wait (SQL Server 2017 and newer only). |
sys.query_store_plan, ..._query, ..._query_text |
Plans and statements for the basket and the queries past the knee. |
sys.dm_os_sys_info |
Worker thread and processor counts. |
What the page gets right
The interval being collected is left out. Its executions so far divided by its full length would report the busiest hour as the quietest.
Interval length is measured, not assumed, because it can change on a live database.
Load counts every execution; cost counts regular ones. An aborted call still arrived, but its duration is the client’s timeout. Aborted and failed executions are counted in the footer.
Limits
- An interval is an average. On hourly intervals, an hour that was idle for fifty minutes and on fire for ten is a quiet hour. This page finds sustained load and never a five minute spike. A shorter
INTERVAL_LENGTH_MINUTESsharpens it, at the cost of Query Store space. - The curve is a correlation. A rise is consistent with queueing and also with the busy hours running heavier statements. The recurring queries measure suppresses the second; it cannot prove the first.
- A knee is always inside the load the window saw. It is found between measured levels, so it never predicts a load the database has not reached.
Messages you may see
This window did not vary enough in load to say how the database scales. Choose a longer window, or one covering a working day and a night.
There is not enough history yet to say how this database scales. At least 4 completed intervals with executions are needed.
This database is at its slowest when it is at its quietest. A different workload runs at quiet times. Hourly Drift shows what runs when.
Related reports
| Report | Why you would go there |
|---|---|
| Load Sensitivity | Which individual queries are held up when the database is busy. |
| Waits by Query | What the queueing is made of. |
| Hourly Drift | What runs at which hour. |
| Plan Regressions | When a query past the knee changed plan. |
| Query Store Health | Whether the intervals this page reads are complete. |
Frequently asked questions
Why levels rather than a dot per interval? Load is skewed: a week of hourly intervals is mostly quiet nights and a few busy afternoons. Levels of equal size put the same evidence behind every mark.
Why is the knee at twice the baseline? A tenth is noise on a handful of hourly averages, ten times is a database that has already fallen over, and doubling is about where a user notices.
Why does the knee change when I switch to CPU cores busy? The intervals are sorted by a different measure of load, so the levels, and the load where cost doubles, are different. The queries past the knee are read again at the new split.
Why is the headroom against the typical interval and not the busiest? The knee is found between measured levels, so the busiest interval is always at or past it. The useful question is how far an ordinary interval’s load can grow before it reaches the knee.