Rank Movement
Overview
CPU by Query answers “what is expensive here”, and it answers it the same way every morning: the biggest queries this week were the biggest queries last week. The question that brings somebody to a monitoring tool is a different one: what is different?
A query that was fortieth on Monday, twentieth on Wednesday and eighth this morning has an unremarkable total for the week and sits in the middle of every ranked list. It is also the most interesting query on the database, and the cheapest one to fix, because it is being caught before anybody is waiting on it.
This report cuts the window into periods (hours, six hour periods or days), ranks every query inside each period, and draws one line per query from period to period. The shape of a line is the answer:
- A line rising steadily is a query taking a larger share of a database that has not grown to match.
- A line that starts part way across is a query that was not running at all earlier in the window: usually a release, a new job or a plan change.
- A spike and a fall back is one bad period rather than a trend.
- Flat lines are a workload whose expensive queries are the ones that have always been expensive.
A place rather than an amount
The chart plots where a query placed, not how much it cost. That is deliberate, and it has two consequences worth knowing.
Ranking inside a period divides out everything that moves the whole database at once. A quiet Sunday, a busy month end and a period that is only partly finished at the right hand edge all cancel, because every query in that period is measured over the same stretch of time. A rise on this chart is always a rise against the rest of the workload, and the newest period can be read as confidently as the oldest.
The cost is that magnitude is gone from the picture: a query can climb twenty places while costing almost nothing. That is why the grid carries each query’s total and its share of the window, why the verdict quotes them, and why a climber that is a rounding error is described as one.
A query that scored nothing in a period (a SELECT writes no log, most statements use no tempdb) is not given a place in it, so the lower half of a ranking is never decided by a tie break.
Where to find it
In the tree, under a database, Real Time → Query Store → Rank Movement.
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 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 and TempDB | avg_log_bytes_used and avg_tempdb_space_used arrived in 2017. On 2016 the page ranks by CPU time instead and says so. |
VIEW DATABASE STATE |
To read the Query Store catalog views. |
The toolbar
| Control | What it does |
|---|---|
| CPU / Duration / Reads / Executions / Log bytes / TempDB | The measure each period is ranked on. |
| 12 h / 24 h / 3 d / 7 d / 14 d | The window, and with it the period length: hours for 12 and 24 hours, six hour periods for 3 days, days for 7 and 14 days. |
| Top 10 / Top 15 / Top 20 / Top 25 | How deep the band goes. A query is on the page if it reached this depth in any period. |
| Turn Query Store on | Only when Query Store is off and a setting would fix it. |
| Refresh | Re-reads Query Store. |
Periods are cut on this computer’s clock, so a day is your day rather than a UTC one. The footer names the offset used. The window ends at the last completed Query Store interval, and starts a whole number of periods before the period that interval is in, so every period except the newest is a whole one.
Reading the chart
At the top, a verdict names the one query most worth knowing about, and six tiles count what happened:
| Tile | What it is |
|---|---|
| Climbing | Queries that gained two or more places from the first period they ran in to the last. The detail names the furthest climb. Click to filter. |
| New in the ranking | Queries in the top N now that did not run at all in the first half of the window. The detail names the highest place one reached. Click to filter. |
| Falling | Queries that lost two or more places. Usually tuning that worked. Click to filter. |
| Dropped out | Queries that were in the top N in the first half of the window and are not now. Click to filter. |
| First place | How many queries led a period, and how often first place changed hands. One query holding it all window is an obvious thing to tune; first place changing hands every period is a database with no single hot query. |
| Shown here | How many of the queries that reached the top N are drawn. At most 25 lines are drawn, and this tile turns amber when some were left out. |
Click a filtering tile again to clear the filter. While a filter is on, the lines the grid is not showing are drawn faded.
Under the tiles, the bump chart:
- One line per query. First place is at the top, and the place numbers are drawn down the left, because this axis counts downwards against every other chart in the product.
- The lane below the band, labeled “outside 10” for a top 10, holds every placing outside the top N. Marks there are hollow and the segments that reach them are dashed, because the lane means “somewhere below” rather than a position. The tooltip gives the actual place.
- A break in a line is a period the query did not run in. The line is never drawn straight across it.
- Color is the trend: red climbing, amber new in the ranking, green falling, gray steady or dropped out.
- The left gutter names each query beside where its line starts, with a dotted leader to its first mark. The right gutter gives its total for the window.
- A period marked * holds less than a whole one: the newest period while it is still being collected, or the oldest one when Query Store’s history starts inside it. That changes the totals in it and not the order.
Hover over a line for the query, its place and cost in the period under the pointer, and how far it moved. Click a line to select its row in the grid; double click to see the statement.
Reading the grid
| Column | What it is |
|---|---|
| Now | The place in the newest period. “> 10 (31st)” is a placing outside the top N; “gone” is a query that did not run. While the newest period is still being collected, a query that has not run in it yet is shown at its place in the period before. |
| Object | The procedure, function or trigger, when the statement belongs to one. |
| Query | The statement text, with the parameter list Query Store prefixes removed. |
| First | The place in the first period the query ran in. |
| Best / Worst | The highest and lowest place across the window. |
| Moved | Places gained from the first period to the last, “+4” or “-2”, or “held”. |
| Trend | New in the ranking, climbing, falling, dropped out or steady. |
| Periods | How many of the window’s periods the query placed in. |
| CPU time (named for the measure) | The query’s total across the window. |
| Share | That total as a share of everything the database did in the window. |
Double-click a row to see the statement with its whole trajectory beside it. Right-click for:
| Action | What it does |
|---|---|
| Explain this trajectory | Every period, one line each: place, the number of queries it was ranked against, the cost and the executions. |
| Show the statement | The statement in the query window. |
| Go to Workload Change | Only for a climbing or new query: whether it ran more often or each run cost more. |
| 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 interval each row belongs to, and so its period. |
sys.query_store_plan, ..._query, ..._query_text |
The query behind each plan. |
sys.database_query_store_options |
The readiness banner and the retention sentence. |
Four things the query gets right
Periods are anchored, not counted from the clock. Period numbers are counted from a fixed date on your clock, and the window starts exactly a whole number of periods before the period holding the last completed interval. Counting from “now minus seven days” instead makes DATEDIFF put most intervals one period too high and silently drops the newest period off the end of the chart.
Only regular executions are ranked. A query canceled by a client timeout records the timeout as its duration, and a query timing out repeatedly would otherwise climb the duration ranking for the wrong reason. 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 is the average multiplied by the executions, never an average of averages.
Places are distinct. Ties are broken by query id, so two queries that cost exactly the same are never drawn on top of each other as one line.
Messages you may see
There is only one day of history in this window. Query Store has data for a single period, so the page can rank the workload but cannot say what moved. Choose a longer window, or come back when the database has been collecting for longer.
No query placed in the last 7 days. Query Store recorded no completed executions with any of the chosen measure in the window. On a quiet database, try another measure or a longer window.
Query Store’s history starts inside the window. The earliest periods are empty, and movement is judged from the first period with data, so a store turned on part way through the window does not report every query as new.
Log bytes needs SQL Server 2017, so CPU time is shown. The chosen measure’s column does not exist on this instance.
Capture mode is AUTO, so cheap or infrequent queries are being discarded. The ranking is missing whatever AUTO dropped.
Related reports
| Report | Why you would go there |
|---|---|
| Workload Change | Whether a climbing query ran more often or each run cost more. |
| Plan Regressions | Whether a query that appeared or climbed changed plan. |
| CPU by Query | The expensive queries right now, from the plan cache, when the ranking is steady. |
| Hourly Drift | Whether a query climbs at one time of day rather than across the window. |
| Performance Baselines | Whether a query is outside its own normal range, whatever its place. |
| Change Significance | Whether a query’s change is larger than its normal variation. |
Frequently asked questions
A query climbed thirty places, but the verdict says it is not worth an afternoon. Its share of the window is small. Places ignore magnitude on purpose, so the verdict checks the share column (5% of the window, or a place in the top three) before calling a climb a problem.
The page says a query is new, but it has been running for months. It did not run in the first half of this window. Weekly jobs appear as new or dropped out depending on which days the window covers; widen the window to see them as steady.
Why does the newest period have a * on it? It is still being collected. The order inside it is still meaningful, because every query in it has been measured over the same few hours, but its totals are smaller than a whole period’s.
Why are the hourly periods at half past the hour? Query Store intervals are aligned to UTC. On a clock that is a half hour off UTC, periods follow the intervals rather than splitting each one in two.
Why at most 25 lines? A reader follows a bump chart one line at a time, and a line crossing forty others cannot be followed. The Shown here tile says how many were left out; choose a smaller top N to see fewer.