Take the 2-minute tour ×
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.

I have upto 10 databases running on a single SQL Server instance on a single server.

I have read about best practice for storage and concluded the following...

1) Transaction Log on own RAID array (Raid 10) or at least on own physical disk

2) Data files(secondary filegroup) on own disk array (Raid 10 or 5)

3) Indexes on own filegroup (Raid 5)

4) Primary on Raid 1 or 5 (only system dbs)

5) Tempdb on Raid 5 or 10

So each of the above will be on separate physical disks?

Should I then have a separate disk for each secondary filegroup of each database? Or can they reside on the same disk? How would this impact on performance?

And similar again with each Primary filegroup - should each one be on its own disk? In this case 10 separate disks for each primary filegroup?

Thanks!

share|improve this question
1  
How big are these files? How big are you expecting them to grow? What is the throughput of the system? What's your budget? –  Greenstone Walker Nov 20 '13 at 21:04
1  
Are all 10 databases consuming an equal amount of I/O? Are they all system-critical? –  Max Vernon Nov 20 '13 at 21:39

1 Answer 1

I just tried quickly to find an answer because there is a lot here, but this is a common question. Didn't find quite what I wanted yet so a few thoughts to guide you:

First: It depends is the default answer here.

It really depends on what the performance characteristics are of your system. You should review what your waits and filestats look like and consider where your pain points are and build according to that. There are sizing concerns and performance concerns.

A few thoughts to guide you:

  • RAID 5 can be fine for data files unless you are doing some heavy writes and performance becomes a concern. So this isn't always good, but a place I feel safe about RAID 5, again your mileage really varies based on your performance needs.

  • Mirroring or RAID 10 is where I tend to like to go with log files and TempDB.

  • In some environments, TempDB needs to be isolated onto it's own first because it is a shared resource and used when queries spill over from memory to disk as well.. In some environments, log files need to be isolated and get better performance because of the insert activity.. In some environments, data files need the best IO (heavy read/reporting).

  • You need to look at Filestats and Waitstats and Perfmon and really see where your pains are.

Using multiple file groups is a great approach/answer for certain workloads. Separating TempDB is usually a best practice though I'd never put TempDB on RAID 5 typically.

And if the environment is pushing no IO? Having no IO waits? I've seen and placed environments all one one big happy set of drives and things have been fine. It really depends on a lot.

I'd spend some time profiling your workload. Look at the PAL tool and run some perfmon scripts on your existing environment. Look at your SQL Server File Statistics. Read about some best practices in SQL Server IO, and make informed decisions and monitor to see how they look as you go.

share|improve this answer
    
actually not a bad answer to refer to here either dba.stackexchange.com/questions/5485/… –  Mike Walsh Nov 20 '13 at 22:05
1  
+1 RAID 5 for tempdb is horrible. SSD? Now we're talking!! –  Max Vernon Nov 21 '13 at 0:22

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.