SQL Server 2019 and the Batch Mode on Rowstore

Hello everyone!

Welcome back guys,

Are you ready to continue the roundup of the new features introduced by SQL Server 2019?

Yes, because if you have read the previous articles by now you will know that there are many.
 

As usual, we will focus on the novelties that allow us to improve performance and therefore also the novelty we will see allow us to achieve greater performance!

Let's talk about Batch Mode on RowStore.

But first we need to take a step back to see what a Batch is!

What is a batch?

Simply stated, a batch is an internal 64 KB wide storage structure that contains a group of rows ranging from 64 to 900 depending on the number of columns of which they are composed.

Each column used by the query is stored in a continuous column vector of fixed-size elements, where the qualifying row vector indicates which rows are still logically part of the batch:

 

How it works? 

 

When batch mode is used for row store data, the rows of data are scanned and loaded into a batch.

For instance, when a batch mode filter operation needs to qualify rows that meet a given column filter criteria, all that is needed is to scan the vector that contains the filtered column and mark the row appropriately in the qualifying rows vector, based on whether or not the column value meets the filter criteria.

What are the benefits of using batches?

Well the advantages are several.
SQL Server in fact evaluates the metadata only once for the whole batch instead of doing it for each row.
In this way the CPU usage is reduced and the processor cache is exploited in a better way.

This technique can improve the performance of our query up to 10 times.

Let's say batches were introduced as early as SQL Server 2012.
However, they were added only to support tables and queries where columnstore indexes were present and for this reason the feature was called Batch Mode on ColumnStore.
Today with SQL Server 2019 the same feature is also made available for tables in which the processing takes place by row and is therefore called Batch Mode on RowStore.

 

But stop chatting, let's try this new feature right away!
Are you ready?

 

Batch Mode on Rowstore

Let's first create a table that we call movements:

Create Table moviments 
(id int identity (1,1), 
year int, Qty float, 
Price float, 
Classificators VarChar(10)
)

Then we fill the table with a large number of rows by performing many inserts as below:

Insert into movimenti (year, Qty , Price , Classificators) values (1980, 1,1.5, 'A')

Then we set the compatibility level to 140 (SQL Server 2017) and execute the following Query:

       
SELECT Classificators ,
       SUM(Qty ),
       SUM(Price )
FROM moviments 
GROUP BY Classificators 
ORDER BY Classificators 

Now let's analyze the results:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 6 ms.

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

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

(1 row affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'moviments'. Scan count 1, logical reads 690, physical reads 0, page server reads 0, read-ahead reads 690, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 125 ms,  elapsed time = 145 ms.

Completion time: 2021-10-08T14:52:58.7186017+02:00

With a table filled with 140.000 of rows we have a CPU time of 125 ms and an elapsed time of 145 ms.

From the execution plan we can see that all operators are in row mode:

Now let's set SQL Server 2019 native compatibility level 150 and redo our tests!

 

Nothing happens! Actual Exection remain in Row!

 

But why?

I running my test into a virtual machine where i installed the Express Version of SQL Server 2019

I guess it depends on the edition and actually on the microsoft site I read that the Enterprise version is needed! oh no! 

I don't have an Enterprise edition on hand but I have a developer edition and so I try it.

 

And it works!

 

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row affected)
Table 'moviments'. Scan count 1, logical reads 690, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 71 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

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

Completion time: 2021-10-08T14:54:38.9217244+02:00

      

And this is the execution plan with the Execution Mode Batch.

Some results:

• Execution time reduces from 145 to 71 ms.
• CPU Time reduces from 125 to 31 ms

We can see how the CPU time drops:

Remember that SQL Server is not required to use this new batch mode.
Indeed, its use is always a cost-based decision.
For example, if the table has fewer than 131071 rows (a magic number) the batch mode on the Rowstore is not used.
Simply the Optimizer has its own (complicated) heuristics.

It would be very nice to see in detail in which cases the batch mode is activated but this is not today's goal. We will see it perhaps in a future post.

 

As general rules:

• In the Query at least one table must have not less than 131072 rows
• Should be present at least an efficient batch operator (join, aggregate or window aggregate)
• At least one of the batch operators should have in input no fewer than 131072 rows.

 

That's all for today! ...and have an happy weekend!

 

Follow me on this blog and also on linkedin Luca Biondi because in the next post we will see and try all the other new functions that SQL 2019 is equipped with.

 

Luca Biondi @ SQLServerPerformance blog!

Next post:

Previous post: Readers' mail: concatenate text from multiple rows ... exceptions and limitations