SQL Server 2019 Intelligent Query Processing: Batch Mode on Row Store

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.

Summary:

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!

2 thoughts on “SQL Server 2019 Intelligent Query Processing: Batch Mode on Row Store

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s