Batch Mode Processing in SQL Server 2019

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.

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

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.

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.

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:

Allowing batch mode, we see:

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.

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.