Post reply

Why is my tempdb growing

  • 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?

  • July 11, 2012 at 9:26 am

    #1511625

    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.

  • July 11, 2012 at 9:28 am

    #1511628

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

  • July 11, 2012 at 9:40 am

    #1511639

    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

  • July 11, 2012 at 10:01 am

    #1511665

    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

  • July 11, 2012 at 10:08 am

    #1511669

    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)
    Intro to Tally Tables and Functions

  • July 11, 2012 at 10:13 am

    #1511672

    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.

  • July 12, 2012 at 2:14 am

    #1511906

    @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

  • July 12, 2012 at 4:52 am

    #1511973

    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.

  • July 12, 2012 at 10:21 am

    #1512168

    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

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

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