June 11, 2008 at 4:38 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 11, 2008 at 9:08 am
June 11, 2008 at 9:09 am
heres link about tempdb
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
June 11, 2008 at 6:43 pm
Robert klimes (6/11/2008)
Having tempdb on its own drive (raid 10 preferable) and having same number of tempdb file as you have cores would be optimal configuration.
This is a SQL 2000 problem that was fixed in SQL 2005 by Instant data file initialization and improvements to the tempdb SGAM allocator. This problem should no longer occur, so this pre-emptive "fix" is no longer necessary, and because of the thrashing it can cause on the Tempdb disk it really is not a good idea.
If you think that you do nonetheless have this problem, then there is a better fix anyway: using TF-1118 to allocate full extents. Though I do not know of any actual documented cases of this problem in SQL 2005.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 12, 2008 at 8:09 am
rbarryyoung (6/11/2008)
Robert klimes (6/11/2008)
Having tempdb on its own drive (raid 10 preferable) and having same number of tempdb file as you have cores would be optimal configuration.This is a SQL 2000 problem that was fixed in SQL 2005 by Instant data file initialization and improvements to the tempdb SGAM allocator. This problem should no longer occur, so this pre-emptive "fix" is no longer necessary, and because of the thrashing it can cause on the Tempdb disk it really is not a good idea.
If you think that you do nonetheless have this problem, then there is a better fix anyway: using TF-1118 to allocate full extents. Though I do not know of any actual documented cases of this problem in SQL 2005.
You are partly correct, partly incorrect here. See pages 19 and 20 of this word document from Microsoft: Working with tempdb in SQL Server 2005 SQL Server Technical Article. The best practice is still to use multiple files. You are correct in that too many files on the same physical disk can lead to thrashing, and this is mentioned in the document. Thus another best practice to create tempdb files striped across multiple fast disks.
Tempdb changes in 2005 have worked to reduce SGAM page issues (primarily temporary object caching - which has some significant limitations), and 1118 is mentioned as an additional item to address contention if it is noticed.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply