Tuesday, July 7, 2009

Performance improvement: Disk Partition alignment Best Practices for SQL Server

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.

Microsoft has published a usefull best practices document about this topic. Take your time to read and understand it. It can improve your performance of your disks up to 30% depending on your storage configuration. 
Within my company Exact we have seen a performance improvement of 10% with aligned partitions on a HP EVA 5000 storage system.

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. Correct disk partition alignment is done by default on Windows Server 2008. On prior versions of Windows Server partitions are misaligned by default.

More details and test results about EVA Partition alignment can be found here

1 comment:

Anonymous said...

Check out: http://mssqlonline.blogspot.com/2009/07/sql-server-disk-partition-alignment.html