Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 seperate drive for tempdb
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

13 Posts

Posted - 05/06/2006 :  05:56:32  Show Profile  Send ashwinreddy.c a Yahoo! Message  Reply with Quote
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 drive

Any thoughts.


Ashwin Reddy

Constraint Violating Yak Guru

314 Posts

Posted - 05/09/2006 :  13:32:36  Show Profile  Reply with Quote
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:

Hope it helps,

Blessings aren't so much a matter of "if they come" but "are you noticing them."
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000