15

I'm building a SQL Server with 48 GB RAM, 1 CPU, & 8 SATA III (6GB/s) SSD drives (128 GB Crucial m4) and an LSI MegaRAID controller (SAS 9265-8i). I expect the typical work load to be mostly reads. There will be some periods of heavier write activity (hourly data syncs w/ 3rd party data providers - nightly backups), but the I suspect the typical read/write ratio is about 90% reads/10% writes.

Option 1:
Logical Drive C: - RAID 1 (2 physical drives) - OS
Logical Drive D: - RAID 10 (6 physical drives) - DB files/logs/tempdb/backups?

OR

Option 2:
Logical Drive C: - RAID 1 (2 physical drives) - OS
Logical Drive D: - RAID 1 (2 physical drives) - Db Files
Logical Drive E: - RAID 1 (2 physical drives) - log files/backups?
Logical Drive F: - RAID 1 (2 physical drives) - tempdb

OR

Option 3:
Other suggestions?

I'm thinking option 1 would give me better performance, since all DB activity would be striped across 3 drives (and mirrored across the other 3 in the array), although option 2 seems to mimic conventional wisdom (which appears to apply more to mechanical drives than SSDs). It seems like Stack Overflow has gone with option 1.

I'm guessing with SSD's it's OK to put every thing on a single logical drive since your server is probably more CPU constrained instead of I/O constrained at that point?

Another question I have is where should I place the nightly backups? We don't want backups slowing down the rest of SQL server, and I'm guessing writing the backups the same location as the logs is a good practice because the read/write behavior in both those cases is sequential writes.

Improve this question
1
  • I once held the belief that there was benefit to logical distribution, but after some fairly extensive research (Michael may still have some of it), we came to the conclusion that logical distribution @ the target level didn't improve performance. So if we were talking physical (spinning) disk, and you wanted 8 data files so as to take advantage of core affinity, it didn't matter if they were 8 files on a single logical volume (on the same physical disk) or if you cut 8 logical partitions for those 8 files (on the same physical disk). I think you'll find it similar logically cutting your SSD
    – Eric Higgins
    Commented Mar 2, 2012 at 19:19

3 Answers 3

Reset to default
9

Conventional wisdom about RAID doesn't apply well to SSDs. They don't really need striping (RAID0). They are prone to failures by-design, but RAID-1 is usually not the right answer for SSD for two reasons: a) is wasteful, halves the capacity of the SSD array (and they are pricey) and 2) SSDs failure characteristics leads towards both drives in the mirror to fail at very close intervals (ie. correlated failures) and thus render the entire array useless. See Differential RAID: Rethinking RAID for SSD Reliability for a lengthier discussion. Some have recommended using Raid-6 for SSDs.

Additionally, the conventional wisdom of SQL Server file layout doesn't apply to SSDs. I would recommend you watch SQL on SSDs: Hot and Crazy Love and go over the benchmark links in this answer.

Improve this answer
1
  • Good point, with RAID 10, I'm probably more vulnerable to correlated failures. Thanks for the Differential RAID link.
    – Robbie
    Commented Mar 3, 2012 at 7:14
8

The standard approach of separating the random IO patterns for data from the sequential of logs simply doesn't apply on SSDs, so I'd choose your option 1 with caveats:

  • Backups MUST be to a separate machine. There is little point in having backups that you can't access in the event of the server going up in smoke.
  • There is some value in separating the data and log drives such that a failure of the data array would allow you to take a tail of log backup.
  • Be mindful that you don't have a hot spare.
  • Be mindful that you have consumer level drives. Don't assume they will be as reliable as the enterprise equivalents at multiple of the cost.
  • Watch the entertaining and very informative SQL on SSDs: Hot and Crazy Love by Brent Ozar.

The issue of separating logs from data where SSDs are used is a matter of RPO (recovery point objective) for the system, rather than performance. If the RPO is defined in minutes go with one shared array and take log backups every [RPO] minutes. If the RPO is defined in seconds go with separate arrays.

To be honest, if the RPO was tight I'd keep SSDs for the data array and use a mirrored pair of expensive (enterprise) reliable spinners for the log.

Improve this answer
2
  • The backups are also copied to a separate machine. They are created on the local machine so I can use SQL Compare/Data Compare and revert changes in the case of regression/user error.
    – Robbie
    Commented Mar 3, 2012 at 6:02
  • Also, the RPO is defined in minutes (I think even in the 10s of minutes would be acceptable for my scenario to be completely honest). The Hot & Crazy Love post does have me thinking about correlated failures. In my limited experience, I've had more motherboard failures & total system failures (power supply/power surge fries everything) then drive failures, so I'm still trying to determine what the most cost effective level of paranoia/prevention would be.
    – Robbie
    Commented Mar 3, 2012 at 7:04
1

You should go with option 2 as follow:

Logical Drive - RAID 1 (2 physical drives)
 1 partition C: OS
 2 partition D: backups / log files
Logical Drive E: - RAID 1 (2 physical drives) - DATA files
Logical Drive F: - RAID 1 (2 physical drives) - INDEX files
Logical Drive G: - RAID 1 (2 physical drives) - tempdb

By separating your data and your indexes in 2 different data files which are then stored in 2 different physical logical drives, you would gain a huge increase in disk io simply because when you query, you would have one disk spinning for your table data and another spinning for your index at the same time.

Leave tempdb separated from your backups as well because a lot of stuff happens in there too. Don't mix up your backups and your data file. even though backups are not done daily, when they occur they take a huge hit on your IO. based on your business and usage of your database, some people or a LOT of people might complain during backup time.

Hope this helps

Improve this answer
3
  • 6
    Nonsense. These are SSDs, not spinners.
    – Mark Storey-Smith
    Commented Mar 2, 2012 at 19:03
  • not nonsense even with sdd there's a number of performance reached that way, although not as much.
    – Nicolas de Fontenay
    Commented Mar 2, 2012 at 19:04
  • 2
    Even with spinners the separation of data from indexes is of no value until your playing in SQLCAT territory. The case for separating tempdb is also never clear cut and very very rarely applies with such a small number of disks.
    – Mark Storey-Smith
    Commented Mar 2, 2012 at 19:24

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