Skip to content

Latest commit

 

History

History
63 lines (46 loc) · 5.97 KB

affinity-input-output-mask-server-configuration-option.md

File metadata and controls

63 lines (46 loc) · 5.97 KB
Raw
title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords
Server configuration: affinity I/O mask
Learn about the affinity I/O mask option. Use it to enhance the performance of SQL Server threads that issue I/Os by binding disk I/O to specified CPUs.
rwestMSFT
randolphwest
07/18/2024
sql
configuration
conceptual
affinity I/O mask option
processor affinity [SQL Server]
binding processors [SQL Server]
CPU affinity mask option

Server configuration: affinity I/O mask

[!INCLUDE SQL Server]

To carry out multitasking, Windows sometimes move process threads among different processors. Although efficient from an operating system point of view, this activity can reduce [!INCLUDE ssNoVersion] 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.

[!INCLUDE ssNoVersion] supports processor affinity with 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 Configure the affinity mask server configuration option. CPU and I/O affinity support for servers with 33 to 64 processors requires that you also use the affinity64 mask and affinity64 I/O mask server configuration options 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 [!INCLUDE ssNoVersion] disk I/O to a specified subset of CPUs. In high-end [!INCLUDE ssNoVersion] online transactional processing (OLTP) environments, this extension can enhance the performance of [!INCLUDE ssNoVersion] threads issuing I/Os. This enhancement doesn't 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 [!INCLUDE ssNoVersion] 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:

Bytes in mask Number of CPUs
1-byte Up to 8 CPUs
2-byte Up to 16 CPUs
3-byte Up to 24 CPUs
4-byte Up to 32 CPUs

To cover more than 32 CPUs, configure a 4-byte affinity I/O mask for the first 32 CPUs and up to a 4-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 [!INCLUDE ssNoVersion] disk I/O operations. A 0 bit specifies that no [!INCLUDE ssNoVersion] disk I/O operations should be scheduled for the corresponding CPU. When all bits are set to 0, or affinity I/O mask isn't specified, [!INCLUDE ssNoVersion] disk I/O is scheduled to any of the CPUs eligible to process [!INCLUDE ssNoVersion] threads.

Because setting the [!INCLUDE ssNoVersion] affinity I/O mask option is a specialized operation, use it only when necessary. In most cases, the default Windows affinity provides the best performance.

When specifying the affinity I/O mask option, you must use it with the affinity mask configuration option. Don't 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're 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 [!INCLUDE ssNoVersion], reconfiguring the affinity I/O mask option requires a restart of the [!INCLUDE ssNoVersion] instance.

Caution

Don't configure CPU affinity in the Windows operating system and also configure the affinity mask in [!INCLUDE ssNoVersion]. These settings are attempting to achieve the same result, and if the configurations are inconsistent, you can have unpredictable results. [!INCLUDE ssNoVersion] CPU affinity is best configured using the sp_configure system stored procedure in [!INCLUDE ssNoVersion].

Related content