I did not hand it a plan with a spill in it. Everybody can find a spill. I handed it a plan with no warnings at all, where every number on the screen looked healthy, and the query underneath was reading five million rows to return seventeen.
SQL SERVER – Understanding WITH RECOMPILE in Stored Procedures
The culprit? Parameter sniffing. The solution? You guessed it: WITH RECOMPILE. Comprehensive Database Performance Health Check