SQL Server Sort Operator – Part2

Hi Geeks,

Continuing from my yesterday’s blog post on SQL Server sort operator part1 , let me modify our query little bit as following then see what impact it has on execution plan.

USE [AdventureWorks2012]

SELECT *
FROM Production.ProductInventory
ORDER BY ProductInventory.ProductID

CIScanPlan

Though we have similar query statement that of yesterday and it includes ORDER BY clause as well, still we don’t see any SORT operation in the execution plan. The reason behind this is column we are using in ORDER BY clause i.e. ProductID is having clustered index defined on it. What this means is, returned data does not have to be sorted again as clustered indexes are already sorted. SQL Server query optimizer is smart enough to detect this, and does not have to sort results explicitly.

Couple of basic checks are mentioned below that can be taken into considerations if we have sort operations in execution plans;

  • Are we missing a WHERE clause?
  • Do we really need to SORT result set?
  • Is it possible to have the data pre-sorted by including a clustered index on the column?
  • Can we re-write query stamen(s)?

In case of excessive SORT operations it is recommended that we should try running a SQL Profiler and analyse Sort Warnings generated. SQL Server by design attempt to sort in memory instead disk as this is comparatively faster. In case SQL Server cannot perform sort operation in memory, it starts writing data in tempdb database and whenever this happens, SQL Server generates sort warnings event. Excessive sort warnings either is an indication to increase memory or to tune tempdb database.

I hope we have an understanding on both SORT operations and operator now. I’ll be back tomorrow with a new operator on One operator a day series.

Happy learning!

Regards,

Kanchan

Like us on FaceBookJoin the fastest growing SQL Server group on FaceBookFollow me on TwitterFollow me 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 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. Required fields are marked *