INF: Understanding How to Set the SQL Server I/O Affinity Option (298402)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions) SP1
This article was previously published under Q298402

SUMMARY

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.

MORE INFORMATION

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:

-I<IO_affinity_mask>

A description and information about IO_affinity_mask follows:

  • Specify which CPUs in a multiprocessor computer are eligible to process SQL Server disk I/O operations.
  • The IO_affinity_mask is a number that can be specified in decimal (not particularly convenient for a large number of CPUs), or hexadecimal if preceded by '0x' or '0X'.
  • The mask is a bitmap in which the rightmost bit specifies the lowest-order CPU(0), the next rightmost bit specifies the next lowest-order CPU(1), and so on.
  • A one byte IO_affinity_mask covers up to 8 CPUs in a multiprocessor computer, a two byte mask covers up to 16 CPUs, a three byte mask covers up to 24 CPUs, and a four byte mask covers up to 32 CPUs.
  • A one bit in the pattern specifies that the corresponding CPU is eligible to perform SQL Server disk IO operations; a zero bit specifies that no SQL Server disk IO operations are to be scheduled for the corresponding CPU.
  • When all bits are set to 0, or IO_affinity_mask is not specified, then SQL Server disk I/O is scheduled to any of the CPUs eligible to process SQL Server threads.
  • The value of the IO_affinity_mask switch cannot be changed while SQL Server is running. To change the IO_affinity_mask switch you must shut down the instance of SQL Server, and then restart it with a new value specified for IO_affinity_mask.
SQL Server 2000 interprets the IO_affinity_mask bits the same way it interprets the affinity mask bits.

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:
  • 0 in both the IO_affinity_mask switch and the affinity mask option.
  • 1 in the IO_affinity_mask switch and 0 in the affinity mask option.
  • 0 in the IO_affinity_mask switch and 1 in the affinity mask option.
As an example, consider a 32 processor computer that is running SQL Server, which requires the following configuration:
  • SQL Server to use 24 out of the 32 CPUs available.
  • Allocate 8 CPUs to SQL Server disk IO and 16 CPUs to all other SQL Server processing.
To achieve this configuration, use these steps:
  1. Set the affinity mask option to associate SQL Server with 16 CPUs for SQL Server processing other than the disk I/O:
      sp_configure "affinity mask", 0x0000FFFF
  go
  reconfigure with override
  go
  2. Shutdown SQL Server.
  3. Start SQL Server with the following switch. This configures SQL Server to use 8 CPUs for Disk I/O: 
      sqlservr -I0x00FF0000
Modification Type:MajorLast Reviewed:7/13/2001
Keywords:kbinfo kbSQLServ2000sp1fix KB298402
©2002 Microsoft Corporation. All rights reserved.