I have a section of a complex query plan where a Sort operator appears directly after an Index Seek and SSMS shows a "Operator used tempDB to spill" warning. It is reported to be taking 70% of the query cost. The statistics appear to be ok going into and out of the operator. How do I find out what is causing the spill and how to fix/improve it?

The branch of the query comes from a sub-query:

        t.DateTransferred as SentToGDS,
        row_number() over(partition by ev.ElectorID order by t.DateTransferred desc) as rowNum
        ElectorVerification ev
    inner join
        Transfer t on ev.UploadTransferID = t.TransferID
        ev.VerificationChannelID = 'ERTP'
) AS ...

Query Branch: enter image description here

Sort Operator:

enter image description here

Output of Index Seek:

enter image description here

  • It doesn't seem you found the branch correctly. In the subquery data is sorted by Transfer.DateTransferred, whereas in the sort operator by ElectorVerification.DownloadDate. Could you add to your question ElectorVerification definition? At least in part of ElectorId and DownloadDate columns. – Denis Rubashkin Jul 3 at 12:45
  • @DenisRubashkin - you hit it in one. Wrong subquery (very similar) which didn't have an optimised index. Added an index with the same ordering as required and the sort operator goes. – Jonathan Twite Jul 3 at 13:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.