RAID 10 array configuration for SQL Server

Question

I have a question regarding RAID 10 and SQL Server. My plan is to purchase an HP DL380 G9 with the 24 drive config. The question is how to break up the array.

The drives will be divided between the 3 bays to even out the traffic on the controller. Keeping in mind random IOPs are better with additional drives in the array, would be be better to:

1. Make 1 large RAID 10 array with logs, tempdb, and data split into separate logical drives. This would be 8-12 drives.

2. Make 2-3 smaller RAID 10 arrays and split up logs, tempdb, and data files by array. This setup would be 4 drives in each RAID 10 array.

The 8-12 drives would be better performing overall (our read/writes are probably 50/50). In scenario 2 would the controller serving 3 different logical drives take a performance hit?

We will use MS SQL Standard 2012 with the max memory (64GB + RAM for OS). Enterprise is probably not an option due to cost.

Answer 1

Based on what you've described I would go with your second option and here is why.

Creating just one array and slicing it up into several logical volumes won't be any different than just having one large logical volume when it comes to I/O since you are still sharing the same underlying drives that make up the arrays.

SQL server runs best if you can separate the I/O types (sequential/random). Log files are written sequentially and any more than one database log on them will change the sequential pattern to random slowing the write operations to the database. Data files are written randomly so you are fine to mix multiple database data files on the same drive array.

For option 2 I would put the log file of tempdb on one array, the log file of your database on another array and the data files for your database and tempdb on the last array.

If you can swing a some additional dollars at the hardware here are a few suggestions that could help you offload some of the I/O.

You can offload the tempdb from the storage array by adding some PCI drives like Fusion IO to place the data and log files on. You should be able to purchase the smallest size they offer unless your database will be doing things that will cause a lot of tempdb usage. You can add those drives from that third array back to the data array which will increase the IOPS available.

Buy more memory for the server. The 64GB limit only applies buffer pool. SQL server will use as much memory that the OS can support for everything else. Kendra Little from Brent Ozar Unlimited discusses the details in her blog post.

The final item would be to use the top end RAID controller that HP offers. You will get 1GB of on board memory to help with write caching to the disks. You should also be able to control the percentage of that memory that is used for read and write caching. You should set it to 100% write as SQL server can't really utilize any read caching on the controller.