Hi Friends,
When we add some basic clauses to our queries, different operators are being displayed in the SQL Server execution plan. Today we will have a look when we introduce an ORDER BY clause in SELECT statements, how execution plan behaves. EXECUTION Plan with ORDER BY Let me write a query then analyze the output;
Here Clustered Index Scan operator output into Sort operator. Sort operator show up in execution plan when query optimizer is sorting the data. When we look at the ToolTip text for Sort operation, very clearly it advise us that query output is sorted in ascending order which is default behavior.
When mouse pointer is moved to Sort Arrow (see below), it shows up sort operator processed 504 rows i.e. these rows are taken from Clustered Index Scan operator then Sort operator sorts them in ASC order and then passes them in sorted order. As can be seen here, Sort operator took 59% of the query cost as there is no index on this column. It is always better to revisit the query cost if they exceed a considerable number, arguably above 50. In such scenarios, we may think on tuning or queries by introducing appropriate checks in WHERE clause and so on. This may not hold good in all cases as again your conditions may result in more records to be sorted so re-writing queries by limiting number of rows could be an option but I leave this choice on situations and may not be a generic approach. We can always think on some basic points if there are more sorting and few of them could be;
- If datasets can be presorted like clustered indexes or creating clustered indexes on appropriate columns?
- If there are more sorting in execution plans, think on how queries can be re-written to reduce them?
- Necessity of sorted operations i.e. if they are needed at all?
Let us change our query a bit and find out how execution plan looks like when you sort using clustered index;
This query is almost similar to the query we used earlier, only thing that has changed here is the column using which sorting is done i.e. this time we sorted our query results using ProductID which is the clustered as such no additional sorting required. If requirement is to sort large amount of data, it is always good to run a profiler trace and consider if any Sort warnings are generated based on that you can take a call how you want to optimize memory and tempdb as primarily memory is used for sorting operations and if data volume is considerable, SQL may start using tempdb. Stay tuned for next post on this series. In between, you can read my earlier posts Part 1, Part 2, Part 3, Part 4, Part 5 on this series for better understanding.
Regards
Kanchan Bhattacharyya
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
