affinity I/O mask 服务器配置选项affinity Input-Output mask Server Configuration Option

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

为了执行多任务, MicrosoftMicrosoft Windows 有时会在不同的处理器之间移动进程线程。To carry out multitasking, MicrosoftMicrosoft Windows sometimes move process threads among different processors. 虽然从操作系统方面而言,此活动是高效的,但是在高系统负荷的情况下,该活动会降低 MicrosoftMicrosoft SQL ServerSQL Server 的性能,因为每个处理器缓存都会不断地重新加载数据。Although efficient from an operating system point of view, this activity can reduce MicrosoftMicrosoft SQL ServerSQL 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 ServerSQL Server 通过以下两个关联掩码选项来支持处理器关联: 关联掩码 (也称为 CPU 关联掩码)和 关联 I/O 掩码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 Server Configuration Option. 对于具有 33 至 64 个处理器的服务器,CPU 和 I/O 关联支持还要求分别使用 affinity64 掩码服务器配置选项affinity64 I/O 掩码服务器配置选项CPU and I/O affinity support for servers with 33 to 64 processors requires the additional use of the affinity64 mask Server Configuration Option and affinity64 Input-Output mask Server Configuration Option respectively.

备注

对具有 33 到 64 个处理器的服务器的关联支持仅在 64 位操作系统上可用。Affinity support for servers with 33 to 64 processors is only available on 64-bit operating systems.

关联 I/O 掩码 选项将 SQL ServerSQL Server 磁盘 I/O 绑定到指定的 CPU 子集。The affinity I/O mask option binds SQL ServerSQL Server disk I/O to a specified subset of CPUs. 在高端 SQL ServerSQL Server 联机事务处理 (OLTP) 环境中,此扩展可以提高 SQL ServerSQL Server 线程执行 I/O 的性能。In high-end SQL ServerSQL Server online transactional processing (OLTP) environments, this extension can enhance the performance of SQL ServerSQL Server threads issuing I/Os. 此增强功能不支持对各个磁盘或磁盘控制器的硬件关联。This enhancement does not support hardware affinity for individual disks or disk controllers.

关联 I/O 掩码 的值指定了在多处理器计算机中有哪些 CPU 可用于处理 SQL ServerSQL Server 磁盘 I/O 操作。The value for affinity I/O mask specifies which CPUs in a multiprocessor computer are eligible to process SQL ServerSQL Server disk I/O operations. 该掩码是一个位图,其中最右边的位指定顺序最低的 CPU(0),该位左边的位指定顺序次低的 CPU(1),依此类推。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. 若要配置超过 32 个处理器,请同时设置 关联 I/O 掩码affinity64 I/O 掩码To configure more than 32 processors, set both the affinity I/O mask and the affinity64 I/O mask.

关联 I/O 掩码 值如下所示:The values for affinity I/O mask are as follows:

  • 在多处理器计算机中,1 字节 关联 I/O 掩码 最多可以涵盖 8 个 CPU。A 1-byte affinity I/O mask covers up to 8 CPUs in a multiprocessor computer.

  • 在多处理器计算机中,2 字节 关联 I/O 掩码 最多可以涵盖 16 个 CPU。A 2-byte affinity I/O mask covers up to 16 CPUs in a multiprocessor computer.

  • 在多处理器计算机中,3 字节 关联 I/O 掩码 最多可以涵盖 24 个 CPU。A 3-byte affinity I/O mask covers up to 24 CPUs in a multiprocessor computer.

  • 在多处理器计算机中,4 字节 关联 I/O 掩码 最多可以涵盖 32 个 CPU。A 4-byte affinity I/O mask covers up to 32 CPUs in a multiprocessor computer.

  • 若要包括超过 32 个 CPU,请为前 32 个 CPU 配置一个 4 字节 关联 I/O 掩码 ,并为余下的 CPU 配置最多 4 字节的 affinity64 I/O 掩码。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.

关联 I/O 模式中某一位的值为 1,则指定相应的 CPU 可执行 SQL ServerSQL Server 磁盘 I/O 操作;值为 0,则指定相应的 CPU 未安排任何 SQL ServerSQL Server 磁盘 I/O 操作。A 1 bit in the affinity I/O pattern specifies that the corresponding CPU is eligible to perform SQL ServerSQL Server disk I/O operations; a 0 bit specifies that no SQL ServerSQL Server disk I/O operations should be scheduled for the corresponding CPU. 如果将所有位设置为零或不指定关联 I/O 掩码,则 SQL ServerSQL Server 磁盘 I/O 将由任何可处理 SQL ServerSQL Server 线程的 CPU 执行。When all bits are set to zero, or affinity I/O mask is not specified, SQL ServerSQL Server disk I/O is scheduled to any of the CPUs eligible to process SQL ServerSQL Server threads.

因为设置 SQL ServerSQL Server“关联 I/O 掩码”选项是一项专用操作,所以建议只在需要时使用。Because setting the SQL ServerSQL Server affinity I/O mask option is a specialized operation, it should be used only when necessary. 大多数情况下,Windows 2000 或 Windows Server 2003 的默认关联就可以提供最佳性能。In most cases, the Windows 2000 or Windows Server 2003 default affinity provides the best performance.

指定关联 I/O 掩码选项后,必须将其与关联掩码配置选项一起使用。When specifying the affinity I/O mask option, you must use it with the affinity mask configuration option. 请勿在“关联 I/O 掩码”开关和“关联掩码”选项中启用相同的 CPU。Do not enable the same CPU in both the affinity I/O mask switch and the affinity mask option. 每个 CPU 对应的位必须处于下列三种状态之一:The bits corresponding to each CPU should be in one of the following three states:

  • 在“关联 I/O 掩码”选项和“关联掩码”选项中均为 0。0 in both the affinity I/O mask option and the affinity mask option.

  • 在“关联 I/O 掩码”选项中为 1,在“关联掩码”选项中为 0。1 in the affinity I/O mask option and 0 in the affinity mask option.

  • 在“关联 I/O 掩码”选项中为 0,在“关联掩码”选项中为 1。0 in the affinity I/O mask option and 1 in the affinity mask option.

“关联 I/O 掩码”选项是一个高级选项。The affinity I/O mask option is an advanced option. 如果使用 sp_configure 系统存储过程来更改该设置,则仅当“显示高级选项”设置为 1 时,才可以更改关联 I/O 掩码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. SQL ServerSQL Server 中,重新配置“关联 I/O 掩码”选项要求重启 SQL ServerSQL Server 实例。In SQL ServerSQL Server, reconfiguring the affinity I/O mask option requires a restart of the SQL ServerSQL Server instance.

注意

请不要在 Windows 操作系统中配置 CPU 关联后,还在 SQL ServerSQL Server 中配置关联掩码。Do not configure CPU affinity in the Windows operating system and also configure the affinity mask in SQL ServerSQL Server. 这些设置实现的效果相同,如果配置不一致,则可能会得到意外的结果。These settings are attempting to achieve the same result, and if the configurations are inconsistent, you may have unpredictable results. SQL ServerSQL Server 最好使用 中的 sp_configure SQL ServerSQL Server选项配置 CPU 关联。CPU affinity is best configured using the sp_configure option in SQL ServerSQL Server.

另请参阅See Also

监视资源使用情况(系统监视器) Monitor Resource Usage (System Monitor)
RECONFIGURE (Transact-SQL) RECONFIGURE (Transact-SQL)
服务器配置选项 (SQL Server) Server Configuration Options (SQL Server)
sp_configure (Transact-SQL)sp_configure (Transact-SQL)