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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.

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.  

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.