SQL Execution plan deep dive (05-Sort)

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.

Sort_Warning

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

Reference: https://www.youtube.com/watch?v=GSZPvF2u6WY

Advertisements
This entry was posted in Execution Plan, SQL and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s