SAN Performance and SQL server

The use of SANs in today network environment is almost common place, but is the performance requirements of the systems using those SANs considered in the design on the SAN setup.

A typical problem I find is people are not considering how the SAN should be setup to get the best performance, primarily for DAX we are talking about the considerations for SQL server, regardless if SQL is visualized or physical.

Lets consider a simple SAN setup. We have a SAN with 8 physical disks, and we want to use RAID 10 (which is the recommended for SQL data storage). What I typically find is customers have setup the SAN to use all the disks in a simple drive group (RAID group). So for the above example that would be 4 drives in the stripe and 4 in the mirror. Then they create LUNs (or virtual drives) for the SQL data, TempDb and logs files and configure SQL to use the different drives to store the data. The belief is because the setup guidelines have said how important it is to separate the storage for the data and logs to separate drives that they are doing the correct thing. But let us look at what this looks like on the SAN.

RAID Array

Above we can see the 8 disks and the 4 disks in the data stripe. When we create the LUNs or virtual disks the system will normally use all the drives in the stripe to maximize performance, with the effective height of the drive varying depending on the size of the allocation required.

As you can see the data and logs are essentially on the same drive, better than a single physical disk as there is a stripe of four disks, but still the performance impact of this will affect your SQL servers ability to handle data, which will directly impact on your DAX environment.

The best way to know if you are having performance issues is to look at your average disk queue length for each drive. A value less than twice the number of drives in the stripe is considered good. So for the above example of 4 disks in the stripe I would want to see normal values on the average disk queue length each drive to be no more than 8. If you are seeing higher numbers than 8 on a regular basis or if this number is a lot higher during a particular process you are testing in DAX then your SQL server data IO is struggling to handle the load and you need to review your drives and SAN setup.

A better solution to reduce your SQL IO load using the same hardware as above would be to put 6 drives in a RAID 10 configuration, and then the other 2 drives in a RAID 1 array. This will significantly improve your SQL performance and move future performance tuning activities from the SAN hard drive configuration to fine tuning SQL indexes and DAX code.

Final note: Most SANs are used to handle more than DAX SQL data and logs, typically storing other virtual drives for other systems and general company files. Any other LUNs or virtual disks allocated to the same array as the SQL data should be reviewed to determine how often it will be accessed and what kind of IO load it will put onto the system, as these other factors can also have the same effect and the SQL log of killing you SQL server performance.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s