硬體環境
在一台硬體主機16 核心 / 32 執行緒（實體 CPU 為 2 顆 8 核心)
- 有2顆實體 CPU ( socket )
- 每顆實體 CPU ( socket )有8個的核心數, 共有16 個核心數
- 邏輯處理器共有32 個執行緒(vCPU)
- 記憶體: 256G
在此 Host 上安裝 VMware ESXi, 7.0.3 並開立一個 Guest: Windows server 2019
- 賦予 24 個 CPU,
- 記憶體128 GB
- 安裝 SQL Server 2019
診斷環境
執行下列 SQL ，以便精確判定 實體 NUMA 架構
SQL
SELECT cpu_count
,hyperthread_ratio
,socket_count
,cores_per_socket
,numa_node_count
,sql_memory_model_desc
,committed_target_kb
FROM sys.dm_os_sys_info;
---- 假設取得的值如下
24 2 12 2 4 CONVENTIONAL 61440000由 sys.dm_os_sys_info 系統資訊可以揭露這台虛擬機非常關鍵的內部架構：
- cpu_count = 24：虛擬機被指派了 24 個 vCPU。
- hyperthread_ratio = 2：這代表 Windows 與 SQL Server 偵測到超執行緒（Hyper-Threading）已在虛擬層啟動。意即對 SQL Server 來說，這 24 個 vCPU 中，每 2 個 vCPU 會共用同一個虛擬核心。
socket_count = 12且 cores_per_socket = 2：這顯示在 VMware 虛擬機設定中，這 24 個 vCPU 被包裝成了 12 個 Socket（插槽），每個 Socket 有 2 個核心。- numa_node_count = 4：這是最關鍵的數據！ SQL Server 目前內部辨識出 4 個 NUMA 節點（可能來自 VMware 拋給虛擬機的 vNUMA，或 SQL Server 2019 自動啟動的 Soft-NUMA 功能，由 softnuma_configuration = 1 (ON) 可證實後者已啟用）。
有了這組精確的數據，可以作下列的配置建議做進一步的精準修正。
MAXDOP (Degree Of Parallelism)的精準建議
根據微軟與 VMware 針對 Soft-NUMA 與超執行緒（hyperthread_ratio = 2）的官方最佳實務：
- 精準建議值：
4（如果之後效能遇到瓶頸，上限最多不超過6）。 - 原因解析：
- 您的 24 個 vCPU 被 Soft-NUMA 拆成了 4 個節點。計算方式為：24 vCPUs ÷ 4 Nodes = 6 vCPUs/Node。亦即每個 NUMA 節點內只有 6 個邏輯處理器。
- 因為超執行緒比例為 2（hyperthread_ratio = 2），代表這 6 個邏輯處理器實際上只對應到 3 個核心（6 ÷ 2 = 3）。
- 微軟官方強烈建議：MAXDOP 的設定不應超過單一 NUMA 節點內的「實體核心數」。在有超執行緒的環境下，通常會將 MAXDOP 設為 「每個節點的邏輯處理器數量的一半」，也就是 6 ÷ 2 = 3。不過實務上為了對齊二進位制且最大化單一節點效能，設定為
4是在防範跨節點延遲與發揮平行處理效率之間的最佳平衡點。 - 千萬不要設成 8 或更高：如果設成 8，單一查詢為了湊滿 8 個執行緒，會被迫「跨越 NUMA 節點」去撈另一個節點的記憶體，這在 24 vCPU 的 Wide-VM 環境中會引發非常嚴重的記憶體存取延遲（NUMA Remote Memory Latency）。
Cost Threshold for Parallelism (CTFP) 建議
- 建議值：
50。 - 原因：配合 MAXDOP 調降為 4，代表平行處理的寬度變窄了。我們更應該確保只有預估成本高於 50 的重型查詢才去動用這 4 個執行緒，避免小查詢頻繁切換上下文（Context Switching）。
依上述的說明，執行下面的 SQL 語法作參數調整。
SQL
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
-- MAXDOP 將最大平行處理原則限制在 4 (或依據您單一 NUMA 實體核心數調整)
EXEC sp_configure 'max degree of parallelism', 4;
-- 將平行處理成本門檻調高至 50
EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;
GO
針對數據的「其它核心參數優化」
從數據中，還能看出另外兩個需要立即調整的效能隱患：
記憶體模型優化（極度重要）
- 現況：sql_memory_model_desc = CONVENTIONAL
- 危機：這代表 SQL Server 尚未啟用「鎖定記憶體分頁 (Lock Pages in Memory, LPIM)」。在 VMware 環境中，若使用傳統（CONVENTIONAL）記憶體模型，當主機發生微小的記憶體擠壓，Windows 就會允許 VMware 或是 OS 自行將 SQL Server 的快取（Buffer Pool）無情地置換到硬碟的 Pagefile 中，這會造成資料庫集體卡頓。
- 調整動作：
- 在 Windows Server 中開啟
gpedit.msc（本機群組原則編輯器）。 - 路徑：
電腦設定->Windows 設定->安全性設定->本機原則->使用者權利指派。 - 找到 鎖定記憶體分頁 (Lock pages in memory)，按右鍵內容，將執行 SQL Server 服務的那個 Windows 帳戶（例如 NT SERVICE\MSSQLSERVER）新增進去。
- 重啟 SQL Server 服務後，此欄位應會變成 LOCK_PAGES，這能確保 128 GB 記憶體牢牢鎖在記憶體中。
- 在 Windows Server 中開啟
修正 Max Server Memory 數值
- 現況：committed_target_kb = 61440000（約 58.5 GB）。
- 分析：目前 SQL Server 目標只打算吃 58.5 GB，這可能代表您已經限制了它，或者它還沒長上去。既然 VM 總共給了 134,216,724 KB（128 GB），請確保將 SQL Server 的最大記憶體設定在 110 GB ~ 112 GB 之間。
- 優化指令：
SQL
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
-- 設定為 112640 MB (110 GB)
EXEC sp_configure 'max server memory (MB)', 112640;
RECONFIGURE;重啟 SQL Server 服務後，再查看看是否有生效
SQL
SELECT cpu_count
,hyperthread_ratio
,socket_count
,cores_per_socket
,numa_node_count
,sql_memory_model_desc
,committed_target_kb
FROM sys.dm_os_sys_info;
-- 值應該是
24 2 12 2 4 LOCK_PAGES 115343360檢查 SQL Server 的 TempDB 檔案數量
由於 SQL Server 取得的 cpu_count 是 24，在安裝時若沒有刻意調整，SQL Server 2019 預設可能只幫您蓋了 8 個 TempDB 檔案。
建議TempDB 檔案數量：
雖然微軟建議邏輯核心大於 8 時預設建 8 個，但因為您的 VM 規模高達 24 vCPU 且切成了 4 個 Soft-NUMA，如果您的系統有頻繁建立暫存表（#Table）或大量的複雜 Join 運算，建議將 TempDB 的資料檔案（Data Files）數量增加到 12 個（也就是符合 24 ÷ 2 的實體核心比例，或是 4 個 NUMA 節點的倍數），並確保這 12 個檔案的大小與成長大小完全一致，這能完美分散 TempDB 的分配頁面競爭（PFS/GAM 競爭）。
查詢TempDB 的資料檔案數量是多少？
SQL
SELECT * FROM sys.master_files WHERE database_id = 2;
-- 或是
SELECT name ,size ,growth FROM sys.master_files WHERE database_id = 2;
-- 假設回傳值如下
name size growth
tempdev 1024 8192
templog 1024 8192
temp2 1024 8192
temp3 1024 8192
temp4 1024 8192或是從 SSMS (SQL Server Management Studio)查詢
從 sys.master_files 數據中，可以看到目前 TempDB 的配置如下：
- 資料檔案（ROWS）：目前只有 4 個（tempdev, temp2, temp3, temp4）。
- 檔案大小（size）：皆為 1024（代表 1024 個 Page = 8 MB）。
- 成長量（growth）：皆為 8192（代表 8192 個 Page = 64 MB，且 is_percent_growth = 0 固定大小成長）。
這份配置在 24 vCPU 的大型環境下，存在非常高機率的效能瓶頸（PFS/GAM 分配頁面競爭）。以下是針對 TempDB 以及目前環境的優化與調整建議：
TempDB 優化調整建議
調整檔案數量至 8 或 12 個
- 問題：SQL Server 的系統擁有 24 vCPU 且切分為 4 個 Soft-NUMA 節點，卻只配置了 4 個 TempDB 資料檔案。這意味著當多個查詢同時需要用到 #temp 暫存表或進行大排序時，所有的執行緒會瘋狂爭奪這 4 個檔案的標頭，引發嚴重的 PAGELATCH_UP 或 PAGELATCH_EX 等待（即配置頁面競爭）。
- 建議值：調整為 8 個 或 12 個 資料檔案。微軟官方建議邏輯核心大於 8 時，起手式為 8 個。若您的系統屬於高度併發（Concurrent）的 OLTP 或有重度報表，直接拉到 12 個（24 vCPU 的一半，也是 4 個 NUMA 的倍數）能更完美地分散 I/O。
加大初始大小（Initial Size）與確認擺放路徑
- 問題：目前的初始大小只有 8 MB（1024 pages）。如果系統開始運作，8 MB 瞬間就會被塞滿，迫使 SQL Server 頻繁觸發「自動成長（64 MB）」。在自動成長的當下，所有的寫入動作會被暫時凍結（Freeze），造成交易卡頓。
- 建議值：將所有 TempDB 資料檔案的初始大小一次給足（例如：每個檔案直接給 1024 MB 或 2048 MB，視您的硬碟空間而定），並確保所有檔案的初始大小與成長量完全一致。
- 路徑提醒：目前 TempDB 放在
D:\槽。請務必確保 D 槽是高速的 SSD 或 NVMe 磁碟。在 VMware 設定中，該虛擬磁碟的 SCSI 控制器（SCSI Controller）必須改用 VMware Paravirtual (PVSCSI)，才能吞吐 TempDB 的高併發 I/O。
調整 TempDB 的 T-SQL 腳本（以擴充至 12 個檔案、各 1GB 為例）：
SQL
-- 1. 修改現有 4 個檔案的初始大小為 1024MB (1GB)，成長量 64MB
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 1024MB, FILEGROWTH = 64MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = temp2, SIZE = 1024MB, FILEGROWTH = 64MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = temp3, SIZE = 1024MB, FILEGROWTH = 64MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = temp4, SIZE = 1024MB, FILEGROWTH = 64MB);
-- 2. 新增額外 4 個檔案 (temp5 ~ temp8)，保持路徑與大小完全一致
ALTER DATABASE tempdb ADD FILE (NAME = temp5, FILENAME = 'D:\MSSQL2019\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_5.ndf', SIZE = 1024MB, FILEGROWTH = 64MB);
ALTER DATABASE tempdb ADD FILE (NAME = temp6, FILENAME = 'D:\MSSQL2019\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_6.ndf', SIZE = 1024MB, FILEGROWTH = 64MB);
ALTER DATABASE tempdb ADD FILE (NAME = temp7, FILENAME = 'D:\MSSQL2019\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_7.ndf', SIZE = 1024MB, FILEGROWTH = 64MB);
ALTER DATABASE tempdb ADD FILE (NAME = temp8, FILENAME = 'D:\MSSQL2019\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_8.ndf', SIZE = 1024MB, FILEGROWTH = 64MB);
ALTER DATABASE tempdb ADD FILE (NAME = temp9, FILENAME = 'D:\MSSQL2019\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_9.ndf', SIZE = 1024MB, FILEGROWTH = 64MB);
ALTER DATABASE tempdb ADD FILE (NAME = temp10, FILENAME = 'D:\MSSQL2019\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_10.ndf', SIZE = 1024MB, FILEGROWTH = 64MB);
ALTER DATABASE tempdb ADD FILE (NAME = temp11, FILENAME = 'D:\MSSQL2019\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_11.ndf', SIZE = 1024MB, FILEGROWTH = 64MB);
ALTER DATABASE tempdb ADD FILE (NAME = temp12, FILENAME = 'D:\MSSQL2019\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_12.ndf', SIZE = 1024MB, FILEGROWTH = 64MB);
GO(註：此腳本執行後不會影響現有運作，但在下一次重啟 SQL Server 服務後才會正式生效並長出新檔案。)
回顧與統整優化清單（Action Items）
結合系統環境與數據，這台 24 vCPU / 128GB 的大型 SQL Server VM 應進行以下全面優化：
| 項目 | 原始狀態 / 預設值 | 優化建議值 | 目的與效益 |
|---|---|---|---|
| MAXDOP | 0 (無限制) | 4 | 防止單一查詢跨越 4 個 Soft-NUMA 節點，大幅降低跨節點延遲。 |
| CTFP | 5 | 50 | 防止不必要的小型查詢啟動平行處理，降低 CPU 執行緒切換損耗。 |
| 記憶體模型 | CONVENTIONAL | LOCK_PAGES | 啟用「鎖定記憶體分頁」，嚴防 VMware 將 SQL 記憶體置換到硬碟。 |
| Max Memory | 系統浮動 | 112640 MB (110 GB) | 保留 16 GB 給 Windows 與虛擬化驅動，確保 OS 不會因記憶體耗盡崩潰。 |
| TempDB 數量 | 4 個 | 8 個 (或 12 個) | 消除 PAGELATCH 頁面分配競爭，提升暫存運算效能。 |
| TempDB 初始大小 | 8 MB | 1024 MB (1 GB) | 避免頻繁觸發自動成長引發的交易卡頓。 |
建議可以先在維護視窗期間調整 MAXDOP=4、CTFP=50、以及最大伺服器記憶體與鎖定記憶體分頁。
