Workload Change

Overview

Every other page photographs the database as it is now and ranks what it finds. The question a DBA is actually handed is different: what changed? The biggest queries this week were the biggest queries last week as well, and the thing that moved is usually nowhere near the top of either list.

This report compares two windows of the same length, the last 24 hours against the 24 hours before it for example, and draws the arithmetic between them as a bridge. It starts at the earlier total, steps up for every query that costs more than it did and down for every one that costs less, and lands on the recent total.

The shape is the answer:

  • One tall step is one query to look at.
  • A staircase of small steps is a workload that grew all over.
  • A tall rise beside a tall fall is a total that barely moved while the work behind it changed completely.
The Workload Change report: the chart above the grid
The whole page on a database with a month of Query Store history.

Busier, or slower?

A query costs more for exactly two reasons, and they are different problems with different fixes:

Reason What it means
It ran more often The database is busier, and the query may be blameless.
Each run cost more The database is slower, and the query is the place to start.

Both are computed for every query and they add up to its change exactly:

(recent runs - earlier runs) x earlier cost per run      it ran more
+ (recent cost per run - earlier cost per run) x recent runs   each run cost more
= the change

A query that did not run at all in the earlier window has no earlier cost per run, so all of its cost counts as running more, which is what a new caller is.

That split is what lets the page say “this database is not slower, it is busier” and show the working. Both situations produce an identical rise in every total elsewhere in the product, and only one of them is a query to tune.

Where to find it

In the tree, under a database, Real Time → Query Store → Workload Change.

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 – go back history, 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.
SQL Server 2017 or newer for Log bytes avg_log_bytes_used arrived in 2017. On 2016 the page shows duration instead and says so.
VIEW DATABASE STATE To read the Query Store catalog views.

The toolbar

Control What it does
Duration / CPU / Reads / Log bytes The measure the two windows are compared on.
4 h / 12 h / 24 h / 7 d The length of each window. Both windows are always the same length.
Before it / Yesterday / Last week Where the earlier window sits.
Turn Query Store on Only when Query Store is off and a setting would fix it.
Refresh Re-reads Query Store.

Both windows are always the same length, because Query Store totals are amounts rather than rates: an hour compared against a day would report every query as having improved enormously.

A comparison that would overlap the recent window (a 7 day window compared with yesterday) is moved to the period immediately before it, and the page says so under the chart.

Reading the chart

The Workload Change chart
The chart on its own, from the same capture.

At the top, a verdict names which of the situations above this database is in, and six tiles carry the totals:

Tile What it is
Was / Now The measure’s total in each window, with the execution counts.
Net change The difference, as an amount and a percentage.
Because it ran more The part of the change that is queries running a different number of times. Click to filter the grid to those queries.
Because each run cost more The part that is each run costing a different amount. Click to filter.
New queries Queries that did not run in the earlier window, and how many stopped. Click to filter.

Click a filtering tile again to clear the filter.

Under the tiles, the bridge:

  1. The rises, largest first, in red.
  2. The falls, largest first, in green.
  3. Everything else, the combined change of every query not drawn as a column. Without this column the bridge would land on the sum of fifteen queries and call that the change in the workload.
  4. Net change, drawn from zero, where the recent window ended up.

Each column starts where the last one finished, and a dotted line joins them. The vertical axis is the change, not the totals: zero is the earlier window’s total. The totals themselves are on the tiles, where there is room to read them.

Hover over a column for the query, its change, and where the running total stands after it.

Reading the grid

The Workload Change grid
The first rows of the grid, from the same capture.
Column What it is
# Rank by the size of the change, in either direction.
Object The procedure, function or trigger, when the statement belongs to one.
Query The statement text, with the parameter list Query Store prefixes removed.
Change The difference between the two windows.
Plan changed Whether the most used plan is a different one, “new query”, or “stopped”. It sits next to Change so it stays on screen on a small display.
Share of movement This query’s change as a share of everything that moved, in either direction.
Ran more The part of the change from running a different number of times.
Cost per run The part of the change from each run costing a different amount.
Runs before / Runs now Regular executions in each window.
Was / Now Cost per run in each window.

Read the two effect columns rather than the change: they say which of the two situations each row is, and those are different pieces of work.

Double-click a row to see the statement with its split beside it. Right-click for:

Action What it does
Explain this change The split for the row, in words.
Show the statement The statement in the query window.
Go to Plan Regressions Only when the plan changed.
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 of the measure 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 retention check.

Three things the query gets right

The recent window ends at the last completed interval, not at the clock. The interval still being filled has partial counts, and including it would make the newest hour look cheaper than it was.

Only regular executions are compared. A query canceled by a client timeout records the timeout as its duration. Aborted and failed executions are counted in the footer instead.

Totals are weighted by executions. Query Store keeps an average per plan per interval, and every total on this page is the average multiplied by the executions, never an average of averages.

Messages you may see

The comparison reaches further back than Query Store keeps. The earlier window starts before STALE_QUERY_THRESHOLD_DAYS. Choose a shorter window or a nearer comparison.

There is nothing to compare this against. Query Store holds nothing for the earlier window: the database was idle then, or Query Store was cleared or turned on since.

Query Store’s history starts after the earlier window begins. The earlier window is only partly covered, so the change is overstated.

Capture mode is AUTO, so cheap or infrequent queries are being discarded. The totals are missing whatever AUTO dropped.

Report Why you would go there
Plan Regressions When the cost per run rose and the plan changed.
Change Significance Whether a change is larger than the query’s normal variation.
Cost Drift When a query’s cost per run changed, rather than how much.
Rank Movement Which queries are climbing the list over several days.
Hourly Drift Whether a busier database is simply a busier part of the day.
Waits by Query What a query that got slower without a plan change is waiting on.

Frequently asked questions

The total went down but the page is full of red columns. Rises and falls are both drawn. A total that fell can still hide queries that got much worse, and those are usually the ones somebody is complaining about.

Why fifteen columns? A reader cannot follow a running total through fifty steps. The rest of the workload is the everything else column, so the bridge still lands on the real total.

A query shows as new, but it has been running for months. It did not run in the earlier window. Queries that run weekly or monthly appear as new and stopped depending on which windows are compared.

Why does a query that got faster appear under “Because it ran more”? It moved for both reasons at once, and running more was the larger. The two effect columns show both.