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.

I've got 5 databases, each 300GB, currently on a RAID 5 array consisting of 5 drives. All the databases are used heavily, at the same time, so drive speed is an issue.

Would I see better performance if I got rid of the RAID 5 configuration and just put each database on a separate drive? The redundancy provided by RAID 5 is not necessary due to mirroring elsewhere.

Will the server then be able to perform reads / writes to different databases drives in parallel? More so at least than when it's in RAID?

This is all on Windows 2003 / SQL 2008.

share|improve this question
    
Is there any way of increasing the spindle count? –  Matt Simmons Apr 28 '10 at 0:04
1  
I could add more drives. ;) –  Mr. Flibble Apr 28 '10 at 0:36
    
Define "used". Are we talking about mostly reads, mostly writes or all the above? This can be a factor in choosing a RAID level. –  John Gardeniers Apr 28 '10 at 1:09
    
@Mr. Flibble: that was hilarious in it's simplicity. +1 for you. –  MattB Apr 28 '10 at 2:51
    
@John Gardeniers. Reads and writes are split 50/50, but I need better read performance than write performance. –  Mr. Flibble Apr 28 '10 at 9:38

2 Answers 2

up vote 7 down vote accepted

Having the databases on different spindles will most likely perform much faster if they are in use at the same time. If they experience busy periods that are offset from each other though a single RAID array will perform faster as a single database's reads will be spread over multiple drives (which it never will if it is on a single drive, of course).

RAID5 is often a performance bottle-neck for databases due to its pour write performance. If your array is a straight 5-drive array then every block written to disk is likely to result in three reads followed by the parity calc followed by two writes (the updated block and the updated parity block) and every pages that is updated in your DB will result in at least two block writes: one to the transaction log and one to the data file. Things will be a little better if you are running 4-drives-plus-hot-spare, but not much. This is why RAID1+0 is usually used instead of RAID5 for database work (or sometimes RAID1E or similar which, where available, perform similarly better).

The fact that everything gets written to the transaction log as well as the datafile is an important fact. You can speed up insert/update operations massively by having the log file(s) and data file(s) on different drives (and so on a different set of heads). You would probably be better off arranging the databases around the five drives such that each has its log and data on different drives. How you arrange this will depend on the expected activity of each database - you want to avoid two databases that will be busy at the same time being on the same drives if possible.

Another option would be to have two separate RAID1 arrays - one for all the log files and one for the data files. I suspect you will see noticeably better performance from this tan from having each DB (both data+log) on its own single drive. Or if you really don't care about redundancy (for example if your mirrors are maintained using a replication/cluster arrangement that means you'll lose very few transactions if the master DBs die) you could have a pair of RAID0s instead for a bit of extra speed.

If a little money for three drives is not a problem and your machine has room for more drives, you could have a pair of RAID10 arrays (one for data, one for logs, 8 drives in total) then you have your redundancy without the RAID5 write performance problem, you have the write performance boost from having the logs and data on separate drives, and the potential read+write boost often seen with RAID10 (4-drive RAID10 with a good controller tends to perform similarly to 2-drive RAID0). Some of our production databases are on such an arrangement and I can tell you that they fly along compared to other arrangements I've tested. If you have a hardware controller supporting RAID1E (it goes by different names on different controllers, unfortunately) then you could do the same with two 3 drive RAID1E arrays, needing only 6 drives.

share|improve this answer
    
That's possibly the best answer I've ever received on a forum since I can remember. Thanks David. –  Mr. Flibble Apr 28 '10 at 0:35
1  
In addition to what @David said, which is spot on, you'll want to look at this as well : msdn.microsoft.com/en-us/library/dd758814.aspx –  Holocryptic Apr 28 '10 at 1:34

RAID 5 has really poor write performance. You are probably better off with RAID 10 for heavily used databases.

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.