SQL Server 2016 – Sort Operator with Batch Mode Processing

Hello Friends,

There are lots of features and improvements announced by Microsoft in SQL Server 2016, one of them is providing the sort operator with batch mode processing. In the programming world, we know that  performing row by row processing take longer time as compared to perform the same processing in batch mode. If the number of rows to be processed are not too many then everything will be running fine like in OLTP environments. Now you should think about the Datawarehouse kind of environments where queries processed millions or billions of rows. In such kind of environments, row by row processing will take longer time to process the rows. In today’s blog post, I’ll show you the performance difference between row mode processing versus batch mode processing on Sort Operator.

Environment details: Here I am using SQL Server 2016 with CU-2. I have created a new database and table. Here the table is partitioned, and I have created a Clustered ColumnStore Index on that. First we will check how many partitioned are there and how many rows are there in each partition:

USE PartitionTruncate
GO
--You can check here about how many partitions with number of rows belongs to the table
select * from sys.partitions where object_id=OBJECT_ID('xtPartitionTruncate')
GO

On above output, we can see that all partitions are compressed with the default compression of column store structure. Now let me execute a query that will perform a sort operation.

DBCC DROPCLEANBUFFERS();
GO
Select id,balance,name from xtPartitionTruncate (NOLOCK) 
WHERE ID<6600000 ORDER BY name
GO

From the above output, we can check that time taken is 38 seconds. Let me show you the mode of the sort operator from the properties of sort operator (just bring the mouse over the sort operator).

Now, you can see that Operator execution mode is Batch. While batch size is 6599999/7334= ~900 rows per batch. Now, we will check the performance with row mode execution. Here, by default SQL Server is using batch mode execution, so I’ll try to change the default behavior using trace flag 9347. This trace Flag will disable the batch mode sort processing. Keep in mind that this trace will work if you are using SQL Server 2016 with CU-1.

DBCC DROPCLEANBUFFERS();
GO
DBCC TRACEON(9347,1)
GO
Select id,balance,name from xtPartitionTruncate (NOLOCK) 
WHERE ID<6600000 ORDER BY name
GO
DBCC TRACEOFF(9347,1)
GO

From the above output, we can check that time taken is 1 minute and 3 seconds. Let me show you the mode of the sort operator from the properties of sort operator (just bring the mouse over the sort operator).

You can easily compare both the executions, Row execution mode versus Batch execution mode for sort operator. Keep in mind that the ColumnStore Index scan is still executing under batch mode.

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook