Batch Mode Processing, Aggregate Pushdown, Segment Elimination, and Plan Reading

Learning outcomes

Columnstore changes storage, but SQL Server can also change how operators consume rows. Batch mode processes groups of rows through vectorized operators instead of invoking row-mode logic one row at a time. A columnstore scan is a common gateway into batch mode, yet SQL Server 2019+ can also choose batch mode on rowstore at compatibility level 150+. This matters because “batch mode” and “columnstore” are related but not identical concepts. A good plan review separates execution mode, segment elimination, predicate/aggregate pushdown and cardinality/memory behavior.

1. Batch mode changes the execution unit

Row mode passes one row at a time between operators. Batch mode passes vectors/batches of rows, allowing operators to amortize function-call overhead and use CPU-efficient processing for scans, joins, filters, sorts and aggregates when the optimizer chooses it. Columnstore was the original major use case, but Batch Mode on Rowstore (BMOR) can consider batch execution for sufficiently large rowstore queries beginning with SQL Server 2019 at database compatibility level 150 or higher.

The optimizer still chooses the plan. BATCH_MODE_ON_ROWSTORE = ON does not force every rowstore query into batch mode; heuristics consider table size, estimated rows and candidate operators. Likewise, a query touching columnstore can contain row-mode operators where a particular operator or feature does not support batch execution.

SELECT name,compatibility_levelFROM sys.databases WHERE database_id=DB_ID();SELECT name,value,value_for_secondaryFROM sys.database_scoped_configurationsWHERE name=N'BATCH_MODE_ON_ROWSTORE';GO

2. Read execution mode from the actual plan, then correlate it with work done

Enable the actual execution plan in SSMS/VS Code or use SET STATISTICS XML ON. Remember the Chapter 10 rule: an actual plan executes the query. On the plan operators, inspect estimated and actual execution mode, row counts, memory grant/warnings, and the storage operator chosen. Then compare with STATISTICS IO/TIME. Operator mode is one part of the explanation, not the result itself.

SET STATISTICS IO ON;SET STATISTICS TIME ON;GOSELECT region_code,       status,       COUNT_BIG(*) AS work_orders,       SUM(labor_minutes) AS total_minutes,       SUM(amount) AS total_amountFROM lab19.WorkOrderFactWHERE opened_at >= '2025-05-01'  AND opened_at <  '2025-09-01'GROUP BY region_code,statusORDER BY region_code,status;GOSET STATISTICS IO OFF;SET STATISTICS TIME OFF;GO

On an Enterprise-family build, a plan may show a columnstore scan feeding a batch-mode hash aggregate, with aggregation pushed into the scan when eligibility rules are met. On Standard/Express, the edition matrix limits some Enterprise columnstore scalability enhancements and batch DOP (Standard DOP 2; Express DOP 1). Therefore, do not use an Enterprise Developer lab result to promise identical operator behavior or throughput in Standard/Express production.

3. Pushdown means doing work nearer the scan

Aggregate pushdown can perform eligible MIN, MAX, SUM, COUNT and COUNT(*) work inside the scan, reducing rows flowing to a separate aggregate. String predicate pushdown can evaluate eligible string predicates against dictionaries/encoded values before materializing every row. These are separate from segment elimination. Segment elimination decides which rowgroups need scanning; pushdown reduces work inside the rowgroups that remain.

Microsoft's SQL Server 2025 edition matrix classifies aggregate pushdown, string predicate pushdown and SIMD optimizations as Enterprise scalability enhancements. Teach the mechanism, but verify the actual plan/edition before claiming it is active. A query can still benefit from column elimination, compression, segment elimination and batch processing when a specific pushdown optimization is absent.

-- Eligible shape: simple aggregate over supported numeric types.SELECT region_code,       SUM(amount) AS total_amount,       COUNT_BIG(*) AS rows_seenFROM lab19.WorkOrderFactWHERE status='CLOSED'GROUP BY region_code;GO-- DISTINCT and expressions can change where aggregation occurs.SELECT region_code,       COUNT(DISTINCT technician_code) AS technicians,       SUM(amount * 1.05) AS adjusted_amountFROM lab19.WorkOrderFactWHERE status='CLOSED'GROUP BY region_code;GO

Compare plans; do not infer pushdown from elapsed time. Look for where the aggregate/filter is represented relative to the columnstore scan and use actual row counts to see how much data flows upward.

4. Segment elimination is independent evidence

SET STATISTICS IO ON;GOSELECT COUNT_BIG(*) AS rows_in_window,       SUM(amount) AS amount_in_windowFROM lab19.WorkOrderFactWHERE opened_at >= '2025-07-01'  AND opened_at <  '2025-07-08';GOSELECT COUNT_BIG(*) AS rows_all,       SUM(amount) AS amount_allFROM lab19.WorkOrderFact;GOSET STATISTICS IO OFF;GO

The messages pane can report Segment reads and segment skipped. If a narrow predicate skips many segments, that is elimination evidence. It does not prove batch mode, and batch mode does not prove elimination. Ordered columnstore can improve elimination by reducing overlapping min/max ranges; a fully qualifying predicate naturally cannot skip the same segments.

5. Rowstore comparison without changing the database globally

For a rowstore table under compatibility 150+, BMOR is considered heuristically. You can use documented query hints to compare allowed/disallowed batch mode during an experiment instead of toggling the whole database in the middle of a shared workload. This lab is an observation exercise; it is not a recommendation to hint production queries permanently.

SELECT region_code,       status,       COUNT_BIG(*) AS orders,       SUM(amount) AS amountFROM lab19.LiveWorkOrderGROUP BY region_code,statusOPTION (RECOMPILE, USE HINT('ALLOW_BATCH_MODE'));GOSELECT region_code,       status,       COUNT_BIG(*) AS orders,       SUM(amount) AS amountFROM lab19.LiveWorkOrderGROUP BY region_code,statusOPTION (RECOMPILE, USE HINT('DISALLOW_BATCH_MODE'));GO

Inspect actual plans rather than expecting a particular winner. Dataset size, edition DOP, statistics, memory and query shape determine whether BMOR is chosen or helpful. The hints deliberately alter optimizer search behavior and should not be deployed just to make a plan screenshot say “Batch.”

6. Production judgment and bridge

Use execution mode as one diagnostic dimension. Capture actual plans under representative parameters, pair them with STATISTICS IO/TIME, Query Store history and workload waits, and separate storage skipping from vectorized execution. Lesson 4 moves to ingestion and maintenance: how batch size, partitioning, delete pressure, REORGANIZE and REBUILD determine whether healthy columnstore structures remain healthy.

Authoritative references

• Columnstore indexes - Query performance
• Intelligent Query Processing details - Batch Mode on Rowstore
• ALTER DATABASE SCOPED CONFIGURATION
• Columnstore indexes in data warehousing
• SQL Server 2025 editions and supported features