2

We have a new Dell R710 server that came with the following storage configuration:

8 x 146GB SAS 10k 6Gbps disks
1 x Perc H700 Integrated Controller (2 x 4 disks - 2 ports each supporting 4 disks)

  1. What would be the optimal configuration if we were just after performance?

  2. What would be the optimal configuration if we were after performance but wanted data resilience.

  3. As per 2 above but with a hot standby disk?

We plan to run Windows 2008 R2 and SQL Server 2008 R2.

Maximising storage capacity isn't a prime concern.

Having scanned Server Fault and other resources, my initial thoughts were:

2 x disks in RAID 1 for OS
4 x disks in RAID 10 for data files
2 x disks in RAID 1 for transaction logs

I'd also thought about putting the tempdb on either the transaction log disks or the OS disks?

Improve this question
2
  • We've played with our disk configuration quite a bit and found that it really doesn't make as big a difference as one would expect. SQL's caching is VERY good. Oh, a side note... We found that with more than 8 processors that we encounter serious latching/performance issues. Our current machine which runs great with 8 cores becomes unusable above that. Sub-second queries can take several minutes, etc. So, just a warning that you;ll need to test with your data, there's no one "best" way...
    – Brian Knoblauch
    Jun 10, 2010 at 13:17
  • Latching can be handled, depending on the specific bottlenecks, by 1) adding additional file groups (lowers contention on allocation structures), 2) partitioning the data (especially with IDENTITY columns: hash the identity and use that for the partition function, so inserts get distributed), 3) pad rows so that fewer rows can fit on one page
    – Mark Sowul
    Nov 26, 2013 at 15:09

3 Answers 3

Reset to default
0

My guess would be to:

  1. For performance only: RAID 0 (this would be seen as only 1 disk having a total storage space of 1168GB;

  2. For performance, but with data resilience: RAID 5+0 (Min. 6 disks required), or you could also opt for a RAID 1+0 configuration, making 4 pairs of RAID 1, and making an array of 4x2 disks in RAID 0;

                                     RAID 0
                                   ║
   ╔═════════════════╦═════════════╩═══════════╦══════════════════════╗
   ║                 ║                         ║                      ║
 RAID 1           RAID 1                    RAID 1                 RAID 1 
(2x146GB)        (2x146GB)                 (2x146GB)              (2x146GB)

For a total storage space of 584GB that will be seen as one only disk because of the RAID 0 stripping feature.

In 3, if you prefer to have a hot spare disk, than RAID 0+5 could become handy.

EDIT #1

After having read your suggestion for a 2-4-2 disk configuration, I guess this wouldn't be needed. I would personally favor a RAID 1+0 configuration as above-illustrated. I don't think the gain will be that much, since you have 8 disks of the same speed. This would have been different if you would have had two disks slower than the 6 others, then I would have considered using those two for the OS. But as of the current situation, I guess the gain would be negligeable.

Furthermore, it is much negigeable if your DBAs or programmers create the right indexes for the right data within their database. Having four disks that fast stripping together, and assuring the resilience through RAID 1, I believe you won't have trouble this way.

I hope this helps! =)

Does this help?

Improve this answer
4
  • It's a pleasure! I'm glad if I helped anyhow. =)
    – Will Marcouiller
    Jun 10, 2010 at 14:53
  • We went for a 2 x RAID 10 (4 disks each) config.
    – Kev
    Jun 22, 2010 at 14:19
  • I'm sorry, but this is very wrong. 1) RAID 0 is highly dangerous and that should be noted. The loss of any one drive loses everything and this is hardly a viable option. 2) It is highly recommended to split the data files and the transaction logs (support.microsoft.com/kb/2033523/en-us). @Kev's setup is pretty much optimal (assuming log files and OS are shared, as opposed to log files and data files shared). Indexing does not change the importance of a proper I/O setup.
    – Mark Sowul
    Nov 26, 2013 at 15:06
  • Finally, RAID 5 (etc) is somewhat better at reading than RAID 1+0, whereas the latter is significantly better at writing
    – Mark Sowul
    Nov 26, 2013 at 15:07
1

I about the same config - you can find some answers here: 1 logical drive vs 2 logical drivers on 1 raid controller to separate data from log files?

Improve this answer
1

I know this is an old question but it's still relevant.

First, some notes on performance. As you mention, there are three "pieces": data files, log files, and tempdb.

1) data files: generally random I/O. If it's a read-heavy workload, RAID 5 is an okay choice. If there are significant writes, then favor RAID 1+0

2) log files: generally sequential writes. RAID 1+0 is optimal, but probably overkill given your constraints, which I'll shortly get to. It is best to separate them for performance and safety reasons(http://support.microsoft.com/kb/2033523/en-us)

3) tempdb: often a significant bottleneck, and requires lots of random read/writes. Does not need to be "safe" because SQL recreates it on startup.

One option, as you did use, is to set up two sets of RAID 1+0. Data files can go on one, log files and OS can go on the other. Whether tempdb is better put on the first or the second depends on your workload. If write-heavy, then put it with the data (so the log files don't get bottlenecked), but if the log files are not a concern then tempdb is better put with them.

This would also be a safe option because many drives need to fail for you to lose data.

Another option would be RAID 1 for the log files (2 disks). Then you can use RAID 1+0 / RAID 5 for the data files (3-4 disks). You can then dedicate the other disks to tempdb. You don't need to use RAID 0, you can just create multiple files for tempdb and assign one to each disk.

If you want to add a hot standby, you're in a tricky spot because of the number of drives: RAID 1+0 requires an even number. Note that RAID 5 has a bit of a risk of data loss during the rebuild because it's a lengthy process and because you need to calculate the parity.

EDIT I did some more reading, and RAID 5 is not really recommended anymore - it's not really that safe because of the rebuild. RAID 1+0 is really the way to go. See, for example: http://en.community.dell.com/dell-groups/dtcmedia/m/mediagallery/19861480/download.aspx (pp. 9-10 in the PDF). One (of several) sources: http://www.reddit.com/r/sysadmin/comments/ydi6i/dell_raid_5_is_no_longer_recommended_for_any/.

Universal notes

  1. keep your log files from getting fragmented. Make sure you size them reasonably with a large auto-growth factor
    http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/

  2. make sure your partitions are aligned properly.

There are two correlations which when satisfied are a fundamental precondition for optimal disk I/O performance. The results of the following calculations must result in an integer value:

Partition_Offset ÷ Stripe_Unit_Size

Stripe_Unit_Size ÷ File_Allocation_Unit_Size
http://technet.microsoft.com/en-us/library/dd758814(v=sql.100).aspx

  1. if possible, set your cluster sizes to 64 KB
Improve this answer
2

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .