SQL 2K and EMC Clariion LUN Configuration

We have a SQL 2K database with an EMC Clariion FC4500 (SAN) as the disk subsystem. We are in the process of adding more drives. A couple of questions have poped up among our DB team relating to how to properly configure the LUNs on each RAID group to achieve optimal performance for SQL 2k. Specifically, is there a rule of thumb that anyone knows of, or follows, that dictates the correct number of LUNs to create on each raid group? For example, we currently have 4 LUNS created on a 5+1 (36GB Drive RAID 5) each containing one database file. Questions are being raised whether this is correct, as a matter of fact there are entities withing the group that insist having more than one LUN per RAID group dramatically affects performance negatively. This runs counter to what I was instructed to do by EMC. I'm pretty neutral on the matter and only want to have the system configured optimally. I cannot find any documentation specifically addressing this, which is why I'm turning to you all. Any help, or direction, you can provide will bring great Karma. Thanks, Tony

There is always a chance of head movement contention on the disk drives if you configure multiple LUNs per RAID group. Depending on how much cache you have in you disk subsystem this can become a real problem. As a general rule of thumb I try to avoid creating multiple LUNs on a single RAID group.

Thanks SQLJunkie! Your reply corroborates what we've learned from Microsoft and EMC. We've spent the last few days digging into this. We've spoken to Microsoft and EMC. Both have said that multiple luns on a raid group will degrade performance, because of disk thrashing. According to one MS Support tech, he believes that this can degrade performance up to 60% on some systems. Thanks again. - Tony.

The Clariion architecure has a few differences which you can benefit from. You'll still have the issue where load on one LUN can negatively affect perfomance on every other LUN in the RAID set, but the lower HBA queue depth that EMC sets should help offset that to some degree.

The major difference is the cache. On the Clariion, you can set the cache ratios. By default the ratio for RAID 5 is set to 60:40. Let's examine why. First, we'll assume a 1:1 ratio or reads to writes. Each read results in one read on the backend. Each write results in two reads and two writes on the backend. For the two comands, one read and one write, at total of three reads and two writes occur on the backend; hence the 60:40 ratio.

In the real world, you ratio may vary. For the average SQL server, it's closer to a 2:1 ratio of reads to writes. When you do the math this comes out 6:2 or a 75:25 split. On the Clariion, it is possible to tune the cache and offset some of the lost cache effect. You can get the exact ratios by using the perfmon physical disk counters, and doing the math from there.