Bottleneck Mix by Database

Overview

Every other query page ranks. Ranking answers “which query costs the most”, and it cannot answer the question that comes straight after it: what is that query doing with its time?

• A query burning processor
• A query queueing for pages off storage
• A query stuck behind somebody else’s lock

produce the same bar on every ranked list, and they lead to three different afternoons: rewrite the query, build an index, or go and find the session at the head of the chain. Picking the wrong one of the three is the most common way a tuning session produces nothing.

Query Store has held both halves of the answer since SQL Server 2017, in two views that nothing joins. Runtime statistics know what a query spent on a processor. Wait statistics know what it queued for. Added together they are what the query cost; divided three ways they are what kind of cost it was. This page is that division, for every database on the instance at once.

It opens with one mark per database, because somebody opening an instance report is asking which database has which kind of problem. Double-click a database and the page shows its queries.

Where to find it

In the tree, right-click an instance and choose Instance Level Reports → Bottleneck Mix by Database.

Requirements

The version is checked before anything else is sent. An older instance gets a message rather than a failed page:

Bottleneck Mix by Database needs SQL Server 2017 or newer.

The three corners

Two groups of waits are left out of the mix entirely:

“Contention” is a corner rather than a diagnosis. Locks, memory grants, compilation and the client failing to read its rows all land in it, which is why the biggest single wait is named on every row of the grid.

Why queueing for a scheduler counts as processor

The CPU wait category is SOS_SCHEDULER_YIELD: a query that was ready to run and waiting its turn on a processor. That is processor pressure, and it is fixed the same way the apex is fixed, by doing less work or adding processors. Counting it as contention would send you looking for a blocker while naming a CPU problem. Waits by Query files the same category under CPU / Scheduler, and the two pages say the same thing about it.

Why parallel waits are left out

An exchange wait (CXPACKET, CXCONSUMER) is mostly a parallel query waiting on its own threads, not on another session. Counted as contention, a busy parallel workload slides toward the contention corner and reads as a blocking problem, when the fix is plan shape, reads and parallelism settings.

It is not dropped. Every row carries a Parallel wait column, every mark’s tooltip gives the figure, the subtitle totals it, and the verdict says so whenever it is a quarter or more of all the waiting.

The two views

Switching views does not go back to the server. Both come from one read.

In the Queries view the top N is taken per database first and then ranked across the instance. Any query in the instance wide top N is necessarily in its own database’s top N, so nothing is lost, and narrowing to one database shows that database’s own top N rather than whatever of it happened to make the instance list.

The toolbar

The view, the window, the top N and the runs choice are remembered between sessions. Narrowing to a database is not: it is a step into the page, not a preference.

Runs defaults to every query. A single execution that spent four minutes behind a lock is exactly the mark somebody is looking for. The higher settings are for a workload buried under one-off ad hoc statements.

Reading the verdict

The band across the top says what the crowd on the chart means, in the order the answers change what you do next.

In the Queries view the wording says “these queries” instead.

Three sentences are added to whichever verdict is showing when they apply:

• Parallel waits are a quarter or more of all the waiting.
• Queueing for a scheduler is a quarter or more of the processor time, which means the instance is short of processor as well as busy.
• Aborted runs sit in the contention corner. A query blocked until its client gives up is recorded as aborted, so these are the contention corner’s own evidence.

When Query Store could not name the wait

When Unknown is the largest part of the contention corner, the verdict says so rather than calling it blocking:

Query Store could not categorize most of it: Unknown accounts for …

A page telling you to go and find the blocker on the strength of uncategorized time would be guessing. What is Active and Waits show the real wait types while the work is running.

Reading the tiles

The three corner tiles always add to 100 percent, so the one to read is whichever is larger than you expected.

Reading the chart

One mark per database or query, placed inside the triangle by how it divided its effort, and sized by how much effort there was.

• Processor is the apex, Storage the bottom left, Contention the bottom right.
• A mark near a corner spent nearly all of itself on that one thing.
• A mark in the middle is a mixture, which is usually the least useful mark on the page.
• Each mark is drawn in its corner’s shape and color: a circle for processor, a square for storage, a diamond for contention. The colors are the ones the Waits report uses for CPU, IO and Locking, and the shapes keep the chart readable in color blind mode and in print.
• Area is the amount. A mark that looks twice as big stands for twice the effort. A tiny mark hard against the contention corner cost almost nothing while it was blocked.
• The dashed lines from the middle mark where each corner stops being the largest of the three.
• The gridlines are every 25 percent, numbered on the edge each set ends on.

Nothing is moved apart to make room. Where a dozen marks share one mix, the ink piling up is what says how many are there.

The shape of the crowd is the finding as often as any single mark is:

A cluster at the top is work that needs doing less, or more processor to do it with. No amount of blocking analysis will help it.

A smear along the left edge is a workload trading between processor and storage, which is the ordinary signature of a missing index.

A crowd along the bottom is work waiting on other work. The fastest query in the list is not the point.

Hover a mark for its three shares with the time behind each, its cost and share of what is drawn, its runs, its biggest wait, how much of its processor time was queueing for a scheduler, and its parallel wait.

The notes under the triangle say which databases have Query Store in READ_ONLY, whose figures stop where it stopped collecting, and which databases were not read at all and why.

The bar under the chart can be dragged to give the triangle more of the page, and the height is remembered.

Reading the grid

Databases view

Queries view

The same columns, with # (rank by effort), Database, Object and Query at the front, and three more:

