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?
Best Answer
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
tempdband indexes at this sort of scale is pointless.