Post reply

TEMPDB with SQL2K5

  • August 16, 2007 at 6:58 am

    #118285

    I have been told that tempdb in SQL2K5 can be a bottle neck. I have also been told one way to help tempdb performance issues is to put tempdb on it's own device and to add a file group per thread to tempdb. Does anyone have any experience doing this? We have moved tempdb to it's own device, but what about the FILEGROUPS?

     

    Thanks,

    DBAMARK

  • August 16, 2007 at 7:11 am

    #727314

    Mark,

    in SQL 2005 the tempDb is used a lot more than in SQL2000 depending on some options like Snapshot Isolation level. For more see here: http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

    If you have performance problems, you can create several files (not Filegroups). The guideline from MS is  one file for each CPU, though on servers with more than 4 CPU's you can go with about one file for each 2 CPU's.

    Markus

    [font="Verdana"]Markus Bohse[/font]

  • August 16, 2007 at 9:37 am

    #727410

    Thanks Markus. This helped.

  • August 16, 2007 at 11:45 am

    #727459

    you can add extra drives, keep the default small by limiting growth and give unlimited growth to the ones on the other drives

  • August 16, 2007 at 8:37 pm

    #727532

    Actually the number I use is 1 file per CPU, all the same size (fixed) and there is also a trace value that is supposed to be set (if you have contention).  The link below is for the tuning advisor.  It will make that recommendation if you need it.  (Sorry can't remember the value). 

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

    FYI, we use tempdb significantly and we have snapshot isolation enabled for 4 of our databases.  One generates about 400M of logs an hour to give you perspective.  The second most active will get that high but usually is around 200M/hour.

  • August 17, 2007 at 6:22 am

    #727603

    Thanks you all for your help.

Viewing 6 posts - 1 through 5 (of 5 total)

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