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:
... ( select ev.ElectorID, t.DateTransferred as SentToGDS, row_number() over(partition by ev.ElectorID order by t.DateTransferred desc) as rowNum from ElectorVerification ev inner join Transfer t on ev.UploadTransferID = t.TransferID where ev.VerificationChannelID = 'ERTP' ) AS ...
Output of Index Seek: