affinity mask 服务器配置选项affinity 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

备注

后续版本的 Microsoft SQL Server 将删除该功能。This feature will be removed in a future version of Microsoft SQL Server. 请不要在新的开发工作中使用该功能,并尽快修改当前还在使用该功能的应用程序。Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. 改为使用 ALTER SERVER CONFIGURATION (Transact-SQL)Use ALTER SERVER CONFIGURATION (Transact-SQL) instead.

为了执行多任务, MicrosoftMicrosoft Windows 有时会在不同的处理器之间移动进程线程。To carry out multitasking, MicrosoftMicrosoft Windows sometimes move process threads among different processors. 虽然从操作系统方面而言,这种活动是高效的,但是在高系统负荷的情况下,该活动会降低 SQL ServerSQL Server 的性能,因为每个处理器缓存都会不断地重新加载数据。Although efficient from an operating system point of view, this activity can reduce 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 and reducing thread migration across processors (thereby reducing context switching); such an association between a thread and a processor is called processor affinity.

SQL ServerSQL Server 通过以下两个关联掩码选项来支持处理器关联:affinity mask(也称为 CPU affinity mask)和 affinity I/O mask。supports processor affinity by means of two affinity mask options: affinity mask (also known as CPU affinity mask) and affinity I/O mask. 有关 affinity I/O maskoption 的详细信息,请参阅 affinity I/O mask 服务器配置选项For more information on the affinity I/O maskoption, see affinity Input-Output mask Server Configuration Option. 对具有 33 至 64 个处理器的服务器的 CPU 和 I/O 关联支持还要求分别使用 affinity64 mask 服务器配置选项affinity64 I/O mask 服务器配置选项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.

关联掩码选项存在于 SQL ServerSQL Server的早期版本中,用于动态控制 CPU 关联。The affinity mask option, which existed in earlier releases of SQL ServerSQL Server, dynamically controls CPU affinity.

SQL ServerSQL Server中,可以配置关联掩码选项而无需重新启动 SQL ServerSQL Server实例。In SQL ServerSQL Server, the affinity mask option can be configured without requiring a restart of the instance of SQL ServerSQL Server. 在您正在使用 sp_configure 时,必须在设置配置选项之后运行 RECONFIGURE 或 RECONFIGURE WITH OVERRIDE。When you are using sp_configure, you must run either RECONFIGURE or RECONFIGURE WITH OVERRIDE after setting a configuration option. 使用 SQL Server ExpressSQL Server Express时,更改关联掩码选项不需要重新启动。When you are using SQL Server ExpressSQL Server Express, changing the affinity mask option does require a restart.

关联掩码的更改是动态进行的,因此可以按需启动和关闭用于在 SQL ServerSQL Server中绑定进程线程的 CPU 计划程序。Changes to the affinity masks occur dynamically, allowing for on-demand startup and shutdown of the CPU schedulers that bind process threads within SQL ServerSQL Server. 当服务器上的条件改变时会发生这种情况。This can occur as conditions change on the server. 例如,如果向服务器上添加了新的 SQL ServerSQL Server 实例,可能需要调整关联掩码选项以重新分配处理器负荷。For example, if a new instance of SQL ServerSQL Server is added to the server, it may be necessary to make adjustments to the affinity mask option to redistribute processor load.

对关联位掩码进行修改需要 SQL ServerSQL Server 启用新的 CPU 计划程序并禁用现有的 CPU 计划程序。Modifications to the affinity bitmasks require SQL ServerSQL Server to enable a new CPU scheduler and disable the existing CPU scheduler. 然后就可以在新的或剩余的计划程序上处理新批处理。New batches can then be processed on the new or remaining schedulers.

若要启动新的 CPU 计划程序, SQL ServerSQL Server 应创建新的计划程序并将其添加到标准计划程序列表中。To start a new CPU scheduler, SQL ServerSQL Server creates a new scheduler and adds it to the list of its standard schedulers. 新的计划程序仅用于新加入的批处理。The new scheduler is considered only for the new incoming batches. 当前批处理会继续在同一计划程序上运行。Current batches continue to run on the same scheduler. 当释放了现有工作线程或创建了新的工作线程后,它们就会迁移到新的计划程序。The workers migrate to the new scheduler as they free up, or as new workers are created.

若要关闭某个计划程序,需要等到该计划程序上的所有批处理都完成其活动并退出之后。Shutting down a scheduler requires all batches on the scheduler to complete their activities and exit. 已关闭的计划程序将被标记为脱机,以防在它上面安排新的批处理。A scheduler that has been shut down is marked as offline so that no new batch is scheduled on it.

服务器处于运行状态时,无论添加还是删除了计划程序,永久系统任务 [如锁监视器、检查点、系统任务线程(用于处理 DTC)和信号进程] 都会继续在原计划程序上运行。Whether a new scheduler is added or removed, the permanent system tasks such as lockmonitor, checkpoint, system task thread (processing DTC), and signal process continue to run on the scheduler while the server is operational. 这些永久系统任务不会动态迁移。These permanent system tasks do not dynamically migrate. 若要在各个计划程序上重新为这些系统任务分配处理器负荷,则需要重新启动 SQL ServerSQL Server 实例。To redistribute processor load for these system tasks across schedulers, it is necessary to restart the SQL ServerSQL Server instance. SQL ServerSQL Server 尝试关闭与某个永久系统任务相关联的计划程序时,该任务会继续在脱机计划程序上运行(而不迁移)。If SQL ServerSQL Server attempts to shut down a scheduler associated with a permanent system task, the task continues to run on the offline scheduler (no migration). 此计划程序将被绑定到修改后的关联掩码中的处理器,在修改之前,不应在其关联的处理器上添加任何负荷。This scheduler is bound to the processors in the modified affinity mask and should not put any load on the processor it was affinitized with before the change. 额外的脱机计划程序不会显著影响系统的负荷。Having extra offline schedulers, should not significantly affect the load of the system. 如果不是这样,则需要重新启动数据库服务器以重新配置这些任务。If this is not the case, a database server reboot is required to reconfigure these tasks.

I/O 关联任务(如惰性编写器和日志编写器)直接受 I/O 关联掩码的影响。The I/O affinity tasks (such as lazywriter and logwriter) are directly affected by the I/O affinity mask. 如果惰性编写器和日志编写器任务不被关联,它们将与其他永久任务(如锁监视器或检查点)遵循相同的规则。If the lazywriter and logwriter tasks are not affinitized, they follow the same rules defined for the other permanent tasks such as lockmonitor or checkpoint.

为了确保新的关联掩码有效,RECONFIGURE 命令将验证正常 CPU 与 I/O 关联是否互相排斥。To ensure that the new affinity mask is valid, the RECONFIGURE command verifies that the normal CPU and I/O affinities are mutually exclusive. 如果不互相排斥,将向客户端会话和 SQL ServerSQL Server 错误日志报告一条错误消息,指明不建议进行这种设置。If this is not the case, an error message is reported to the client session and to the SQL ServerSQL Server error log, indicating that such a setting is not recommended. 运行 RECONFIGURE WITH OVERRIDE 选项将允许 CPU 与 I/O 关联不互相排斥。Running RECONFIGURE WITH OVERRIDE options allows CPU and I/O affinities that are not mutually exclusive.

如果指定的关联掩码试图映射到不存在的 CPU,RECONFIGURE 命令会向客户端会话和 SQL ServerSQL Server 错误日志报告一条错误消息。If you specify an affinity mask that attempts to map to a nonexistent CPU, the RECONFIGURE command reports an error message to both the client session and the SQL ServerSQL Server error log. 在这种情况下,RECONFIGURE WITH OVERRIDE 选项将不起作用,并将再次报告相同的配置错误。Using the RECONFIGURE WITH OVERRIDE option has no effect in this case, and the same configuration error is reported again.

您也可以不在由 Windows 2000 或 Windows Server 2003 操作系统分配了特定工作负荷的处理器上执行 SQL ServerSQL Server 活动。You can also exclude SQL ServerSQL Server activity from processors assigned specific workload assignments by the Windows 2000 or Windows Server 2003 operating system. 如果将代表某个处理器的位设置为 1,SQL ServerSQL Server 数据库引擎将会选择该处理器来进行线程分配。If you set a bit representing a processor to 1, that processor is selected by the SQL ServerSQL Server Database Engine for thread assignment. 如果你将“关联掩码”设置为 0(默认值),则 Microsoft Windows 2000 或 Windows Server 2003 计划算法会设置线程的关联。When you set affinity mask to 0 (the default), the Microsoft Windows 2000 or Windows Server 2003 scheduling algorithms set the thread's affinity. 如果你将 关联掩码 设置为任一非零值,则 SQL ServerSQL Server 关联会将该值解释为指定可供选择的处理器的位掩码。When you set affinity mask to any nonzero value, SQL ServerSQL Server affinity interprets the value as a bitmask that specifies those processors eligible for selection.

