Disk or partition alignment is something new to me. I have heard of it before but I have never used nor understood it’s meaning until a colleague at work brought it to my attention for a customer we are trying to help out.
The information in this post is taken from a blog posting entitled Disk Partition Alignment Best Practices for SQL Server by Jimmy May and Denny Lee at Microsoft.
You have a server with a RAID volume Ã¢â‚¬â€œ For the example we will say RAID 10.
We will also say that you are using the default FAU (File Allocation Unit) of 4096 bytes for an NTFS volume.
Hard disks are configured with 63 hidden sectors. These sectors are used to store the MBR (Master Boot Record) and such. These 63 sectors would be advertised by the disk hardware to Windows so that Windows knew to avoid them when formatting and partitioning the disk.
To allow for these 63 sectors, Windows Server 2003, Windows XP and previous operating systems where designed to offset the start of the first partition on any volume by 32,256 bytes, exactly 31.5 KB.
The problem with 31.5 KB is that it doesn’t scale well across the stripes on the disk. The problem is that because the partition is offset by an unequal number every n th FAU (File Allocation Unit) written or read to or from the disk crosses a stripe boundary.
Whilst this poses to concern to Windows because the I/O request still resides on the same LogicalDisk, at the PhysicalDisk and storage controller level this results in multiple I/O operations. The reason for this is that the controller will have to order two commands to the disk: A read or a write request per stripe.
Windows Server 2008 and Windows Vista saw a change to the way in which Windows configured the partition offset to attempt to achieve partition alignment.
Starting with these newer operating systems, the default partition offset is 1,048,576 bytes, exactly 1024 KB or 1 MB. This is a much better solution because it scales well across the most common stripe sizes Ã¢â‚¬â€œ 64 KB, 128 KB, 256 KB and less frequently used 512 KB and 1024 KB. This method also ensures that the hidden disk sectors (the 63 mentioned earlier) are skipped.
This graph (taken from the SQL Server Blog) demonstrates the performance difference between an un-aligned 6 or 8 disk RAID volume and an aligned 6 or 8 disk RAID volume. This experiment uses RAID 10.
Here is the configuration used to achieve the results:
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 each run, DBCC DROPCLEANBUFFERS was executed to clear the SQL Server buffer cache, which ensured that all data required to satisfy the workload was loaded from disk.
As you can see from the graph, a 6 disk RAID 10 volume reduced it’s duration to execute the query by 10 seconds with a reduced disk seek and latency time of 10 ms.
Other examples of data in the document demonstrate up to 40% increase I/O throughput and 20% improved query execution time for a BI data warehouse server.
As I mentioned, Windows Server 2008 and Vista attempt to achieve partition alignment out of the box, however it shouldn’t be relied solely upon to do the work for you. You should still perform your own investigation into how to align your partitions.
For information on how to look for partition alignment or more information on the subject, refer to the original posting over on the SQL Server site at http://msdn.microsoft.com/en-us/library/dd758814(printer).aspx
After performing some WMIC script dumping and a little bit of DISKPART you will have enough information to perform the required byte calculations to complete the alignment.
I should also point out if you are planning on using Windows Server 2008 to do the alignment for you with it’s new 1024 KB offset then you need to format the volume in Windows Server 2008 Ã¢â‚¬â€œ Partitions which are already present after a Windows Server 2003 or older upgrade do not automatically adjust their offset, nor can this be done. You will need to duplicate your data to another location, format the volume which is of course destructive and then reload the data.