软件 NUMA (SQL Server)Soft-NUMA (SQL Server)

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

新式处理器在每个插槽上都有多个内核。Modern processors have multiple cores per socket. 通常每个插槽表示为单个 NUMA 节点。Each socket is represented, usually, as a single NUMA node. SQL Server 数据库引擎在每个 NUMA 节点上划分多个不同内部结构和分区服务线程。The SQL Server database engine partitions various internal structures and partitions service threads per NUMA node. 借助每个插槽包含 10 个或更多内核的处理器,使用软件 NUMA 拆分硬件 NUMA 节点通常可以提高可伸缩性和性能。With processors containing 10 or more cores per socket, using software NUMA to split hardware NUMA nodes generally increases scalability and performance. SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 之前,基于软件的 NUMA (soft-NUMA) 需要编辑注册表才能添加节点配置关联掩码,并且是针对主机级别而不是每个实例进行配置。Prior to SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2, software-based NUMA (soft-NUMA) required you to edit the registry to add a node configuration affinity mask, and was configured at the host level, rather than per instance. SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 和 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,当 SQL Server 数据库引擎SQL Server Database Engine 服务启动时会在数据库实例级别自动配置 soft-NUMA。Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 and SQL Server 2016 (13.x)SQL Server 2016 (13.x), soft-NUMA is configured automatically at the database-instance level when the SQL Server 数据库引擎SQL Server Database Engine service starts.

备注

软件 NUMA 不支持热添加处理器。Hot-add processors are not supported by soft-NUMA.

自动软件 NUMAAutomatic Soft-NUMA

使用 SQL Server 2016 (13.x)SQL Server 2016 (13.x),只要 SQL Server 数据库引擎SQL Server Database Engine 在启动时检测到每个 NUMA 或插槽内的物理内核数目超过 8 个,在默认情况下就会自动创建 soft-NUMA 节点。With SQL Server 2016 (13.x)SQL Server 2016 (13.x), whenever the SQL Server 数据库引擎SQL Server Database Engine detects more than eight physical cores per NUMA node or socket at startup, soft-NUMA nodes are created automatically by default. 计算节点中的物理内核时,不区分超线程处理器内核。Hyper-threaded processor cores are not differentiated when counting physical cores in a node. 如果检测到每个插槽内的物理内核数目多于 8 个,SQL Server 数据库引擎SQL Server Database Engine 会创建 soft-NUMA 节点,在理想情况下包含 8 个内核,但也可少至每节点 5 个逻辑内核,或多达 9 个。When the detected number of physical cores is more than eight per socket, the SQL Server 数据库引擎SQL Server Database Engine creates soft-NUMA nodes that ideally contain eight cores, but can go down to five or up to nine logical cores per node. 可以通过 CPU 关联掩码限制硬件节点的大小。The size of the hardware node can be limited by a CPU affinity mask. NUMA 节点数永远不会超过支持的 NUMA 节点的最大数目。The number of NUMA nodes never exceeds the maximum number of supported NUMA nodes.

可以使用带有 SET SOFTNUMA 参数的 ALTER SERVER CONFIGURATION (Transact-SQL) 语句来禁用或重新启用 soft-NUMA。You can disable or re-enable soft-NUMA using the ALTER SERVER CONFIGURATION (Transact-SQL) statement with the SET SOFTNUMA argument. 更改此设置的值后,需要重新启动数据库引擎才会生效。Changing the value of this setting requires a restart of the database engine to take effect.

SQL ServerSQL Server 检测到硬件 NUMA 节点的每个节点或插槽内物理内核多于 8 个时,此图显示可在 SQL Server 错误日志中看到的关于 soft-NUMA 的信息类型。The figure below shows the type of information regarding soft-NUMA that you see in the SQL Server error log, when SQL ServerSQL Server detects hardware NUMA nodes with greater than eight physical cores per each node or socket.

2016-11-14 13:39:43.17 Server      SQL Server detected 2 sockets with 12 cores per socket and 24 logical processors per socket, 48 total logical processors; using 48 logical processors based on SQL Server licensing. This is an informational message; no user action is required.     
2016-11-14 13:39:43.35 Server      Automatic soft-NUMA was enabled because SQL Server has detected hardware NUMA nodes with greater than 8 physical cores.     
2016-11-14 13:39:43.63 Server      Node configuration: node 0: CPU mask: 0x0000000000555555:0 Active CPU mask: 0x0000000000555555:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.    
2016-11-14 13:39:43.63 Server      Node configuration: node 1: CPU mask: 0x0000000000aaaaaa:0 Active CPU mask: 0x0000000000aaaaaa:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.    
2016-11-14 13:39:43.63 Server      Node configuration: node 2: CPU mask: 0x0000555555000000:0 Active CPU mask: 0x0000555555000000:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.     
2016-11-14 13:39:43.63 Server      Node configuration: node 3: CPU mask: 0x0000aaaaaa000000:0 Active CPU mask: 0x0000aaaaaa000000:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.   

备注

SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 开始,使用跟踪标志 8079 以使 SQL ServerSQL Server 能够使用自动 Soft-NUMA。Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2, use trace flag 8079 to allow SQL ServerSQL Server to use Automatic Soft-NUMA. SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,此行为由引擎控制,跟踪标志 8079 不再有效。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) this behavior is controlled by the engine and trace flag 8079 has no effect. 有关详细信息,请参阅 DBCC TRACEON - 跟踪标志For more information, see DBCC TRACEON - Trace Flags.

手动软件 NUMAManual Soft-NUMA

通过禁用自动 soft_NUMA 并编辑注册表,可手动配置 SQL ServerSQL Server 来使用 soft-NUMA,以添加节点配置关联掩码。To manually configure SQL ServerSQL Server to use soft-NUMA, disable automatic soft-NUMA, and edit the registry to add a node configuration affinity mask. 借助这种方法,软件 NUMA 掩码可以表示为二进制、DWORD(十六进制或十进制)或 QWORD(十六进制或十进制)注册表项。When using this method, the soft-NUMA mask can be stated as a binary, DWORD (hexadecimal or decimal), or QWORD (hexadecimal or decimal) registry entry. 若要配置前 32 个 CPU 以后的 CPU,请使用 QWORD 或 BINARY 注册表值(在低于 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 的版本中,不能使用 QWORD 值)。To configure more than the first 32 CPUs use QWORD or BINARY registry values (QWORD values cannot be used prior to SQL Server 2012 (11.x)SQL Server 2012 (11.x)). 修改注册表后,必须重启 数据库引擎Database Engine,soft-NUMA 配置才会生效。After modifying the registry, you must restart the 数据库引擎Database Engine for the soft-NUMA configuration to take effect.

提示

CPU 从 0 开始编号。CPUs are numbered starting with 0.

警告

错误编辑注册表会严重损坏您的系统。Incorrectly editing the registry can severely damage your system. 更改注册表项之前,建议您备份计算机中的所有重要数据。Before making changes to the registry, we recommend that you back up any valued data on the computer.

考虑这样一个示例:计算机有八个 CPU,但没有硬件 NUMA。Consider the example of a computer with eight CPUs, that does not have hardware NUMA. 配置了三个软件 NUMA 节点。Three soft-NUMA nodes are configured.
数据库引擎Database Engine实例 A 配置为使用第 0 个到第 3 个 CPU。数据库引擎Database Engine instance A is configured to use CPUs 0 through 3. 安装 数据库引擎Database Engine 的第二个实例并将其配置为使用第 5 个到第 8 个 CPU。A second instance of the 数据库引擎Database Engine is installed and configured to use CPUs 4 through 7. 该示例可以直观表示为:The example can be visually represented as:

CPUs 0 1 2 3 4 5 6 7

Soft-NUMA <-N0--><-N1-><----N2---->

SQL Server <instance A ><instance B>

大量使用 I/O 的实例 A 现在有两个 I/O 线程和一个惰性编写器线程。Instance A, which experiences significant I/O, now has two I/O threads and one lazy writer thread. 执行大量占用处理器操作的实例 B 仅有一个 I/O 线程和一个惰性编写器线程。Instance B, which performs processor-intensive operations, has only one I/O thread and one lazy writer thread. 可以向实例分配不同的内存量,但是与硬件 NUMA 不同,它们都从同一个操作系统内存块中接收内存,并且不具有从内存到处理器的关联。Differing amounts of memory can be assigned to the instances, but unlike hardware NUMA, they both receive memory from the same operating system memory block, and there is no memory-to-processor affinity.

惰性编写器线程与物理 NUMA 内存节点的 SQLOS 视图有关。The lazy writer thread is tied to the SQLOS view of the physical NUMA memory nodes. 因此,不管存在什么硬件,物理 NUMA 节点都将等于创建的惰性编写器线程数。Therefore, whatever the hardware presents as the number of physical NUMA nodes, this will be the number of lazy writer threads that are created. 有关详细信息,请参阅工作原理:Soft NUMA、I/O 完成线程、惰性编写器工作线程和内存节点For more information, see How It Works: Soft NUMA, I/O Completion Thread, Lazy Writer Workers and Memory Nodes.

备注

升级 的实例时,不复制 Soft-NUMA SQL ServerSQL Server注册表项。The Soft-NUMA registry keys are not copied when you upgrade an instance of SQL ServerSQL Server.

设置 CPU 关联掩码Set the CPU affinity mask

在实例 A 中运行下面的语句,通过设置 CPU 关联掩码将它配置为使用 CPU 0、CPU 1、CPU 2 和 CPU 3:Run the following statement on instance A to configure it to use CPUs 0, 1, 2, and 3 by setting the CPU affinity mask:

ALTER SERVER CONFIGURATION   
SET PROCESS AFFINITY CPU=0 TO 3;  

在实例 B 中运行下面的语句,通过设置 CPU 关联掩码将它配置为使用 CPU 4、CPU 5、CPU 6 和 CPU 7:Run the following statement on instance B to configure it to use CPUs 4, 5, 6, and 7 by setting the CPU affinity mask:

ALTER SERVER CONFIGURATION   
SET PROCESS AFFINITY CPU=4 TO 7;  

将软件 NUMA 节点映射到 CPUMap soft-NUMA nodes to CPUs

使用注册表编辑器程序 (regedit.exe) 添加以下注册表项,从而将 soft-NUMA 节点 0 映射到 CPU 0 和 CPU 1、将 soft-NUMA 节点 1 映射到 CPU 2 和 CPU 3,以及将 soft-NUMA 节点 2 映射到 CPU 4、CPU 5、CPU 6 和 CPU 7。Using the Registry Editor program (regedit.exe), add the following registry keys to map soft-NUMA node 0 to CPUs 0 and 1, soft-NUMA node 1 to CPUs 2 and 3, and soft-NUMA node 2 to CPUs 4, 5, 6, and 7.

提示

若要指定 CPU 60 到 63,请使用 QWORD 值 F000000000000000 或 BINARY 值 1111000000000000000000000000000000000000000000000000000000000000。To specify CPUs 60 through 63, use a QWORD value of F000000000000000 or a BINARY value of 1111000000000000000000000000000000000000000000000000000000000000.

在以下示例中,假定你有 DL580 G9 服务器,每个插槽(共 4 个插槽)具有 18 个内核并且每个插槽都位于其自己的 K 组中。In the following example, assume you have a DL580 G9 server, with 18 cores per socket (in four sockets), and each socket is in its own K-group. 可能创建如下所示的 soft-NUMA 配置:每个节点六个内核,每个组三个节点,总共有四个组。A soft-NUMA configuration that you might create would look something like the following: six cores per Node, three nodes per group, four groups.

具有多个 K 组的 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 服务器示例Example for a SQL Server 2016 (13.x)SQL Server 2016 (13.x) server with multiple K-Groups 类型Type 值名称Value name 值数据Value data
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node0HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node0 DWORDDWORD CPUMaskCPUMask 0x3F0x3F
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node0HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node0 DWORDDWORD 分组Group 00
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node1HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node1 DWORDDWORD CPUMaskCPUMask 0x0fc00x0fc0
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node1HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node1 DWORDDWORD 分组Group 00
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node2HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node2 DWORDDWORD CPUMaskCPUMask 0x3f0000x3f000
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node2HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node2 DWORDDWORD 分组Group 00
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node3HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node3 DWORDDWORD CPUMaskCPUMask 0x3F0x3F
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node3HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node3 DWORDDWORD 分组Group 11
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node4HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node4 DWORDDWORD CPUMaskCPUMask 0x0fc00x0fc0
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node4HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node4 DWORDDWORD 分组Group 11
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node5HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node5 DWORDDWORD CPUMaskCPUMask 0x3f0000x3f000
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node5HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node5 DWORDDWORD 分组Group 11
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node6HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node6 DWORDDWORD CPUMaskCPUMask 0x3F0x3F
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node6HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node6 DWORDDWORD 分组Group 22
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node7HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node7 DWORDDWORD CPUMaskCPUMask 0x0fc00x0fc0
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node7HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node7 DWORDDWORD 分组Group 22
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node8HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node8 DWORDDWORD CPUMaskCPUMask 0x3f0000x3f000
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node8HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node8 DWORDDWORD 分组Group 22
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node9HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node9 DWORDDWORD CPUMaskCPUMask 0x3F0x3F
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node9HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node9 DWORDDWORD 分组Group 33
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node10HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node10 DWORDDWORD CPUMaskCPUMask 0x0fc00x0fc0
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node10HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node10 DWORDDWORD 分组Group 33
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node11HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node11 DWORDDWORD CPUMaskCPUMask 0x3f0000x3f000
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node11HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node11 DWORDDWORD 分组Group 33

元数据Metadata

可以使用以下 DMV 来查看 soft-NUMA 的当前状态和配置。You can use the following DMVs to view the current state and configuration of soft-NUMA.

备注

虽然可以使用 sp_configure (Transact-SQL) 来查看自动软件 NUMA 的运行值,但不能使用 sp_configure 更改其值。While you can view the running value for automatic soft-NUMA using sp_configure (Transact-SQL), you cannot change its value using sp_configure. 必须使用带有 SET SOFTNUMA 参数的 ALTER SERVER CONFIGURATION (Transact-SQL) 语句。You must use the ALTER SERVER CONFIGURATION (Transact-SQL) statement with the SET SOFTNUMA argument.

另请参阅See Also

将 TCP IP 端口映射到 NUMA 节点 (SQL Server) Map TCP IP Ports to NUMA Nodes (SQL Server)
affinity mask 服务器配置选项 affinity mask Server Configuration Option
ALTER SERVER CONFIGURATION (Transact-SQL) ALTER SERVER CONFIGURATION (Transact-SQL)
sys.dm_os_nodes (Transact-SQL)sys.dm_os_nodes (Transact-SQL)