none
Tempdb Multiple File Clarification

    Question

  • Hi all, I am looking to add another file for my tempdb and move the tempdb to its own Drive (on its own LUN, currently they are on shared data/log drives), how should I go about  naming the files and what are the file type formats.

    I did not seem to come across anything that definitively states how the multiple filese should be set up, so should I have a primary Filegroup that holds tempdev.mdf and then add another Filegroup and add the File to it, or add it as belonging to Primary Filegroup??

    Additionally, am I only to add data files not additional log files???

    How should I set up the file size and growth on both files? Currently I am set at initial file size of 1 MB for the tempdev and 1 MB  for the templog, autogrowth is 10% on each, current file size is 4.2 GB.

    Currently I have 2 processors  (quads), but I don't think we need 8 data files at this time so I wanted to start with just 1 additional... I am not experiencing any contention, but I know several users like to use temp tables , and I am trying to thwart off any performance issues before they arise.

     

    Thanks in advance for all your help,

    ZMan079

    "John"

     


    Wednesday, August 10, 2011 12:37 PM

Answers

  • Hi,

    Make sure you set-up the TempDB data files with the same properties, i.e. size, auto-growth increments.

    Pre-size the datafiles to what you "think" they'll eventually stablize at.  Growing datafiles can be a resource expensive operation, so you're better off creating them at, for example, 1GB each.  Auto-grow should be there as a failsafe just in case it does need to expand, and should be set equally on each data file.

    No need to add multiple log files, as that gets written to sequentially.  Typically the only time you'd add another log file is to alleviate a space issue, and needed to rollover to another drive with additional storage.  But again, you should pre-size the log file to a reasonable size.

    As for the number of tempdb files, the general rule of thumb is somewhere between 1/2 and 1/4 of the number of cores.  You'll also see gains even if the datafiles are on the same LUN.

    Here's a good link to read about it all, from Paul Randal: http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx

    Hope this helps,

    Andrew

     

    • Proposed as answer by John Eisbrener Wednesday, August 10, 2011 1:19 PM
    • Marked as answer by Stephanie Lv Tuesday, August 16, 2011 11:20 AM
    Wednesday, August 10, 2011 12:57 PM
  • Hi,

    As far as I'm aware, it doesn't matter that TempDB is on the primary filegroup, just that it has its own dedicated disk(s).

    Here's a good reference for your other tempdb questions: http://msdn.microsoft.com/en-us/library/ms175527.aspx

    Also, just a pointer on the difference between files (mdf, ldf, ndf) and filegroups http://msdn.microsoft.com/en-us/library/ms179316.aspx

    Thanks,

    Andrew

    • Marked as answer by Stephanie Lv Tuesday, August 16, 2011 11:20 AM
    Wednesday, August 10, 2011 7:46 PM

All replies

  • Hi,

    Make sure you set-up the TempDB data files with the same properties, i.e. size, auto-growth increments.

    Pre-size the datafiles to what you "think" they'll eventually stablize at.  Growing datafiles can be a resource expensive operation, so you're better off creating them at, for example, 1GB each.  Auto-grow should be there as a failsafe just in case it does need to expand, and should be set equally on each data file.

    No need to add multiple log files, as that gets written to sequentially.  Typically the only time you'd add another log file is to alleviate a space issue, and needed to rollover to another drive with additional storage.  But again, you should pre-size the log file to a reasonable size.

    As for the number of tempdb files, the general rule of thumb is somewhere between 1/2 and 1/4 of the number of cores.  You'll also see gains even if the datafiles are on the same LUN.

    Here's a good link to read about it all, from Paul Randal: http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx

    Hope this helps,

    Andrew

     

    • Proposed as answer by John Eisbrener Wednesday, August 10, 2011 1:19 PM
    • Marked as answer by Stephanie Lv Tuesday, August 16, 2011 11:20 AM
    Wednesday, August 10, 2011 12:57 PM
  • John,

     

    Thanks for the response!!!!

     

    Should the data files then be part of the rimary group (.mdf) or should they be part of another Filegroup (.ndf's)???

     

    Thanks,

    John

    Wednesday, August 10, 2011 4:56 PM
  • Hi,

    As far as I'm aware, it doesn't matter that TempDB is on the primary filegroup, just that it has its own dedicated disk(s).

    Here's a good reference for your other tempdb questions: http://msdn.microsoft.com/en-us/library/ms175527.aspx

    Also, just a pointer on the difference between files (mdf, ldf, ndf) and filegroups http://msdn.microsoft.com/en-us/library/ms179316.aspx

    Thanks,

    Andrew

    • Marked as answer by Stephanie Lv Tuesday, August 16, 2011 11:20 AM
    Wednesday, August 10, 2011 7:46 PM