通过防止 SQL ServerSQL Server 线程在某个特定的处理器上运行,Microsoft Windows 2000 或 Windows Server 2003 可以更好地评估 Windows 专用的系统进程处理。By segregating SQL ServerSQL Server threads from running on particular processors, Microsoft Windows 2000 or Windows Server 2003 can better evaluate the system's handling of processes specific to Windows. 例如,在运行两个 SQL ServerSQL Server 实例(实例 A 和实例 B)的具有 8 个 CPU 的服务器上,系统管理员可以使用关联掩码选项将第一组的 4 个 CPU 分配给实例 A,将第二组的 4 个 CPU 分配给实例 B。若要配置 32 个以上的处理器,应同时设置关联掩码和 affinity64 掩码。For example, on an 8-CPU server running two instances of SQL ServerSQL Server (instance A and B), the system administrator could use the affinity mask option to assign the first set of 4 CPUs to instance A and the second set of 4 to instance B. To configure more than 32 processors, set both the affinity mask and the affinity64 mask. 关联掩码 值如下所示:The values for affinity mask are as follows:

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

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

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

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

  • 若要涵盖 32 个以上的 CPU,可为前 32 个 CPU 配置一个 4 字节关联掩码,为其余的 CPU 配置一个最多 4 字节的 affinity64 掩码。To cover more than 32 CPUs, configure a four-byte affinity mask for the first 32 CPUs and up to a four-byte affinity64 mask for the remaining CPUs.

因为设置 SQL ServerSQL Server 处理器关联是一种专用操作,所以建议只在需要时使用。Because setting SQL ServerSQL Server processor affinity is a specialized operation, it is recommended that it be used only when necessary. 大多数情况下,Windows 2000 或 Windows Server 2003 的默认关联可提供最佳性能。In most cases, the Microsoft Windows 2000 or Windows Server 2003 default affinity provides the best performance. 在设置关联掩码时还应考虑其他应用程序对 CPU 的需求。You should also consider the CPU requirements for other applications when setting the affinity masks. 有关详细信息,请参阅 Windows 操作系统文档。For more information, see your Windows operating system documentation.

备注

可以使用 Windows 系统监视器来查看和分析单个处理器的使用情况。You can use the Windows System Monitor to view and analyze individual processor usage.

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

  • 在 affinity mask 选项和 affinity I/O mask 选项中均为 0。0 in both the affinity mask option and the affinity I/O mask option.

  • 在 affinity mask 选项中为 1,在 affinity I/O mask 选项中为 0。1 in the affinity mask option and 0 in the affinity I/O mask option.

  • 在 affinity mask 选项中为 0,在 affinity I/O mask 选项中为 1。0 in the affinity mask option and 1 in the affinity I/O mask option.

注意

请不要在 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 最好使用 SQL ServerSQL Server中的 sp_configure 选项配置 CPU 关联。CPU affinity is best configured using the sp_configure option in SQL ServerSQL Server.

示例Example

例如,设置关联掩码选项时,如果选择处理器 1、2 和 5 作为可用的处理器,并将位 1、2 和 5 设置为 1,位 0、3、4、6 和 7 设置为 0,则将指定十六进制值 0x26 或等于 38 的十进制值。As an example of setting the affinity mask option, if processors 1, 2, and 5 are selected as available with bits 1, 2, and 5 set to 1 and bits 0, 3, 4, 6, and 7 set to 0, a hexadecimal value of 0x26 or the decimal equivalent of 38 is specified. 从右至左对位进行编号。Number the bits from right to left. 关联掩码选项按从 0 到 31 的方式来计算处理器,这样在以下示例中,计数器 1 表示服务器上的第二个处理器。The affinity mask option starts counting processors from 0 to 31, so that in the following example the counter 1 represents the second processor on the server.

sp_configure 'show advanced options', 1;  
RECONFIGURE;  
GO  
sp_configure 'affinity mask', 38;  
RECONFIGURE;  
GO  

以下是具有 8 个 CPU 的系统的 关联掩码 值。These are affinity mask values for an 8-CPU system.

十进制值Decimal value 二进制位掩码Binary bit mask 允许 SQL Server 线程在哪些处理器上运行Allow SQL Server threads on processors
11 0000000100000001 00
33 0000001100000011 0 和 10 and 1
77 0000011100000111 0、1 和 20, 1, and 2
1515 0000111100001111 0、1、2 和 30, 1, 2, and 3
3131 0001111100011111 0、1、2、3 和 40, 1, 2, 3, and 4
6363 0011111100111111 0、1、2、3、4 和 50, 1, 2, 3, 4, and 5
127127 0111111101111111 0、1、2、3、4、5 和 60, 1, 2, 3, 4, 5, and 6
255255 1111111111111111 0、1、2、3、4、5、6 和 70, 1, 2, 3, 4, 5, 6, and 7

affinity mask 选项是一个高级选项。The affinity mask option is an advanced option. 如果使用 sp_configure 系统存储过程来更改该设置,则仅当“显示高级选项”设置为 1 时,才可以更改 affinity maskIf you are using the sp_configure system stored procedure to change the setting, you can change affinity mask only when show advanced options is set to 1. 执行 Transact-SQLTransact-SQL RECONFIGURE 命令后,新的设置将立即生效,且不需要重新启动 SQL ServerSQL Server 实例。After executing the Transact-SQLTransact-SQL RECONFIGURE command, the new setting takes effect immediately without requiring a restart of the SQL ServerSQL Server instance.

非一致性内存访问 (NUMA)Non-uniform Memory Access (NUMA)

当使用基于硬件的非一致性内存访问 (NUMA) 并设置了关联掩码时,节点中的每个计划程序都将关联到它自己的 CPU。When using hardware based non-uniform memory access (NUMA) and the affinity mask is set, every scheduler in a node will be affinitized to its own CPU. 未设置关联掩码时,每个计划程序都关联到 NUMA 节点内的 CPU 组,映射到 NUMA 节点 N1 的计划程序可对节点中的任何 CPU 计划工作,但是不能对与其他节点关联的 CPU 计划工作。When the affinity mask is not set, each scheduler is affinitized to the group of CPUs within the NUMA node and a scheduler mapped to NUMA node N1 can schedule work on any CPU in the node, but not on CPUs associated with another node.

针对单个 NUMA 节点执行的任何操作都只能使用该节点中的缓冲区页。Any operation running on a single NUMA node can only use buffer pages from that node. 当针对多个节点上的 CPU 以并行方式执行操作时,可以使用所涉及的任何节点中的内存。When an operation is run in parallel on CPUs from multiple nodes, memory can be used from any node involved.

许可问题Licensing Issues

动态关联受 CPU 许可的严格约束。Dynamic affinity is tightly constrained by CPU licensing. SQL ServerSQL Server 不允许对关联掩码选项进行任何违反许可策略的配置。does not allow any configuration of affinity mask options that violates the licensing policy.

启动Startup

如果在 SQL ServerSQL Server 启动期间或在数据库附加期间,指定的关联掩码违反了许可策略,则引擎层将会完成启动进程或数据库附加/还原操作,然后将关联掩码的 sp_configure 运行值重置为零,并向 SQL ServerSQL Server 错误日志发出一条错误消息。If a specified affinity mask violates the licensing policy during SQL ServerSQL Server startup or during database attach, the engine layer will complete the startup process or database attach/restore operation, and then it will reset the sp_configure run value for the affinity mask to zero, issuing an error message to the SQL ServerSQL Server error log.

重新配置Reconfigure

如果在运行 Transact-SQLTransact-SQL RECONFIGURE 命令时,指定的关联掩码违反了许可策略,则系统将向客户端会话和 SQL ServerSQL Server 错误日志报告错误消息,要求数据库管理员重新配置关联掩码。If a specified affinity mask violates the licensing policy when running Transact-SQLTransact-SQL RECONFIGURE command, an error message is reported to the client session and to the SQL ServerSQL Server error log, requiring the database administrator to reconfigure the affinity mask. 在这种情况下,不接受任何 RECONFIGURE WITH OVERRIDE 命令。No RECONFIGURE WITH OVERRIDE command is accepted in this case.

另请参阅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)
ALTER SERVER CONFIGURATION (Transact-SQL)ALTER SERVER CONFIGURATION (Transact-SQL)