"tempdb" size -- 550GB!

Question

"tempdb" size -- 550GB!

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 1

Attila (5/15/2009)
I'd recommend to run a trace, and look for autogrow events.
Paralelly start a script which runs in every minutes, and writs out the inputbuffer of each spid>49, plus the last batch start time.
I expect you will see that there are multiple autogrowth events from the same command, which is running very long time, and you can catch waht does it executing.
Adding sql/SP completed events to the trace could help further.

I believe that's already been done. That's how the OP found the bad proc to begin with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 2

Robert (5/15/2009)
See if there is any kind of sort in execution plan. Cross join can produce a huge result, but not necessarily anything in tempdb.
If you have an "order by" or "union" or "distinct" and there is no index to use, it has to materialize the whole resultset and sort it to order it and/or remove duplicates.

I agree that SORTs can use a fair bit of TempDB, but not a 550GB TempDB. Credits to Navy Beans says we'll find out that this was an accidental cross-join. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Robert-378556 SSCertifiable Points: 5917 More actions · Answer 3

Jeff Moden (5/15/2009)
I agree that SORTs can use a fair bit of TempDB, but not a 550GB TempDB. Credits to Navy Beans says we'll find out that this was an accidental cross-join. 🙂

Maybe.

Let me show my point with example:

TableA=60mil rows, TableB=1000

select * from tableA, tableB resultset is 60 bil. rows, surely well over 550GB, but won't create anything in tempdb

select * from tableA A join tableB B on (A.Key=B.Key) normal inner join, uses index for join, still nothing in tempdb

select * from tableA A join tableB B on (A.Key=B.Key) order by A.Label+B.description normal inner join, uses index for join, but has to materialize resultset to apply order by, to sort it.

If 60mil rows take 550GB, that's 9k per row, kind of likely size for a 5 table (left) join, so it looks like a proper join with sort. It doesn't matter which case is it, if anything of this helps, we did well.:cool:

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 4

Heh... actually, now that I lookback at it, part of the problem was, indeed, an ORDER BY. It was actually a combination of both that killed our TempDB. They had left out some (not all) criteria and it had formed an unintentional cross-join and they tried to sort the result (billions of rows). We fixed the unitentional cross-join and the problem went away.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)