軟體 NUMA (SQL Server)Soft-NUMA (SQL Server)

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse 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 會要求您編輯登錄來新增節點設定親和性遮罩,並且是在主機層級進行設定,而不是根據執行個體。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 Database EngineSQL Server Database Engine 服務啟動時,會自動在資料庫執行個體層級設定軟體 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 Database EngineSQL 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 Database EngineSQL Server Database Engine 在啟動時於每個 NUMA 節點或通訊端偵測到超過八個實體核心,就會根據預設自動建立軟體 NUMA 節點。With SQL Server 2016 (13.x)SQL Server 2016 (13.x), whenever the SQL Server Database EngineSQL 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. 當偵測到每個通訊端的實體核心超過八個時,SQL Server Database EngineSQL Server Database Engine 會建立軟體 NUMA 節點,此節點在理想情況下會包含八個核心,但可以減少至每個節點五個或增加至最多九個邏輯核心。When the detected number of physical cores is more than eight per socket, the SQL Server Database EngineSQL 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.

您可以搭配使用 ALTER SERVER CONFIGURATION (Transact-SQL) 陳述式與 SET SOFTNUMA 引數來停用或重新啟用軟體 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 節點在每個節點或通訊端有超過八個實體核心時,您會在 SQL Server 錯誤記錄檔中看到的軟體 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 使用自動軟體 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

若要手動設定 SQL ServerSQL Server 使用軟體 NUMA,請停用自動軟體 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,請使用 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 EngineDatabase Engine,軟體 NUMA 設定才會生效。After modifying the registry, you must restart the Database EngineDatabase 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 EngineDatabase Engine 執行個體 A 是設定成使用 CPU 0 到 3。instance A is configured to use CPUs 0 through 3. Database EngineDatabase Engine 的第二個執行個體安裝及設定為使用 CPU 4 到 7。A second instance of the Database EngineDatabase 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. 如需詳細資訊,請參閱 How It Works:Soft NUMA, I/O Completion Thread, Lazy Writer Workers and Memory Nodes(運作方式:軟體 NUMA、I/O 完成執行緒、LAZY WRITER 背景工作角色和記憶體節點)。For more information, see How It Works: Soft NUMA, I/O Completion Thread, Lazy Writer Workers and Memory Nodes.

注意

當您升級 執行個體時,不會複製 軟體 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、1、2 和 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、5、6 和 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),新增下列登錄機碼,以將軟體 NUMA 節點 0 對應到 CPU 0 和 1、將軟體 NUMA 節點 1 對應到 CPU 2 和 3,並且將軟體 NUMA 節點 2 對應到 CPU 4、5、6 和 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 伺服器。該伺服器的每個插槽 (共有四個插槽) 安裝有 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. 您可建立的軟體 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 來檢視軟體 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. 您必須搭配使用 ALTER SERVER CONFIGURATION (Transact-SQL) 陳述式與 SET SOFTNUMA 引數。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)