A fair test of row mode vs batch mode changes only the execution mode. This post uses a query hint to force row mode at the same compatibility level, checks that both modes return the same rows, and times five runs of each on a three million row table. It also shows a query where batch mode gains nothing.
Finding Regressed Queries in Query Store After a Deployment
Find regressed queries in Query Store using weighted before-and-after duration and CPU, then compare plans and changed procedure text.
Batch Mode on Rowstore: A Simple Example in SQL Server
Batch mode on rowstore lets SQL Server process rows in groups, even when the table has no columnstore index. This post builds a million row table, reads the execution mode of each plan operator, and flips four switches to show when you get batch mode and when you don’t. It also answers why a plan can stay in row mode.
RID Lookups on Heaps vs Key Lookups on Clustered Tables
Compare RID lookups on heaps with clustered key lookups, inspect forwarded records, and test covering indexes with actual plans and reads.
Estimated Plan With a Temp Table: Why It Guesses One Row
An estimated plan for code that fills a temp table guesses one row for every read of that table. This post compares the estimated plan and the actual plan on SQL Server 2025, shows where the numbers split, and explains when to trust each one.
Sequence Project and Segment: How Window Functions Show Up in a Plan
A window function leaves Segment and Sequence Project operators in a query plan, and a Sort in front of them. This post tests ROW_NUMBER and a running total on SQL Server 2025, shows the batch mode Window Aggregate that replaces them, and removes the Sort with an index.
The Filter Operator: When SQL Server Reads Everything and Keeps a Few Rows
The Filter operator keeps a few rows after the plan has read and sorted many more. This post shows one on SQL Server 2025 with the actual plan and the logical reads, then rewrites the query so it seeks instead and reads about a fifth as many pages.