What the Where to look column says

The sentence is written from the corner, not from the wait, because the corner is what the mark shows.

Waited for a scheduler longer than it ran. The instance is short of processor, so look at what else is burning CPU at the same time and not only at this query.

Almost all processor. It is doing too much work rather than being held up: look at the plan shape and the row estimates behind it.

Mostly waiting for storage. An index that turns a scan into a seek removes the reads rather than making them faster. Where the biggest wait is Tran Log IO, it points at commit frequency and log write latency instead.

Mostly held up, on <wait>. Followed by that category’s guidance, the same text the Waits report uses. Unknown and Worker Thread get sentences of their own.

Chart and grid interactions

Double-clicking a query gives you the whole statement, not the 400 characters the page keeps. It is read from that database’s Query Store at the moment you ask, with the plan beside it, so the Plan Analysis button inside the Query Advisor is live. The row’s full split into CPU time, scheduler queueing, storage, contention and parallel wait is shown alongside.

Databases that were not read

Every user database is either on the page or named under the chart with the reason. The reasons are:

Copy Query Store scripts puts a script on the clipboard with one statement for each database that was skipped for one of the first two reasons:

-- [Sales]: Query Store is off
ALTER DATABASE [Sales] SET QUERY_STORE = ON (
        OPERATION_MODE = READ_WRITE,
        MAX_STORAGE_SIZE_MB = 2048,
        QUERY_CAPTURE_MODE = ALL,
        SIZE_BASED_CLEANUP_MODE = AUTO,
        CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30));

-- [CRM]: Wait statistics capture is off
ALTER DATABASE [CRM] SET QUERY_STORE (WAIT_STATS_CAPTURE_MODE = ON);

Nothing is collected retrospectively. A database turned on today appears on the page once an interval has closed.

Nothing here runs anything

Turning Query Store on changes what the engine records in every database it touches, and that belongs in somebody’s change window rather than behind a click on an instance report. The scripts are copied, never run.

Where the data comes from

Three things about the arithmetic

The parts are effort, not elapsed time. A parallel query spends processor on several threads at once and waits on several of them at once, so its parts can add up to more than the clock it occupied. A mark is its share of what it cost, not of how long somebody waited for it.

Both units are converted to milliseconds before anything is added. Runtime statistics are in microseconds and wait statistics in milliseconds, and a query that forgot the difference would draw every wait a thousand times too small next to the CPU it is added to.

Every execution type is counted. A query canceled by a client timeout while it waited on a lock is exactly what the contention corner is for. Aborted and exception runs are counted apart so the grid can show how many there were.

The window

Each database’s window ends at its own last completed Query Store interval, never at the clock, and counts back the length you chose. The interval being filled right now holds a partial count, and including it makes the freshest hour look like a collapse. With the default one hour interval, 1 h means the last complete hour.

The window is worked out per database because the interval length is a per database setting. The subtitle gives the latest window end on the page, and the Databases view gives each one.

Which databases are read

The four system databases are left out. The monitoring database is not: its collector is real work on the instance, and on a busy monitored server it can be one of the larger marks.

The read is one batch on one connection, a database at a time, each inside its own error handler, so one database that cannot be read does not take the others down with it. A database that fails part way has anything it had already contributed taken back out, so it is either on the page whole or named in the notes, never half of each.

Messages you may see

Bottleneck Mix by Database needs SQL Server 2017 or newer. Query Store’s per query wait statistics arrived in SQL Server 2017.

No database on this instance has Query Store collecting wait statistics. The detail counts the user databases by reason. Copy Query Store scripts on the toolbar copies the statements that would turn it on.

There are no user databases on this instance. System databases are not part of this page.

The Query Store read did not finish in 300 seconds. Every database with Query Store on is read in turn. A shorter window makes it lighter.

Nothing that was read cost anything measurable over the last 24 hours. The databases were read and nothing ran in the window.

Related reports

Frequently asked questions

Why does a query’s effort exceed its elapsed time? Because it went parallel. Effort is what the instance spent; elapsed is how long somebody waited. The mark is placed by what it cost.

Why is parallelism not counted as contention? Because an exchange wait is mostly a parallel query waiting on its own threads. Counted as contention, a parallel workload reads as a blocking problem. The Parallel wait column and the verdict still show how much of it there is.

Almost every mark is at the processor apex. Usually a genuinely processor bound workload. It can also be a database where wait capture was turned on recently: intervals from before that have processor time and no waits, which pulls the mark toward the apex until the window only covers intervals collected since.

A database I expected is missing. Look at the notes under the triangle. Every user database that was not read is named there with the reason, and a database that was read but did no work in the window is simply not drawn.

The shares do not match Waits by Query. They are shares of a different total. This page divides by the effort that is drawn, which is processor plus waits and counts every execution type. Waits by Query divides by elapsed time and counts regular executions only.

Why is the monitoring database on the chart? Because its collector is real work on the instance. Leaving it out would hide load the instance is genuinely carrying.

The page takes a while to load. Every database with Query Store on is read in turn. Choose a shorter window, and turn Query Store off, or its capture mode down, in databases nobody needs to tune.

I chose 1 h and the page is empty. The window ends at the last completed interval. A database with a 60 minute interval has nothing to show until the first full hour has closed, and one with a longer interval than the window has no interval wholly inside it.

Where does the Where to look sentence for a contention row come from? From the row’s biggest wait. It is the same guidance the Waits report gives for that category.