SQL Server Performance

Sql 2005 Tempdb

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by neo302, May 11, 2006.

  1. neo302 New Member

    Hey,
    We do rapid and mostly inserting. We are testing a 2005 box which processes way slower than our 2000 box, although the 2005 box is much better (faster cpus etc). The only thing is the 2000 box has 2meg L2 cache and the 2005 has 1meg L2 cache. We use the TempDB a lot. I know 2005 uses the tempdb more than 2000.

    Do you think moving all of the tempdb work to a new separate db would help although now it will be doing more logging? I've done all the optimizations I know of and our 2005 box just keeps getting killed by the 2000 box. It will be a LOT of work, I just want to know what you guys think first.

    Thanks!
  2. thomas New Member

    Have you got tempdb on a separate physical drive? You need to really for SQL Server 2005 because it's so heavily used in ways it wasn't in previous versions.
  3. neo302 New Member

    quote:Originally posted by thomas

    Have you got tempdb on a separate physical drive? You need to really for SQL Server 2005 because it's so heavily used in ways it wasn't in previous versions.

    Hey,
    Yes, Tempdb is on RAID 0, regular db MDF on Raid 0 and regular db log on Raid 0.
    All are on seperate Raid 0 setups.
    Thanks!
    Justin
  4. mmarovic Active Member

    Have you applied sp1?
  5. satya Moderator

    I have followed thishttp://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1180698_tax301334,00.html?adg=301324&bucket=ETA tip and so far so good at our end.

    TEMPDB on RAID 0 I'm not comfortable with that option for recoverability option.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. neo302 New Member

    quote:Originally posted by satya

    I have followed thishttp://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1180698_tax301334,00.html?adg=301324&bucket=ETA tip and so far so good at our end.

    TEMPDB on RAID 0 I'm not comfortable with that option for recoverability option.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

    Hey Satya!
    Thank you for the info. That is interesting, multiple files. Is that multiple files on the same drive?
    Thanks!
  7. satya Moderator

    Yes all in same drive.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. cmdr_skywalker New Member

    I suggest you use the RAID0 for tempdb only. Tempdb need not to be saved since it is always truncated everytime you restart the server. I agree with the link, use RAID1 for the sequential logs and system dbs. Use RAID5 for other dbs.

    May the Almighty God bless us all!
    www.empoweredinformation.com
  9. joechang New Member

    this issue is that a disk failure on the tempdb may bring down SQL Server unless there are multiple tempdb files, each on a different disk, and there is a way to just kill any queries using the failed drive and reconfigure the tempdb to stop using the failed drive
  10. Haywood New Member

    quote:Originally posted by joechang

    this issue is that a disk failure on the tempdb may bring down SQL Server unless there are multiple tempdb files, each on a different disk, and there is a way to just kill any queries using the failed drive and reconfigure the tempdb to stop using the failed drive

    This might work, but it will require a sql server restart for the tempdb changes to take effect.

    Keep your tempdb on a raid1 at a minimum...
  11. satya Moderator

    True and for the recoverability option I would recommend, though RAID 0 offer you best performance... but without good recoverability that will be no good.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  12. gaurav_bindlish New Member

  13. druer New Member

    I'm confused on your question and subsequent answer. In your question you asked
    quote:Do you think moving all of the tempdb work to a new separate db ...
    But then when someone asked if your TempDb was on a seperate physical drive you said that it was.

    If it is already on a seperate drive, I'm not sure what your question means.

    I have had success reducing the bottleneck and improving speed using multiple TempDb datafiles. The rule of them as I was told was 1 per "logical" CPU. Meaning we have 4 Hyperthreading CPU's, which the system thinks is 8 CPU's so I created 8 identically sized TempDB files. Under 2000 the "identical size" was required because of a bug in the algorithm that chose a data file to use, in that it would select largest if there was a largest, or it would probably choose next available if there was no largest.

    I'll mention it just in case ... Is there any chance you have more indexes and/or triggers on the tables being inserted to on 2005 than you do in 2000.


    Hope it helps,
    Dalton

    Blessings aren't so much a matter of recieving them as they are a matter of recognizing what you have received.

Share This Page