SQL Server Sort Operator – Part1

Hi Friends,

When we add some basic clauses to a query statement, different operators start appearing on the execution plan and today we are going to discuss SQL Server Sort operator which is one of them.

Let us run a simple SELECT statement with an ORDER BY clause.

USE [AdventureWorks2012]

SELECT *
FROM Production.ProductInventory
ORDER BY ProductInventory.LocationID

SortQueryPlan

We see that, clustered index scan outputs into Sort operator and it is used to show that query optimizer is sorting data in the execution plan. If we do not specify any sort order, default is always ascending which can be seen on the ToolTip.

SortToolTip

   

In our example, clustered index scan has passed 1069 rows to sort operator which are then sorted in order by sort operator. These rows are then returned in appropriate order (see image(s) below and above).

CIScanToolTip

Point to be noted here is sort operator took 76% of cost of the query which is no good and we have seen that sorting is done within query execution. In short, clustered index scan outputs the data into sort operator and this can be very expensive at different times. Arguably, if cost is above 45% to 50% then it is recommended to have a look at the query statements.

We are going to discuss more on this in tomorrow’s blog post, watch out this space for more.

Happy learning!

Regards,

Kanchan

Like us on FaceBookJoin the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook

   

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published.