SQL VMware SAN Disk Alignment


I am about to build a SQL 2005 Server (on Server 2008 R2) as a VMware Virtual Machine. The database data files will be stored on raid 5 lun and log files will be stored on raid 10 lun using vmdk's. The san in question is an EMC CX4-120.

I have been reading best practice disk alignment articles all day and i am getting a little confused. Anyway, I have found this article "http://msdn.microsoft.com/en-us/library/dd758814.aspx" to be of help and have decided that it will be important for me to follow its recomendation on "Essential Correlations: Partition Offset, File Allocation Unit Size, and Stripe Unit Size"

It states that "There are two correlations which when satisfied are a fundamental precondition for optimal disk I/O performance. The results of the following calculations must result in an integer value:

Partition_Offset ÷ Stripe_Unit_Size

Stripe_Unit_Size ÷ File_Allocation_Unit_Size"

I am thinking of setting the Partition_Offset of both luns to 64 KB or should i leave it at the Server 2008 default of 1024 KB? I am thinking of setting each NTFS drive File_Allocation_Unit_Size to 64KB as well, is this recomended? My last question is what is Stripe_Unit_Size and what should i be setting it to?? I dont really understand what this is.


Who is Participating?
jakethecatukConnect With a Mentor Commented:
Ah...I've been waiting for this question to come up.  It's one of those questions that will cause a lot debate and you may not get the right answer.

Anyway, whatever stripe size and partition offset you go with WILL HAVE TO BE ONE that is recognised and supported by VMWare.  Your Windows 2008 Server will be writing directly to a file which will reside inside the VMWare File System and not directly to raw disk.

Take a look in this document from VMWare - http://www.vmware.com/pdf/Perf_Best_Practices_vSphere4.0.pdf (page 26).

However, if you are going to use RDM (raw device mappings) for your SQL, then you will need to consider the recomendations from Microsoft.

Hope that helps you a little...
coolsport00Connect With a Mentor Commented:
Interesting how we have the same identical SAN :)

To help answer your question, I believe this MS KB explains it a bit more:

I suggest reconfiguring your offset to 64KB.

For more on stripe unit sizing, see these articles:
Understanding SUS - http://publib.boulder.ibm.com/infocenter/eserver/v1r2/index.jsp?topic=/diricinfo/fqy0_cstripe_copy.html
Fine-tuning SUS - http://publib.boulder.ibm.com/infocenter/eserver/v1r2/index.jsp?topic=/diricinfo/fqy0_csetstrp.html

I also think it depends on the DB sizes...it just may not matter too much if you don't have high I/O on your DBs, but yes...you do want best practices for disk performance.

Hope this helps.

DavidConnect With a Mentor PresidentCommented:
With an external RAID subsystem, whether SAN-connected, or DAS, you need to optimize your client computers so that the I/O requests match the native block sizes of the RAID.  

From the 10,000 foot view, let's say that the EMC's internal disk config for the LUNs that have been allocated are RAID10 with a stripe size of 64KB/disk.  Let's also say that you are given a RAIDS (equivalent to RAID5 in EMC talk) with stripe size of 64KB/disk, and it is configured as a 4+1.   So the native I/O size of the RAID10 is 128KB, the other is 256.

Now, here is the problem.   If VMWARE wants to write to disk at I/O sizes of 512KB, then VMWARE is going to be generating a costly 4 I/Os on the RAID10, and 2 I/Os on the RAIDS.   This is grossly inefficient.

The biggest bottleneck you will ALWAYS have is the physical disk drive. You must minimize the number of I/O requests and make the I/O size match what the hardware can most efficiently provide.  These tuning docs are appropriate for JBOD, not an external RAID.   You must start by asking your storage admin about provisioning your slice for large block I/O, and then make everything, starting at the VMWARE I/O request size match it.  

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.