SQL Server NUMA

SQL Server NUMA (Non-Uniform Memory Access)

自動軟體 NUMA

若要手動設定 SQL Server 使用軟體 NUMA，請停用自動軟體 NUMA，並編輯登錄來新增節點設定親和性遮罩。

使用 SQL Server 2016 (13.x) 時，只要 SQL Server Database Engine 在啟動時於每個 NUMA 節點或通訊端偵測到超過八個實體核心，就會根據預設自動建立軟體 NUMA 節點。 計算節點中的實體核心時，不會區分超執行緒處理器核心。 當偵測到每個通訊端的實體核心超過八個時，SQL Server Database Engine 會建立軟體 NUMA 節點，此節點在理想情況下會包含八個核心，但可以減少至每個節點五個或增加至最多九個邏輯核心。 硬體節點的大小可由 CPU 關連遮罩限制。 NUMA 節點數目永遠不會超過支援的 NUMA 節點數目上限。

您可以搭配使用 ALTER SERVER CONFIGURATION (Transact-SQL) 陳述式與 SET SOFTNUMA 引數來停用或重新啟用軟體 NUMA。 變更此設定值需要重新啟動資料庫引擎才會生效。

手動軟體 NUMA

若要手動設定 SQL Server 使用軟體 NUMA，請停用自動軟體 NUMA，並編輯登錄來新增節點設定親和性遮罩。

1.編輯登錄

設定舉例:

Database Engine 執行個體 A 是設定成使用 CPU 0 到 3。 Database Engine 的第二個執行個體安裝及設定為使用 CPU 4 到 7。 

2.設定 CPU 相似性遮罩

對執行個體 A 執行下列陳述式，藉由設定 CPU 相似性遮罩來設定它使用 CPU 0、1、2 和 3：

ALTER SERVER CONFIGURATION   

SET PROCESS AFFINITY CPU=0 TO 3;

對執行個體 B 執行下列陳述式，藉由設定 CPU 相似性遮罩來設定它使用 CPU 4、5、6 和 7

ALTER SERVER CONFIGURATION   

SET PROCESS AFFINITY CPU=4 TO 7;

3.將軟體 NUMA 節點對應到 CPU

舉例

使用登錄編輯程式 (regedit.exe)，新增下列登錄機碼，

將軟體 NUMA 節點 0 對應到 CPU 0 和 1

將軟體 NUMA 節點 1 對應到 CPU 2 和 3

將軟體 NUMA 節點 2 對應到 CPU 4、5、6 和 7。

NUMA configurations

1.ERRORLOG

當 SQL Server 偵測到硬體 NUMA 節點在每個節點或通訊端有超過八個實體核心時，您會在 SQL Server 錯誤記錄檔中看到的軟體 NUMA 資訊類型。

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.

For each NUMA node there is in the server there will be a line in the ERRORLOG file.

Server Node configuration: node 0: CPU mask: 0x00000000000000ff:0 Active CPU mask: 0x00000000000000ff:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

000000ff for 8 表示這個NUMA node配置8個core

2.performance monitor or sys.dm_os_performance_counters DMV

SELECT  *

FROM    sys.dm_os_performance_counters

WHERE   object_name = 'SQLServer:Buffer Node'

        AND counter_name = 'Page life expectancy'

如果回傳2筆表示有2個硬體NUMA node

Errorlog 自動啟動軟體NUMA，分配成4個NUMA node

2021-06-18 10:08:31.49 Server      SQL Server detected 2 sockets with 16 cores per socket and 32 logical processors per socket, 64 total logical processors; using 64 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

2021-06-18 10:08:32.38 Server      Automatic soft-NUMA was enabled because SQL Server has detected hardware NUMA nodes with greater than 8 physical cores.

2021-06-18 10:08:32.53 Server      Node configuration: node 0: CPU mask: 0x0000000055555555:0 Active CPU mask: 0x0000000055555555:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

2021-06-18 10:08:32.53 Server      Node configuration: node 1: CPU mask: 0x00000000aaaaaaaa:0 Active CPU mask: 0x00000000aaaaaaaa:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

2021-06-18 10:08:32.53 Server      Node configuration: node 2: CPU mask: 0x5555555500000000:0 Active CPU mask: 0x5555555500000000:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

2021-06-18 10:08:32.53 Server      Node configuration: node 3: CPU mask: 0xaaaaaaaa00000000:0 Active CPU mask: 0xaaaaaaaa00000000:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

SELECT node_id, node_state_desc, memory_node_id, processor_group, cpu_count, online_scheduler_count,
idle_scheduler_count, active_worker_count, avg_load_balance, resource_monitor_state
FROM sys.dm_os_nodes
WITH(NOLOCK)
WHERE node_state_desc <> N'ONLINE DAC' OPTION (RECOMPILE)

With SQL Server 2016 (13.x), whenever the 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. 

When the detected number of physical cores is more than eight per socket, the 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. The size of the hardware node can be limited by a CPU affinity mask. The number of NUMA nodes never exceeds the maximum number of supported NUMA nodes.

Soft-NUMA (SQL Server)

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/soft-numa-sql-server?view=sql-server-ver15