The introduction of the Batch Execution Mode on the Rowstore Indexes has caused a significant change in how things will work for the big aggregation queries on the Microsoft Data Platform.

The traditional types of indexes that have existed since the very first SQL Server version, are commonly called Rowstore Indexes because they internally store the values of every column, together, in a row sequence. In the Columnstore Indexes, the values of the same column are stored together, which besides other things, helps greatly to improve the compression and processing times. One of the main reasons for the amazing speed, of the queries running with the Columnstore Indexes, is the Batch Execution Mode which worked exclusively for the queries containing the Columnstore Indexes … until now … Starting with the SQL Server 2019 CTP (Community Technology Preview) 2.0, Microsoft has finally introduced support for the Batch Execution Mode to the queries Rowstore Indexes, and I am confident that the Data Platform world is about to change like never before.

The difference between the Row Execution Mode and the Batch Execution Mode is that the traditional Row Execution Mode processes are performed on a row-by-row basis, essentially through the GetNext() function between different iterators in the execution plans. The Batch Execution Mode is vector-based, processing and grouping the data into batches - between 64 and 912 rows at a time. With the help of SIMD instructions, improvements by 10s and even 100s of times can be achieved when processing big amounts of data (millions and billions of rows).

Since SQL Server 2012, we have had the Batch Execution Mode available for the Columnstore Indexes in Enterprise Edition exclusively. The Standard/Web/Express Editions of the SQL Server became the support for the Columnstore Indexes, and hence the Batch Execution Mode, in the most wonderful Service Pack ever – the Service Pack 1 for the SQL Server 2016.

Microsoft kept pushing the limits by democratizing the Data Platform and, in March of 2018, the Azure SQL Database (starting with the S3 Edition), and together with the Elastic Pools, became available for those running Standard Editions, not just Premium Editions – which is a great deal for those using the Standard Edition of the Azure SQL Database for Development purposes.

There has always been a kind of unspoken promise, of making the Batch Execution Mode available for the traditional Rowstore Indexes, or at least different ways of injecting the Batch Execution into the execution plans. Unfortunately, this has always felt like a bit of a dirty trick, with a lot of optimizations, like Table or Index Scan, not being available at all. It’s clear that, until this time, Microsoft was definitely working with the Row Execution Mode (row by row) only.

Now, with SQL Server 2019 CTP 2.0, there are no hidden tricks – with the help of the internal heuristics, SQL Server & Azure SQL Database Query Optimizer can recognize a situation where Batch Mode execution can be of advantage and will apply it automatically to your query processing – thus improving its performance.

Imagine executing a rather simple query against the 10GB TPC-H database:

SELECT COUNT(DISTINCT [l_shipdate])
FROM dbo.lineitem

 

In the above image you can see that the Index Scan operation is executed on the Rowstore Index (Storage – RowStore) while running in the Batch Execution Mode (Actual Execution Mode). This, together with other execution plan changes that are caused by the heuristics preference for the Batch Execution Mode, causes the query to deliver results in 4.7 seconds on my Azure VM, while the Rowstore Execution Mode needs a little bit over 19 seconds – meaning over 4 times the improvement. 

BATCH_MODE_ON_ROWSTORE:

 

ALTER DATABASE SCOPED CONFIGURATION 

 

    SET BATCH_MODE_ON_ROWSTORE = OFF;

The other option would be a user hint:

OPTION(USE HINT('DISALLOW_BATCH_MODE'));

When applied over a concrete query, the user hint will force the query to be executed under Row Execution Mode. This is a great set of tools that will allow Developers and Database Administrators to execute a total control of the workload’s behavior.

Currently, there are some noticeable limitations to how Batch Mode over the Rowstore Indexes works right now, but I am confident that most of the blatant ones will be fixed before the RTM (Release To Manufacture). Some limitations are still to be expected for the 2019 release, such as the support for the Index Scan operations on the In-Memory Indexes or disabling the Batch Execution Mode if the query uses or reads the LOB data.

I am a huge believer in the Batch Execution Processing, and am anxiously waiting for the final release of the SQL Server 2019 to hear all the amazing stories of customer successes and huge number of workloads simply getting faster.