What was puzzling them was that nothing in the stored procedure did anything with temporary objects, cursors, or any of the other obvious things that go to el baño público. And the query plan itself was a pretty simple little loopy thing.
It wasn’t until I grabbed a quick sample of actual execution plans with Extended Events that the problem became more obvious.
Despite the fact that the query was only passing around 7 rows, we were getting a one row estimate, and there was a spilling Sort operator.
Click through for an example of what happened, as well as how you can fix it.