2

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.

2

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.

  • Thanks the the explanation. At least 2 of the recommendations are workable. 128GB will be the total RAM. The controller is P440ar with 2GB FBWC. Fusion IO is probably going to be too expensive for this project, but might be a future enhancement. This will be a reporting/warehouse server so temp db will probably be the most used. I'm thinking some write intensive SSD's would be a decent compromise in performance if the tempdb files live there. – Fatty McButterpants Aug 31 '15 at 15:31
  • Your very welcome. If you can get SSDs for at least the tempdb log and data files you'll make a big jump in your capable IOPs. Also when you are buying the SSDs the larger you can buy the better performance you will get. Take a look at Brent Ozar's SSDs hot and crazy love post. It talks about the larger the drive the more on board controllers it will usually have. Here is a direct link to the video – Aaron Aug 31 '15 at 15:53
  • To finish the thought, 2.5" SSD's would be RAID1 for tempdb. Sort of a performance/cost compromise vs PCI SSD. The smaller SSD's are fairly reasonable $ from HP. – Fatty McButterpants Aug 31 '15 at 15:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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