Batch execution mode is a new optimization feature in SQL Server. In this article, we'll explore how batch execution mode works and how you can use it to get faster query results on rowstore data. We'll go over the restrictions and the prerequisites for enabling batch execution mode and understand it with an example.

Prerequisites for Enabling Batch Execution Mode

There are a few prerequisites before you can use batch mode. They are:

• Compatibility Level - Batch execution mode requires a compatibility level of at least 150 to be enabled. Check your database compatibility level before enabling this feature.
• Hardware - Batch execution mode requires a certain amount of memory and CPU to be enabled. Make sure that your hardware is up to the task before enabling the feature.
• Rowstore Table - Batch execution mode only works on rowstore tables. Check to make sure your query is applicable to rowstore tables.

Enable Batch Execution Mode

First, you must check a few things.

• Check that your compatibility level is at least 150.
• Make sure your query is written for rowstore tables only.
• Ensure that your hardware can handle the additional memory and CPU utilization.
• You can use below query to enable batch mode on rowstore

Once this is done, you can run this DDL to enable batch mode:

ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = ON;

Use this code disable batch mode on rowstore:

ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = OFF;

You can also disable batch mode on rowstore for a specific query by using the DISALLOW_BATCH_MODE query hint.

The Benefits of Batch Execution Mode

There are a few benefits to using batch mode:

• Optimized Memory Usage - Batch execution mode reduces memory usage and lowers the required memory grants for queries. This makes larger queries faster and more efficient.
• Improved Query Performance - Batch execution mode processes multiple rows at once, leading to improved query performance. This means faster and more accurate results with less time spent waiting for queries to finish.
• Better CPU Utilization - Batch execution mode efficiently utilizes the CPU, which can decrease the overall CPU time per query by up to 50% in certain cases.

We will see this with example. We will run following query on SQL server with database compatibility set to 140, all the caches are cleaned before running the query. We will note down the execution mode and CPU time.

SELECT  [SalesOrderID]
      ,sum ([OrderQty]*[ProductID]) as sale
      ,[ModifiedDate]
  FROM [Sales].[SalesOrderDetail]

Here database compatibility level is 140 and actual execution mode is Row.  as we have enabled statistics lets see the CPU time.

Here you can see the CPU time is 6933ms, and elapsed time is  13762ms. Now lets run the same query by setting the compatibility level to 160 and for fair comparisons lets reset the caches.

Now you can see the changes in execution plan and actual execution mode is changed to Batch mode. Let's see the CPU time,

You can see the CPU time as 2941ms and elapsed time 11954 , even though not much of effect on elapsed time but CPU time is drastically reduced to 1/3rd showing improvement.

Limitations and Restrictions

There are a few limitations on using batch mode.

• Batch Execution Mode Complexity - Only supported on simple queries that do not require index intersection, hash join, or many subqueries.
• Data Types -Batch execution mode only supports certain data types and conversions
• Resources - Batch execution mode may require additional resources such as memory and CPU, so make sure your system is up to the task before enabling it.

It's important to understand the limitations and restrictions of batch execution mode before enabling it. These restrictions don't apply to all queries, but it's best to test the feature thoroughly before implementing it in a production environment.

Best Practices for Optimizing Batch Execution Mode

Utilize Columnstore Indexes - Columnstore indexes work particularly well with batch execution mode due to their ability to process large amounts of data quickly.

Minimize Data Conversion - Since batch execution mode is designed to work with specific data types, it's best to minimize the number of data conversions in your query to increase performance and reduce resource usage.

Use Proper Query Design - The best use of batch execution mode occurs in queries without many joins or subqueries. Be sure your queries are optimized for performance before enabling batch execution mode.

Conclusion

Batch execution mode can be a game-changer in terms of query performance when used with rowstore data. By following best practices and considerations outlined in this document, you can ensure that your queries are maximizing the potential benefits of batch execution mode. Test thoroughly and enable with confidence, knowing that you're on your way to lightning-fast query results