none
TempDB multiple file question ?

    Question

  • I have a server with two, Quad core processors running SQL server 2005.

    From doing some research it appears i should set up eight TEMPDB files for optimum performance.

    I have one RAID1 array running 15k rpm 73GB split into two drives, 'C' and 'E'

    I have a second RAID1 array running 10k rpm 300 GB, set up as one drive, 'M'.

    At the moment, i have one TEMPDB file on the 'C' drive and the data resides on the 'E' drive.

    Based on my current configuration, where would be the optimum placement of the other TEMPDB files, and do i really need eight of them.

    Thanks    

    Tuesday, June 17, 2008 3:40 PM

Answers

All replies

  • 13 views and no feedback !

    Someone must know about this ?? 

     

    Tuesday, June 17, 2008 8:16 PM
  • Hi,

     

    Your setup generally should be like this

     

    C: Operating System files

    D: MDF

    E: LDF

    F: Tempdb

     

    you just need one file for your tempdb and it should be RAID 10 if possible

     

    With your current setup (only two drives) it going to be hard to get good performance

     

     

    Wednesday, June 18, 2008 6:49 PM
  • Thanks Gary!

    I ordered three new SAS 15k rpm drives, to add to the five i already have.

    This will allow me to create four RAID1 arrays and follow your suggestions.

    My application is an Applicant Tracking System, and to improve performance i have set up full text indexing on my resume body table. (Due to the random nature of imported resumes)

    Where do you suggest i place the catalog file?

    Thanks

     

    Wednesday, June 18, 2008 8:02 PM
  • There's a good TechNet article on using tempdb.

    http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

    HTH...

    Joe

    Thursday, June 19, 2008 3:15 PM
    Moderator
  • To add to Joe's comment, you should also read the following articles as well:

     

    Optimizing tempdb Performance

    SQL Server Scalability FAQ

     

    Friday, June 20, 2008 1:24 PM
    Moderator
  • Expanding on Gary's HW database platform:

     

    C (RAID1): Operating System files/SQL server software, master, model, msdb

    D (RAID5): MDF - data

    E (RAID1): LDF - including tempdb LDF

    F (RAID1): Tempdb MDF

     

    Naturally, if there is business cost justification all the above can be on RAID10. Also placing busy key tables and indexes on dedicated RAID1 drive will boost performance.
    Sunday, June 22, 2008 12:38 AM
    Moderator
  • Thanks guys !

     

    Monday, June 23, 2008 12:41 PM
  • Does anyone have a good article on creating a RAID10 array?

    Thanks

     

     

    Monday, June 23, 2008 2:29 PM
  • My recommendation would be to work with your storage vendor to get their recommended settings for this. It is partially determined by your hardware as to specifically how you would create the array itself.

    Monday, June 23, 2008 3:49 PM
    Moderator
  • OK, I'm having a rookie sql DUH moment.

    I have created the four arrays, per sqlusa's suggestion.

    I copied the various mdf, ldf files to the new drives.

    The reason i copied instead of moved is so i can go back and delete after i get the new DB working.

    Now when i go to restart sql server 2005, it errors with a named pipes provider error 40, and sql error 2.

    Since you cant copy sql files with it running and after copying the files it wont restart, i'm confused.

    Thanks

      

     

    Tuesday, June 24, 2008 5:37 PM
  • Check out this article on how to move databases with detach - attach.

     

    This is the way to move tempdb to a different location.

     

    Let us know if this helpful.

    Tuesday, June 24, 2008 5:50 PM
    Moderator
  • You don't have to do a complete detatch/attach of the databases to move their specific files.  I find that it is much easier to use ALTER DATABASE to modify the file location with the following syntax:

     

    Code Snippet

    ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' )

     

     

     

    Then you simply shut the SQL Database Engine down, and copy the file to the new location, and start the SQL Services back up.  This is just another method of moving the files around, to be considered.  Attach/Detach will definately get the job done if you are more familiar with that route.

     

    http://msdn.microsoft.com/en-us/library/ms345483.aspx

    Tuesday, June 24, 2008 6:57 PM
    Moderator
  • Thanks guys, worked like a charm!

    BTW I really did not see much improvement in performance going from two RAID1 arrays to four RAID1 arraysSad

    Would RAID10 across 6 disks have been a lot better?

       

     

    Wednesday, June 25, 2008 12:11 PM
  •  Jonathan Kehayias wrote:

     

    Then you simply shut the SQL Database Engine down, and copy the file to the new location, and start the SQL Services back up.  This is just another method of moving the files around, to be considered.  Attach/Detach will definately get the job done if you are more familiar with that route.

     

     

    Just a note. Both shutting the engine and detach, leave the database files in "properly closed" mode. That is why both methods work OK.

    Wednesday, June 25, 2008 12:30 PM
    Moderator