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