SQL Server Performance Forum – Threads Archive
Sql 2005 Tempdb
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!
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.
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
Have you applied sp1?
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.
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.
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!
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.
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
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
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…
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.
This should be useful …
http://www.sqlskills.com/blogs/kimberly/PermaLink.aspx?guid=8be9d388-b354-429f-8c98-e45989a4bebe Gaurav
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
http://blogs.msdn.com/gauravbi/default.aspx
I’m confused on your question and subsequent answer. In your question you asked
quote
o 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.
o you think moving all of the tempdb work to a new separate db …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.
]]>