Why is my tempdb growing

Question

Why is my tempdb growing

mitzyturbo

Hall of Fame

Points: 3452
More actions
July 11, 2012 at 9:06 am

#258819

Hi all
Might sound a bit stupid but I'm struggling to determine why my tempdb (SQL Server 2000, so I can't use DMV's) is growing for a certain command. The only query I could pick up from Quest (Foglight performance analysis) that was running around about the same time the the tempdb mdf increased) is running a straight select using sums with the relevant group by and having clauses. No temp tables used and nor order by.
I know that sorting can have an impact on tempdb growth but I wouldn't have thought such a simple query could take nearly 20 minutes to run returning 200K+ rows or for that matter cause tempdb to grow. I'll crack open the indexes on the tables involved but any suggestions as to other factors to check against this query or others to see why it would cause tempdb to grow?

Viewing 10 posts - 1 through 9 (of 9 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Since you are running a aggregate query, SQL Server has to sort the data by the group by columns so that it can properly complete the aggregation. This will impact tempdb as this is where SQL Server will build the work table(s) it uses for the query.

mitzyturbo Hall of Fame Points: 3452 More actions · Answer 2

Thanks Lynn, thought the aggregation would impact it but nowhere near the level it appears to do

Daxesh Patel Hall of Fame Points: 3208 More actions · Answer 3

How much tempDB grows by this query?

What data types? any blobs?

How much sql memory? Min/Max

How much "Memory Manager/Total Server Memory(KB)" from PerfMon?

Also consider size of data in 200K+ rows

mitzyturbo Hall of Fame Points: 3452 More actions · Answer 4

Tempdb MDF doesn't seem to grow now when I run the query again which seems strange, I'm guessing this is down to the result set getting cached in memory

Haven't used Perfmon to trap any of this yet, just using Quest. It does show a big Latch Wait when it runs though

The server itself only has 4GB of RAM

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Are any of the aggregates in correlated subqueries? If so, are they based on inequalites such as "<=", etc???

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

mitzyturbo (7/11/2012)
Tempdb MDF doesn't seem to grow now when I run the query again which seems strange, I'm guessing this is down to the result set getting cached in memory
Haven't used Perfmon to trap any of this yet, just using Quest. It does show a big Latch Wait when it runs though
The server itself only has 4GB of RAM

If you didn't shrink it (bad idea any way as it could corrupt tempdb) or restart SQL Server (which will recreate tempdb at its original size), it may not need more space for subsequent runs of the query.

mitzyturbo Hall of Fame Points: 3452 More actions · Answer 7

@jeff - The HAVING clause is based on a value being > sum(xxxx)

So the query has to run this check for ever single record in the table

mitzyturbo Hall of Fame Points: 3452 More actions · Answer 8

On further inspection, this query is not the reason why the tempdb grew. I have ran it a few times myself and it has not hit the growth rate that would equate to a 30GB increase. Having gone through the Quest logs of other T-SQL statements that was running over this 3 day period, I can't see anything else that would have caused this (BTW if anyone know's how to kick out a text file of all T-SQL statements in Foglight so I can search for all INSERT INTO # statements that would be great)

In Foglight Performance Analysis, TempDB itself doesn't show any queries that made any large increase which I find strange.

InQuest Capacity Manager, I can see the growth quite plainly of 30GB but this tool doesn't provide the SQL statements that were written at the time

Now I'm thinking if perhaps another query was written on another server which has a linked server to this one that may have caused this (pretty awkward to narrow down as quite a few do have this server as a linked server).

Also if anyone is more familiar with Quest tools in relation to issues like this I'd appreciate any suggestions.

mitzyturbo Hall of Fame Points: 3452 More actions · Answer 9

Found the offending query in the end, all down to the way I was trapping the offending queries in Foglight.

Ended up searching for every T-SQL statement and Batch that ran in the 3 day window, had to go through each one (I'm sure there's an easier way of finding this). The offending query was inserting millions of records into temp tables - apologies that this turned out to be such a routine query and not something that would have got the SQL juices flowing 🙂

Quest knowledge base wasn't great when looking for tips on such issues, if anyone else is Foglight savy or is well accustomed to using this as a tool to find such answers can they forward me on some good sources.

Thanks for the help on this