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

Sort Operator with Batch Mode Processing

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

Sort Operator with Batch Mode Processing

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).

Sort Operator with Batch Mode Processing

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

Sort Operator with Batch Mode Processing

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).

Sort Operator with Batch Mode Processing

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

One Comment on “SQL Server 2016 – Sort Operator with Batch Mode Processing”

Leave a Reply

Your email address will not be published. Required fields are marked *