Make your SQL Server fly! Still on Batch Mode… The Part 6

Still on Batch Mode… – Part 6: Compatibility & Query Settings That Influence Execution

Back to the series: Part 5 – How to Force Batch Mode on Rowstore

Hi guys, We’re still on Batch Mode, because understanding when it works is just as important as knowing how to force it.

Today we go deeper: we will see compatibility levels, memory grants, cardinality estimation, and trace flags.

Happy reading and may your CPU stay cool under heavy aggregation!

Compatibility Requirements

Batch Mode on Rowstore requires both:

  • ✔ SQL Server 2019 or newer
  • ✔ Database compatibility level ≥ 150

So, if your database runs at compatibility 140 or lower then Batch Mode on Rowstore simply cannot activate.

Check Compatibility


SELECT name, compatibility_level
FROM sys.databases
WHERE name = DB_NAME();

Change Compatibility


ALTER DATABASE CURRENT
SET COMPATIBILITY_LEVEL = 150;
GO

Always test before upgrading — optimizer behavior changes across compatibility levels.

Deep Dive: Query Settings That Influence Batch Mode

A) Memory Grants

Batch Mode relies heavily on correct memory grants. If the optimizer estimates too little memory, it may avoid Batch Mode.

Hash joins and aggregations in Batch Mode are sensitive to memory availability. 


-- Check memory grant info in actual execution plan
SET STATISTICS XML ON;

SELECT CustomerID, SUM(Amount)
FROM Sales
GROUP BY CustomerID;

Look for:

  • MemoryGrantInfo in execution plan XML
  • Spills to tempdb
  • Excessive granted memory

Underestimated memory → spills → Batch Mode advantage reduced.

B) Cardinality Estimation

Batch Mode decisions depend on row count estimates.

If the optimizer estimates very small row counts, it may choose Row Mode because the overhead of Batch Mode is not considered worthwhile.

Compatibility level 150 also enables newer Cardinality Estimation behavior.

You can compare estimation behavior using: 


-- Force legacy CE
OPTION (QUERYTRACEON 9481);

-- Force new CE
OPTION (QUERYTRACEON 2312);

Be cautious with trace flags — use only for testing or troubleshooting.

C) Trace Flags & Engine Behavior

Certain trace flags and hints influence optimizer behavior and Batch Mode eligibility.

  • QUERYTRACEON 9347 (Batch Mode related testing scenarios)
  • QUERYTRACEON 9453 (diagnostic behavior)
  • USE HINT('ForceBatchMode')

SELECT CustomerID, SUM(Amount)
FROM Sales
GROUP BY CustomerID
OPTION (USE HINT('ForceBatchMode'));

Forcing Batch Mode does not guarantee better performance.

Always compare:

  • Elapsed time
  • CPU time
  • Logical reads

When Batch Mode May Not Appear

  • Low estimated row counts
  • Small tables
  • Insufficient memory grant
  • Unsupported operators in plan
  • Wrong compatibility level

Batch Mode is a cost-based decision — not a guaranteed feature.

Takeaways

  1. Batch Mode on Rowstore requires SQL Server 2019+ and compatibility level ≥ 150.
  2. Memory grants strongly influence performance.
  3. Cardinality estimation affects whether Batch Mode is chosen.
  4. Trace flags are powerful but must be used carefully.
  5. Always benchmark Row Mode vs Batch Mode.

Batch Mode is not magic. It’s math, memory, and cost-based optimization.


...As usually we Benchmark Everything Properly so you can do it too!

Theory is good. Execution plans are better. But benchmarks are the truth.

Let’s compare Row Mode vs Batch Mode under controlled conditions.

Step 1 – Prepare a Large Dataset


-- Example large dataset generator
SELECT TOP (5000000)
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID,
    ABS(CHECKSUM(NEWID())) % 1000 AS CustomerID,
    ABS(CHECKSUM(NEWID())) % 500 AS ProductID,
    ABS(CHECKSUM(NEWID())) % 1000 AS Amount
INTO SalesBig
FROM sys.objects a
CROSS JOIN sys.objects b;

Make sure compatibility level is 150.

Step 2 – Row Mode Benchmark


SET STATISTICS IO, TIME ON;

SELECT CustomerID, SUM(Amount)
FROM SalesBig
GROUP BY CustomerID;

Record:

  • CPU time
  • Elapsed time
  • Logical reads
  • Execution plan operator mode

Step 3 – Forced Batch Mode Benchmark


SET STATISTICS IO, TIME ON;

SELECT CustomerID, SUM(Amount)
FROM SalesBig
GROUP BY CustomerID
OPTION (USE HINT('ForceBatchMode'));

Compare the metrics again.

On large aggregations, CPU reduction can be dramatic.

Step 4 – Clear Cache (Optional Controlled Test)


CHECKPOINT;
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;

⚠ Never run this on production.

Step 5 – Inspect Runtime Statistics via DMV


SELECT 
    qs.total_worker_time,
    qs.total_elapsed_time,
    qs.total_logical_reads,
    qs.execution_count,
    SUBSTRING(qt.text, qs.statement_start_offset/2,
    (CASE 
        WHEN qs.statement_end_offset = -1
        THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
        ELSE qs.statement_end_offset 
     END - qs.statement_start_offset)/2) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.total_worker_time DESC;

This gives you real aggregated performance data.

What You Should See

  • Lower CPU usage in Batch Mode
  • Same logical reads (usually)
  • Better performance on large scans and aggregations
  • Minimal benefit on small datasets

If row count is small, Batch Mode overhead may negate benefits.

Final Thought

Batch Mode is not about forcing features.

It’s about measuring, comparing, and understanding.

Performance tuning without benchmarking is guessing. Batch Mode without understanding memory and cardinality is gambling.


Seeeee you on Part 7!

Luca Biondi @ 2026

Comments

Post a Comment

I Post più popolari

Speaking to Sql Server, sniffing the TDS protocol

Image
Hello friends!  If yesterday you read my post and found it definitely boring (for the topic of course and not for how it was written to the author 😁) well today I promise you that the topic will be much more interesting! Let's talk about Sniffing , let's talk about going to see what happens when we "log in" or when we send a Query to SQL Server.  We will do this by taking a look to the little known tabular data stream (TDS) protocol built on top of the TCP/IP protocol. Ready! The TDS protocol What is the Tabular data stream (TDS) protocol?  The TDS is the protocol used by the SQL Server client Net-Library to communicates with SQL Server . TDS is specific to SQL Server and it is a low-level protocol that specifies both commands and data in a specific arrangement.  TDS encapsulate the TCP/IP protocol. Sniffing TDS packets you can read from the network the requests that your application do to the SQL Server and read the answer .   How to Sniff data from the ne...
Read more

SQL Server, find text in a Trigger, Stored Procedures, View and Function. Two ways and what ways is better

Image
Hi guys, Welcome back! Today a light post about a question that has been asked to me several times. The question is: how to  search for text inside a trigger or a stored procedure ? We have two different ways. A first way using a system view called syscomments: SELECT obj.xtype, text , * FROM sysobjects obj JOIN syscomments com on obj.id = com.id WHERE TEXT like '%text_that_i_am_searching_for%' A second way using a system view called sql_module: SELECT o. name AS Object_Name, o. type_desc , m. definition FROM sys.sql_modules m JOIN sys.objects o ON m. object_id = o. object_id WHERE m. definition Like '%text_that_i_am_searching_for%' ;   Another question is? What differences do we have in using one way instead of the other? Which solution is better? You have to consider that the system view sys.syscomment is obsolete and will be removed sooner or later. So don't use it in production enviroiments. The sys.sql_m...
Read more

SQL Server, Avoid that damn Table Spool!

Image
Hi guys, Welcome back for another post! A light one inspired from what i do during my work: many times I optimize queries rewriting them or adding index and i like it very much! Sometimes you need to use a little imagination, but everything you learn ... sooner or later it will come in handy! Enjoy the reading mates! Avoid the Table Spool, when you can A few days ago I came across a query whose execution time was very high ... or so it was for the client complaining about the problem. Before recreating it, let's create the structures and fill the tables with some data Create table PriceList (Id int identity (1,1) primary Key ,Code Varchar (20)) Insert into PriceList (code) select top 1000 right( '0000' + cast ( row_number () over ( order by id) as varchar (4)),4) from artico Insert into PriceList (code) select top 4000 right( '1000' + cast ( row_number () over ( order by id) as varchar (4)),4) from artico Create table OrdersList (Id int identity...
Read more