SQL Server 2019 New Features: Batch Mode on rowstore
Author: Anup Gopinathan | 5 min read | August 29, 2019
SQL Server 2019 public review CTP 2.0 brings us some exciting query performance improvements like the Batch mode on rowstore. This advanced capability of the Intelligent Query Processing was introduced in SQL Server 2012 as a part of columnstore indexes feature.
In SQL Server 2019 Batch mode does not require a columnstore index as in the previous versions. Columnstore was not a practical option for many databases as it could be using some of the features that are not supported by columnstore (like triggers, CDC change data capture, replication to name a few).
The Good
Batch mode is helpful with analytic-type queries with a lot of aggregation, sorts and group by operations. CPU bound queries also see improvements. It is also helpful where you cannot use columnstore indexes. Batch mode performs scans and calculations using batches of up to 900 rows, depending on the number and size of columns. To enable the batch mode processing all you need to do is to set the database compatibility to 150.
I am using my own database credit for the demonstrations, I restored it to SQL Server 2019 CTP instance and to SQL Server 2017 version. Let’s run the below query on both instances:
ALTER DATABASE [credit] SET COMPATIBILITY_LEVEL = 150 --set the database to level 150 on SQL Server 2019 CTP instance
Set statistics IO,TIME on
--Enable Actual execution plan
--Run the query on both instances
SELECT
[member_no]
,SUM([charge_amt]) AS [Amount]
,SUM([charge_amt] *0.08) AS [Tax]
,SUM([charge_amt]) + SUM([charge_amt] *0.08) AS [Total]
FROM [credit].[dbo].[charge_1]
where charge_dt > = '2017-07-01 00:00:00.000'
group by [member_no]
SQL Server 2019 vs SQL Server 2017 (Batch mode vs Row mode)
Execution plan SQL Server 2019
Execution plan SQL Server 2017
A change in execution plan can be seen. Also, did you notice the additional details of the execution plan in 2019? I used SSMS v18.0 Preview 6.
Statistics IO/Time
As for the performance, you will see that there is a fair improvement in regard to elapsed time but a huge gain in CPU Time. A note from books online
“Batch mode on rowstore can only help by reducing CPU consumption. If your bottleneck is IO-related, and data is not already cached (“cold” cache), batch mode on rowstore will NOT improve elapsed time. Similarly, if there is not enough memory on the machine to cache all data, a performance improvement is unlikely.”
A quick comparison using http://statisticsparser.com/
SQL Server 2019
SQL Server 2017
Clustered Index scan properties
SQL Server 2019
SQL Server 2017
You see the properties on the left show Actual execution mode as Batch and the number of batches 4820 whereas the image on the right has Row mode execution and 0 batches. The query did perform better with batch mode on my laptop without any changes to the query.
The Not so Good
Batch mode did work great for most of the tests I did and that too without having to change the query at all. But, some queries did not perform well with batch mode. For example, the below query. This table mostly has distinct rows hence there is no aggregation performed, in such cases the row mode performed better.
After multiple runs on both versions, I found the query on SQL Server 2017 had better CPU time and elapsed time.
Select member_no
,SUM([payment_amt]) [payment_amt]
,SUM([payment_principle]) [payment_principle]
,SUM([payment_latechg]) [payment_latechg]
,SUM([payment_amt]) + SUM([payment_latechg])
FROM [credit].[dbo].[payments]
where member_no != 230253 and payment_dt > '2018-07-31 00:00:00.000'
GROUP BY [member_no]
ORDER BY [member_no]
SQL Server 2019
SQL Server 2017
Configuring Batch mode
Batch mode is set as default in databases with compatibility level 150, so in situations where there are such queries (I am sure there would be other scenarios too) in databases that can affect the performance adversely. I am not sure whether having batch mode as default is a good idea, however we have few ways to control them.
One way to control them is to use hints “ALLOW_BATCH_MODE” and “‘DISALLOW_BATCH_MODE”. Using the hint on the above query.
Select member_no
,SUM([payment_amt]) [payment_amt]
,SUM([payment_principle]) [payment_principle]
,SUM([payment_latechg]) [payment_latechg]
,SUM([payment_amt]) + SUM([payment_latechg])
FROM [credit].[dbo].[payments]
where member_no != 230253 and payment_dt > '2018-07-31 00:00:00.000'
GROUP BY [member_no]
ORDER BY [member_no]
OPTION(RECOMPILE, USE HINT('DISALLOW_BATCH_MODE'));
The execution plan generated is exactly same as the one you will see with a row mode (SQL Server 2017)
Secondly, batch mode can be controlled by disabling/enabling using the database scoped configuration
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = OFF; --Disable
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = ON; --Enable
While hint can be used at query level, remember that SQL Server can choose to ignore the hints and use batch when it deems it is beneficial to use it. Whereas database scope configuration work on entire database level. Can you try using both in conjunction?
Final Thoughts
Batch mode does help a great deal with aggregating queries, CPU workloads and improve performance. The improvement is instantaneous, as soon as the database compatibility level is set to 150. It can be useful in scenarios where setting up column store is not an option, for example, you have replication which does not support columnstore.
Batch mode has limitations and will not function for:
- In memory OLTP tables,
- or for any index other than on-disk heaps and B-trees
- Query with LOB columns including XML columns and sparse column sets
Batch mode on rowstore can cause your existing plan to change. Like many of the SQL features it has its pros and cons which should be tested.
