RAID10 vs. RAID5 for data files

We are trying to evaluate the potential performance improvements that could be gained by switching an existing applications .mdf files to a RAID10 LUN vs. the existing RAID5 LUN they are living on right now. The transaction log already lives on a RAID10 LUN.

So the question is, if we move data files to RAID10 would the end user see performance improvements in the GUI? or would it be just faster writes to the data files when a checkpoint occurs and the changes get written to the data file but no improvement would be seen at the end user level?

There are two types of activity to consider here: reads and writes.

As you correctly point out, MDF/NDF writes are done through the checkpoint process, and users shouldn't have their transaction time affected.

Reads happen when the data that is needed is not yet in RAM (in the buffer cache). So ideally, user activity shouldn't be affected often. But when it is, the data can be fetched quite quickly whether RAID 10 or 5. The pain point for RAID 5 is with writes not reads.

But if you lose a disk - what then?

With RAID 10 there's no loss of performance. When a replacement disk is put in, the mirror is copied over it. With RAID 5, every read requires some maths to be done. And when a new disk comes, it has to read a lot and write a lot.

But RAID 5 is cheaper. 1TB of disk will get you 800GB of space (depending on your setup), but RAID 10 will only get you 500GB. Disk activity is slightly more expensive though.

It's "fine" to put data files (not logs) on RAID 5. But if disk is cheap enough, RAID 10 is better.

StackExchange Database Administrators Q#96256, answer score: 6