}

Batch Mode Processing in SQL Server 2019

10/12/2021

Early in 2019, when SQL Server 2019 was a community preview and the release was many many months away, we were delighted to see the performance benefits of batch mode processing for some row data queries (that is, no columnstore index). Now that SQL Server 2019 is being installed in production facilities, and batch mode can be used whenever the query optimizer decides, we need to take a closer look.

When Microsoft introduced batch mode processing in 2012, it was designed to work with columnstore indexes. Two distinct technologies worked together to enhance the performance of so-called "analytical" queries, which usually means queries involving gropuing and aggregation over a large number of rows. Columnstore indexes reduced IO; batch processing reduced CPU usage by feeding data to the CPU in a way that makes better use of CPU cache memory.

In SQL Server 2019, batch mode processing can improve the performance of analytical queries on tables that do not have a columnstore index. However, without a columnstore index, we should not expect to see any improvement in IO. Batch mode processing renders CPU usage more efficient. That's what it does. Period. Nothing else. Queries that run slow because of large IO demands will not see any performance improvement in batch mode and the optimizer may likely reject that alternative and stick with row processing.

Let's Take a Look


SELECT d.CalendarMonth

,SUM(fs.SalesAmount)

FROM DimDate d

JOIN FactSales fs

ON fs.DateKey = d.DateKey

GROUP BY d.CalendarMonth

This query has to read a lot of rows, but also must group and sum as it goes. Let's take look at the execution plan.

execution plan 01

Checking the popup window for the first icon on the right we confirm that the query engine has opted for batch mode.

popup window

If we right-click on the first icon on the right and select "Properties" (or just hit F4) we see that the data were divided into 3785 batches split between two threads. Since there are 3406089 rows in this particular table and a single batch cannot exceed 900 rows, 3785 is what we expect to see. The same query can be executed on the Contoso FactOnlineSales table, which has almost 13 million rows. This would require 14032 batches.

right-click on the first icon

Adaptive Join


It's worth pointing out that this particular query plan included another relatively new feature, an adaptive join. It is worth noting that in SQL Server 2019, we not only see Estimated and Actual Join Type in the list, but Estimated and Actual Execution Mode as well.

Estimated and Actual Execution Mod

But Does It Work?


We could, in fact, evaluate the effectiveness of batch mode in this particular case by running the query in 2017 compatibility mode (i.e. 140). But this is a bad idea in general, since the query may require 2019 features other than batch mode. When can temporarily disable batch mode with a 'USE HINT' query hint.

SELECT d.CalendarMonth

,SUM(fs.SalesAmount)

FROM DimDate d

JOIN FactSales fs

ON fs.DateKey = d.DateKey

GROUP BY d.CalendarMonth

OPTION(RECOMPILE, USE HINT('DISALLOW_BATCH_MODE'));

When we run the query with this hint, we obtain the following values for CPU time and Execution Time:

CPU time

Allowing batch mode, we see:

batch mode

The total elapsed time for this particular analytic query was less that a third of what it was in conventional row mode.

Conclusion


Batch mode processing improves query processing by enhancing the effectiveness of the CPU; it does not help queries that are limited primarily by IO. If we wish, we can experiment by disallowing batch mode using a query hint.

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