Why is there a problematic sort in my SQL Server delete query plan?

Question

I have a very large table (150m+ rows) in SQL Server 2012 (web edition) that has no clustered index and one non-clustered index. When I run this delete statement: ``` DELETE TOP(500000) FROM pick WHERE tournament_id < 157 ``` (column name is in the non-clustered index), the execution plan produced by SQL Server looks like this: The sort step looks problematic - it takes up 45% of the cost, and it is causing an alert saying "operator used tempdb to spill data during execution." The query is taking several minutes to run, and I feel like it should be quicker. Two questions: - Why is there a sort step in the plan? - Any ideas how to overcome the spill? The server has 64gb of RAM and tempdb is sized at 8x 4gb data files. I can definitely revisit the indexing strategy on this table if that might help. Hope this all makes sense - thanks in advance for any tips.

Accepted Answer

I agree that there seems to be no good reason for a sort here.

I don't think it is needed for Halloween protection as it doesn't show up in the `= 157` version of the plan.

Also the sort operation is sorting in order of `Key Asc, Bmk ASC` (presumably to get them ordered sequentially in index order) but this is the order the forward index seek on the very same index is returning the rows in anyway.

One way of removing it would be to obfuscate the `TOP` to get a narrow (per row) rather than a wide (per index) plan.

```
DECLARE @N INT = 500000

DELETE TOP(@N) 
FROM pick
WHERE  tournament_id < 157 
OPTION (OPTIMIZE FOR (@N=1))
```

You'd need to test to see if this actually improved things or not.

Why is there a problematic sort in my SQL Server delete query plan?

Solution

Problem