I won't post the code now as the issue has been resolved. Basically, when you suddenly see Scans and Logical Reads to 'Worktable' from the output of 'SET STATISTICS...

SSCommitted,

The same query on a test (VM) server run in less than 0.5 second. On a very well specified production server, anywhere between 40 and 140 seconds....

The execution plans are about the same. It's on the IO stats that throw up the difference with all those reads to Worktable in tempdb. Something is obviously...

Same db restored to test. Resultset is the same apart from a few extra rows inserted into the production server.

What seems to be the issue is that SQL made...