Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Setting up a box for SQL Server 2008, which would give the best performance (heavy OLTP)? The more drives in a RAID-10 array the better performance, but will losing 4 drives to dedicate them to the transaction logs give us more performance.

12-drives in RAID-10 plus one hot spare.

OR

8-drives in RAID-10 for database and 4-drives RAID-10 for transaction logs plus 2 hot spares (one for each array).

We have 14-drive slots to work with and it's an older PowerVault that doesn't support global hot spares.

share|improve this question
1  
Can you just setup the system and run a real test? That will give you far better results then general advice. –  Zoredache Nov 23 '11 at 19:40
    
Running SQLIO under both configurations might give you better information. microsoft.com/download/en/… –  jl. Nov 23 '11 at 20:26

4 Answers 4

up vote 5 down vote accepted

I would go 10/2. Logs are usally small and sequential, and your RAID controller should be able to queue them efficiently enough to write them onto 2 disks in raid 1 without disturbing the rest. If your RAID controller can't do this then forget about any split and just go 12 if you are in a hurry. If you've got time run some tests and see what works. Shame about the lack of global hot spares, but anyway it sounds like you've got a decent box to play around with.

share|improve this answer
    
Agreed on the 10/2 setup. I have a 6/2 setup myself and it works a whole lot better than the alternative, about a 30% performance boost. –  Tim Brigham Nov 23 '11 at 19:51
    
I would agree on the 10/2 setup, because of the IO characteristics mentioned elsewhere. Using postgres and pgbench, I got an average increase of around 5500-6k TPS when I moved the database logs to a separate array. –  Kendall Nov 23 '11 at 20:22

As far as my limited knowledge goes there are two main factors to consider

  1. The database file operations will generally be random so you would want to maximise your IOPS for that disk where as your transaction logs file operations will generally be sequential so you want good raw throughput

  2. You want to store your main database and transaction logs separately that way - if you lose your main database disks you can regen the DB from a backup + transaction logs, and if you just lose your transaction logs, you still have the main DB.

Of course its a lot more complicated that the summary above but thats the basics. Therefore I would say use two separate arrays and if you have the capacity to use RAID10 for both then great.

share|improve this answer

You should do pretty well with 12 drives in RAID-10 but make sure the server has decent amount of memory. The reasoning is if you have enough memory most of the data writes will be cached and many of writes will be consolidated in a single IO transaction after all when it comes to writing data to disk. This is especially true for OLTP database. The caching give you enough bandwidth for transaction log writes to the same drive. You can improve write performance even further by using hardware RAID controller with built-in cache protected by BBU in write-back mode.

Also note, that using 8+4 or 10+2 is good option as well, the only drawback that I can see with this is inefficient use of storage. Otherwise it is solid.

share|improve this answer

You must determine your workload first. And test, test, test (Can't stress that enough):

Firstly, the statement "the more disks in my RAID 10, the faster it is" isn't correct. This depends greatly on how your RAID system implements this RAID level (distribution of writes/reads/striping/buffers...I could go on).

Secondly, while it's always a great idea to store transaction logs in a different place (disk) than the DB, the effective gain depends on your workload, are there many, but small, writes, or are there few, but huge writes, what's the write/read ratio, how big are reads...etc. pp.

You say you're doing a lot of OLTP. From my understanding of this term, this means a lot of reads, not many writes (...does it? Confirm this first). In this case, it's fine to store the transaction logs in the same place - so I'd go for 12 drive.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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