Disk Partition Alignment For SQL Server 2008

Overview

Disk partition alignment is a powerful tool for improving SQL Server performance, a Best Practice that is essential, yet often overlooked.

Windows Server 2008 attempts to align new partitions out-of-the-box, yet disk partition alignment remains a relevant technology for partitions created on prior versions of Windows or on volumes that have not been subject to a Windows 2008 Format such as volumes in a SAN environment.

This document is an extract of the Microsoft White paper

Disk Partition Alignment Best Practices for SQL Server 2008

It documents performance for aligned and nonaligned storage and why nonaligned partitions can negatively impact I/O performance; it explains disk partition alignment for storage configured on Windows Server 2003, including analysis, diagnosis, and remediation; and it describes how Windows Server 2008 attempts to remedy challenges related to partition alignment for new partitions yet does not correct the configuration of pre existing partitions.

I have included the most relevant portions in to this document to try an make it a concise guide, however please refer to the Microsoft Document should you require any further background or explanation as to why Disk Partition Alignment is required.

Disk Partition Alignment.

Introduction

Noncompliance with storage configuration Best Practices for the Microsoft SQL Server is a common root cause of support calls to Microsoft for poorly performing Database Systems.

The reason is often shown to be misalignment between Windows, Storage, Disk Controllers, and Cache segment lines.

Failure to perform partition alignment may result in significant performance degradation.

Disk partition alignment is a requirement for partitions from which high performance is demanded and that were not created on RAID disk devices on Windows Server 2008.

Unless performed at the time of partition creation, the default alignment offset will result in unaligned partitions.

Earlier versions of Windows and some Manufacturers’ default SAN Settings create disk partitions by default to boundaries based on the Cylinder/Head/Sector (CHS) addressing scheme used by previous generation disk controllers.

Pre existing partitions attached to Windows Server 2008 maintain the original, flawed alignment under which they were created.

• MBR basic
• MBR dynamic
• GPT basic
• GPT dynamic 

 Scope

The information contained in this document applies to Windows basic and dynamic disks with master boot record (MBR) partitions.

However, disk partition alignment is a Best Practice, and it is required for optimal performance for each of these hard drive configurations:

Description 

The following is a simplified characterization of partition misalignment:

Disk array hardware reports 63 reserved (hidden) sectors, and Windows obediently implements this information, reserving these sectors at the beginning of the first partition of a disk.

The master boot record (MBR) resides within these hidden sectors.

The compliance by Windows with 63 hidden sectors reported by disk hardware results in misalignment with stripe units, disk controllers, and cache segment lines.

In all versions of Windows earlier than and including Windows Server 2003, these reserved sectors do not coincide with fundamental physical boundaries.

The result is that single clusters of user data are written across multiple stripe units of the RAID.

Every nth operation is affected (n depends on file allocation unit (cluster) size and stripe unit size).

Fundamental physical boundaries in disk controllers and other hardware are also violated.

Across a striped array, a single I/O coming into the array controller turns into multiple I/Os if the request crosses one or more stripe unit boundaries.

The cumulative effect can contribute to substantial performance degradation. 

In all cases, similar principals are at work:

Due to misalignment, clusters of data are written across physical boundaries, requiring unintended, unnecessary I/Os that result in performance degradation.

Partition Alignment in Windows Operating Systems 

The way partition alignment works depends on the version of Windows being used and the version in which the partition alignment was created.

Windows Server 2008 - New Partitions.

 In Windows Server 2008, partition alignment is usually performed by default.

The default for disks larger than 4 GB is 1 MB.

The setting is configurable and is found in the registry at the following location:

HKLM\SYSTEM\CurrentControlSet\Services\VDS\Alignment

However, if OEM setups are delivered (for example, with recovery partitions), even fresh installations of Windows Server 2008 having partitions with undesirable partition starting offsets are possible.

Whatever the operating system, confirm that new partitions are properly aligned.

Windows Server 2008 – Pre existing Partitions. 

New partitions on Windows Server 2008 are likely to be aligned.

Yet partitions created on earlier versions of Windows and become associated with Windows Server 2008 maintain the properties under which they were created.

That is, in the absence of partition alignment being explicitly performed, these partitions are not aligned.

Windows Server 2003. 

Partitions created on Windows Server 2003 by default are not aligned.

Partition alignment must be explicitly performed.

System Drives.

System drives in versions of Windows prior to Windows Server 2008 cannot be aligned.

Fortunately, workloads associated with system partitions of dedicated SQL Server computers are typically not as sensitive to partition misalignment as disks dedicated to I/O intensive uses, for example, SQL Server database files from which high-performance is demanded.

System drives on fresh installations of Windows Server 2008 should be aligned by default.

Virtual Drives.

 Virtual drives and the host drives on which they reside must be aligned for optimal performance.  

Starting Partition Offsets. 

The performance benefit of disk partition alignment requires configuration of valid starting partition offsets.

Several tools report the starting partition offset.

The results are reliable only in specific contexts.

Valid starting partition offsets and tools used to report them are discussed in this section.

Valid Starting Partition Offsets. 

Because versions of Windows earlier than and including Windows Server 2003 comply with the 63 hidden sectors reported by disk hardware, and because the most common sector size 512-byte sectors, the default (and suboptimal) starting partition offset is 32,256 bytes, exactly 31.5 KB.

Explicitly defining the starting offset from 31.5 KB to exactly 32 KB might seem like a legitimate approach.

In fact, as mentioned earlier, 64 KB is typically the minimum (and a common) valid starting partition offset for SQL Server because of the correlations described later.

When choosing a valid partition starting offset, refer first to the storage vendor best practices.

Make certain their recommendations correlate with the stripe unit size and file allocation unit size configured for SQL Server.

In the absence of definitive vendor information, choose the Windows Server 2008 default.

Windows Server 2008 partition alignment defaults to 1024 KB (that is, 1,048,576 bytes). 

This value provides a durable solution.

It correlates well (as described later) with common stripe unit sizes such as 64 KB, 128 KB, and 256 KB as well as the less frequently used values of 512 KB and 1024 KB.

Also, the value virtually guarantees hidden structures allocated by storage.

Dynamic Disk Partition Offsets: dmddiag.exe –v.

The command-line utility Dmdiag.exe is used to determine the partition offsets of Windows dynamic volumes.

Important: Neither the output of the wmic command nor any other tool designed only for basic disks, reliably reports starting partition offsets of Windows dynamic disks.

The tool is available in the support tools folder of Windows Server 2003.

In Windows Server 2008, the tool has been renamed diskdiag.exe.

To determine the starting partition offset of dynamic disks, execute the following command.

dmdiag -v

The output has several sections; the sections that are relevant to analyzing the starting partition offset of existing volumes are generated only if the -v switch is used.

Those sections (and relevant columns) are:

Dynamic Disk Information (Rel Sec)

LDM Volume Information (Rel Sectors)

Focus on these sections and columns.

Subsequent sections of the output that report offsets can be misleading, and they are unlikely to be reliable for interpreting starting partition offsets of dynamic volumes.

Note that Microsoft tools, including even dmdiag, may be unreliable for reporting starting partition offsets of dynamic volumes created by third-party vendors.

For example, dmdiag does not report correct starting partition offsets of dynamic volumes created by Veritas Enterprise Administrator.

In these cases, consult your vendor for the tools and techniques required for proper analysis.

 Command-Line Partition Alignment Tools. 

Disk partition alignment is not available from the Disk Management snap-in (diskmgmt.msc).

Windows provides two tools to implement disk partition alignment: diskpart.exe and diskpar.exe.

Note the presence or absence of a “t” in their names.

Both utilities are powerful and should be exercised with caution.

Diskpar.exe reliably reports partition alignment in terms of bytes.

However, results are valid only for MBR basic disks, and this tool is no longer supported by Microsoft.

Diskpart.exe reports alignment for basic disks in terms of kilobytes.

As noted, the Windows Server 2003 (and earlier) default alignment is 32,256 bytes, exactly 31.5 KB; unfortunately DiskPart rounds this up to 32 KB.

Though DiskPart is the tool of choice to implement partition alignment, the value it reports for partition offset is not sufficiently granular.

Therefore, use the wmic command to report partition offsets of basic disks; use dmdiag –v for Windows dynamic disks.

Implementation 

This section provides information about using specific measurements and tools to implement the recommendations presented earlier in this white paper.

It also addresses issues to consider regarding vendors.

Essential Correlations: Partition Offset, File Allocation Unit Size, and Stripe Unit Size

Use the information in this section to confirm the integrity of disk partition alignment configuration for existing partitions and new implementations.

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

Of the two, the first is by far the most important for optimal performance.

The following demonstrates a common misalignment scenario:

Given a starting partition offset for 32,256 bytes (31.5 KB) and stripe unit size of 65,536 bytes (64 KB), the result is 0.4921875.

This is not an integer; therefore the offset & strip unit size are not correlated.

This is consistent with misalignment.

However, a starting partition offset of 1,048,576 bytes (1 MB) and a stripe unit size of 65,536 bytes produces a result of exactly 8, an exact integer, which is consistent with alignment.

Note that file allocation unit (cluster) size commonly correlates with common stripe unit sizes.

The performance question here is usually not one of correlation per the formula, but whether the cluster size is the NTFS default of 4,096 bytes or has been explicitly defined at 64 KB, which is a best practice for SQL Server.

 Stripe Unit Size. 

Windows does not have a reliable way to determine stripe unit sizes.

These values are obtained from vendor disk management software or from your SAN administrator.

File Allocation Unit Size. 

Run this command for each drive to see the file allocation unit size reported in bytes per cluster.

fsutil fsinfo ntfsinfo c:

fsutil fsinfo ntfsinfo e:

etc...

An appropriate value for most installations should be 65,536 bytes (that is, 64 KB) for partitions on which SQL Server data or log files reside.

 Vendor Considerations. 

Consult the storage vendor for their recommendation.

In the absence of definitive information, consider implementing disk partition offset with the Windows Server 2008 default of 1024 KB. 

Conclusion. 

Many factors contribute to optimal disk I/O performance.

For disk partitions created with Windows Server 2003, partition alignment properly correlated with stripe unit size and file allocation unit size is a best practice, and it provides a fundamental foundation for optimal performance.

Windows Server 2008 aligns partitions by default.

When servers are upgraded to Windows Server 2008, preexisting partitions are not automatically aligned and must be rebuilt for optimal performance.

Thus, until existing misaligned partitions created using Windows Server 2003 are rebuilt properly, disk partition alignment will remain a relevant technology.

The results of the following calculations must result in an integer value:

Partition_Offset ÷ Stripe_Unit_Size

Stripe_Unit_Size ÷ File_Allocation_Unit_Size

Of the two, the first is by far the most important for optimal performance.

A starting partition offset of 1,048,576 bytes (1 MB) and a stripe unit size of 65,536 bytes produces a result of exactly 8, an exact integer, which is consistent with alignment.

Note that file allocation unit (cluster) size commonly correlates with common stripe unit sizes.

The performance question here is usually not one of correlation per the formula, but whether the cluster size is the NTFS default of 4,096 bytes or has been explicitly defined at 64 KB, which is a best practice for SQL Server 2008.