Improve SQL Server performance by 30%

Changing disk partition alignment could improve query performance by 30%, according to a new whitepaper from Microsoft.

Disk partition alignment is a powerful tool for improving SQL Server performance, but too few administrators understand the concepts and how to work out the best alignments. In fact, configuring optimal disk performance is often viewed as much art as science.

If you're wondering how disk partition alignment can have any major effect, the simple answer is that Windows reserves 63 hidden sectors at the beginning of the first partition of a disk. The master boot record (MBR) resides within these hidden sectors. Unfortunately, these reserved sectors don't coincide with the actual physical boundaries of the disk, so single clusters of user data are written across multiple stripe units of the RAID. This means that instead of being accessed in a single I/O operation, each data request needs at least two operations. In an experiment reported in the new whitepaper, correcting the alignment reduced both disk latency and query duration by approximately 30 percent.

Windows Server 2008 attempts to align new partitions out-of-the-box, but those databases stored on partitions created under older versions of Windows can benefit just as much from disk partition alignment. The new whitepaper looks at relative performance for aligned and nonaligned storage, and shows how nonaligned partitions can have a negative impact on I/O performance.

It explains disk partition alignment for storage configured on Windows Server 2003, including analysis, diagnosis, and remediation. It also describes how Windows Server 2008 attempts to work out the best partition alignment for new partitions but does not correct the configuration of preexisting partitions.

Other topics covered in the whitepaper include how to implement partition alignments, 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.

You can read the whitepaper at Disk Partition Alignment Best Practices for SQL Server