June 12, 2008 at 1:27 am
Hi all
I 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 systems
D is raid 5 and holds the database data files
L is raid 1 and holds the log file and tempdb
Controller 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
June 12, 2008 at 2:14 am
It's always recommended to put TempDB on its own drive, especially if it's heavily used.
The recomendations to split TempDB into multiple files is usually to avoud contention on allocation pages, typically 2:1:3. Are you seeing latch waits on that page?
If you are, I would suggest start with splitting TempDB into 4 files, all the same size and they can all be on the same drive. If you're still seeing contention, then split again into 8 files.
I don't think that splitting will help with contention on sysindexes. No matter how many data files get created, there is only 1 sysindexes in a database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2008 at 9:57 am
I would rather create the 2nd Raid 1 for TempDB on Controller 1 since it is faster.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply