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)
1 Comment. Leave new
I see that the only difference in the 2 queries was the compatibility level. However, it is my understanding that the plan optimizer will only consider BatchMode if a Columnstore Index exists on the table. Did such an index exist and if so can you provide the details on the index? TIA