SQL Server 未使用具有 20 個核心限制和關聯遮罩的所有 NUMA 記憶體

我有一台具有此規格的物理 SQL Server 2016 SP1 機器：

• CPU 插槽：2
• 每個插槽的核心數：44
• 多執行緒：是
• 核心數：88
• 記憶體：256 GB
• SQL Server 最大記憶體：240 GB
• NUMA：CPU1 128 GB CPU2 128 GB

我們正在執行 SQL Server 2016 Enterprise Edtion CAL 許可，而不是 Core，因此由於許可限制，我們只能使用 20(40) 個核心。預設情況下，affinity mask 啟用 CPU1 上的前 40 個核心。這意味著不使用 CPU2 和 NUMA 節點記憶體。

我們更改了關聯遮罩以啟用 CPU1 和 CPU2，從而使兩個 NUMA 節點上的記憶體都可用於 SQL Server。

ALTER SERVER CONFIGURATION

SET PROCESS AFFINITY CPU = 1 TO 20, 65 TO 84;

來自 Glenn Berry 的資訊和選擇https://www.sqlskills.com/blogs/glenn/balancing-your-available-sql-server-core-licenses-evenly-across-numa-nodes/

-- SQL Server NUMA node information 

SELECT node_id, node_state_desc, memory_node_id, processor_group, online_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);

-- SQL Server schedulers by NUMA node

SELECT parent_node_id,   SUM(current_tasks_count) AS [current_tasks_count], SUM(runnable_tasks_count) AS [runnable_tasks_count],   SUM(active_workers_count) AS [active_workers_count],   AVG(load_factor) AS avg_load_factor FROM sys.dm_os_schedulers WITH (NOLOCK) WHERE [status] = N'VISIBLE ONLINE' GROUP BY parent_node_id;

David Alcock 的資訊和選擇https://www.sqlshack.com/monitoring-memory-clerk-and-buffer-pool-allocations-in-sql-server/

SELECT TOP 10 [type], SUM(pages_kb) / 1024 AS SizeMb FROM sys.dm_os_memory_clerks GROUP BY [type] ORDER BY SUM(pages_kb) / 1024 DESC

這表明正在使用大約 90 GB 的記憶體。

此伺服器上的數據庫總計為 285 GB。sys.dm_os_buffer_descriptors 報告緩衝池正在使用 65 GB，其中 10 GB 為空。

select * from sys.dm_os_process_memory

physical_memory_in_use_kb large_page_allocations_kb locked_page_allocations_kb total_virtual_address_space_kb virtual_address_space_reserved_kb virtual_address_space_committed_kb virtual_address_space_available_kb page_fault_count memory_utilization_percentage available_commit_limit_kb process_physical_memory_low process_virtual_memory_low 249659780 0 0 137438953344 489700832 251863768 136949252512 715604512 100 47434048 0 0

有沒有辦法確定是否所有記憶體都在使用？或者 4 個節點離線的事實是否意味著這種情況？

非常感謝

1）查看sys.dm_os_memory_nodes的結果。如果您正確配置了 SQL Server，您應該會看到 memory_node_id 0 和 1 的行。這意味著 SQL Server 可以訪問您伺服器上的所有記憶體。

我有 memory_node_id 0 和 1。

2. 查看 sys.dm_os_memory_clerks 的結果，了解您最喜歡的支持 NUMA 的記憶體管理員。例如，您可以查看緩衝池是否分佈在多個 NUMA 節點上：

SELECT memory_node_id, pages_kb FROM sys.dm_os_memory_clerks WHERE [type] = N'MEMORYCLERK_SQLBUFFERPOOL';

您可能會看到記憶體節點 0 和 1 的頁面。您可能不會看到，這並不一定代表問題。

這顯示了節點 0 和 1 的頁面。

3）查看sys.dm_exec_query_resource_semaphores的結果。Target_memory_kb 應該是最大伺服器記憶體的 70-80% 左右。如果它比 192 GB 更接近 96 GB，那麼您可能會遇到問題。

對於 resource_semaphore_id 0，target_memory_kb 為 150-180 GB。較高的值是最大伺服器記憶體的 70%。

這一切都表明所有記憶體都可供 SQL Server 使用，即使目前並未全部使用。並且緩衝池的使用會隨著時間的推移而增加。

謝謝

通常，SQL Server 僅根據需要增加已用記憶體。您不能斷定 NUMA 節點的本地記憶體對 SQL Server 不可用，因為每個 NUMA 節點都有 128 GB 的記憶體，而查詢表明只有 90 GB 的記憶體在使用。如果您想驗證您的配置，我知道有很多方法可以做到這一點，但並非所有方法都對生產友好。在所有情況下，您都可以將現在獲得的結果與使用預設關聯遮罩獲得的結果進行比較。

1）查看sys.dm_os_memory_nodes的結果。如果您正確配置了 SQL Server，您應該會看到memory_node_id0 和 1 的行。這意味著 SQL Server 可以訪問您伺服器上的所有記憶體。

2. 查看sys.dm_os_memory_clerks的結果，了解您最喜歡的支持 NUMA 的記憶體管理員。例如，您可以查看緩衝池是否分佈在多個 NUMA 節點上：

SELECT memory_node_id, pages_kb
FROM sys.dm_os_memory_clerks
WHERE [type] = N'MEMORYCLERK_SQLBUFFERPOOL';

您可能會看到記憶體節點 0 和 1 的頁面。您可能不會看到，這並不一定代表問題。

3. 查看sys.dm_exec_query_resource_semaphores的結果。Target_memory_kb應該是最大伺服器記憶體的 70-80% 左右。如果它比 192 GB 更接近 96 GB，那麼您可能會遇到問題。

4) 啟用TF 834作為啟動參數後重新啟動 SQL Server 。使用跟踪標誌 834，SQL Server 在啟動時將使用大頁面的緩衝池增加到它能夠執行的最大大小。如果來賓作業系統中的記憶體碎片化，您可能看不到最大伺服器記憶體。啟用此跟踪標誌將對您的工作負載產生一些影響，因此我只能建議在非生產場景中執行這樣的測試。但是，如果您確實需要親眼看到 SQL Server 使用所有記憶體，這可能是一個不錯的選擇。

引用自：https://dba.stackexchange.com/questions/203027