26 performance tuning questions and solutions, A block-sorting lossless data compression algorithm, Are "bad" statistics the reason my query is slow?, Avoiding Sort task in Execution Plan, Bad performance of SQL query due to ORDER BY clause, Blocked sort-based indexing, but the execution plan is still showing a distinct sort, Case in order clause causes bad page queries, Collapse, Estimated Execution Plan SQL Server Sort?, Execution plan - Sort Operator, How to tune SQL queries, How to understand SQL Server 2008 Execution Plan *sort*?, Indexing for Sort Performance, Interview questions for SQL Server Performance Tuning, Looking for SQL Optimization Interview Questions, performance sql server, Performance tips for faster SQL queries, Performance Tuning, Performance Tuning for SQL Server, Performance Tuning the Whole Query Plan, Query Optimization, Query Performance Tuning, Showplan Operator, Showplan Operator of the Week - SORT, ShowPlan Operator of the Week - Split, Sort, Sort - Is it Really a Blocking Iterator?, Sort operation in execution plan, Sort Showplan Operator, sorting - Blocking sort operators, SQL Complex Queries, SQL Optimization Interview Questions, sql performance, sql performance and tuning, sql performance explained pdf, sql performance tips, SQL Performance Tuning, sql performance tuning and optimization, sql performance tuning interview questions, sql performance tuning tips, SQL Query Optimizer, SQL Query Tuning or Query Optimization, sql server - Optimizing SQL queries by removing Sort, sql server - Why is there a sort showing up in my execution, SQL SERVER Interview questions, SQL server optimization interview questions and answers, sql server performance query, sql server performance slow, SQL Server Performance Tuning, SQL Server Performance Tuning Tips, SQL SERVER Tips, SQL Tuning Overview, The blocking nature of aggregates, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases, Ways to minimize sort operations, with no ORDER BY clause
Execution Plan Operator – Properties, Types – Blocking / Non-Blocking?
In this post I would like to talk sorting algorithms SQL Server uses for sorting. Well SQL Server uses 2 algorithms (or their flavours) to sort data. They are
• Quick Sort
• Merge Sort
It’s actually a matter of WHERE the data is when it’s being sorted. If & while the data is in memory, Quicksort is used.
If and when a tempDB spill occurs, an on-disk Merge Sort takes place.
SQL Server begins sorting in memory using Quick Sort algorithm. Memory grant required in this case = 200% * Input Size. If the memory grant is increased it will spills everything (Entire immediate sort and remaining input rows). After that the sorting will be completed on disk using Merge Sort algorithm.
Now what is Memory grant fraction – It is a number between 0 and 1. 1 means 100% of the granted memory. One can view this by right clicking the sort operator and looking in the properties window. The example below was taken from a query with only a single Sort operator, so it has the full query workspace memory grant available during both input and output phases
Picture – showing Memory grant fraction-
SQL Server has two kinds of execution plan operators. They are
For details about blocking and non-blocking operators click here – https://msbiskills.com/2015/07/05/execution-plan-operator-properties-types-blocking-non-blocking/
Now we know that Sort is a blocking operator, as it is very clear that unless we have all the data how can we sort as we don’t know which row will be on the top and which one at the bottom. Now let’s see the below query and is execution plan.
-- USE AdventureWorks2012 GO SELECT * FROM Production.TransactionHistory ORDER BY TransactionDate --
Picture showing execution plan – The sort operator and the parallelism.
This is a parallel plan; all the operators are working in parallel (Parallelism – is a vast topic, shall cover this basics about this in upcoming posts) but the root iterator. Now we know that sort is a blocking operator and we have parallel symbol inside it. That is because Sort iterator has to consume all the input rows (In Open() Method) before it starts sorting but it starts producing output rows once the final run starts. Now let’s check the properties of sort operator and different threads to verify actually whether sort happens in parallel or not? Here the data on each thread is sorted separately and then Gather streams takes all the inputs and combine then together into a single stream.
There are eight threads present in the sort iterator properties because my laptop is an 8 core laptop. Basically numbers of threads are determined by MAXDOP (Maximum degree of Parallelism) for the plan.
Picture showing sort operator properties – Threads.
1. Sort is a blocking operator. It starts producing output rows once the final run starts.
2. Sort is not a parallel aware iterator
3. It uses Quick Sort and Merge Sort (or their Variants internally) to sort data.
4. You can fix the Sort by creating an Index, but always consider whether you really need that or not. If you have other queries which might get affected by this heavily.
That’s all folks; I hope you’ve enjoyed learning about Sort operator and its details, and I’ll see you soon with more “Performance Tuning” articles.
Pawan Kumar Khowal