Recommendations and Guidelines on configuring disk partitions for SQL Server

Article ID: 2023571 - View products that this article applies to.
Expand all | Collapse all

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 2008 R2

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

 

 

Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.

Properties

Article ID: 2023571 - Last Review: April 3, 2012 - Revision: 3.0
APPLIES TO
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Standard
Keywords: 
KB2023571

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com