Disk subsystem configuration for Tempdb

  • Hi,

    We have a Separate Dedicated Drive (T:\) for TempDb on SAN. Both tempDb.mdf & tempDblog.ldf are on Drive T:\

    Is this correct configuration OR still I need to place TemDblog.ldf file on to a different drive other than T:\ ?

    Please advice..

  • It completely depends upon your system and how much information is being passed through the tempdb. Generally I would say you are ok with them on the same drive.

    As an example, I have mine on the same drive. I have 3 production servers all with 30+ GB tempdb .mdf files running on SCSI 15000 rpm disk and never have contention issues.

    Fraggle

  • gmamata7 (9/19/2009)


    Hi,

    We have a Separate Dedicated Drive (T:\) for TempDb on SAN. Both tempDb.mdf & tempDblog.ldf are on Drive T:

    Is this correct configuration OR still I need to place TemDblog.ldf file on to a different drive other than T:\ ?

    Please advice..

    I would keep them together, splitting the data files and log files is more for user databases. the only thing i would recommend, is having one data file per cpu for your tempdb. so if you have a 8 processor box, you would have 1 mdf and 7 ndf and 1 log file for your tempdb database.

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

  • Interesting SilverFox. I have never heard that. What is the purpose of that? What type of performance improvements do you see when you do that? Are you putting each mdf/ndf on the same disk? Do you ever run into contention issues with doing that?

    Fraggle

  • assigning a temp datafile, to each core (given they were created correctly, all the same size) will avoid contention for the main tempdb usage, major increase in performance.

    There is a algorithm involved to "spread the load" between the data files (all seen as the tempdb).

    Implemented it myself a few months ago, and never looked back 🙂

    I think MS recommends it as a BP.

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • it is a best practise, we have 16 cpu machines here, tempdb is split, 1 mdf and 15 ndf. size has to be the same, I normally set them to 512mb each, autogrowth 64mb unrestricted.

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

  • This week I am splitting my TempDB file to the number of physical CPUs (8 dual core) for an upcoming production environment.

    -Forum Etiquette: How to post Performance Problems[/url]

    -Forum Etiquette: How to post data/code to get the best help [/url]

  • Here's a link to a page containing details on how to identify tempdb contention, along with the suggestion of creating one data file per CPU:

    http://blogs.msdn.com/sqlserverstorageengine/archive/2009/01/11/tempdb-monitoring-and-troubleshooting-allocation-bottleneck.aspx

    Cheers

    Chris

  • Silverfox (9/22/2009)


    it is a best practise, we have 16 cpu machines here, tempdb is split, 1 mdf and 15 ndf. size has to be the same, I normally set them to 512mb each, autogrowth 64mb unrestricted.

    Correct, but for the best performance it is better to stop autogrow and make each TempDB Data file equal = (D/N)-P, where D is TempDB_Data disk size, N is number of CPUs, P is parameter, which allows you avoid a space issue (usually =1Gb). 🙂 So, files never grow, and initial size is MAX size.

    In this configuration you will jump in performance because TempDB doesn't need any time for space allocation.

  • Sorry, but you have not given us enough information to advise you. How many spindles underly the proposed tempdb drive? Are these spindles shared for any other IO load?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Sergey Vavinskiy (9/22/2009)


    Silverfox (9/22/2009)


    it is a best practise, we have 16 cpu machines here, tempdb is split, 1 mdf and 15 ndf. size has to be the same, I normally set them to 512mb each, autogrowth 64mb unrestricted.

    Correct, but for the best performance it is better to stop autogrow and make each TempDB Data file equal = (D/N)-P, where D is TempDB_Data disk size, N is number of CPUs, P is parameter, which allows you avoid a space issue (usually =1Gb). 🙂 So, files never grow, and initial size is MAX size.

    In this configuration you will jump in performance because TempDB doesn't need any time for space allocation.

    That is the exception rather than the rule, if you size tempdb correctly it will not be an issue.

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

  • Something I haven't seen anyone discuss is do you need to put each of these on a separate drive or do you still get the performance increase if you keep them on the same drive. Does anyone actually get IO performance decreases with this?

    Fraggle

  • Fraggle-805517 (9/23/2009)


    Something I haven't seen anyone discuss is do you need to put each of these on a separate drive or do you still get the performance increase if you keep them on the same drive. Does anyone actually get IO performance decreases with this?

    Fraggle

    If you have too many files on too few spindles you WILL get performance decrease.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Fraggle-805517 (9/23/2009)


    Something I haven't seen anyone discuss is do you need to put each of these on a separate drive or do you still get the performance increase if you keep them on the same drive. Does anyone actually get IO performance decreases with this?

    Fraggle

    I havent worked at a company that implemented it, but you would in theory get a bigger performance increase. as each file would be on a dedicated drive. and yes you do get a performance increase, even if you use multiple files and they are on the same drive, but the increase would be more if they were on separate drives.

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

Viewing 14 posts - 1 through 13 (of 13 total)

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