Disk Partition Alignment (Sector Alignment) for SQL Server: Part 4: Essentials (Cheat Sheet)

The purpose of this post is to document Disk Partition Alignment Essentials. It is intended for engineers who are already familiar with disk partition alignment yet want a “cheat sheet”.

As most of you know, partition alignment is an essential best practice. We are seeing I/O enhancements in the lab & at important real-life customer sites of 25% - 40% measured by a variety of metrics. Your mileage may vary. It will be sometime before Windows Server 2008 is ubiquitous & existing partitions are re-built. In the meantime, disk partition alignment will remain a relevant technology

Disk Partition Alignment is an essential foundation of high I/O performance. It must be performed on partitions prior to formatting & addition of user data. Existing partitions must be re-built; Data on existing volumes must be backed up, then restored after alignment & re-formatting.

Additional details are available in a previous post:

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

http://blogs.msdn.com/jimmymay/archive/2008/10/14/disk-partition-alignment-for-sql-server-slide-deck.aspx

In addition, stay tuned for the white paper sponsored by SQL CAT at www.sqlcat.com.

 

Attached Document

The attached doc is a compressed Word.doc version of this post.

Applies to:

Partition alignment is important for all categories of disks:

· MBR basic

· MBR dynamic

· GPT basic

· GPT dynamic

Three Values, Two Essential Correlations

Perform these calculations for each partition which must result in integer values:       

Partition_Offset ÷ Stripe_Unit_Size

Stripe_Unit_Size ÷ File_Allocation_Unit_Size

Of the two, the first is far more important. Use the information below to divine this information.

Stripe Unit Size

Windows cannot reliably report stripe unit size. For local storage & DAS, vendor utilities should be able to provide the information. Otherwise, talk to your SAN man (or woman) to get the stripe unit size.

File Allocation Unit Size

Run this command for each drive to get the file allocation unit size:

fsutil fsinfo ntfsinfo c:

fsutil fsinfo ntfsinfo d:

etc...

Values should be 65536 bytes (64KB) for partitions on which SQL Server data or log files reside.

Starting Partition Offset Analysis for Existing Partitions

Basic Disks

Run this command to get the starting offsets for basic disks:

wmic partition get BlockSize, StartingOffset, Name, Index

wmic Output

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 53688139776

512 2 Disk #0, Partition #2 161062322176

512 0 Disk #1, Partition #0 65536

512 0 Disk #2, Partition #0 32256

Dynamic Volumes

Analyzing basic dynamic disks is not quite as straightforward.

· The wmic command is NOT VALID for dynamic disks.

· Analysis for Windows dynamic & 3rd party dynamic disks is different.

Windows Dynamic Volumes

· The status of windows dynamic volumes requires dmdiag with the -v option: dmdiag -v

· The -v option generates two sections if Windows dynamic volumes are present:

dmdiag -v Section

Column

---------- Dynamic Disk Information -----------

Rel Sec

---------- LDM Volume Information -----------

Rel Sectors column

· Be mindful that the output is in units of sectors & requires conversion to bytes for purposes of the correlations cited above.

dmdiag –v Partial Output

---------- Dynamic Disk Information -----------

 DiskGroup: S0029170Dg0

  Group-ID: e60175bf-47ce-45e0-b725-9bcf73cc2a43

   ...<some columns redacted for brevity>...

   Sub Disk Rel Sec Tot Sec Vol Type DevName

   ======== ======= ======= ======== =========

   Disk1-01 128 16384 Simple Harddisk4

   Disk1-02 16512 33527208 Spanned Harddisk4

etc...

---------- LDM Volume Information -----------

   ...<some columns redacted for brevity>...

   Volume Volume Size Total Rel Vol Plex

   Name Type Sectors Size Sectors State State

   ====== ====== ======= ======= ======= ====== ======

   Volume1 Simple 16384 16384 128 ACTIVE ACTIVE

   Volume2 Simple 16384 16384 128 ACTIVE ACTIVE

etc...

· Even in the absence of the -v switch, the output is fairly verbose. There are many sections which purport to report starting partition offset. As I understand it, the Logical Disk Manager (LDM) “spoofs” tools built for basic disks so they won’t overwrite dynamic volumes. The output of these sections is NOT to be trusted for dynamic volumes. For example, the following reveals the classic default misaligned value of 32,256 bytes. However, the Partition Type 0x42 betrays that this is a dynamic volume & thus the starting partition offset information in this section is NOT reliable:

::: !!!UNRELIABLE for Starting Offsets of Dynamic Volumes!!! :::

---------- Partition Table Info Disk 1 ----------

...<redacted for brevity>...

        Starting Partition Hidden Partition Partition

  Offset (bytes) Length (bytes) Sectors Number Type (HEX)

          32,256 268,431,980,544 63 0 0x42

               0 0 0 1 0x00

               0 0 0 2 0x00

               0 0 0 3 0x00

...<redacted for brevity>...

MBR 3rd Party Dynamic Volumes

Dynamic volumes created by 3rd party vendors may not be properly interpreted by dmdiag -v. For example, Veritas dynamic disks require their proprietary tools to determine whether existing volumes are aligned.

 

GPT basic & dynamic

I haven’t tested this on GPT disks and look forward to doing so.

Implementation: DISKPART Example

Here’s an example of performing partition alignment, providing a drive letter, & initiating an async format:

C:\diskpart

Microsoft DiskPart version 6.0.6000

On computer: ASPIRINGGEEK

DISKPART> list disk

DISKPART> select disk 3

DISKPART> create partition primary align=1024

DISKPART> assign letter=E

DISKPART> format fs=ntfs unit=64K label="MyFastDisk" nowait

Note that the format command is available only in Windows Server 2008 & Vista.

Microsoft Documentation

This the mother of all articles on disk partition alignment. It’s by PFE Robert Smith:

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

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

This is the best white paper I’ve seen on this topic. It’s by SQL CAT member Mike Ruthruff. It is mercifully brief &, as our friends at Guinness might say, simply “Brilliant!”:

Predeployment I/O Best Practices

SQL Server Best Practices Article

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx

Cited above, my deck:

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

http://blogs.msdn.com/jimmymay/archive/2008/10/14/disk-partition-alignment-for-sql-server-slide-deck.aspx

Again, stay tuned for the white paper from SQL CAT.

Acknowledgements

Among the many engineers who have assisted, my special thanks to Microsoft Sr. Dev Lead Deborah Jones for her help in the divination of dynamic volumes.

 

Administrivia
Jimmy May , MCDBA, MCSE, MCITP: DBA + DB Dev | Senior Performance Consultant: SQL Server
A.C.E.: Assessment Consulting & Engineering Services
http://blogs.msdn.com/jimmymay
Performance is paramount: Asking users to wait is like asking them to leave.

Disk Partition Alignment (Sector Alignment) for SQL Server - Part 4 - Essentials (Cheat Sheet) -- Jimmy May.zip