Temp DB question

  • I am considering a change to how we configure new SQL Servers... On our servers tempdb get's it's own drive. Data file and log file autogrowth is set to 10% / unlimited growth. Wouldn't it be better to just use up all the space on that drive since the drive is not used for anything else and autogrowth slows stuff down? It seams logical to me - does anyone see a downside to this approach?

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Alan.B (4/9/2015)


    I am considering a change to how we configure new SQL Servers... On our servers tempdb get's it's own drive. Data file and log file autogrowth is set to 10% / unlimited growth. Wouldn't it be better to just use up all the space on that drive since the drive is not used for anything else and autogrowth slows stuff down? It seams logical to me - does anyone see a downside to this approach?

    File growth should almost never be setup as a % so far as I'm concerned. And, there is only 1 downside to preallocating the entire disk and that is you will never know if a runaway query is in process. I've got a 16 core box with a 100 GB "T:" drive for TempDB. I've allocated 8 2GB files for data and 2GB for the log file. It's never had to grow. I have an alert setup on it for when it does grow because it will mean that someone might be doing something stupid that I need to look at. Having it grow and give me the alert on the first grow will give me time to figure out if it's stupid or not.

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

  • Don't use up all the space on any of your drives. Always have a one or two mb file called "deletemeinemergencies.txt" that you can use to gain a little space in the event of one of Jeff's runaway queries.

    But, if you want to fill the space you can, but just turn off auto-grow. You do lose that bit of monitoring like Jeff says.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Thank you Grant and Jeff!

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 4 posts - 1 through 3 (of 3 total)

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