I kept note of typical Best Practices and like to incorporate Storage Vendor Best Practices and Microsoft Best Practices into my SQL designs.  This article relates to the disk subsystem more than it relates to virtual disks.  Some of it I’m sure you might see familiar…

First, there has been recent changes in what is recommended for newer Operating Systems (Server 2008 and higher) that state 1024 disk partition offset.  From what I can tell, the Storage Vendors seem to be slightly different (EMC, NetAPP, DELL, Hitachi, etc).

So, as we know, partition alignment is different than Allocation Unit size. Allocation Unit size is important to match your application write behavior, to minimize small write chunks on the partition. Alignment offset is designed to avoid the MBR and make sure the server writes full stripes matching the storage system.

I’ll first explain the offset setting. This should shed some light:

  1. Why it is important?
  2. Why we do it?
  3. Why we set it to 64K?
  4. What References can I use?

 

From the EMC Storage Operations Guide:

The purpose of aligning partitions with a 64k offset is that 64k (128 sectors) is the Clariion default strip element size. A strip element is the size of the strip that the array writes to the physical disks, in the Raid group. Windows systems write the master boot record (MBR) in the first 512 bytes. So by default the first write will occur on the 513th byte, and will spill over into the second stripe element by 512 bytes (see Figure 1).         

Figure 1 – Default Partition Layout

clip_image001[6]

Setting the offset tells Windows to skip the entire first stripe element, and begin writing on the second stripe. This keeps writes from spanning multiple strips (see Figure 2).

Figure 2 – Partition with 64 KB Offset

clip_image002[6]

Since each time a stripe element is written to, a single disk IO is used. If you span multiple stripes then each write takes 2 IOs per disk. On a storage array this can quickly add up, and degraded performance.

What they mentioned in the guide has been a long standing best practice implemented by EMC for as long as I can remember. It is based on a known hardware performance number (128 sectors), and measurable performance problem (stripe crossing). Since the goal is only to start writing the partition on or after sector 129, any value divisible evenly by 64K will work. The larger the number (i.e. Microsoft’s recommendation of 1024k) the more wasted strips we have in the beginning.

image

As you can see 64 KB or 1024 KB wouldn’t impact performance. This being said EMC is now recommending to increase from 64 KB to 1024 KB to match Microsoft standards. BUT 64 KB does not impact performance. Here is the quote from EMC knowledge base iPrimus article.

“Previously EMC has stated to align partitions at sector 128 by using align=64 .  Using align=64  is perfectly fine for EMC Storage and for EMC standards, but to remain in-line with Microsoft standards please use align=1024.  Please see Microsoft technet article 929491  , which details that you should align at sector 2048 using align=1024.”

SQL (2008 – 2012) Best Practices:

http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx

SQL 2005 Best Practices:

http://technet.microsoft.com/en-us/library/cc966412.aspx

MS also says to verify using this calculation in this article also referenced below http://support.microsoft.com/kb/929491 :

To verify that an existing partition is aligned, divide the size of the stripe unit by the starting offset of the RAID disk group. Use the following syntax:

((Partition offset) * (Disk sector size)) / (Stripe unit size)

Here is a KB article that references slow disk performance that I’ve held onto for Server 2000 and 2003:

http://support.microsoft.com/kb/929491

Category: Cloud