Summary
The performance of a storage system used by SQL Server is dependant on various configuration parameters that are applied at the partition, disk, controller, SAN, RAID and device driver levels. In some instances these configuration parameters are tuned at each level to provide optimal performance for the entire storage solution. In some, these configuration values are left to the default. It is important to review these configuration parameters with the storage system vendor to make sure it is the appropriate setting for SQL Server I/O patterns.
One of the important configuration parameter is the disk partition alignment or the starting offset of a partition on a disk. If this value is specified incorrectly at the time of creation of the partition, then the I/O performance could suffer. This is very important when these partitions form RAID volumes and the partition alignment is not appropriate for the stripe unit size. If the partition alignment is not configured properly, I/O requests could take more time to finish since they might be split into multiple requests.
The default partition alignment value chosen depends on the Operating System where the partition was created. The default value used while creating partitions in Windows Server 2008 is 1,048,576 bytes. This value works well with commonly used stripe unit sizes of 64 KB, 128 KB and 256 KB. When you create a partition ensure that the starting offset is an exact multiple of the stripe unit size. For example, Partition_Starting_Offset modulo Stripe_Unit_Size should be zero.
More Information
For a detailed discussion on the various configuration values that you need to evaluate, please refer to the whitepaper : Disk Partition Alignment Best Practices for SQL Server. This whitepaper contains information on the default values for various Operating Systems where the partitions were created.
For more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product, see the following table:
|
Rule software |
Rule title |
Rule description |
Product versions against which the rule is evaluated |
|
SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) |
Disk partition starting offset may be incorrect |
The SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides a rule to detect these conditions where the appropriate value for partition starting offset is not configured. The SQL Server 2008 R2 BPA supports both SQL Server 2008 and SQL Server 2008 R2. If you run the BPA tool and encounter a Warning with the title of Database Engine - Disk partition starting offset may be incorrect, then review the partition configuration values with the recommended values specified in the whitepaper referenced above. You can use the following WMI command to list all the partitions and their starting offset values: wmic partition get BlockSize, StartingOffset, Name, Index You can execute the above command from the regular command prompt. You can use the diskpart Windows utility to specify the partition alignment when creating partitions for use with SQL Server. Use these recommendations for configuring partitions that will be used for storing user database files as well as system databases like tempdb and msdb |
SQL Server 2008 |
|
SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) |
Disk partition starting offset may be incorrect |
The SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) provides a rule to detect these conditions where the appropriate value for partition starting offset is not configured. If you run the BPA tool and encounter a Warning with the title of Database Engine - Disk partition starting offset may be incorrect, then review the partition configuration values with the recommended values specified in the whitepaper referenced above. You can use the following WMI command to list all the partitions and their starting offset values: wmic partition get BlockSize, StartingOffset, Name, Index You can execute the above command from the regular command prompt. You can use the diskpart Windows utility to specify the partition alignment when creating partitions for use with SQL Server. Use these recommendations for configuring partitions that will be used for storing user database files as well as system databases like tempdb and msdb. |
SQL Server 2012 |
