Diskpart & "diskpar " correct alignment

http://technet.microsoft.com/en-us/library/dd758814(v=sql.100).aspx


Using Diskpart.exe to Perform Partition Alignment, Assign Drive Letters, and Assign File Allocation Unit Size

The good news is that partition alignment is simple to perform; the bad news is that partition alignment must be done at partition creation time, prior to partitions being formatted. This is great if you have a new SAN, but it might be painful to convert large amounts of existing data on misaligned partitions.Caution: Realigning partitions using any tool is a destructive operation, because it wipes out existing data.Here is a template for using diskpart.exe to perform partition alignment, assign a drive letter, and format a partition.Command Line SyntaxDiskpart list disk select disk <DiskNumber> create partition primary align=<Offset_in_KB> assign letter=<DriveLetter> format fs=ntfs unit=64K label="<label>" nowait Here is an example in which the F: drive is created on disk 3, aligned with an offset of 1,024 KB, and formatted with a file allocation unit (cluster) size of 64 KB.Command Line SyntaxC:\>diskpart Microsoft DiskPart version 6.0.6001 Copyright (C) 1999-2007 Microsoft Corporation. On computer: ASPIRINGGEEK DISKPART> list disk   Disk ###  Status      Size     Free     Dyn  GPT   --------  ----------  -------  -------  ---  ---   Disk 0    Online       186 GB      0 B   Disk 1    Online       100 GB      0 B   Disk 2    Online       120 GB      0 B   Disk 3    Online       150 GB   150 GB DISKPART> select disk 3 Disk 3 is now the selected disk. DISKPART> create partition primary align=1024 DiskPart succeeded in creating the specified partition. DISKPART> assign letter=F DiskPart successfully assigned the drive letter or mount point. DISKPART> format fs=ntfs unit=64K label="MyFastDisk" nowait Note that nowait initiates asynchronous formatting, so that the formatting of multiple partitions can begin one after another, allowing the operations to proceed in parallel. The format option is not available in Windows Server 2003.Diskpart can be executed directly from the command line or scripted. The /s switch specifies an input script file.



Command-Line Partition Alignment Tools: Diskpart.exe and Diskpar.exe

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.The Windows 2000 Resource Kit introduced the command-line utility diskpar.exe. Its successor, diskpart.exe, was introduced in Windows Server 2003. Note the presence or absence of a "t" in their names. The /align option debuted in Windows Server 2003 Service Pack 1 (SP1). 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 thewmic command to report partition offsets of basic disks; usedmdiag –v for Windows dynamic disks.



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 your 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 hardware are skipped.

Basic Disk Partition Offsets: wmic.exe

Windows can be interrogated for disk-related information via Windows Management Instrumentation (WMI). A straightforward method for obtaining partition starting offsets of Windows basic disks is this Wmic.exe command.Command Line Syntaxwmic partition get BlockSize, StartingOffset, Name, Index The value for Index is the same as disk number in the Disk Management Microsoft Management Console (MMC) snap-in (Diskmgmt.msc); wmic volume can also be used to map disk indexes and drive letters.

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



Disk Partition Alignment Best Practices for SQL Server

SQL Server 2008174 out of 185 rated this helpful - Rate this topicWriters: Jimmy May, Denny LeeContributors: Mike Ruthruff, Robert Smith, Bruce Worthington, Jeff Goldner, Mark Licata, Deborah Jones, Michael Thomassy, Michael Epprecht, Frank McBath, Joseph Sack, Matt Landers, Jason McKittrick, Linchi Shea, Juergen Thomas, Emily Wilson, John Otto, Brent DowlingTechnical Reviewers: Mike Ruthruff, Robert Smith, Bruce Worthington, Emily Wilson, Lindsey Allen, Stuart Ozer, Thomas Kejser, Kun Cheng, Nicholas Dritsas, Paul Mestemaker, Alexei Khalyako, Mike Anderson, Bong KangPublished: May 2009Applies to: SQL Server 2008Summary: Disk partition alignment is a powerful tool for improving SQL Server performance. Configuring optimal disk performance is often viewed as much art as science. A best practice that is essential yet often overlooked is disk partition alignment. 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.This paper 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 preexisting partitions.The following topics are also included: background information, implementation, vendor considerations, two essential correlations, valid starting partition offsets, and the simple protocol to align partitions, define file allocation unit size, and assign drive letters. It includes results from tests that show how partition alignment can affect performance for SQL Server 2008.You can also download a Microsoft Word version of this article.

Introduction

Configuring optimal disk performance is often viewed as much art as science. Yet an understanding of disk performance best practices can result in significant improvements. Some of the many factors that affect disk I/O performance include the number, size, and speed of disks; file allocation unit size; configuration of host bus adapters (HBAs) and fabric switches; network bandwidth; cache on disks, controllers, and storage area networks (SANs); whether disks are dedicated, shared, or virtualized; RAID levels; bus speed; number of paths from disk I/O subsystem to servers; driver versions for all components; factors related to RAID stripes sizes; and workload.A best practice that is essential yet often overlooked is disk partition alignment.The Windows Server® 2008 operating system attempts to align new partitions out of the box, yet disk partition alignment remains a relevant technology for partitions created on prior versions of the Windows® operating system.Noncompliance with storage configuration best practices for the Microsoft® SQL Server® database software is a common root cause of support cases. 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 created on RAID disk devices on versions of Windows prior to Windows Server 2008. As we will explain, new partitions on Windows Server 2008 are created at a starting offset which (with high probability) aligns with underlying RAID stripe units.For systems from which high performance is required, it is essential to experiment with representative workloads and determine the validity of disk partition alignment for your environment.Unless performed at the time of partition creation, the default alignment offset will result in unaligned partitions on versions of Windows up to and including Windows Server 2003. These versions of Windows create disk partitions by default to boundaries based on the Cylinder/Head/Sector (CHS) addressing scheme used by previous generation disk controllers. Preexisting partitions attached to Windows Server 2008 maintain the original, flawed alignment under which they were created.This paper 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 preexisting partitions.The following topics are also included:Background informationImplementationVendor considerationsTwo essential correlationsValid starting partition offsetsThe paper also covers the simple protocol to align partitions, define file allocation unit size, and assign drive letters.

