Select Page

SQL Server 2019 New Features: Batch Mode on rowstore

Anup Gopinathan | | 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 2019

Execution plan SQL Server 2017

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 2019

SQL Server 2017

SQL Server 2017

Clustered Index scan properties

 
SQL Server 2019

SQL Server 2019

SQL Server 2017

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 2019

SQL Server 2019

SQL Server 2017

SQL Server 2017

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.

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017
code

Recover a Table from an RMAN Backup in an Oracle 12c

This blog post will is to show a table restore for one table in a container database

Megan Elphingstone | February 2, 2017
Oracle DBA Skills

8 Things Every Beginner Oracle DBA Should Know

A checklist of eight critical skills and areas an entry-level Oracle DBA should be familiar with to succeed on the job.

Patrick Gates | November 30, 2016

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS