Some times we have this huge server, dedicated to SQL Server. It processes thousands or even millions of transactions a day. It should keep up because we really did buy the works, the hard drive and memory such load requires. And yet, it does not deliver what you paid for.

Well some people got curious. Not just me, but, including me. Here is a aggregation of these finding.

If basic partition(s) or dynamic volume(s) are created on a disk that is not track aligned, an I/O operation may cross, or straddle, disk track boundaries. If an I/O operation does straddle a track boundary, it can consume extra resources or cause additional work in the storage array, leading to performance loss.

What does this mean?

The fact is that under windows the MBR (Master Boot Record) takes up the first 63 sectors. So the first track starts one sector to early at the 64th sector which misaligns it with the underlying physical disk and results in serious performance degradation.

Properly aligning the partition on a disk or disk array is a fundamental best practice. Moreover this can have a great impact on a SQL Server. Since SQL Server is a fine tuned system the misaligned partition can cause slowdown up to 90%.

Correctly aligning partitions in your environment could, and most frequently, will cause performance increase from 10% to 30%. And it has even been experience improvement up to 50% on the read, write in the SQL database.

Disk Partition Alignment for SQL Server

The most important data structure on the disk is the Master Boot Record (MBR), which resides on the first sector of the disk. MBR contains the boot-loader and partition table. The partition table maintains starting and ending sector values, which in Windows are only 6 bits in length. Therefore, their maximum value is 63 due to this limited number of bits and the fact that sector enumeration begins at 1, not 0. Also, it may be easier to think of a disk as a sequence of blocks (rather than sectors) starting from address zero and incrementing until the end of the disk. Note that block enumeration begins at 0, not 1.

Windows creates partitions on cylinder boundaries and, by default, allocates the first 63 sectors as hidden sectors. With a physical disk that maintains 64 sectors per track, Windows always creates the partition starting at the 64th sector (block address 63), which misaligns it with the underlying physical disk and results in serious performance degradation. If we use the Windows default partition location (63), an I/O of 4,096 bytes (8 sectors/blocks) starting at the beginning of the partition will write one block to the last block of the first track and seven blocks to the start of the second track. This means the I/O will straddle the first and second tracks. This will require the storage array to reserve two cache slots for the data and will also require two flush I/O operations to the disk, which will impact the performance.

For illustration purposes, in Figure 2 the partition is broken into 4-KB blocks and arranged in sequence with two different colors. It clearly shows that one of every 8 blocks would straddle a track boundary.

Suppose a partition is created along the track boundary of the underlying disk. The partition layout in the physical disk would be as depicted in Figure 3. In this case, there are no 4-KB blocks straddling a track boundary.

According to performance analysis information, I/O to a misaligned partition in a storage area network (SAN) with 64 sectors per track would result in the following:

• Any I/O of 32 KB or larger will always cause a boundary crossing.
• Any random I/O of 16 KB will cause a boundary crossing 50 percent of the time.
• Any random I/O of 8 KB will cause a boundary crossing 25 percent of the time.
• Any random I/O of 4 KB will cause a boundary crossing 12.5 percent of the time.

Results

How much do you get out of this? In Jimmy May presentation he talks of results such as:

• Hitachi High-End SANs 8% – 12%

• Financial Firm: 40% faster ETL, Re-built Multi-TB SharePoint Backend

• Telecom Firm: Maintenance: 4 hours >> 90 minutes

  • 1M Row Insert into 140M Row table: 6:59 >> 0:52

  • 1M Row Insert into 500M Row table: 40% faster
  • Re-built Dozens of TBs of Data

From the information I gathered the average increase in performance for SQL Server is from 10% to 30%.

Verify if disk are align

There are many ways to do so here I provide one of them. Microsoft offers a how to on their support network. More details on this can also be found in the attach PDF.

To get the starting offset of your partition you perform the below command in your command prompt.

C:\>wmic partition get BlockSize, StartingOffset, Name, Index
BlockSize  Index  Name                   StartingOffset
512        0      Disk #0, Partition #0  1048576
512        1      Disk #0, Partition #1  42950721536
512        2      Disk #0, Partition #2  53688139776

Knowing the my disk index 0 is my partition c:

