Post reply

TempDB

  • November 1, 2018 at 3:49 am

    #376223

    Hi,
    Please i need your advise,to reduce TempDB allocation contention i must add more mdf files.I have a drive allocated for tempDB only.
    What is best, to spread the files across the drives or put them in one drive?
    How many files must i add for a 24 processor?I read somewhere and it says 1 file per processor,do i need 24 files for tempDB?

    Thanks

    T

  • November 1, 2018 at 4:16 am

    #2012009

    Keep them all on one drive.  You're doing this to reduce contention on the header page (that's not a technical term - I can't remember what it's actually called), of which there is one per file, not to spread the IO across drives.

    How many data files do you have at the moment?  If you have the default of one, then increase to four initially.  You may find yourself needing to increase again if you're still seeing contention, but that's fine - a few at a time is the way to go.  There is a somewhat complex formula for the recommended number of files according to your processor configuration - it involves NUMA nodes and all sorts.  It's out there if you want to search for it.

    John

  • November 1, 2018 at 5:30 pm

    #2012129

    If there's that much contention for TempDB, I'm thinking there might be some code problems that need to be fixed.

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

  • November 9, 2018 at 6:46 am

    #2012953

    tmmutsetse - Thursday, November 1, 2018 3:49 AM

    Hi,
    Please i need your advise,to reduce TempDB allocation contention i must add more mdf files.I have a drive allocated for tempDB only.
    What is best, to spread the files across the drives or put them in one drive?
    How many files must i add for a 24 processor?I read somewhere and it says 1 file per processor,do i need 24 files for tempDB?

    Thanks

    T

    Suggest you to install whoisactive and findout , you have a tempdb contention and find the query and go on.

    https://www.sqlserverblogforum.com/dba/whoisactive-capturing-a-sql-server-queries-performance-tuning/

    https://www.sqlserverblogforum.com/dba/tempdb-database-is-full/

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • November 9, 2018 at 4:44 pm

    #2013041

    Start with 4 data files, 1 log file. If there's still contention, add another 4 data files. Repeat until there's no more contention.
    And make sure that they're all the same size and have the same growth settings.

    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

  • November 20, 2018 at 6:35 am

    #2014089

    My tempDB is actually on C drive  which i think is very wrong.Unfortunately there is only 2 drives  C and D( data drive).Is it better to move tempDB to data drive? i have 4data files and 1 log file.

    Thanks

    T.

  • November 20, 2018 at 3:55 pm

    #2014155

    tmmutsetse - Tuesday, November 20, 2018 6:35 AM

    My tempDB is actually on C drive  which i think is very wrong.Unfortunately there is only 2 drives  C and D( data drive).Is it better to move tempDB to data drive? i have 4data files and 1 log file.

    Thanks

    T.

    If all you have is a C and D drive - then leave tempdb on the C drive.  If this is a VM then have a new drive presented to the server that is large enough to support tempdb (plus some growth) and move the tempdb files to the new drive.  If this is a physical machine, verify the configuration of each drive - are they local drives or SAN?  What is the RAID level on each drive?  Is it a single drive that is partitioned into a C and D - if so then it really doesn't matter as this is going to the same spindle(s).

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • November 21, 2018 at 5:48 am

    #2014197

    Thanks  Jeff

  • November 26, 2018 at 1:10 pm

    #2014568

    If only 2 drives, see if you can have mount point drive configured. This way you can isolate only TEMPDB into this drive.

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

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