Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
INF: Understanding How to Set the SQL Server I/O Affinity Option
Article ID: 298402 - View products that this article applies to.
This article was previously published under Q298402
This article discusses what I/O affinity is and how to use the I/O affinity option that is available in SQL Server 2000 SP1 or later. In an online transaction processing (OLTP) environment, the I/O affinity option may provide performance enhancement in high-end, enterprise-level SQL Server environments that are running on computers with 16 or more CPUs. This option supports only SQL Server disk I/Os and does not support any hardware affinity for individual disks or disk controllers.
SQL Server 2000 Service Pack 1 extends the functionality of the SQL Server 2000 affinity mask configuration option by introducing the IO_affinity_mask switch.
The affinity mask configuration option defined in the sp_configure stored procedure allows you to specify which CPUs on a multiprocessor computer are to be used to run threads from an instance of SQL Server. You can use the affinity mask configuration option to exclude SQL Server threads from processors that you want to reserve for operating system processes. For more information about the affinity mask option, see SQL Server 2000 Books Online. Similarly, IO_affinity_mask allows you to specify which CPUs are configured to run SQL Server threads related to I/O operations.
When you are running an instance of SQL Server on large, enterprise-level multiprocessor computers with more than 16 CPUs, you may attain additional performance benefits by using the IO_affinity_mask switch in conjunction with the affinity mask option. This provides the capability to specify which CPUs are affinitized for SQL Server disk operations and which CPUs service the remaining processing associated with SQL Server.
In almost all cases, leaving IO_affinity_mask at its default setting results in the best performance. Some sites may see an improvement in performance by setting the IO_affinity_mask option.
You may create a performance bottleneck for non-disk related CPU requirements if the number of CPUs allocated to SQL Server disk IO processing is more than what the system needs for disk IO processing. Conversely, a performance bottleneck for disk IO may be created if you enable less CPUs to SQL Server disk IO processing than what the system needs for disk IO processing.
To activate the IO_affinity_mask option, you must add an extra static input parameter as a SQL Server startup parameter in Enterprise Manager or on the sqlservr command line. (Currently, this option is not available for the sp_configure stored procedure.) For example:
A description and information about IO_affinity_mask follows:
If you specify the IO_affinity_mask switch, Microsoft suggests that you use it in conjunction with the affinity mask configuration option. Make sure not to enable a CPU for both the IO_affinity_mask switch and affinity_mask option.
The bits corresponding to each CPU should be in one of the following three states:
SQL Server requires systems to support ‘ guaranteed delivery to stable media ’ as outlined under the Microsoft SQL Server Always-On Storage Solution Review program. Fo For more information about the input and output requirements for the SQL Server database engine, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/967576/ )Microsoft SQL Server Database Engine Input/Output Requirements
Article ID: 298402 - Last Review: October 26, 2007 - Revision: 1.3