Temp DB, where to put

[MaffewW]

we're building 2 x new sql2000 servers and were wondering your thoughts on tempdb

we dont have the luxury of being its own array, storage space is probably more important than total optimum performance at this time hence:

we've got 8 x 72 gb drives

and need to go
c: (OS and programme files) raid 1, 2 x drives
e: (data), raid 5 4 x drives
f: (logs), raid 1, 2 drives

it would appear tempdb data and log files would be best located on the OS drive for us for performance/space? has anyone any thoughts on this. I also read 1 physical file per processor, anyone got this this set up?

Using SQL2k Standard, SP4, Compaq, Dual Core Quad Processors, 4 x gig ram

your tempdb set up thoughts much appreciated

thanks
Matt

Matt

Brighton, UK

 

[ptheriault]

1 physical file per processor, anyone got this this set up?

Yes, and it has helped with performance. IF you can't put your tempdb on it's own drive then I would put it on the log drive. you'll get better performance out of the raid 10.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLDenis]

>>1 physical file per processor

that should be 1 physical file per Core or CPU, hyperthreading doesn't count. In your case 2*4=8 files

However this is for 2005, where I have seen improvements in 2000 not that much because tempdb is not used as much as in SQL 2005

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[MaffewW]

ah thanks for your reply

though raid1 not 10

The problem we might have with tempdb on the log drive is space

we definately cant put it on its own drive, well we could in theory which would lose us 72gig of storage which we cant really trade off for the performance.

I guess what I'm asking is, is the OS drive (R1) better or the data drive (R5) and is it best practise to put the data and log on the same one.

And re the multiple files (sorry should have said Win2003 standard) it would appear we're not going to benefit from 8 physical files rather than 4 on this set up

cheers

Matt

Brighton, UK

 

[SQLDenis]

why 4? you have a dual quad core right? This means 2 CPUs with 4 cores each

from Storage Top 10 Best Practices (

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx)

Lining up the number of data files with CPU’s has scalability advantages for allocation intensive workloads. 

• It is recommended to have .25 to 1 data files (per filegroup) for each CPU on the host server. 
 
• This is especially true for TEMPDB where the recommendation is 1 data file per CPU.
 
• Dual core counts as 2 CPUs; logical procs (hyperthreading) do not.

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions