In the previous post, we learned about Table variable deferred compilation. In this blog, lets focus on the batch mode on rowstore feature introduced in SQL Server 2019. This feature improves the performance of the analytical queries using the batch mode query processing. This feature is for CPU optimization helping analytical queries to run faster. We do not have to specify this option if the database compatibility is 150.
This feature is especially for the analytical queries for CPU bound analytic workloads without needing the columnstore indexes. We can specifically mention the hints in the query for using the batch mode or not.
Columnstore Indexes are used for the data warehouses and analytical purposes improving the I/O and optimizing the CPU through the batch mode execution query processing. Columstore indexes are not suitable for the OLTP systems as these causes overhead for the deletes and update operations. Using batch mode on row store feature will help solve this problem for the OLTP analytical workloads.
Row mode execution will processes are performed on row by row basis where as the batch mode will process as a batch of 900 rows. This is a huge benefit as this feature will use the CPU efficiently.
First, Let’s see with an example of how disabling the batch mode on rowstore option works by running the below query turning the feature OFF
USE AdventureWorks2016Big GO ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = OFF;
Then run the below query and check the actual execution plan
SELECT ModifiedDate,CarrierTrackingNumber , SUM(OrderQty*UnitPrice) FROM Sales.SalesOrderDetailEnlarged GROUP BY ModifiedDate,CarrierTrackingNumber
When you observe the query plan and hover over the clustered index scan, the actual execution mode is ‘Row’ mode and it has processed 9341409 rows.
Now, enable the Batch mode by using the HINT in the same query and run again. You can also enable the Batch mode on database level option to turn back ON (as we turned OFF for the previous example)
SELECT ModifiedDate,CarrierTrackingNumber , SUM(OrderQty*UnitPrice) FROM Sales.SalesOrderDetailEnlarged GROUP BY ModifiedDate,CarrierTrackingNumber OPTION(USE HINT('ALLOW_BATCH_MODE'))
There are some limitations that will not allow the use of the batch mode processing like OLTP tables, Indexes that doesn’t have B-Tree structures, heaps, LOB columns, XML and Sparse columns. Batch mode on Row store feature will significantly improve the analytical workloads with queries having groupby, aggregations or sorts operations. Queries which will result in one few rows or queries which do not do any aggregation operations will not see much performance gains with this feature.
In this post, we learned about Batch Mode on Row Store feature of SQL Server 2019. This feature will help the analytical workloads and use CPU effectively. If your databases are in 150 compatibility mode, then you are already taking advantage of this new feature.
In the next blog post, we will learn about the next Intelligent Query Processing feature T-SQL Scalar UDF Inlining Feature
Thanks for Reading!