Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ugdend
Starting Member
2 Posts |
 Posted - 2008-06-06 : 10:53:15
|
| Hi allI am currently carrying ut some tuning work on our sql server 2000 database, in particular tempdb.I've read a lot about tuning tempdb and would like some advice....My setup is:-The server has 2 disk controllers.Controller 1 has 3 logical drives:-C is raid 1 and holds the operating systemsD is raid 5 and holds the database data filesL is raid 1 and holds the log file and tempdbController 2 has 1 logical drive:-E is raid 5 and only holds 1 filegroup (pertaining to database on D) which has only 1 heavily accessed table in it.Controller 1 is significantly faster than controller 2 - 3X faster for writing, 2X faster for reading and 1.5X faster for random seek.My questions are:-1. I can create a second raid 1 array on contoller 2. Would I benefit from moving tempdb to this new raid 1 array on controller 2 given the speed compared to controller 1 ? It would get it away from the log file and onto a disk of it's own.2. I've read a lot about creating multiple datafiles in tempdb. At the moment when created tempdb is 1mb and grows to about 750mb over the course of a month or so. The server has 4 X dual processors and i was thinking of creating 8 X 100mb datafiles on tempdb.Profiler shows a lot of locks acquired/locks released on tempdb and i occaisionally see a 'pagelatch_up' on 2:1:92 (sysindexes) in sysprocesses.Any advice would be gratefully received.David Uden |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-06-06 : 12:18:28
|
| At the very least, you should create tempdb large enough so that it does not have to grow.I would use performance monitor to see if there would be much benefit to creating more tempdb files. Look at the disk queue length on the different drives to see how busy they are.CODO ERGO SUM |
 |
|
|
ugdend
Starting Member
2 Posts |
Posted - 2008-06-09 : 05:48:40
|
| I record daily monitoring average stats - disk queue length daily averages are:-D - between 1 and 3 - usually nearer 1 than 3 (3 spindles)E - between 1 and 3 - usually around 2 (7 spindles)L - never above 0.2 (2 spindles)Controller 1 - between 0.5 and 3 - normally around 1 |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-09 : 22:23:37
|
| Looks like disk holds tempdb is not that busy, but I prefer to separate tempdb from log files. |
|
|
|
|
|
|
TEMPDB Multiple Datafiles and Location