Tuesday, June 24, 2008

Disk Formatting & Partition Alignment for SQL Server

Cluster Size

SQL Server reads and writes in 64K blocks.

Therefore , format drives you provision for SQL data & logs with a cluster size of 64K.

Simple Really :)

Partition Alignment

Before you format the drives however, there is the matter of Partition Alignment.

The following formulae are published by Microsoft to help determine partition alignment.

Partition_Offset / Stripe_Unit_Size = integer (this is the most important)


Stripe_Unit_Size / File_Allocation_Unit_Size   = integer

In the absence of the stripe size info, the alignment should at the very least be changed from it’s 32K default when establishing the partition.

If we can’t find Stripe size information, the best we can do is use an alignment value of 1024K (1MB) which is common to many SANs and is compatible with the 64K Cluster size.

NB : Alignment is handled automatically on Windows 2008.

IT Knowledgebase : How much performance are you losing my not aligning your drives?
MSDN : Disk Partition Alignment Best Practices for SQL Server


To set alignment >

C:\>DISKPART

Microsoft DiskPart version 5.1.3565

Copyright (C) 1999-2003 Microsoft Corporation.
On computer: DEV008

DISKPART>SELECT DISK 1

Disk 1 is now the selected disk

DISKPART>CREATE PARTITION PRIMARY ALIGN=1024

DiskPart succeeded in creating the specified partition


You'll then want to format the drive in a 64K cluster size.

No comments: