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 |
ashwinreddy.c
Starting Member
13 Posts |
Posted - 2006-05-06 : 05:56:32
|
Hi All,I was posed with the question of placing the tempdb on a seperate drive. For this i need to know how heavily tempdb is used. I have placed some counters like worktables/sec, workfiles/sec etc and i have found that on one of the servers the value has exceeded the threshold value of 20. Is this the only way on which i can decide the tempdb is used heavily and needs a seperate drive. Can anyone suggest are there any other ways on which i can decide tempdb is used heavily and need to go for seperate driveAny thoughts.ThanksRegardsAshwin ReddySQLServer DBA |
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-05-09 : 13:32:36
|
You can move your current TEMPDB data file if you need to/can using the Alter Database .. modify file command that you'll find in Books On Line or online. If you have multiple physical drives/drive arrays you can increase your performance by splitting your Logfile off, your indexes off from your data, and by moving your TEMPDB to multiple files if you have multiple processors and your system does a lot of work in TEMPDB.I just went through this very thing, and yes I was able to add more datafiles for TEMPDB without having to bring down my server. The key that I uncovered (after assuming for a very long time that it wouldn't work based on history) is that you need to create all of the data files with exactly the same size. Apparently the engine behind the scenes first tries to find the largest file, and if it finds one that is "largest" will use that one all of the time. But if there are multiples that are the same size then it appropriately just iterates them for each subsequent process call.According to what I read you should have 1 datafile for each of the processors in your system. The person that instructed me had a hyper-threading system which shows 2 logical processes for each chip and thus I, like her, setup 8 datafiles for our 4 processor machine. The server I did this on has very heave TEMPDB use by the application, and I was immediately able to see I/O shifting among the drives, that previously had been pounding the drive which contained the only TEMPDB data file. The following article describes everything: http://support.microsoft.com/kb/328551Hope it helps,DaltonBlessings aren't so much a matter of "if they come" but "are you noticing them." |
|
|
|
|
|