SQL Server 2016 Sort Order Batch Processing Mode

By: Ahmad Yaseen   |   Updated: 2016-12-07   |   Comments (1)   |   Related: > SQL Server 2016

Problem

The default processing mode used in sorting data in SQL Server 2014 and earlier is row processing mode, where the SQL Server engine will process the data row by row, which works for most OLTP workloads without huge tables. But for data warehouse workloads, with very large tables that contain millions or billions of rows, the row processing mode will take forever. Is there any enhancement in the processing mode in SQL Server 2016?

Solution

SQL Server 2016 comes with many new features and enhancements. One of these enhancements in the query optimizer is the batch processing mode for the sort operator, which was processed row by row in previous SQL Server versions.

In this tip, we will go through a demo that shows us the processing mode used in SQL Server 2016 and the one used previously, and the benefits of this enhancement.

We will start by creating the BatchTest table in the MSSQLTipsDemo database with a clustered columnstore index on the FirstName column using a SQL Server 2016 instance as follows:

USE MSSQLTipsDemo 
GO
CREATE TABLE BatchTest (
ID int IDENTITY (1,1) ,
FirstName nvarchar(50) ,
LastName nvarchar(50),
DOB DateTime
)
GO
CREATE CLUSTERED INDEX IXC_BatchTest ON BatchTest (FirstName);  
GO  
CREATE CLUSTERED COLUMNSTORE INDEX IXC_BatchTest ON BatchTest WITH (DROP_EXISTING = ON);  
GO

Row Processing Mode in SQL Server

Once the table has been created, we will change the database compatibility level to 120, which is SQL Server 2014:

USE master 
GO
ALTER DATABASE [MSSQLTipsDemo] SET COMPATIBILITY_LEVEL = 120
GO

Clear the plan cache:

USE [MSSQLTipsDemo]
GO
DBCC DROPCLEANBUFFERS();
GO

And run the below SELECT statement:

SET STATISTICS TIME ON
SELECT * FROM BatchTest WHERE  FirstName like 'Joh%' ORDER BY FirstName asc 
SET STATISTICS TIME OFF

The generated execution plan will show us that a columnstore clustered index scan occurred in addition to the sort operator that is used to achieve the ORDER BY T-SQL statement as follows:

Clicking on the Sort operator, we will find that the processing mode used is row processing mode:

And the time needed to execute the query in row processing mode is 11ms as shown below:

Batch Processing Mode in SQL Server

Now let’s test the same scenario in SQL Server 2016. We will change the database compatibility level to 130:

USE master 
GO
ALTER DATABASE [MSSQLTipsDemo] SET COMPATIBILITY_LEVEL = 130
GO

Clear the plan cache again:

USE [MSSQLTipsDemo]
GO
DBCC DROPCLEANBUFFERS();
GO

And run the same SELECT query:

SET STATISTICS TIME ON
SELECT * FROM BatchTest WHERE  FirstName like 'Joh%' ORDER BY FirstName asc 
SET STATISTICS TIME OFF

The generated execution plan is a little bit different from the previous plan generated with SQL Server 2014 (the Filter operator is not included here):

But if we click on the sort operator in the execution plan, you will find that although the query is running using a serial plan, the default processing mode for the sort operator is the batch processing mode, which process the data in batches rather than processing the data row by row:

And the time required to execute the query in batch mode is about half the time required to process the same data row by row:

SQL Server Trace Flag 9347

Another method that can be used to disable the default batch processing mode for the sort operator in SQL Server 2016 is using Trace Flag 9347, which can be enabled at the server, session and/or query level.

In our demo here, we will disable it at the query level using the QUERYTRACEON option as follows:

USE [MSSQLTipsDemo]
GO
DBCC DROPCLEANBUFFERS();
GO
SET STATISTICS TIME ON
SELECT * FROM BatchTest WHERE  FirstName like 'Joh%' ORDER BY FirstName asc OPTION (QUERYTRACEON 9347)
SET STATISTICS TIME OFF

The generated execution plan from the previous query will show us that the data is sorted row by row as the Batch processing mode is disabled for this query as shown below:

It is clear from the previous results that the Batch processing mode for the sort operator, in which the data will be sorted and processed in batches, is better and faster than processing the data using the row processing mode, in which the data will be sorted row by row. You can test this enhancement by testing the previous demo in your data warehouse and find big performance improvements.

Next Steps

• Read more about How To Implement Batch Processing in SQL Server Integration Services SSIS.
• Check out Enabling SQL Server Trace Flag for a Poor Performing Query Using QUERYTRACEON.

About the author

Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips