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
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 FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook
