SQL Server Performance

Tempdb log file location and Filegroup optimizations

Discussion in 'ALL SQL SERVER QUESTIONS' started by hernan93, Aug 6, 2012.

  1. hernan93 Member

    I've been reading articles about tempdb optimizations and I already understand a lot better the different ways to archive it. I read (for example) the following links:

    Optimizing tempdb Performance
    http://msdn.microsoft.com/en-us/library/ms175527(v=sql.105).aspx

    Recommendations to reduce allocation contention in SQL Server tempdb database
    http://support.microsoft.com/kb/328551

    However, there are a couple of questions I couldn't answer to myself with just the reading. The best practices read as follow:

    1. "Put the tempdb database on disks that differ from those that are used by user databases."
    But, the "tempdb database" is the datafiles and the log file, Is it enough to have a dedicated disk for the whole tempdb or is necesary to have two disk, in order to split the datafiles and the log files??
    If you only have 3 disk for organize your databases phisical desing, which option would you choose between the following configurations?
    a. One disk for database files. one disk for log files. one disk for tempdb (data and log files). or...
    b. One disk for database files. one disk for log files including the tempdb log file. One disk for tempdb datafiles.

    2. "Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs."
    Regarding this, all the additional database files created in order to improve the disk bandwidth for the tempdb should be placed on the primary filegroup, or should I create additional filesgroups?? For example, in a 16 CPUs server I decided to use 8 datafiles, should I have the primary filegroup with 8 datafiles or could be better to have two filegroups with 4 files each??

    Thank you in advance for your recommendations.

    Hernan.
  2. Shehap MVP, MCTS, MCITP SQL Server

    Here below are the answers :

    1- Much preferred to go with the option "B" to have a disk for user DB files and another one for Temp DB files and the 3rd for the entire of log files including TempDB files but it is recommended to configure the last one assigned for log files with RAID 1/0 type for a better performance and make it small as much as you can to be like 100 GB to match up with the usual equation of any IO subsystem " Less storage capacity , more storage performance and vice versa"

    2- Just create 8 files in the same file group "primary" since it will be useless if you create additional file groups without assigning tables or indexes for these new file groups since all of schema of TempDB are created in the primary file group only.

    Kindly let me know if any further help is needed

Share This Page