How can I use less tempdb?

ms_34 2017-01-12 14:43:46

*Running this against a 3rd party DB so adding indexes is out of the question right now
*The query can be modfifed in any manner
*Want to see if there's anyway to reduce tempdb usage before asking for another 300 GB of space for the tempdb file

Aaron Bertrand 2017-01-12 14:56:49
A single query needs an additional 300GB of tempdb space? This seems wrong, without even looking at the plan. Have you contacted the vendor and asked why a table that can grow to ~2 billion rows is a heap?

You might want to break the query up into smaller parts, like perhaps just dump the unique key values from REFER into a temp table with supporting indexes, then join to that. Or dump the 8 million filtered rows, then apply the window function to that, and remove the rest before joining. But if you have to read 1.6 billion rows from a heap every time, I don't have a lot of confidence in how much that will help.

(And to be clear, dumping some subset of the data to a temp table is a better use of tempdb than where it's actually killing you, which I suspect is the sort. Tough to really tell from an estimated plan that looks like it originally came from SSMS or the plan cache – Might be useful to generate an actual plan from within Plan Explorer, and post that.)