I have a new server box intended as a dedicated SQL Server 2012 server. With the new box, I am now faced with a question on what should be the optimal number of physical disk I would configure on each RAID 10 array that I would set. Having the answer to the question would greatly help me in deciding as to how many partitions I will create to support the separation of SQL Server’s data, transaction log and tempdb.
To come up with an answer, I conducted some simple SQLIO test to determine which array configuration is best suited for the read-query-intensive application that I am about to set up. The test should be able to answer the following:
- Is a 6-disk RAID 1+0 array better than a 4-disk RAID 1+0 array?
- Is an 8-disk RAID 1+0 array better than a 6-disk RAID 1+0 array?
The Test
I carefully devised the following tests:
- Run an SQLIO READ test using a 10GB file on a RAID 1+0 array with either 4, 6, 8 and 10 disks.
- Run an SQLIO WRITE test using the same configurations as the READ test.
- Each SQLIO test case should be run 5 times and get the average from the results.
- Prior to each test, I have to destroy the RAID configuration using the server’s RAID utilities, set it up with full initialization, create and format the partition.
The Results
The following are numbers produced by SQLIO:
READ Test
|
Disks |
IOs/sec |
MBs/sec |
Latency (ms) |
||
|
Min |
Ave |
Max |
|||
|
4 |
1,323.70 |
10.34 |
0 |
23 |
544 |
|
6 |
1,580.82 |
12.35 |
0 |
19 |
562 |
|
8 |
1,817.43 |
14.19 |
0 |
17 |
498 |
|
10 |
1,878.32 |
14.37 |
0 |
16 |
495 |
WRITE Test
|
Disks |
IOs/sec |
MBs/sec |
Latency (ms) |
||
|
Min |
Ave |
Max |
|||
|
4 |
715.82 |
5.59 |
0 |
44 |
152 |
|
8 |
1,197.28 |
9.35 |
0 |
26 |
199 |
My Conclusion
- I conducted the READ test first which influenced my decision to just instead run the full WRITE test on a 4 and 8 disk array.
- After the READ test, it seems to suggest that having 4 and 6 disks in an array don’t seem to have a significant difference in terms of performance between the two.
- Going beyond 8 disks, performance wouldn’t improve much
- With only 14 physical disks to utilize, I ended up with 2 RAID 1+0 arrays. One with 4 disks and the other with 8 disks.
Additional Information
- I only run each test once on a 6 and 10 disk arrays so I have not included the figures in the results.
- I tested the 10-disk array configuration just to confirm if there will be significant performance gain.
- Disks used were the ordinary 250GB SATA drives.
Caution
The results presented here do not, in any way, represent a recommendation. It merely shows based on my specific configuration. It may differ in other server configurations and I have no way to know the possible results of other test using a different server configuration. It is therefore highly recommended to conduct your own testing when you have a new server.
**************************************************
Mountain Province, Philippines
**************************************************
Toto Gamboa is a consultant specializing on databases, Microsoft SQL Server and software development operating in the Philippines. He is currently a member and one of the leaders of Philippine SQL Server Users Group, a Professional Association for SQL Server (PASS) chapter and is one of Microsoft’s MVP for SQL Server in the Philippines. You may reach him by sending an email to totogamboa@gmail.com
totogamboa.wordpress.com 