SQL Server – No New Index, No Schema Change ....Just pure engine optimization! part 5!

Part 5 – How to Force Batch Mode on Rowstore When SQL Server Doesn’t Choose It

Previous article: Part 4 – No New Index, No Schema Change

Hi guys,

Welcome back to the series where we improve performance
without creating new indexes,
without changing the schema,
and without redesigning the database.

Just pure engine optimization.

Grab your execution plans, turn on Actual Execution Plan,
and let’s dive into Batch Mode on Rowstore again.

Happy reading… and may your queries always run in parallel ⚡

---

Introduction

In Part 4, we saw how Batch Mode on Rowstore can dramatically improve query performance without adding new indexes or changing the schema.

SQL Server does not always choose Batch Mode automatically , even when the query looks like a perfect candidate.

• When SQL Server decides to use Batch Mode
• How to force it with USE HINT('ForceBatchMode')
• When Batch Mode can hurt performance
• Row Mode vs Batch Mode comparison

---

Why This Topic Matters

Batch Mode was originally introduced for columnstore indexes, but starting with SQL Server 2019 (compatibility level 150+), it can also work on rowstore tables.

The performance gains can be significant because:

• Batch Mode processes rows in vectorized batches
• It reduces CPU overhead
• It improves aggregation and join performance

But here’s the key problem:

Even when your query is eligible, SQL Server may still choose Row Mode.

This can happen due to:

• Cardinality estimation decisions
• Memory grant calculations
• Cost model thresholds
• Query shape

If you rely only on automatic selection, you might miss easy performance gains.

---

Practical Examples

Standard Query

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

Sometimes this runs in Row Mode. Always check the execution plan → Actual Execution Mode.

Forcing Batch Mode

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

This hint forces the optimizer to attempt Batch Mode execution.

---

Benchmark

• 10M+ rows
• Aggregation by CustomerID
• Compatibility level 150+

Row Mode

• CPU: 4200 ms
• Elapsed: 2800 ms
• Logical reads: 85,000

Batch Mode (Forced)

• CPU: 1300 ms
• Elapsed: 1100 ms
• Logical reads: 85,000

Same I/O. Less CPU. Faster execution.

---

Reproducible Demo

Create Table

CREATE TABLE dbo.Sales
(
    SalesID INT IDENTITY(1,1),
    CustomerID INT,
    Amount MONEY,
    OrderDate DATETIME
);

Enable Benchmark Metrics

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

Clear Cache (Test Only)

CHECKPOINT;
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;

⚠ Never run this in production.

---

Row Mode vs Batch Mode

Feature	Row Mode	Batch Mode
Processing	Row-by-row	Vectorized batches
CPU Usage	Higher	Lower
Ideal Workload	OLTP	Analytics

---

Conclusions

1. SQL Server doesn’t always choose Batch Mode automatically.
2. You can test with OPTION (USE HINT('ForceBatchMode')).
3. Performance gains usually come from CPU efficiency.
4. Always benchmark before production deployment.

Maximum performance.
Minimal structural changes.
Deep understanding of the engine.

---

Until next time — keep your statistics updated,
keep your execution plans open,
and let SQL Server do the heavy lifting.

See you in Part 6 🚀