Batch Mode Candidates
Overview
In row mode SQL Server passes one row at a time between the operators of a plan and pays the cost of the call for every row. In batch mode it passes up to 900 rows at once. For analytic work, hash joins, hash aggregates, sorts and window aggregates over large numbers of rows, that is often the difference between a query taking minutes and the same query taking seconds, on the same hardware with the same indexes.
Every plan records which mode each operator was expected to run in, EstimatedExecutionMode. This report reads the busiest plans Query Store holds and asks, for each one: is it still doing analytic work a row at a time, and what could moving it be worth?
Columnstore Health reads the columnstore indexes that already exist. This report asks the question before that: whether anything on the database does the kind of work that wants one.
Why a share and not a ranking
Batch mode only touches certain operators, so what it can be worth to a query is capped by how much of that query’s cost sits in those operators. The page calls that share the reach. A query with 60% of its cost in reach cannot be made more than 2.5 times faster by any change to how that part runs:
the most any change could be worth = 1 / (1 - reach)
at an assumed speedup s on that part = 1 / ((1 - reach) + reach / s)This is Amdahl’s law. The curves are nearly flat until the reach is very high, which is why the page is drawn on the share rather than ranked by cost: the most expensive query on the list can have almost nothing in reach.
The reach is measured, from the optimizer’s own operator costs in the plan. The speedup is an assumption, chosen on the toolbar, because SQL Server cannot say what a query would have cost in a mode it did not run in.
The six conditions
Every plan is tested in order and stops at the first condition it fails, so a plan stopped by one was never tested against the rest. The grid’s Outcome column says where each plan stopped.
| Outcome | What it means | What to do |
|---|---|---|
| Nothing to accelerate | No hash join, hash aggregate, sort, window aggregate or adaptive join. | Nothing. The plan is correctly in row mode. |
| Too little of the plan | The work batch mode could take is under 20% of the plan’s cost, so nothing could make it 1.25 times faster. | Nothing here. Whatever is slow is in the rest of the plan. |
| Already in batch mode | The same test, where part of the plan already runs in batch mode. | Nothing. |
| Too few rows | The widest operator that could move handles fewer than 50,000 rows. | Nothing. There are not enough rows to fill batches. |
| Compatibility level | Batch mode on rowstore is available on this instance, and the database is below compatibility level 150. | Raise the compatibility level, after testing: it changes the cardinality estimator too. |
| Nothing large enough | No table the plan reads holds 131,072 rows, the size the engine’s own rule wants. | Nothing. A columnstore index would not repay itself either. |
| Changed too often | The largest table has been written more than once per row since SQL Server started. A columnstore index would live in delta stores. | A judgement: a nightly load reads the same as constant change on this counter. |
| Candidate | Passed every condition. | See the What to do column. |
| Not a query | An index build, statistics update or other maintenance. | Nothing. Batch mode is chosen per query. |
Versions and editions
- Batch mode on rowstore arrived in SQL Server 2019, at compatibility level 150, in Enterprise and Developer Edition. The compatibility level condition only applies where that is true.
- On SQL Server 2016 and 2017, and on Standard Edition, batch mode on rowstore does not exist. The page skips that condition, and for a candidate the way in is a nonclustered columnstore index on the largest table the query reads.
- Finding candidates works on SQL Server 2016 and newer.
Where to find it
In the tree, under a database, Real Time → Query Store → Batch Mode Candidates.
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. |
| SQL Server 2019 or newer, Enterprise or Developer, compatibility level 150 | For batch mode on rowstore itself. Not needed to find candidates. |
| Query Store on for the database | The plans and their runtime statistics live in Query Store. |
VIEW DATABASE STATE |
To read the Query Store catalog views and sys.dm_db_partition_stats. |
VIEW SERVER STATE (optional) |
For the update counts behind Changed too often. Without it the page still loads, and that condition reads no updates. |
The toolbar
| Control | What it does |
|---|---|
| CPU / Duration / Logical reads / Executions | What “busiest” means, and the currency the savings are in. CPU is the default. |
| 4 h / 24 h / 3 d / 7 d | The window of Query Store history. It ends at the last completed interval. |
| 25 / 50 / 100 / 200 plans | How many of the busiest plans are read. 50 is the default. |
| Assume 2x / 5x / 10x | The assumed speedup on the part of a plan batch mode reaches. Changing it redraws the page 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. |
The choices are remembered for the next visit.
Reading the chart
At the top, a verdict says, in this order of priority: that the compatibility level is turning batch mode on rowstore off for plans that want it; how many candidates there are and what the best one could be worth; what is stopping the most work when there are no candidates; that the plans wanting batch mode already have it; or that nothing does analytic work at all.
Up to six tiles follow:
| Tile | What it is |
|---|---|
| Plans read | How many of the busiest plans were read. |
| Candidates | Plans that passed every condition. Click to show only those in the grid. |
| CPU time it could save (or the chosen measure) | What the candidates would save at the assumed speedup: the saving, not their whole cost. |
| Best single candidate | The candidate with the largest cost, and how many times faster it could be. |
| Biggest thing in the way | The condition holding back the most cost, when there is one. |
| Already in batch mode | Plans already mostly in batch mode, and their share of the window. |
The payoff chart
- The horizontal axis is the reach, from 0% to 100%. It always runs the whole way, because it comes from the law and not from the data.
- The vertical axis is times faster, on a logarithmic scale from 1x to 10x.
- The solid black curve is the most any change could be worth. The shaded area above it is out of reach for everything.
- The blue dashed curve is the assumed speedup from the toolbar. Every plan sits on it.
- The gray dashed curves are the other two speedups, for comparison.
- The dotted rule at 1.25x, with an upright at 20% reach, marks where a change starts to be worth doing: to the left of the upright, nothing can reach 1.25x.
Each plan with any work batch mode accelerates is a circle, sized by the chosen measure:
| Mark | Meaning |
|---|---|
| Filled, orange | A candidate. |
| Filled, green, with a hole in the middle | Already in batch mode. |
| Hollow | Blocked by one of the conditions. Its color names which one. |
| A slash through the mark | Worth more than 10x at the assumed speedup, pinned to the top edge. |
The gap between a mark and the solid curve above it is all a better engine could ever add. Hover over a mark for the reach, the payoff and the outcome.
Reading the grid
One row per plan read, in the order Query Store ranked them.
| Column | What it is |
|---|---|
| # | Rank by the chosen measure. |
| Outcome | Where the plan stopped in the conditions above. |
| Object | The procedure, function or trigger the statement belongs to. |
| Query | The statement, with the parameter list Query Store prefixes removed. |
| What to do | The action for the outcome, with any caveat. |
| Reach | The share of the plan’s cost in row mode work batch mode could take over. |
| Could be | Times faster at the assumed speedup. |
| CPU time (or the chosen measure) | The plan’s total in the window. |
| Share | That total as a share of everything every plan in the window spent. |
| Rows through | Rows handled by the widest operator that could move. |
| Largest input | The largest table the plan reads. |
| Its rows now | That table’s rows now, from the catalog. The plan’s own figure is from when it compiled. |
| Mode now | row, or batch and the share of the plan already in batch mode. |
| Executions | Regular executions in the window. |
| Plan | The Query Store plan id. |
| Last run | When the plan last ran, UTC. |
The What to do column takes the width the others leave, so it grows with the window. The last columns (Rows through, Largest input, Its rows now, Mode now, Executions, Plan and Last run) sit past the edge of a 1280 pixel screen; scroll right for them, or hover over a row, whose tooltip lists every column in full.
Double-click a row for the statement with its reading beside it. Right-click for:
| Action | What it does |
|---|---|
| Show the statement | The statement in the query window. |
| Copy the columnstore index script | For a candidate whose largest table has no columnstore index: the statement, copied to the clipboard. Nothing is run. |
| Copy query text | The statement, without the parameter list. |
| Explain batch mode and the six conditions | The conditions and the arithmetic, in words. |
| Copy the query behind this report | The batches the page runs, ready for SSMS. |
About the script
- It is always a nonclustered columnstore index: it sits beside the rowstore, the optimizer uses whichever is cheaper per query, and it can be dropped again in a moment.
- Columns are named. Columns a nonclustered columnstore index cannot hold,
varchar(max),nvarchar(max)andvarbinary(max),xml,text,ntext,image,sql_variant,timestampand the CLR types, are left out and listed above the statement. Computed columns are left out. - A table with a sparse column cannot have a columnstore index at all; the grid says so and no script is offered.
ONLINE = ONis written only on SQL Server 2019 or newer in Enterprise or Developer Edition.- A note says that a build on more than one worker produces smaller row groups, and that
MAXDOP = 1avoids it.
Where the data comes from
| Source | What it gives |
|---|---|
sys.query_store_runtime_stats and ..._interval |
Executions and totals per plan in the window. |
sys.query_store_plan |
The plan document, query_plan, for the busiest plans only. |
sys.query_store_query, ..._query_text |
The statement and its object. |
sys.databases |
The compatibility level. |
SERVERPROPERTY('EngineEdition') |
Whether the edition has batch mode on rowstore and online index builds. |
sys.indexes, sys.columns, sys.types |
Existing columnstore indexes, sparse columns and column types. |
sys.dm_db_partition_stats |
Rows in each table now. |
sys.dm_db_index_usage_stats |
Updates to each table since SQL Server last started. |
How the plans are read
The documents are returned as text and read by Database Health Monitor, not with XQuery on the server, because a plan nested deeper than 128 levels cannot be shredded with the XML methods. For every operator the reader takes its execution mode, its own cost (its subtree cost less its children’s), the rows it handles (the larger of what it produces and what its children hand it, over every run), and the table it reads. Operators then fall into three roles:
| Role | Operators |
|---|---|
| The work batch mode is for | Hash Match, Adaptive Join, Sort, Top N Sort, Window Aggregate, Window Spool |
| Carried along with it | Scans, Filter, Compute Scalar, Concatenation, Bitmap, Segment, Sequence Project |
| Not affected | Seeks, lookups, Nested Loops, Merge Join, spools, writes, and the rest |
The reach is the cost of the first two roles still in row mode, divided by the plan’s cost.
What the numbers are
- Totals count regular executions only, weighted by executions.
- The window ends at the last completed Query Store interval.
- Costs and rows come from the plan Query Store captured first. Table sizes are also read from the catalog now, and the catalog figure is used when there is one.
- The saving is each candidate’s cost times (1 – 1 / payoff) at the assumed speedup.
Messages you may see
This database is at compatibility level 140, so batch mode on rowstore is off entirely. Plans that want batch mode, on tables large enough, are held back by the compatibility level alone.
Nothing here is a candidate, and the thing stopping the most of it is … Work down the grid: the Outcome column says which condition each plan stopped at.
Nothing in the busiest plans does the kind of work batch mode is for. The normal reading for a transactional database.
N plans were too large or malformed to read to the end. What was read is real, and nothing is claimed about the rest of those plans.
Related reports
| Report | Why you would go there |
|---|---|
| Columnstore Health | The state of the columnstore indexes that already exist. |
| CPU by Query | The queries costing the most CPU, whatever mode they run in. |
| Page Reads by Query | The queries reading the most pages. |
| Plan Regressions | Forcing an older plan back if raising the compatibility level makes a query worse. |
| Plan Resource Profile | Where a plan’s resources go. |
Frequently asked questions
Why is a query with a huge CPU cost not a candidate? Its reach is low: most of its cost is in seeks, lookups or loops, which batch mode does not change. Or it stopped at an earlier condition; the Outcome column says which.
Why is the speedup a choice? Batch mode commonly makes analytic operators several times faster and occasionally much more, and SQL Server cannot say what a particular query would have cost in a mode it did not run in. The reach does not depend on the choice, and neither does the solid curve.
Why are index builds listed as Not a query? Query Store records an index build as an insert from a sort over a scan, which looks exactly like analytic work. The page recognizes the statement text and leaves it alone.
Does the report change anything? No. Scripts are copied to the clipboard for you to read and run yourself.