There are 3 layer for the disk allocations.
1. SQL Server allocate one extent at a time. Each Extent is 8 Pages. Each Page is 8K. So SQL Server is read/write 64K.
2. Database file system has it own allocation unit. In NTFS term, it is the cluster size which is decide when we format the disk. The default cluster size is 4K and it is recommend from MSFT to use 4K for OS. However, for SQL server disk, if we use 4K, that means we have to read at least 16 times to get 1 extent.
3. Storage level. If you are using SAN/RAID, it has its own strip size. usually is set to the 64KB but you have to confirm with your SAN admin.
How to check the cluster size
Below example show the cluster size is 4K.
How to check the offset size
Below example show the offset size as 1024K on the Win2008R2 server. It is default for the Win2008R2. However, if you upgrade the OS from Win2003 to Win2008, the offset size would remain when you first create the disk at the time the Win2003 was installed. The Win2003 default has 63 hidden sectors which each sector for the hard disk is 512 bytes so the offset usually is 31.5K ( 512 * 63).
wmic partition get BlockSize, StartingOffset, Name, Index
What is Disk alignment?
When the hidden sector is 31.5KB and the disk strip is 64K. For every 8th 4K NTFS cluster read would require 2 IO (64-31.5)/4 to the IO storage system. To resolve this, we can make the hidden sector to 32KB instead of 31.5KB.
The goal for align the disk is to reduce the trip from SQL server to file system and to the IO sub system.
Why disk alignment is important for SQL Server?
When the disk is align with NTFS file system cluster and IO subsystem, this would reduce the IO trips for the SQL Server and improve the performance.
It is recommend to use 64KB or larger 128KB the NTFS file system cluster to improve the read ahead.
If the OS is fresh install Win2008 ( not upgrade from Win2003), the offset is already 1024KB, this already satisfy most common IO subsystem strips( 64KB, 128KB,256KB).
- Disk Partition Alignment Best Practices for SQL Server: http://msdn.microsoft.com/en-us/library/dd758814(v=sql.100).aspx
- Disk Partition Alignment (Sector Alignment): Make the Case: Save Hundreds of Thousands of Dollars http://blogs.msdn.com/b/jimmymay/archive/2009/05/08/disk-partition-alignment-sector-alignment-make-the-case-with-this-template.aspx