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?


  • 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? Commented Nov 20, 2013 at 21:04
  • 1
    Are all 10 databases consuming an equal amount of I/O? Are they all system-critical?
    – Hannah Vernon
    Commented Nov 20, 2013 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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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