SQL Server 2016 Sort Order Batch Processing Mode


By:   |   Updated: 2016-12-07   |   Comments (1)   |   Related: More > 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:

Row Processing Mode in SQL Server Query Plan

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

Sort Operator Properties with Row Processing Mode in SQL Server

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

Query Stats for Row Processing Mode in SQL Server

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

Execution Plan in Batch Processing Mode in SQL Server

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:

Sort Operator Properties in Batch Processing Mode in SQL Server

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:

Query Stats in Batch Processing Mode in SQL Server

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:

query plan sort properties for SQL Server Trace Flag 9347

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


Last Updated: 2016-12-07


get scripts

next tip button



About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelorís degree in computer engineering as well as .NET development experience.

View all my tips
Related Resources





Comments For This Article




Wednesday, December 07, 2016 - 5:54:15 AM - Koen Verbeeck Back To Top

Interesting, thanks.



download


Recommended Reading

New Drop If Exists Syntax in SQL Server 2016

New CREATE OR ALTER statement in SQL Server 2016 SP1

Automatic Updates for SQL Server Management Studio

SQL Server 2016 Always Encrypted

Advanced Techniques to Transform Relational Data to JSON in SQL Server 2016





get free sql tips
agree to terms


Learn more about SQL Server tools