Note: This blog is continuation to earlier blog: SQL Execution plan deep dive (04-Spool)
In this blog we will see how sort impacts the performance.
Fundamentally sorting is used across most of execution plans and it works good with small to medium data but sorting time increases as the data grows. As long as Server memory is sufficient to complete the sort operation we are in a better position but when SQL Server runs out of memory and start using page file performance will be significantly impacted.
The yellow symbol in above image indicates that the memory was sufficient to complete sort operation which can cause performance issues.
Following are SQL Queries which gives same results but second query is adjusted to sort small amount of data multiple times.
use AdventureWorks2014 GO SELECT TOP (500) WITH TIES ProductId, ActualCost FROM ( SELECT ProductId, ActualCost, ROW_NUMBER() OVER(PARTITION BY ProductId ORDER BY ActualCost DESC) AS r FROM bigTransactionHistory WHERE ActualCost >= 50 AND ProductId BETWEEN 10 AND 2000000 ) AS x WHERE x.r = 1 ORDER BY x.ActualCost DESC; GO -- 23 Seconds -- more number of small sorts gives good performance SELECT TOP (500) WITH TIES p.ProductId, x.ActualCost FROM bigProduct AS p CROSS APPLY ( SELECT bt.ActualCost, ROW_NUMBER() OVER(ORDER BY bt.ActualCost DESC) AS r FROM bigTransactionHistory AS bt WHERE bt.ProductId = p.ProductId AND bt.ActualCost >= 50 ) AS x WHERE p.ProductId BETWEEN 10 AND 2000000 AND x.r = 1 ORDER BY x.ActualCost DESC; GO -- 9 Seconds