Scope

The information presented here applies to Windows basic and dynamic disks with master boot record (MBR) partitions.Details related to GUID partition table (GPT) disks are not addressed. However, disk partition alignment is a best practice, and it is required for optimal performance for each of these hard drive configurations:MBR basicMBR dynamicGPT basicGPT dynamicPerformance characteristics of dynamic disks and GPT disks will be addressed in subsequent publications on the SQL Server Customer Advisory Team Web site (www.sqlcat.com).

Background Information

You may hear the terms partition alignment, disk alignment, volume alignment, and sector alignment used synonymously. This paper uses the term partition alignment.Many customers are unaware of partition alignment. Even experienced disk administrators may be unfamiliar with it. Explanations are often initially met with disbelief.Engineers familiar with the topic may underestimate its importance. For example, some customers think it is useful only for Microsoft Exchange Server. In fact, partition alignment is important for all servers from which high performance is expected—especially SQL Server.This section presents essential terms and history.

Terms

Discussion of disk I/O includes terms that are often used ambiguously and interchangeably—sometimes reflecting accepted definitions, sometimes not. A common framework for communication is important. Hard drives contain many components; the important ones for this discussion are labeled in Figure 2 in the "Appendix". Figures 3 and 4 in the same section provide graphical documentation.Hard drives house platters, one or more thin, circular disks, on the surfaces of which are the electronic media that store information.Each side of each platter has thousands of tracks. The set of tracks with the same diameter from all platter surfaces comprises a cylinder. (For modern drives, the cylinder concept is no longer relevant, and tracks are no longer arranged in concentric circles, yet it is useful to understand the origin of the terms.) Each platter surface has a dedicated read/write head. Tracks are divided into sectors. A sector is the minimum chunk of data that can be read or written to a hard drive. Historically, sector size has been fixed at 512 bytes. Newer drives may offer 1 KB, 2 KB, or 4 KB sectors.Many engineers will recognize file allocation unit by another name: cluster. Cluster size is determined when the partition is formatted by the operating system (or the user). For example, if the sectors of a hard drive are 512 bytes, a 4 KB cluster has 8 sectors, and a 64 KB cluster has 128 sectors.Stripe size is the size of one entire stripe spread across all the disks in a RAID-0, RAID-10, RAID-5, or RAID-6 disk group. Stripe unit size is the size of each element of the stripe, as stored on each member of the disk group. Stripe size is a product of the stripe unit size and the number of disks in a RAID group. Stripe unit size is the attribute of a RAID disk group that can be configured by administrators. A stripe unit is the collection of bits on each disk exactly equal to the stripe unit size.

Description

Understanding the nuances of partition alignment is not necessary to follow the simple protocol required for optimal alignment. For more information about how to execute partition alignment, see the "Implementation" section later in this paper.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 XP and 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. For more information about the effect in experimental and production environments, see the "Performance Impact" section later in this paper.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.In the absence of an explicit vendor recommendation, use a partition offset that complies with the correlation discussed in the section "Essential Correlations: Partition Offset, File Allocation Unit Size, and Stripe Unit Size". 64 KB is a common, valid starting partition offset because it correlates well with fundamental physical boundaries in disks, controllers, and cache. Other valid starting partition offsets exist. The Windows Server 2008 default is 1024 KB. For more information, see the "Valid Starting Partition Offsets" section later in this paper.

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. The following sections describe how partition alignment works in Windows Server 2008, the Windows Vista® operating system, and Windows Server 2003 and earlier.

Windows Server 2008 and Windows Vista: New Partitions

In Windows Vista as well as 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\AlignmentHowever, if OEM setups are delivered (for example, with recovery partitions), even fresh installations of Windows Server 2008 having partitions with undesirable partition starting offsets have been observed.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 and Earlier

Partitions created on versions of Windows up to and including 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. As described in the "Appendix", modern disks are proprietary "black boxes" and disk partition alignment does not enhance performance for individual disks; however, cache line misalignment may still apply. Make certain that performance thresholds, particularly disk latency, are not exceeded for all disks, including those on which the operating system and SQL Server data and log files reside.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.  The guidelines described here apply to the respective guest & host operating systems.

Description

Understanding the nuances of partition alignment is not necessary to follow the simple protocol required for optimal alignment. For more information about how to execute partition alignment, see the "Implementation" section later in this paper.

Performance Impact

In the detailed experiment below, alignment reduced both disk latency and query duration by approximately 30 percent. The performance of six aligned disks was comparable to or better than eight nonaligned disks.This work was done on a DELL PowerEdge 2950 with two dual core 3.00 GHz Intel Xeon processors, a PERC 5/E controller, and 8 GB of physical RAM. Six or eight disks, SAS DAS, 73 GB 15K RPM, were configured in RAID 10 with a cluster size of 64 KB and a stripe unit size of 64 KB. Windows Server 2003 and SQL Server 2005 were installed. A query was executed that the customer used to benchmark performance. Prior to ea