What is Batch Mode on Rowstore in SQL Server?

Under compatibility level 150, in both SQL Server 2019 and Azure SQL Database, you now can use batch mode for CPU-bound analytic type workloads without requiring columnstore indexes. There is no action needed to turn on batch mode aside from being on the proper compatibility mode. You also have the ability to enable it as a database scoped configuration option (as shown below), and you can hint individual queries to either use or not use batch mode (also shown below). If you recall in my earlier blogs on columnstore, it is batch mode in conjunction with page compression that drastically increases query performance. This feature, Batch Mode on Rowstore, allows all operators enabled for batch mode to operate as such in SQL Server.

What does this mean? It means that query operations can process data faster, more efficiently and mimic what makes columnstore so fast. Instead of reading row by row (row store mode) it can read in chunks i.e. batches of 900 rows at a time. The performance impact of this can be tremendous which effectively uses CPU more efficiently.

Just like columnstore this only benefits analytic type workloads or data warehouses, as mentioned above. This is meant for aggregations and joins that process thousands of rows. It will not benefit you when processing singleton lookups. If where clause that does not look up a range of values and is just satisfying predicates, then batch mode does not provide a benefit.

How does the engine know when to use batch mode? According to docs.microsoft.com  the query processor uses heuristics and will make decision based on three checks. An initial check on tables sizes, operators used and cardinality estimates. Then the optimize checks to see if there are cheaper plans it can use. If no alternative better plans are available, the optimizer will choose batch mode. There are some limitations that will prevent the use of batch mode such as, in-memory OLTP tables or for any index other than B-Trees or on-disk heaps. It will also not work for LOB columns including sparse and XML columns.

You can easily decipher when batch mode is used to run query inside the operator’s properties. Let’s see a demo.

To demo I want to first show you a plan NOT using Batch Mode on Row Store, so let’s turn the feature off because as I mentioned earlier it is already enabled for compatibility mode 150 by default. Run the below database scope configuration script to turn it off.

USE AdventureworksDW2016CTP3
GO
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = OFF;

Now let’s run this query and make sure we capture the execution plan.

SELECT FS.[ProductKey],
       AVG([SalesAmount]),
       [UnitPrice]
FROM [dbo].[FactResellerSalesXL] FS
    JOIN dbo.DimProduct DP
        ON DP.ProductKey = FS.ProductKey
GROUP BY FS.[ProductKey],
            [UnitPrice]

Note the Table Scan. By hovering over it you can see the operator’s properties and see the Actual Execution Mode says ROW and it processed 11,669,653 rows.

Now let’s run it again in Batch. Instead of changing compatibility lets just turn on the feature with an OPTION HINT.

SELECT FS.[ProductKey],
       AVG([SalesAmount]),
       [UnitPrice]
FROM [dbo].[FactResellerSalesXL] FS
    JOIN dbo.DimProduct DP
        ON DP.ProductKey = FS.ProductKey
GROUP BY FS.[ProductKey],
            [UnitPrice]
OPTION(RECOMPILE, USE HINT('ALLOW_BATCH_MODE'));

You can clearly see the optimizer chose to use BATCH mode based on our HINT. In addition, you can see it ran significantly faster at only 405 ms versus 1.119s using row mode. In general, we’ve seen queries that benefit from batch mode running in almost half of what row mode performance is and columnstore in batch mode performance.

Let’s go ahead and change back to the default Batch Mode again for our database just to prove it would have used batch mode without the use of our hint. Run the below and look at the plan.

ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = ON;
GO
SELECT FS.[ProductKey],
       AVG([SalesAmount]),
       [UnitPrice]
FROM [dbo].[FactResellerSalesXL] FS
    JOIN dbo.DimProduct DP
        ON DP.ProductKey = FS.ProductKey
GROUP BY FS.[ProductKey],
            [UnitPrice]

BINGO! There you have it!

If you are already using compatibility mode 150 you are already taking advantage of this feature and may not even realized. For those that have not made the leap to 2019 I highly recommend it, if only for this little gem which is one of the many reasons why you should upgrade.