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.
Create an Index Online in SQL Server: What ONLINE = ON Locks
To create an index online in SQL Server, add WITH (ONLINE = ON) to CREATE INDEX, and writers keep working while the index builds. This post compares a plain build with an online build on a three million row table, measures what each one blocks and what the online build costs, and shows where it still waits.
Autogrowth Settings: Check Every Database File in SQL Server
To check autogrowth settings for every database file, read sys.master_files and turn its raw page counts into sizes you can judge. This post builds a demo database with three differently set files, reports the current size, the next growth step and the room left, and fixes the weak settings. It also explains why the same query can return no rows.
Missing Index Suggestions: Three Checks Before You Create One
Review missing index suggestions against existing indexes, key order, and INCLUDE size before adding extra write overhead.
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.