Waits by Query

Overview

The Waits report says the instance spends its life on LCK_M_U. The CPU by Query report says which queries burn processor. Nothing joined the two, which meant nobody could answer the only question that matters: which query is doing the waiting.

This report answers it. One row per query, with its elapsed time broken into the CPU it burned and the categories it waited in.

“This procedure took forty seconds of wall clock. Four of those were CPU, thirty were lock waiting, six were buffer IO.”

That sentence is the entire discipline of wait based tuning, and it changes what somebody does next – completely.

• A query that is 90 percent CPU needs a better plan.
• The same query at 90 percent Lock needs a look at what else is touching the table, and rewriting it will achieve nothing.

Without this page a reader has to guess which of those two worlds they are in, and both guesses cost a day.

The last column is the point

Every dominant wait routes to the report that owns that problem:

That is what turns eighty reports from a catalogue into a diagnostic path. The wait category is a diagnosis, every diagnosis in this application already had a report, and what was missing was the line between them.

Where to find it

In the tree, under a database, Real Time → Waits by Query.

The node is hidden below SQL Server 2017, one version later than its Query Store sibling and for a specific reason: sys.query_store_wait_stats arrived in 2017, not with Query Store itself.

Requirements

The second off switch

WAIT_STATS_CAPTURE_MODE can be OFF while Query Store itself is READ_WRITE and looks perfectly healthy. The result is a page with no waits on it, which reads as a database with no waits.

That is its own banner state on this page, and it has its own toolbar button, Turn wait capture on, which shows this statement, asks, and runs it. The same action is on the grid’s right-click menu.

ALTER DATABASE [YourDatabase] SET QUERY_STORE (WAIT_STATS_CAPTURE_MODE = ON);

It costs very little: the waits are aggregated into the same intervals Query Store already writes. Nothing is collected retrospectively, so the page stays empty until an interval has closed.

Nothing else on the instance raises this.

Reading the chart

One stacked bar per query. The segments, in order:

1. CPU, hatched, in a colour that is not in the wait palette.
2. Each wait category, largest first, in the colour the Waits report uses for it.
3. The unattributed remainder, drawn as an outline rather than a fill.

Three things about how it is drawn

CPU is not a wait. It is the leading segment, hatched, and labelled apart in the legend. Colouring it like a category would have the chart claim that running is a kind of waiting, and a stacked bar is exactly the shape that makes that claim without saying it.

The wait colours come from the same place the Waits report gets them. A category is the same colour and the same word here as it is there, so somebody arriving from the instance wait totals is looking for a colour they have already seen rather than reading a second legend.

The segments do not sum to elapsed time, and the difference is drawn. Query Store records waits and elapsed time separately and they disagree: some waiting is not categorised, and parallel workers wait at the same time as each other. The remainder is the outlined segment at the end of each bar. Scaling the coloured segments to fill the bar instead would be inventing an attribution.

The bar is not normalised either. Every bar is against the longest on the page, so the ranking survives the split into segments.

The mapping is lossy, in one direction

Query Store has roughly two dozen wait categories and each collapses many raw wait types into one bucket. They are mapped onto the taxonomy the Waits report already uses.

The mapping is lossy in one direction only: several Query Store categories land on the same Database Health Monitor category and none of them splits. That is safe. A reader who follows a colour from this page to the Waits report finds more detail there, never less, and never a different answer.

Two of the mappings are judgement calls and are worth naming:

• Compilation is put with CPU, because compiling is processor work rather than waiting on a resource, even though it can queue behind a semaphore.
• Transaction and Log Rate Governor are put with Transaction Log, because both are waiting on the log to move.

The three views

By category is second on purpose. Starting from the category is backwards for tuning: somebody arrives at this page knowing a query is slow, not knowing a category is large. It earns its place as a check on the other view – if one category dominates every bar then the problem is the instance rather than the query.

The toolbar

Reading the grid

Double-click a row to see the full split beside the statement, with the per category guidance the Waits report uses. Right-click for Go to, which opens that report.

When either switch this page depends on is off, the toolbar carries the way out of it, and so does the right-click menu – whatever is selected, because the grid has no rows to select in that state:

Both can be on the menu at once. Where no ALTER DATABASE would fix the state – SQL Server 2016 or older, a read only database, the ERROR state – the menu carries the reason as a greyed line rather than an action that could not work.

Chart and grid interactions

Where the data comes from

The unit trap

The two sources are in different units: runtime statistics in microseconds, wait statistics in milliseconds. They are joined on the same interval here, and a version of this query that divides both by the same number draws the waits a thousand times too small next to the CPU they are being compared with. Both are converted to seconds inside the query rather than in the report, so the two halves cannot be converted differently by two pieces of code written a week apart.

execution_type is filtered on both sides. It exists on the wait statistics as well as on the runtime statistics, and filtering one without the other puts the waits of a cancelled query against the duration of a completed one.

Messages you may see

The Waits by Query report requires SQL Server 2017 or newer. sys.query_store_wait_stats does not exist on this instance.

WAIT_STATS_CAPTURE_MODE is OFF on this database. Query Store is on and healthy, and it is recording no per query waits. The banner carries the statement that starts it.

Query Store on this database is READ_ONLY and is collecting nothing. Everything below the banner is history from before it stopped.

No query ran in the window. A quiet database, or a window shorter than the gap since anything last ran.

Related reports

Frequently asked questions

Why do the segments not fill the bar? Because waits and elapsed time are measured separately and do not sum. The outlined segment at the end is the difference, drawn rather than hidden.

Why is CPU drawn differently from the waits? Because it is not a wait. A stacked bar that coloured it like a category would quietly claim that running is a kind of waiting.

A category here has a different name from the Waits report. Query Store’s categories are coarser and several of them map onto one of ours. Following the colour to the Waits report always gives more detail, never a different answer.

Why is the sum of the waits larger than the elapsed time on a parallel query? Because eight workers waiting for one second each is eight seconds of waiting inside one second of elapsed time. The remainder is floored at zero when that happens.

Can I see which wait type inside a category, rather than the category? Not from Query Store: it records only the category. The Waits report has the wait types, for the instance rather than for the query.

I turned wait capture on and the page is still empty. Nothing is collected retrospectively, and the waits are aggregated into Query Store’s own intervals. There is nothing to draw until an interval has closed.