2/28/2023
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 grouping 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.
Batch Mode Processing FAQs
What is batch mode processing in SQL Server 2019?
Batch mode processing is a technology introduced by Microsoft in SQL Server 2012 to enhance the performance of analytical queries, which usually involve grouping and aggregation over a large number of rows.
How does batch mode processing work with columnstore indexes?
Batch mode processing was originally designed to work with columnstore indexes, which reduce IO, and batch processing reduces CPU usage by feeding data to the CPU in a way that makes better use of CPU cache memory.
Can batch mode processing improve query performance on tables without a columnstore index?
Yes, 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.
What types of queries does batch mode processing improve the performance of?
Batch mode processing improves the performance of "analytical" queries, which typically involve grouping and aggregation over a large number of rows.
How can we check if batch mode processing is being used for a query?
We can check if batch mode processing is being used for a query by checking the properties of the first icon on the right in the execution plan.
Can we disable batch mode processing for a query if desired?
Yes, we can temporarily disable batch mode processing for a query by using a 'USE HINT' query hint. However, it's generally not recommended to do so since the query may require other SQL Server 2019 features besides batch mode.
How effective is batch mode processing in improving query performance?
The effectiveness of batch mode processing in improving query performance depends on the query itself. Queries that are limited primarily by IO demands may not see any performance improvement in batch mode processing. However, allowing batch mode processing can significantly improve the performance of analytical queries that involve grouping and aggregation over a large number of rows, as demonstrated in the example in the blog.
How can I learn more about SQL Server 2019 to advance my skills?
We recommend attending Learning Tree course 2107, Microsoft SQL Server Training Course, to learn how to use the various administrative and development tools within SQL Server, including the Microsoft BI (Business Intelligence) stack of Integration Services, Analysis Services, and Reporting Services. This is SQL Server training for beginners, so no prior experience is needed to take this course. You will learn the basics of SQL Server and gain the introductory skills needed to maximize SQL Server’s benefits and progress onto intermediate and advanced training in SQL.
This piece was originally posted in 2021 and has been reposted with an updated FAQ and formatting.
