Load Sensitivity
Overview
Every ranked page measures a query against the other queries. This one measures a query against itself, in two different states of the database, which is the only way to separate the two kinds of slow query every tuning session runs into.
- A query that is slow at three in the morning on an idle database is slow because of what it does: the plan, the indexes, the data it touches. The work is tuning it.
- A query that takes four milliseconds at three in the morning and two seconds at nine is slow because of what else is running. Nothing about it changed. The work is finding what it queues behind, and tuning its plan is close to wasted effort.
A ranked list of slow queries cannot tell these apart. This page can.
Quiet intervals against busy intervals
The page takes the completed Query Store intervals in the window and sorts them by how hard the database was working, measured as CPU seconds per second across every execution. It cuts them into thirds, sets the middle third aside, and measures each query twice: over the quietest third and over the busiest third.
The middle third is left out on purpose. Intervals either side of a median are as alike as any two intervals in the window, so comparing halves would draw half the evidence from pairs that barely differ.
Each query becomes an arrow from its quiet reading to its busy reading, and the direction is the finding:
| Reading | What it looks like | What it means |
|---|---|---|
| Held up when busy | Slower by the chosen multiple, at much the same rate of executions | Something else is holding it up: locks, memory, the log, CPU contention. |
| Busier and slower | Slower, and running at least twice as often | Part of the load it is slowed by. Cut the calls or make each one cheaper. |
| Steady under load | Neither | It does not care what else is running. |
| Faster when busy | Faster by the chosen multiple | Usually a cache that is warm during the day and cold at night. |
A change of less than a millisecond per execution is never a finding, whatever the multiple says.
Where to find it
In the tree, under a database, Real Time → Query Store → Load Sensitivity.
The page needs SQL Server 2017 or newer, because it reads per query wait statistics. On SQL Server 2016 it shows a message instead.
Requirements
| Requirement | Why |
|---|---|
| SQL Server 2017 or newer | sys.query_store_wait_stats arrived in SQL Server 2017. |
| Query Store on for the database | The history lives inside Query Store. |
WAIT_STATS_CAPTURE_MODE = ON |
For the wait columns. The page still classifies queries without it, using CPU. |
VIEW DATABASE STATE |
To read the Query Store catalog views. |
VIEW SERVER STATE (optional) |
To read the instance’s logical processor count, which decides whether the busy intervals were busy at all. |
The toolbar
| Control | What it does |
|---|---|
| Against CPU per run / Against runs per hour | What the arrows move across. Both views plot duration per execution up the side. |
| 24 h / 3 d / 7 d | The window whose intervals are cut into thirds. |
| Slower 1.5 x / 2 x / 3 x / 5 x | How much slower counts as slower. Changing it reclassifies without reading Query Store again. |
| 2 runs / 10 runs / 50 runs | How many regular executions a query needs on each side to be compared. |
| Turn wait capture on | Only when wait statistics capture is off. |
| Refresh | Reads Query Store again. |
Reading the chart
A verdict says which kind of slow this database has. It leads with the reading the window cannot support: when the busy third was not meaningfully busier than the quiet one (less than 1.25 times the CPU), or used less than a twentieth of the instance’s processors, it says the database was never really busy, because a query that looks insensitive to load has not been put under any.
| Tile | What it is |
|---|---|
| Busy intervals | How many times the quiet third’s CPU the busy third used, and how many cores that was. |
| Held up when busy | Queries slower under load at the same rate, and the time they lost. Click to filter the grid. |
| Busier and slower | Queries slower under load that also ran at least twice as often. Click to filter. |
| Steady under load | Queries that did not move. Click to filter. |
| Faster when busy | Queries that got faster. Click to filter. |
| Time lost when busy | The extra time the flagged queries took in the busy intervals, and its share of the extra time across every compared query. |
Click a filtering tile again to clear the filter.
The arrows
Each query is an arrow: a hollow circle at its quiet reading and a head at its busy reading.
- Against CPU per run (the default): straight up means the query took longer while doing the same work, which is waiting. Up and to the right means it did more work per call, which points at a plan or parameter values rather than contention.
- Against runs per hour: up and to the right means the query ran more often and got slower.
Both axes are logarithmic, so the length of an arrow is the multiple it moved by: a query that doubled is drawn the same length whether it went from 1 ms to 2 ms or from a minute to two.
A movement too short to have a direction is drawn as a hollow dot. The thickness of an arrow is the time the busy intervals spent on that query, so the queries carrying the load stand out.
Color is the reading: red held up when busy, amber busier and slower, blue steady, green faster.
Hover over an arrow for both readings, how many intervals each was seen in, and the wait that grew. Click it to select its row.
Reading the grid
Up to 100 queries, ranked by the time the busy intervals lost to them.
| Column | What it is |
|---|---|
| # | Rank by added time. |
| Object | The procedure, function or trigger, when the statement belongs to one. |
| Query | The statement, with the parameter list Query Store prefixes removed. |
| Reading | Held up when busy, Busier and slower, Steady under load, or Faster when busy. |
| Quiet avg / Busy avg | Duration per regular execution in each third. |
| Change | The busy reading as a multiple of the quiet one; negative is faster. |
| Quiet/hr / Busy/hr | Executions per hour of each third. |
| Added | The extra time over the quiet speed, across the busy executions. |
| Wait grew | The wait category whose time per execution grew most from quiet to busy. |
| Plans | Plans used across the two thirds. More than one is a reason to check Plan Regressions first. |
| Last run (UTC) | The last execution in the window. |
CPU per execution and recorded wait per execution in each third are not grid columns, so the grid fits a 1280×900 window; both are in the double-click text and in Explain this reading.
Double-click a row for the statement with both readings beside it. Right-click for:
| Action | What it does |
|---|---|
| Explain this reading | Both readings side by side, where the extra time went, and the plans. |
| Show the statement | The statement in the query window. |
| Go to Waits by Query | To see what the query waits on across the whole window. |
| Go to Plan Regressions | When the query used more than one plan, or a different plan in each third. |
| 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_interval |
The intervals in the window, ending at the last completed interval. |
sys.query_store_runtime_stats |
CPU per interval for the ranking, and each query’s executions, duration and CPU in each third. |
sys.query_store_wait_stats |
Each query’s waits in each third, by category. Idle, Tracing and User Wait (WAITFOR) are left out. |
sys.query_store_plan, ..._query, ..._query_text |
Plans and statements. |
sys.dm_os_sys_info |
The logical processor count. |
What the query gets right
The window ends at the last completed interval. The interval still being filled has partial counts and would be filed as a quiet one.
Busy is CPU per second, not CPU. Interval length can change on a live database, and a raw sum would file every short interval as quiet.
Readings use regular executions only. Aborted and failed executions still count towards how busy an interval was, and are counted in the footer.
Totals are weighted by executions. A query’s reading is its total time divided by its total executions in that third, never an average of averages.
Messages you may see
This database was never really busy in the last N days. The busy third was not meaningfully busier than the quiet one. Choose a window that contains a working day. Queries that slow down anyway are still marked.
Not enough collection intervals to compare yet. The window needs at least 6 completed intervals with work in them.
Nothing ran often enough on both sides of the comparison. Choose fewer runs on the toolbar, or a longer window.
WAIT_STATS_CAPTURE_MODE is OFF on this database. The wait columns are empty. The Turn wait capture on button starts it.
Related reports
| Report | Why you would go there |
|---|---|
| Throughput and Latency Headroom | How the whole database’s cost per call changes with load, and where it starts to rise. |
| Waits by Query | What a held up query waits on. |
| Active Queries | What is holding requests up right now. |
| Plan Regressions | When a query used different plans in the quiet and busy intervals. |
| Parameter Sensitive Plans | When CPU per call grew with duration. |
| CPU by Query | To rank queries that are busier and slower by what they cost. |
Frequently asked questions
A query is marked Held up when busy, but its CPU per run also grew. The reading is decided by duration and execution rate. The verdict and the explanation say where the extra time went: when CPU per execution grew as much as duration, the query is doing more work per call, and a plan or parameter change is the better lead than contention.
Why are some queries missing? A query must run at least the chosen number of times in both the quiet and the busy third. The footer says how many could not be compared.
The busy intervals are at night. Busy is measured by CPU, not by the clock. On a database whose heaviest work is an overnight batch, the busy third is overnight.
Why is the middle third left out? So the two readings come from intervals that are genuinely unalike.