SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

ashwinreddy.c
Starting Member

India
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.

Thanks

Regards
Ashwin Reddy
SQLServer DBA

druer
Constraint Violating Yak Guru

USA
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: http://support.microsoft.com/kb/328551



Hope it helps,
Dalton

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  
 New 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.06 seconds. Powered By: Snitz Forums 2000