C:\>fsutil fsinfo ntfsinfo c:
NTFS Volume Serial Number :       0x0af22bc1f22bafbd
Version :                         3.1
Number Sectors :                  0x0000000004ffffff
Total Clusters :                  0x00000000009fffff
Free Clusters  :                  0x00000000002e8986
Total Reserved :                  0x00000000000007f0
Bytes Per Sector  :               512
Bytes Per Cluster :               4096
Bytes Per FileRecord Segment    : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length :           0x00000000097d0000
Mft Start Lcn  :                  0x00000000000c0000
Mft2 Start Lcn :                  0x00000000004fffff
Mft Zone Start :                  0x0000000000793620
Mft Zone End   :                  0x000000000079fe40
RM Identifier:        CBD70D39-B4C5-11DD-B78E-806E6F6E6963

I will make 2 calculation from them, If my partition are align the result of both will be integer. If you get decimals your disks are definitely not align.

StartingOffset		Bytes Per Cluster
1048576	÷	4096	=	256
Bytes Per Cluster		Bytes Per FileRecord Segment
4096	÷	1024	=	4

Performing Disk Alignment

With a physical disk that maintains 64 sectors per track, Windows always creates the partition starting at the sixty-forth sector, therefore misaligning it with the underlying physical disk. To be certain of disk alignment, use Diskpart.exe, a disk partition tool. Diskpart.exe is a utility provided by Microsoft in the Windows Server 2003 Service Pack 1 Support Tools that can explicitly set the starting offset of the master boot record (MBR). By setting the starting offset, you can track alignment and improve disk performance.

Before You Begin

Diskpart is a data destructive utility. When used against a disk, all data on the disk will be wiped out during the storage track boundary alignment process. Therefore, if the disk on which you will run Diskpart contains data, backup the disk before performing the following procedure.

---

Note: Diskpart can only be used with basic disks. Diskpart cannot be used with dynamic disks. Diskpart supersedes the functionality previously found in Diskpar.exe. Diskpar and Diskpart should only be used if the drive is translated as 64 sectors per track.

---

Diskpart.exe is located in the following directory by default on systems running Windows Server 2003 : C:\WINDOWS\system32

To align Exchange I/O with storage track boundaries using Diskpart.exe

If the disk you are aligning is already blank (raw), proceed to Step 3. If the disk contains data, backup the disk before proceeding.

1. Delete all partitions on the disk.
2. Open a command prompt, and execute Diskpart.exe.

3. At the Diskpart command prompt, type List Disk and press Enter. If the disk you want to align does not appear in the list make sure that it exists and is accessible using the Disk Management snap-in.

4. At the Diskpart command prompt, type Select Disk X, where X is the number of the disk as shown in the output of the List Disk command. Diskpart should return a message that indicates that Disk X is the selected disk.

5. At the Diskpart command prompt, type Create Partition Primary Align=X, where X is either 32 or 64, depending on the recommendation from your storage vendor. If your storage vendor does not have any specific recommendations, it is recommended that you use 64.

6. At the Diskpart command prompt, type Assign Letter=<DriveLetter>. For example, to assign letter Z to the disk, type Assign Letter=Z.

7. Once the drive letter is assigned, type exit to exit out of the Diskpart utility.

8. Use the Disk Management snap-in or the Windows Format command to format the partition as an NTFS-formatted partition.

References

• Microsoft Support: Predeployment I/O Best Practices SQL Server Best Practices Article

• Microsoft Support: A Description of the Diskpart Command-Line Utility

• Microsoft TechNet: How to Align Exchange I/O with Storage Track Boundaries

• Microsoft TechNet: Disk performance may be slower than expected when you use multiple disks in Windows Server 2003, in Windows XP, and in Windows 2000

• Disk Partition Alignment (Sector Alignment) for SQL Server: Part 1: Slide Deck

• Disk Partition Alignment (Sector Alignment) for SQL Server: Part 2: Adding HP EVA 8000 to Veritas Enterprise Administrator “Track Alignment Settings” Dialog

• Disk Partition Alignment for SQL Server by Jimmy May

• Symantec Veritas Storage Foundation™ 5.0 for Windows Best Practices for Storage Management