}

Batch Mode Processing for Row Data in SQL Server 2019

3/5/2019

[:en]

Batch mode processing was introduced in SQL Server in 2012, the same time as columnstore indexes. The combination of the two prevented SQL Server from needlessly processing column values not needed by a query, as it must do in classic row processing. This processing mode also permits SQL Server to take better advantage of cache memory on modern multi-core chips with potentially enormous performance gains. Though it was possible in special circumstances to trick the query engine into using batch processing on row data, generally batch-processing was designed for columnstore data only.

One of the many new SQL Server 2019 features directly relating to query performance is the ability to use batch processing mode on row data as opposed to columnstore data. As is often the case, however, the practical benefit of this new ability will depend upon how clever the query optimizer is at knowing when to use it, and when traditional row processing will prove superior.

This new capability does not require any special settings or configurations. You just write queries as usual. As you know, query plans can get very complicated very quickly so let's strip away as much complexity as we can and focus on some extremely simple queries.



Comparison of Batch and Row Processing for Some Simple Queries

These simple queries were chosen because they do not require the processing of many columns within the row data, and are therefore good candidates for batch processing. We can change the database compatibility level from 140 to 150 to compare performance with and without the option of batch processing

USE [ContosoRetailDW]

go

SET STATISTICS TIME ON

GO

alter database ContosoRetailDW set compatibility_level = 140;

go

select count(*) from dbo.FactOnlineSales;

select count(ReturnAmount) from dbo.FactOnlineSales;-- ReturnAmount is nullable

select count(DateKey) from dbo.FactOnlineSales; -- DateKey is not nullable

go

alter database ContosoRetailDW set compatibility_level = 150;

go

select count(*) from dbo.FactOnlineSales;

select count(ReturnAmount) from dbo.FactOnlineSales;-- ReturnAmount is nullable

select count(DateKey) from dbo.FactOnlineSales; -- DateKey is not nullable

go



Compatibility Level 140 (Row Mode)

SQL Server Execution Times:

CPU time = 3140 ms, elapsed time = 2156 ms.

SQL Server Execution Times:

CPU time = 5423 ms, elapsed time = 3527 ms.

SQL Server Execution Times:

CPU time = 2953 ms, elapsed time = 2069 ms.



Compatibility Level 150 (Batch Mode)

SQL Server Execution Times:

CPU time = 782 ms, elapsed time = 517 ms.

SQL Server Execution Times:

CPU time = 1656 ms, elapsed time = 1032 ms.

SQL Server Execution Times:

CPU time = 828 ms, elapsed time = 660 ms.

clustered index scan

The use of batch mode processing in compatibility level 150 can be easily confirmed by examining the execution plan. The execution times would suggest that batch mode processing is better but of course nothing involving plan optimization is ever simple. SQL Server 2019 provides batch procesing as an option; for any particular query the optimizer must still decide that batch mode processing is the better choice. At present, this decision is made on the basis of heuristics, which is a fancy word for educated guessing.



Information on the Optimizer's Decisions

Microsoft has added a couple of extended events permitting the curious administrator to peek into the batch mode decision-making. These events are not yet available for selection in the graphical interface, but it is easy to create an event session in code:

CREATE EVENT SESSION [BatchModeTest] ON SERVER

ADD EVENT sqlserver.batch_mode_heuristics,

ADD EVENT sqlserver.batch_mode_scan_on_rowstores

WITH (STARTUP_STATE=OFF)

Perhaps the most interesting aspect of these events is the hint that SQL Server may, in the future, provide greater information about the inner workings of the query optimizer via extended events.



Conclusion

For some queries, batch mode processing provided by SQL Server 2019 can provide significant performance improvements. Part of this gain is accounted for by the match between the workings of batch mode and the cache memory architecture of modern-day multicore chips.



Related Training:

SQL Server Training

[:]

Dan Buskirk

Written by Dan Buskirk

The pleasures of the table belong to all ages.” Actually, Brillat-Savaron was talking about the dinner table, but the quote applies equally well to Dan’s other big interest, tables of data. Dan has worked with Microsoft Excel since the Dark Ages and has utilized SQL Server since Windows NT first became available to developers as a beta (it was 32 bits! wow!). Since then, Dan has helped corporations and government agencies gather, store, and analyze data and has also taught and mentored their teams using the Microsoft Business Intelligence Stack to impose order on chaos. Dan has taught Learning Tree in Learning Tree’s SQL Server & Microsoft Office curriculums for over 14 years. In addition to his professional data and analysis work, Dan is a proponent of functional programming techniques in general, especially Microsoft’s new .NET functional language F#. Dan enjoys speaking at .NET and F# user’s groups on these topics.

Chat With Us