Large query increases TempDB

Question

I have huge query on my SQL 2005 Server. This have to be run once everyday but when this query runs temp db grows from 2GB to 48GB. What is the best way top optimize or find the reason why tempdb is growing when this query adds/updates on 80K records with (~120 columns) on a single table?

What should I do on this query that tempdb wouldn't grow so much?

Any suggestions will be appreciated.

NOTE: This query doesn't have any temptables or table variables or CTEs. just bunch of

INSERT INTO ... with MULTITABLE JOINS with SUBQueries...

Answer 1

You may want to look at this. It's likely that your query is using a temp-table to run, but it's very hard to tell without knowing anything about it.

Looking at your question update, it seems probable your subqueries are using the temptable strategy, which floods your TempDB. Try to get rid of those subqueries and/or reduce the amount of data you are working with in a single query run should help reduce growth of your TempDB.

Answer 2

Without seeing the exact code, it is hard to help you. But the query seems to need to be optimized.

Of course you could just size your temp db to stay at 48 GB, at least this way it won;t have to take the time to grow when this thing runs.