Question regarding TempDB

  • Hi all,

    I have a question regarding the way TempDB works.

    Currently, I have a database which is on a drive that contains ~130GB of space. The database is taking ~60GB, and will continue to grow daily.

    TempDB is also sitting on the same drive (not the system drive BTW). TempDB is currently taking ~60GB.

    This is becoming an issue, as eventually the drive will run out of space even if TempDB remains the same size it is currently.

    What is the best way of addressing this problem? As far as I see it, I have two solutions:

    1) Stop and restart the server. This will reset TempDB to its initial size. However, will this just result in a problem arriving again in the future when TempDB grows back to 60GB? If I understand TempDB correctly, it never shrinks in size - if an operation requires a large amount of TempDB space, TempDB will grow accordingly to fit the operation, but once the operation is complete, it will remain at this size. Therefore, eventually that same operation will run again, and set the size of TempDB back to 60GB again. If I choose this solution, then I will need to schedule regular restarts of the server.

    1) a) On that note, is that a solution which is commonly employed? Scheduling regular restarts of the server, that is.

    2) Buy a new drive and move either TempDB or the database to that drive. Of course, eventually this will result in the same problem, but presumably it will take much longer to arrive.

    3) Optimize all the queries so that TempDB isn't taxed as much, by removing blocking operations. Of course this is probably the ideal solution, but its also the one that is least feasable, since it requires a much greater effort.

    It seems to me like if a new drive is not available, option 1) is the only real solution. Anyone else have any ideas?

  • 1 is not a solution. It's a mitigation, nothing more and it's not a particularly good one. No, it is not a commonly employed 'solution' to restart SQL often to reset TempDB. In fact, you should have TempDB's starting size set to the size it normally reaches so that you don't have the overhead of growing tempDB after a restart.

    Your solutions are 2 and 3. Add more drive space and optimise the queries to use less TempDB. Yes, it's work (and it pays very well)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I'll second the motion on 2 & 3.

    A good addition to 2 would be to move tempdb onto a separate RAID array from the main production database. If you can do that you can often get a significant performance boost on the whole server. Works even better if it's on a separate I/O channel, of course. But the main thing is, instead of replacing your current 130 Gb array, add another one and move one or the other database onto it while leaving the other one behind.

    (Yes, I realize I'm assuming you have these on RAID arrays instead of single drives, for redundancy and all that. Thought I'd better phrase it all that way, just in case it makes a difference in your solution.)

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • That's pretty much what I thought. Alright, thanks, I'll present the two ideas to my superiors and see which one they go for.

  • For best results, if you're running a busy production server, you should place TempDB on a seperate drive and split it up into one file for each CPU. That way, each scheduler has its own TempDB file to work with, which can greatly speed things up when running parallel operations (a disk will always be slow compared to a CPU, but at least if they're not all constantly asking for the same file...;-)) All TempDB files should be equally sized and have the same autogrowth settings.


    Kind regards,

    Vegard Hagen
    Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
    @vegard_hagen on Twitter
    Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)

    It is better to light a candle than to curse the darkness. (Chinese proverb)
  • http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%281230%29-tempdb-should-always-have-one-data-file-per-processor-core.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Yes, my apologies for not being more precise. When there are more than 8 CPU cores, I generally don't create a TempDB file for each one above the initial 8. It's pretty much as the post says. And as always: It depends...


    Kind regards,

    Vegard Hagen
    Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
    @vegard_hagen on Twitter
    Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)

    It is better to light a candle than to curse the darkness. (Chinese proverb)

Viewing 7 posts - 1 through 6 (of 6 total)

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