1

At my company we are in the process of setting up a new SQL Server 2012 box. We have 6 physical drives with 3,000 gigs each. We are considering two different RAID configurations and are wondering which would be faster:

With 6 physical drives with 3000 gigs each:

Option 1: One big RAID 10 with three partitions. One partition for OS, one for data, and one for transaction logs (the partitions would be to protect against unconstrained growth).

Option 2: One RAID 1 using two drives with a partition for the OS and a partition for the transaction logs and a second RAID 10 using the four remaining drives for the data

longneck
  • 23,082
  • 4
  • 52
  • 86
programmer
  • 155
  • 7
  • 2
    You got 6 slow discs totally unsuitable for databases and try to make a dedicated server out of that? Note - 3000g each means max. 7200 RPM and 3.5" - there is no 3000gb 10g SAS disc that I know of and no larger than 1000gb Velociraptor (which is the only 10k SATA). This will be slow, regardless how you make it - whoever bought it went for "size cheap" over "suitable for database". – TomTom Jan 15 '14 at 19:09

3 Answers3

4

Assuming all spinning-platter disks and no flash disks, then the traditional "correct" layout would be option 2 because it isolates the IOPS for the transaction logs from the database files. This is the right decision in most use cases. There are workloads that do not benefit from this layout, but almost no real-world workloads that suffer under it.

If you have a large write cache (1 GB or greater) on your RAID controller (and you should if you're serious about performance) then layout has even less of an impact. In this case, pick option 1.

Also, if you choose option 1, don't make separate volumes for the logs and data. The only thing separate volumes on the same physical media gets you is headaches when you realized you picked the wrong sizes and it's difficult to change now.

longneck
  • 23,082
  • 4
  • 52
  • 86
0

In theory, RAID 10 performs faster because the amount of data physically read/written to single disk is 1/3 (in your case) in comaprison to RAID 1 configuration. Besides, it doesn't make much sense to have 4 drives in RAID 1.

Anyway, you should test both cases in lab environment to make well-grounded decision.

gevial
  • 1,324
  • 9
  • 13
0

As mentioned before, a traditional DBA would try to keep the database IO as far as possible from the log IO. To be exact, that depends on your database configuration (and usage). I would recommend to go with Option #2 - and (if possible) add an independent volume for the system. 3t Hard drives are not the fastest ones at random IO, so keeping the system (and pagefile) away from the database array would be better.

bjoster
  • 4,805
  • 5
  • 25
  • 33