SQL SERVER – Batch Mode in RowStore – Performance Comparison

Earlier this week, I wrote about SQL SERVER – Simple Example of Batch Mode in RowStore. It was very well received and lots of people started to ask questions after reading the blog post. The most asked question was about the Performance difference between RowMode and BatchMode. Let us see in this blog post the performance comparison between Batch Mode and Row Mode.

Here is the sample script which has run on the very large table of the Stackoverflow database. You can use any other large database or just your own table which is huge. Before you run the following query, please enable the actual execution plan in SQL Server Management Studio as we are going to compare them later on.

USE StackOverflow2013
GO
-- turn on the graphical plan here CTRL+M
SET STATISTICS IO, TIME ON
GO
-- SQL Server 2017
ALTER DATABASE StackOverflow2013 SET COMPATIBILITY_LEVEL = 140
GO
-- RowMode in RowStore
SELECT SUM(CommentCount) TotalComment,
Score
FROM Posts
GROUP BY Score
ORDER BY Score DESC
GO
-- SQL Server 2019
ALTER DATABASE StackOverflow2013 SET COMPATIBILITY_LEVEL = 150
GO
-- BatchMode in Rowstore
SELECT SUM(CommentCount) TotalComment,
Score
FROM Posts
GROUP BY Score
ORDER BY Score DESC
GO

AS we have enabled statistics IO and TIME, we can see the various details in the message window.

Here are the details for the first query which ran for Compatibility Level 140 (SQL Server 2017) and had Row Execution Mode in the execution plan.

Table ‘Worktable’. Scan count 0, logical reads 0
Table ‘Workfile’. Scan count 0, logical reads 0
Table ‘Posts’. Scan count 9, logical reads 4189540

SQL Server Execution Times:
CPU time = 24312 ms, elapsed time = 20758 ms.

Here are the details for the second query which ran for Compatibility Level 150 (SQL Server 2010) and had Batch Execution Mode in the execution plan.

Table ‘Posts’. Scan count 9, logical reads 4189540
Table ‘Worktable’. Scan count 0, logical reads 0

SQL Server Execution Times:
CPU time = 19512 ms, elapsed time = 16948 ms.

On my machine, there was a clear difference of 4 seconds of difference in performance for both the queries. It was very clear that BatchMode was very efficient compared to RowMode.

Please note that the result may vary on your system as the query depends on how your system is configured.

Let us see the execution plan for the query.

It is very clear from the execution plan that both the query takes exactly the same cost in the execution plan 50%-50%, however in reality Batch Mode Execution on RowStore was very efficient.

Reference: Pinal Dave (https://blog.sqlauthority.com)