Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I have a fairly large transactional database (100GB) with a lot of users. The database access is solely via stored procs that make heavy use of temp tables, table variables, cursors and other fun things.

I'm trying it out on a new box in an effort to boost the performance. The new box has 5 separate physical hard drives. I am struggling trying to come up with an optimal setup (mostly though ignorance, I am a c# dev normally). So far, I got the following:

Drive C: OS, SQL install, TempDB log
Drive D: Database data
Drive E: Database log
Drive F: TempDB data
Drive G: Databases indexes

Am I going about the right way? Any glaring mistakes?

share|improve this question
Since you didn't mention it, I'm guessing you haven't considered a RAID configuration for fault tolerance? And also I wouldn't put the tempdb log file on the OS partition. – Thomas Stringer Jun 21 '12 at 23:22
@Shark Each drive has fault tolerance. I should have said 5 logical hard drives, each of which is on a separate physical spindle. So you are saying that it's ok for tempdb data and log files to be on the same hard drive? – AngryHacker Jun 21 '12 at 23:26

1 Answer

up vote 6 down vote accepted

If there is room for more drives, order more drives. If there isn't room for more drives, order more drives... and an external enclosure.

I'm assuming this data has at least some value to the business and from your comment we can also infer that you're experiencing performance issues already. So, zero redundancy and single spindle performance isn't going to cut it.

If you absolutely have no other choice than to stick with these 5 drives, I'd probably go for a 4 disk RAID10 with a hot spare. Possibly, maybe, all 5 in RAID5. Separating tempdb and indexes at this sort of scale is pointless.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.