affinity I/O mask Option
To carry out multitasking, Microsoft Windows 2000 and Windows Server 2003 sometimes move process threads among different processors. Although efficient from an operating system point of view, this activity can reduce Microsoft SQL Server performance under heavy system loads, as each processor cache is repeatedly reloaded with data. Assigning processors to specific threads can improve performance under these conditions by eliminating processor reloads; such an association between a thread and a processor is called processor affinity.
SQL Server 2005 supports processor affinity by means of two affinity mask options: affinity mask (also known as CPU affinity mask) and affinity I/O mask. For more information on the affinity mask option, see affinity mask Option. CPU and I/O affinity support for servers with 33 to 64 processors requires the additional use of the affinity64 mask Option and affinity64 I/O mask Option respectively.
Note
Affinity support for servers with 33 to 64 processors is only available on 64-bit operating systems.
The affinity I/O mask option binds SQL Server disk I/O to a specified subset of CPUs. In high-end SQL Server online transactional processing (OLTP) environments, this extension can enhance the performance of SQL Server threads issuing I/Os. This enhancement does not support hardware affinity for individual disks or disk controllers.
The value for affinity I/O mask specifies which CPUs in a multiprocessor computer are eligible to process SQL Server disk I/O operations. The mask is a bitmap in which the rightmost bit specifies the lowest-order CPU(0), the bit to its immediate left specifies the next-lowest-order CPU(1), and so on. To configure more than 32 processors, set both the affinity I/O mask and the affinity64 I/O mask.
The values for affinity I/O mask are as follows:
- A 1-byte affinity I/O mask covers up to 8 CPUs in a multiprocessor computer.
- A 2-byte affinity I/O mask covers up to 16 CPUs in a multiprocessor computer.
- A 3-byte affinity I/O mask covers up to 24 CPUs in a multiprocessor computer.
- A 4-byte affinity I/O mask covers up to 32 CPUs in a multiprocessor computer.
- To cover more than 32 CPUs, configure a four-byte affinity I/O mask for the first 32 CPUs and up to a four-byte affinity64 I/O mask for the remaining CPUs.
A 1 bit in the affinity I/O pattern specifies that the corresponding CPU is eligible to perform SQL Server disk I/O operations; a 0 bit specifies that no SQL Server disk I/O operations should be scheduled for the corresponding CPU. When all bits are set to zero, or affinity I/O mask is not specified, SQL Server disk I/O is scheduled to any of the CPUs eligible to process SQL Server threads.
Because setting the SQL Server affinity I/O mask option is a specialized operation, it should be used only when necessary. In most cases, the Windows 2000 or Windows Server 2003 default affinity provides the best performance.
When specifying the affinity I/O mask option, you must use it with the affinity mask configuration option. Do not enable the same CPU in both the affinity I/O mask switch and the affinity mask option. The bits corresponding to each CPU should be in one of the following three states:
- 0 in both the affinity I/O mask option and the affinity mask option.
- 1 in the affinity I/O mask option and 0 in the affinity mask option.
- 0 in the affinity I/O mask option and 1 in the affinity mask option.
The affinity I/O mask option is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change affinity I/O mask only when show advanced options is set to 1. In SQL Server 2005, reconfiguring the affinity I/O mask option requires a restart of the SQL Server instance.
Warning
Do not configure CPU affinity in the Windows operating system and also configure the affinity mask in SQL Server. These settings are attempting to achieve the same result, and if the configurations are inconsistent, you may have unpredictable results. SQL Server CPU affinity is best configured using the sp_configure option in SQL Server.
See Also
Concepts
Setting Server Configuration Options
Other Resources
Monitoring Resource Usage (System Monitor)
RECONFIGURE (Transact-SQL)
sp_configure (Transact-SQL)