February 24, 2009 at 3:29 pm
We have a situation where we have TEMPDB on the same disk as the user databases. Also, we have read the research where the consensus is to put TEMPDB on its own disk resource. In addition, it is recommended that there be one db file per cpu core with all the files the same size. This will be 4 db files for us, plus the log.
Unfortunately, we do not have an extra raid, mirrored disk to put TEMPDB on all by itself. We think our best candidate is our logical E:, which physically is on the same disk as logical C: There is plenty of room there for us on E:, no db files there, or txn logs, etc. However, what is the downside of sharing the same physical disk as C: (contention with O/S, pagefile, etc.)?
Thanks in advance to anyone willing to share their thoughts on this topic.
Regards,
C. Cortez
February 24, 2009 at 7:02 pm
We have a situation where we have TEMPDB on the same disk as the user databases. Also, we have read the research where the consensus is to put TEMPDB on its own disk resource. In addition, it is recommended that there be one db file per cpu core with all the files the same size. This will be 4 db files for us, plus the log.
Unfortunately, we do not have an extra raid, mirrored disk to put TEMPDB on all by itself. We think our best candidate is our logical E:, which physically is on the same disk as logical C: There is plenty of room there for us on E:, no db files there, or txn logs, etc. However, what is the downside of sharing the same physical disk as C: (contention with O/S, pagefile, etc.)?
Tempdb is supposed to be on it's own set of spindles to avoid synch/asynch collisions. I have put tempdb on C as well because of this very reason at a client. The downside is that when the o/s decides it wants IO, it yields to nothing and nobody. You may or may not experience anything, but it is worth mentioning. Try to remove all of the applications from C:\ and give it a shot. It will probably be better than on your data file drives.
As far as files go, yes, 4 equal files, with 1-3 set to not grow, and the 4th to autogrow. In order to leverage the round-robin effect of mapping temp tables, these files must be the same size at all times. Also make sure that if it's SQL 2000 you have the switch (can't remember it) or the correct service pack to take advantage. Give them plenty of room so they don't have to grow. SQL 2005 and up uses tempdb a ton; fire up perfmon and compare, it will amaze you.
Good luck.
February 25, 2009 at 8:42 am
I think Lee is talking about the trace flag 1118.
How implementing trace flag -T1118 reduces contention
http://support.microsoft.com/kb/328551
MJ
February 26, 2009 at 8:42 am
Lee / MJ -
Thank you both very much. Your advice is much appreciated.
Have a great day!
C. Cortez
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply