Statement Hot Spots
Overview
Once you know which procedure is expensive, the next question is always which statement inside it the time is going to. The module reports rank whole procedures, and the Query Store pages rank single statements with the procedure’s name in a column. Neither puts one procedure’s statements side by side, so answering the question by hand means reading the procedure, looking each statement up, and adding the results together.
This report does that for the twelve busiest modules in the window. For every statement Query Store measured inside them it shows what the statement cost, what share of its module that is, and where in the module’s text the statement sits.
The number that only exists once the statements are together
Query Store counts executions per statement. On its own that count means little: forty thousand executions is a lot for a nightly job and nothing for a login check. Beside the other statements of the same module, counted over exactly the same intervals, it is strong evidence of row by row work.
A statement that runs many times as often as the typical statement of its own module is inside a WHILE loop or a cursor, whether or not it is expensive per run. Ten microseconds four million times is forty seconds of the database and a row no ranked list will ever put near the top.
The typical statement is the module’s median statement by execution count:
- Not the mean, because a loop drags the mean up and then compares itself against its own effect.
- Not the smallest, because a statement inside an
IFbranch runs less often than the module is called, and every ordinary statement would then look repeated. - Not the procedure’s call count from
sys.dm_exec_procedure_stats, which lives in the plan cache, resets on eviction and restart, and would be a ratio of two different afternoons.
A statement is only called a loop on evidence: its module must have more than one statement, the statement must have run at least 100 times, and it must be at least 2% of the module’s cost.
Where to find it
In the tree, under a database, Real Time → Query Store → Statement Hot Spots.
The Query Store folder is hidden below SQL Server 2016, and on master and tempdb, where Query Store cannot be turned on.
Requirements
| Requirement | Why |
|---|---|
| SQL Server 2016 or newer | Query Store arrived in SQL Server 2016. |
| Query Store on for the database | The statement history lives inside Query Store. |
VIEW DATABASE STATE |
To read the Query Store catalog views. |
VIEW DEFINITION on the modules |
To read the module text that statements are placed in. Without it the costs and readings still appear, with no line numbers. |
The toolbar
| Control | What it does |
|---|---|
| Duration / CPU / Reads / Executions | The measure the modules are ranked by and each statement’s share is taken of. Changing it reads Query Store again, because a different measure picks different modules. |
| 1 h / 4 h / 12 h / 24 h / 3 d / 7 d | The window. |
| Loop 5x / 10x / 25x / 100x | How many times its module’s typical statement a statement has to run before it is called a loop. Changing it redraws without reading Query Store again. |
| Turn Query Store on | Only when Query Store is off and a setting would fix it. |
| Refresh | Re-reads Query Store. |
Reading the chart
At the top, the verdict names the worst finding across all twelve modules, with the module and the line, and six tiles carry the summary:
| Tile | What it is |
|---|---|
| Busiest module | The module that cost the most on the chosen measure, and its share of the whole database. |
| Its worst statement | Where that module’s most expensive statement is, its share of the module and its executions. |
| Runs in a loop | Statements running far more often than their module’s typical statement. Click to filter the grid to them. |
| Carries the module | Statements that are 60% or more of their module. Click to filter. |
| Worth a look | Statements that are 10% or more of their module. Click to filter. |
| Module share | What these twelve modules are of the whole database on the chosen measure. |
Click a filtering tile again to clear the filter.
The code map
The chart maps one module at a time, starting with the busiest. Click any grid row to map that row’s module.
- The strip on the left is the whole module, from line 1 at the top to its last line at the bottom, whether or not anything was measured at either end. Each statement Query Store measured is a colored mark at its own lines, so the gaps between marks are drawn to scale. Four expensive statements in the last twenty lines of a four hundred line procedure look different from the same four spread through it.
- Each row on the right is one statement, with a leader line back to its mark, a bar for its cost, the cost itself, and its share of the module. A statement called a loop carries a badge with how many times its module’s typical statement it ran.
- A row with no leader line is a statement that could not be found in the module’s current text. It is not placed somewhere plausible: the page says it was not found.
- When there is not room for every row, the smallest statements lose their rows first and the note under the strip says how many. Their marks stay on the strip.
| Color | Reading |
|---|---|
| Red | Runs in a loop |
| Orange | Carries the module |
| Blue | Worth a look |
| Gray | Minor |
Hover over a mark or a row for the lines, the statement, its executions and share, its cost, and the reading in words. For an encrypted module the strip is empty and says so, and the rows are still listed.
Reading the grid
One row per statement, grouped by module in ranking order and, inside each module, in the order the text has them, with statements that could not be placed after them.
| Column | What it is |
|---|---|
| Module | The procedure, function or trigger. |
| Statement | The statement text, with the parameter list Query Store prefixes removed. |
| Reading | Runs in a loop, Carries the module, Worth a look or Minor. Takes the width the other columns leave, so it stays on screen. |
| Total | The statement’s total on the chosen measure. |
| Share of module | That total as a share of the module’s total. |
| Per run | Average duration of one execution. |
| Runs vs typical | Executions divided by the module’s median statement’s executions. |
| Where | The line or lines in the module’s current text, or not found, encrypted or no text. Sorts by position. |
| Executions | Regular executions in the window. |
| Reads a run | Average logical reads of one execution, in pages. |
| Rows a run | Average rows of one execution. |
| Plans | How many plans the statement ran on. |
| Last run (UTC) | When it last ran in the window. |
Hover a row to see every column in full, including the ones past the right edge of the grid.
Double-click a row to see the statement with its figures beside it. Right-click for:
| Action | What it does |
|---|---|
| Explain this statement | The statement’s figures and reading in words. |
| Show the statement | The statement in the query window. |
| Map module on the chart | Only when the row belongs to a module the chart is not showing. |
| Show the module text | The module’s current definition. |
| Go to Plan Regressions | Only when the statement ran on more than one plan. |
| Copy statement text | The statement, without the parameter list. |
| Copy module name | The schema qualified name. |
| 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 averages per plan per interval. |
sys.query_store_runtime_stats_interval |
The window boundaries. |
sys.query_store_plan, sys.query_store_query |
The statement behind each plan, and object_id, the module it was compiled in. |
sys.query_store_query_text |
The statement text and its length. |
sys.objects |
The module’s name, type and modify date. |
sys.sql_modules |
The module’s current text, which statements are located in. |
How a statement is placed
Query Store does not record where in a module a statement came from, so the report searches for the statement’s text inside the module definition. Two differences between the stored text and the module’s source are handled:
- Query Store puts a parameter list in front of a parameterized statement, for example
(@i int)UPDATE dbo.Account SET ... WHERE account_id = @i. No procedure contains that prefix. It matters most for statements inside a loop, which are parameterized because the loop variable is a parameter to them, so the prefix is removed before searching. - Query Store drops the statement’s trailing semicolon. The text without it is still found.
The search is exact. A module altered since a statement last ran no longer contains that statement’s text, so the statement is shown as not found rather than matched to something similar. The same text appearing twice in one module is placed at the first copy, because Query Store keeps one set of counters for both.
What the query gets right
The window ends at the last completed interval, not at the clock.
Only regular executions are measured. Aborted and failed executions are counted in the footer.
Totals are weighted by executions, never an average of averages.
Modules are chosen first and then every statement of each is returned. A top list of statements would hold the expensive half of a procedure and none of the cheap half, and every share taken from it would be too large.
Dropped modules are left out. Their statements are still in Query Store, but there is no text to place them in and no procedure to change.
Messages you may see
Nothing in the window ran inside a procedure, function or trigger. Either the window is too short, or the application sends its statements to the database directly.
One statement could not be found in the current text of the module it belongs to, and that module was altered inside this window. The statement ran against a version of the module that no longer exists.
One module here is encrypted, so there is no text to place its statements in. A module created
WITH ENCRYPTIONhas no readable definition. Its statements are listed with their costs and readings, and Query Store shows their text as** Encrypted Text **.
One module returned no text, which is what a login without VIEW DEFINITION on it sees. Grant
VIEW DEFINITIONto get line numbers.
Related reports
| Report | Why you would go there |
|---|---|
| SPs by Logical Writes | Which procedures cost the most as a whole. |
| Plan Warnings | What is wrong with the plan of a statement that carries its module. |
| Missing (indexes) | Index suggestions for the expensive statement. |
| Plan Regressions | When a statement’s plan changed and it got slower. |
| Deployment Impact | What a module cost before and after it was last altered. |
| Cost Drift | When a statement’s cost per execution changed. |
Frequently asked questions
Why is a statement shown as not found when I can see it in the procedure? The procedure was probably altered since the statement ran, often by a change elsewhere in the same statement, such as a comment inside it or a different literal. The old version keeps its own row.
The statement inside my loop is cheap. Why is it red? Because the fix for a loop is not to tune the statement, it is to do the work in one set based statement. A cheap statement run a hundred thousand times is still a hundred thousand round trips through the engine.
Why are only twelve modules shown? Every statement of each module is returned, so twelve modules is already a lot of rows. The ranking measure on the toolbar decides which twelve.
The line numbers are off by a few lines. A module’s stored text is the whole batch it was created in, including any comments before CREATE PROCEDURE in the same batch. Line 1 is the first line of that stored text, which is what sp_helptext and Show the module text show.