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 IO ON' when this didn't happen before, it is down to bad joins, aggregates, views and so on... but data growth or server resource issues can all probably trigger it. The solution is to re-write the code. In our case, remove the views and directly reference the underlying tables.

We have a similar poor performing query that also uses the Worktable a lot but in that case SQL has no choice because the query uses UNIONs and SQL has to use Worktable to remove duplicate rows in the result set. UNION ALL doesn't try to remove dup rows so is a better performer.

Thanks,

Zarty