硬體環境
在一台硬體主機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 個 執行緒(vCPU)
- 記憶體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、以及最大伺服器記憶體與鎖定記憶體分頁。
其他補充
Guest 的 vCPU 由 24 降為 12 vs16
當前的硬體架構下，這是一個百利而無一害的優化決定。許多人直覺認為「CPU 給越多越快」，但在虛擬化（VMware ESXi）環境中，過度指派（Overcommit）大於實體核心數的 vCPU，反而會導致嚴重的效能倒退（Performance Degradation）。
將 vCPU 從 24 降為 12 是一個非常合適且安全的調整方向，甚至在特定硬體架構下，12 vCPU 的效能穩定度會比 16 vCPU 還要更高。
可以根據這台 SQL Server 的業務負載型態來做最後決定：
- 選擇 12 vCPU 的情境：
- 這台實體主機上若還有裝其他的虛擬機（不論多小）。
- 若資料庫屬於 OLTP 類型（電商、ERP、大量使用者同時上線登錄資料、點查，CPU 主要是應付大量連線而不是單一複雜計算）。
- 預期效果：回應時間極度平穩，徹底告別因虛擬化排程引發的瞬間卡頓。
- 選擇 16 vCPU 的情境：
- 這台實體主機是 SQL Server 專用機（Guset VM 只有這台大傢伙，沒有其他 VM 搶資源）。
- 若資料庫屬於 OLAP / 報表 / 資料倉儲類型（經常需要跑幾百萬筆資料的 Heavy Join、統計報表、Big Data 運算）。
- 預期效果：單一大型查詢吞吐量最大化。
因為實體主機是 2 顆 8 核心 (雙腳座 Socket，共 16 實體核心 / 32 執行緒)，這意味著硬體架構的單一實體 NUMA 節點上限就是「8 個實體核心」。
在這個硬體限制下，將 Guest VM 的 vCPU 降為 12 個，是比 16 個更完美的黃金配置！
為什麼「雙 8 核」配 12 vCPU 最完美？
- 避免 16 vCPU 引發的「跨節點記憶體延遲（NUMA Remote Memory Access）」
- 如果您配置 16 vCPU，扣除超執行緒（HT）後，VMware 勢必得將這 16 個核心拆在 2 顆不同的實體 CPU 上（例如 Socket 0 給 8 核，Socket 1 給 8 核）。這會導致 SQL Server 在執行大查詢時，頻繁透過 CPU 之間的總線去存取另一顆 CPU 的記憶體，效能大打折扣。
- 如果配置 12 vCPU，VMware 內部通常會切成 2 個 vNUMA 節點（每節點 6 vCPU）。這 6 個 vCPU 可以完全封閉在各自的實體 8 核心 Socket 內，各自擁有充足的本地記憶體頻寬，本地存取率接近 100%。
- 為實體主機預留 4 顆實體核心的核心紅利
- 主機總共 16 實體核心，VM 拿走 12 個，剩下的 4 個實體核心（搭配超執行緒共 8 個邏輯執行緒），剛好可以留給 VMware ESXi 系統核心、儲存網路驅動程式（Storage/Network I/O Handling）以及防毒或備份等背景作業。
- 這樣能保證您的 SQL vCPU 絕對不會遇到 CPU Ready Time（排程空轉等待），維持極致的低延遲。
12 vCPU / 雙 8 核 環境下的參數
SQL
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
-- 1. 調整平行處理參數（針對 12 vCPU 優化）
EXEC sp_configure 'cost threshold for parallelism', 50;
EXEC sp_configure 'max degree of parallelism', 4; -- 先觀察 4 的成效
-- 2. 鎖定最大記憶體（128GB 扣除 16GB 給 Windows/VMware Tools = 112GB）
EXEC sp_configure 'max server memory (MB)', 112640;
RECONFIGURE;
GO1. MAXDOP (最大平行處理原則)
- 最終建議值：
3或4。 - 原因：因為每個 Soft-NUMA 節點內只有 6 個 vCPU。
- 設為
3（節點內邏輯核心的一半）：這是微軟官方在超執行緒環境下的最標準外掛，能確保平行處理完全不會搶占同核心的 HT 執行緒。 - 設為
4：在實務上，由於大部分布林運算與平行處理以 2 的倍數（2, 4, 8）對齊效率最高，設為4可以微幅榨乾節點資源，且不至於跨越實體 Socket 邊界，也是非常優良的配置。
- 設為
2. Cost Threshold for Parallelism (CTFP)
- 最終建議值：
50。 - 原因：配合 MAXDOP 縮減到 3 ~ 4，調高門檻可以完美過濾掉 90% 的短交易小查詢，將 CPU 資源留給真正需要平行運算的大型報表。
3. TempDB 檔案數量
- 最終建議值：
8個資料檔案。 - 原因：雖然 CPU 降為 12，但微軟針對核心數 8~16 的環境，官方推薦最佳解就是 8 個資料檔案。這能完美兼顧降低 PFS 頁面競爭與維持檔案系統的管理效率（請參考前文 T-SQL 腳本，將初始大小一次給足如 1GB，成長量固定 64MB 即可）。
SQL Server 效能監控工具
工具一: 觀察 CPU 是否有異常「跨 NUMA 節點存取」監控腳本
這個腳本透過讀取 SQL Server 內部的效能計數器（Performance Counters），去計算 遠端節點記憶體存取（Remote Node Page Allocations） 的比例。
- 健康指標：「遠端記憶體存取比例」應低於 1% ~ 5%。如果這個比例很高（例如 20% 以上），代表 CPU 頻繁跨越 Socket 去撈資料，這在 12 vCPU 調整後應該會大幅改善。
SQL
WITH NUMA_Counters AS (
SELECT
counter_name,
-- 先將計數值轉為 FLOAT 避免後續加總相乘時溢位
CAST(cntr_value AS FLOAT) AS cntr_value_float
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Buffer Node%'
AND counter_name IN ('Local node page lookups/sec', 'Remote node page lookups/sec')
),
Aggregated AS (
SELECT
SUM(CASE WHEN counter_name = 'Local node page lookups/sec' THEN cntr_value_float ELSE 0 END) AS LocalLookups,
SUM(CASE WHEN counter_name = 'Remote node page lookups/sec' THEN cntr_value_float ELSE 0 END) AS RemoteLookups
FROM NUMA_Counters
)
SELECT
CAST(LocalLookups AS DECIMAL(20,2)) AS [本地節點記憶體讀取次數],
CAST(RemoteLookups AS DECIMAL(20,2)) AS [遠端節點記憶體讀取次數 (跨NUMA)],
CASE
WHEN (LocalLookups + RemoteLookups) = 0 THEN 0
ELSE CAST((RemoteLookups / (LocalLookups + RemoteLookups)) * 100.0 AS DECIMAL(5,2))
END AS [跨 NUMA 記憶體存取比例 (%)]
FROM Aggregated;
-- 結果
本地節點記憶體讀取次數 遠端節點記憶體讀取次數 (跨NUMA) 跨 NUMA 記憶體存取比例 (%)
56022626731673504.00 0.00 0.00診斷指南：若剛重啟過 SQL Server，此計數器會重新計算。建議在系統運行一段時間、並經歷過離峰與尖峰（跑報表）後執行此查詢。若「跨 NUMA 比例」趨近於 0%，代表 12 vCPU 完美對齊了實體硬體。
數據解讀與現況診斷:
這個 0.00% 的結果，揭露了您這台虛擬機（24 vCPU）現階段效能瓶頸的真正內幕：
- 目前沒有「物理上」的跨節點記憶體延遲
因為 Remote node lookups 是 0，這代表目前為止，SQL Server 需要撈取的資料，百分之百都剛好在它自己所屬的 NUMA 節點主記憶體內。 - 揭發隱形元兇：所有的問題都卡在「虛擬化排程」與「內部執行緒互搶」
既然記憶體存取沒有跨節點（100% Local），但系統之前依然可能遇到效能低下或 CPU 飆高的問題，這就完全證實了我們之前的推論：瓶頸根本不在記憶體傳輸，而是 24 vCPU 太大，導致 VMware ESXi 底層在 16 實體核心的主機上，無法順暢地進行 CPU 時間片調度（也就是嚴重的 CPU Ready Time 競爭）。 - 這更堅定了「降核至 12 vCPU」是絕對正確的決定
目前雖然記憶體完美鎖在本地，但因為虛擬核心給太多（24核），CPU 的運算排程依舊塞車。降到 12 vCPU 後，SQL Server 可以維持完全在本地記憶體運算的高速優勢，同時又徹底釋放了 VMware 的 CPU 排程壓力，效能穩定度會迎來質的飛躍。
工具二: 用來檢查 SQL Server 內部執行緒等待（Wait Stats）的檢視工具
這是資料庫效能調校中最核心的「等待統計分析（Wait Stats）」。這個腳本會排除掉系統日常閒置的無用等待，直接列出目前伺服器排名前 10 大的效能殺手。
請在 SSMS 中執行以下診斷腳本：
SQL
WITH [WaitStats] AS (
SELECT
[wait_type],
[wait_time_ms] / 1000.0 AS [WaitS],
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
[signal_wait_time_ms] / 1000.0 AS [SignalS],
[waiting_tasks_count] AS [WaitCount], -- 已修正欄位名稱
100.0 * [wait_time_ms] / NULLIF(SUM(CAST([wait_time_ms] AS FLOAT)) OVER(), 0) AS [Percentage], -- 改用 FLOAT 避免溢位
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT',
N'CLR_SEMAPHORE', N'CXCONSUMER', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
N'RESOURCE_QUEUE', N'SERVER_DIAGNOSTICS_REGISTRY_INTERVAL', N'SLEEP_BPOOL_FLUSH',
N'SLEEP_DBSTARTUP', N'SLEEP_DBCONTROL_WAKEUP', N'SLEEP_SYSTEMTASK', N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAITENTRIES', N'WAIT_FOR_RESULTS',
N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WHEN_JOB_COMPLETED', N'XE_TIMER_EVENT',
N'XE_DISPATCHER_WAIT', N'XE_LIVE_TARGET_TVF', N'PREEMPTIVE_OS_AUTHENTICATIONOPS',
N'REDO_THREAD_PENDING_WORK', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE'
)
)
SELECT TOP 10
W1.[wait_type] AS [等待類型 (Wait Type)],
CAST(W1.[WaitS] AS DECIMAL(16, 2)) AS [總等待時間 (秒)],
CAST(W1.[ResourceS] AS DECIMAL(16, 2)) AS [資源本身等待時間 (秒)],
CAST(W1.[SignalS] AS DECIMAL(16, 2)) AS [CPU排程等待時間 (秒)],
W1.[WaitCount] AS [觸發次數],
CAST(W1.[Percentage] AS DECIMAL(5, 2)) AS [佔比 (%)],
N'https://www.sqlskills.com/help/waits/' + W1.[wait_type] AS [官方與權威詳解連結]
FROM [WaitStats] AS W1
WHERE W1.[Percentage] > 0.01
ORDER BY W1.[WaitS] DESC;
GO調整前後的重點觀察對象（解讀指引）：
- CXPACKET：
- 意義：平行處理的執行緒等待（其中一個核心跑完了，在等其他偷懶的核心）。
- 調整後變化：當您把 MAXDOP 從 0 降為 4，且 vCPU 降為 12 之後，這個等待的次數與佔比應該要大幅下降。
- SOS_SCHEDULER_YIELD：
- 意義：執行緒因為用完了 CPU 的時間切片（Quantum），被迫釋放 CPU 給別人用，代表 CPU 有密集的運算壓力。
- 調整後變化：如果您在 24 vCPU 時這個值很高，代表遭遇了 VMware 的 CPU Ready Time 排程卡頓；降為 12 vCPU 後此值若變平穩，說明 CPU 調度重回健康軌道。
- PAGELATCH_UP / PAGELATCH_EX：
- 意義：記憶體頁面（最常見於 TempDB 的配置頁）競爭。
- 調整後變化：當您將 TempDB 補足至 8 個檔案、且大小一次給足（如 1GB）後，此等待應該要近乎消失。
建議可以在降核與調整參數前執行一次「工具二」的等待統計，並截圖或記錄下來。調整上線一到兩天後，再次執行相同的查詢對比。
執行「歸零重算」與觀測步驟
步驟一：手動清除舊的等待統計歷史紀錄
SQL
-- 清除開機至今的所有等待統計累積值
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO步驟二：讓系統正常運作（建議觀察 1 小時或經歷尖峰期）
清空後，請讓這台 12 vCPU 的 SQL Server 正常跑一段時間（例如：收集 1 個小時的數據，或者是執行一次平時會卡頓的日常作業/報表），讓它累積純淨的「12 vCPU 新環境數據」。
步驟三：再次執行工具二腳本
經歷一段時間的累積後，再次執行我們之前的 工具二（等待統計修正版）腳本。
Top 10 類型解析
針對這份 12 vCPU 環境下收集 2 小時的 Top 10 等待類型 進行逐一的白話文解析，並提供評估區間與解讀方向。
在 SQL Server 的調校世界中，絕大多數的資源等待都是「愈低愈好」，但有幾種屬於「背景閒置」的等待，它們的值愈高，反而代表伺服器愈輕鬆。以下是詳細拆解：
1. SOS_WORK_DISPATCHER
- 白話解釋：這是 SQL Server 的工作執行緒（Threads）處於無事可做、正在泡咖啡等工作進來的閒置狀態。
- 理想數值區間：在非 24 小時極端高壓的伺服器上，通常佔比會落在 70% ~ 95% 之間。
- 愈高愈好，還是愈低愈好？：愈高愈好（佔比愈高愈好）。這個值非常高（如您的 86.19%），代表您的 12 vCPU 算力非常充沛，大部分時間都好整以暇地在等工作，完全沒有過載。
2. SLEEP_TASK
- 白話解釋：這是一個泛指 SQL Server 內部背景工作（例如系統自我監控、暫存快取清理等）在時間到之前閉眼睡覺的狀態。
- 理想數值區間：日常累積通常佔比在 1% ~ 5% 之間。
- 愈高愈好，還是愈低愈好？：正常現象，不用刻意追求高低。只要它沒有伴隨嚴重的 TempDB 異常（Hash Spill）或緩衝池卡頓，這只是系統日常呼吸的證明。
3. FT_IFTS_SCHEDULER_IDLE_WAIT
- 白話解釋：全文檢索（Full-Text Search）引擎的排程器目前處於閒置、正在等待任務排入隊伍的狀態。
- 理想數值區間：通常佔比在 0% ~ 5% 之間。
- 愈高愈好，還是愈低愈好？：愈高愈好。代表全文檢索目前沒有塞車，背景執行緒都輕鬆地在等事情做。
4. LAZYWRITER_SLEEP
- 白話解釋：Lazy Writer 是 SQL Server 用來清理記憶體、將髒頁（Dirty Pages）寫回硬碟的背景計時工。這個等待代表它每隔 1 秒檢查一次發現記憶體很夠，於是繼續睡覺。
- 理想數值區間：每 1 顆 CPU 核心每秒就會穩定貢獻 1 秒的等待，佔比通常在 1% ~ 5%。
- 愈高愈好，還是愈低愈好？：愈高愈好。這代表您的 128 GB 記憶體非常充足，Lazy Writer 不需要驚醒過來瘋狂清空快取。
5. SOS_SCHEDULER_YIELD
- 白話解釋：執行緒非常順暢地把 4 毫秒的 CPU 時間用完了，自願禮讓出來排到隊伍最後面，等待下一次輪到它運算。
- 理想數值區間：在虛擬化健康環境下，重點看 「平均每次等待時間」應小於 5 ~ 10 毫秒（ms）。您的數值為 1.76 毫秒，屬於神級的健康水準。
- 愈高愈好，還是愈低愈好？：總時間與平均時間都愈低愈好。雖然它是正常運作的產物，但如果它的總時間或平均時間飆高，就代表虛擬主機底層有嚴重的 CPU Ready（搶不到實體 CPU）或者是嚴重的記憶體大範圍掃描。
6. SP_SERVER_DIAGNOSTICS_SLEEP
- 白話解釋：SQL Server 內部的系統健康診斷機制（用來自我偵測有沒有當機、或提供給容錯移轉叢集確認生死），在每輪診斷之間的睡覺等待。
- 理想數值區間：固定每幾秒觸發一次，佔比通常在 0.5% ~ 2%。
- 愈高愈好，還是愈低愈好？：良性背景等待，不用理會。
7. HADR_FILESTREAM_IOMGR_IOCOMPLETION
- 白話解釋：高可用性架構（如 AlwaysOn）的 FILESTREAM 串流管理員在背景睡覺，每 0.5 秒醒來確認一次有沒有檔案要同步。
- 理想數值區間：不論您有沒有開 AlwaysOn 都會固定出現，佔比在 0.5% ~ 2%。
- 愈高愈好，還是愈低愈好？：純背景閒置，完全可以忽略。
8. FT_IFTSHC_MUTEX
- 白話解釋：全文檢索工作執行緒與外部處理程序（fdhost.exe）在進行資料傳輸時的內部互斥鎖、同步等待。
- 理想數值區間：通常佔比小於 2%。
- 愈高愈好，還是愈低愈好？：愈低愈好。數值極低（1.11%）代表您的全文檢索運作非常同步，沒有執行緒卡死互搶的情況。
9. CXPACKET
- 白話解釋：大查詢啟動了「平行處理」，跑得快的 CPU 核心已經做完工作了，正在原地擺工、等待其他跑得慢的核心歸隊的同步等待。
- 理想數值區間：健康調校後的系統應控制在 5% 以下（您的數值只有完美的 0.28%）。
- 愈高愈好，還是愈低愈好？：愈低愈好。這個值過高代表平行處理分工極度不均勻、或者太多微小查詢被設定錯誤的舊參數（如舊的 CTFP=5）硬逼著啟動平行處理，導致 CPU 資源浪費。
10. LATCH_EX
- 白話解釋：執行緒正在等待讀取或修改 SQL Server 記憶體內部的某些非網頁型資料結構（例如：緩衝池控制區、檔案增長控制表等）的排他鎖。
- 理想數值區間：正常高併發系統下，累積佔比應小於 1% ~ 2%。
- 愈高愈好，還是愈低愈好？：愈低愈好。過高（例如超過 10%）通常代表有嚴重的記憶體結構競爭（如物件被極大量併發搶佔）。您的 0.24% 非常安全健康。
試算說明
SOS_SCHEDULER_YIELD 的「總等待時間」與「觸發次數」相除計算出來的平均值。
具體的數學計算公式與步驟如下：
1. 提取表格中的原始數據
- 總等待時間 (秒) =
9,952.14秒 - 觸發次數 =
5,628,391次
2. 計算公式
平均每次等待時間 (毫秒)=總等待時間 (秒)×1,000觸發次數/觸發次數
3. 將數值代入計算
- 先將「秒」換算成「毫秒」（1 秒 = 1,000 毫秒）：9,952.14 秒×1,000=9,952,140 毫秒
- 除以總共觸發的次數，求出每一次發生的平均時間： 9,952,140 毫秒÷5,628,391 次≈1.7682 毫秒
四捨五入後，就得出了 1.76 毫秒（或是精準一點看是 1.77 毫秒）。
移除 TempDB 的資料檔案
要移除 TempDB 的資料檔案（例如 tempdb_mssql_9.ndf 到 tempdb_mssql_12.ndf），SQL Server 要求檔案必須是完全空白（沒有任何配置的頁面）才能進行刪除。
SQL
SELECT name
,physical_name
,size
FROM sys.master_files
WHERE database_id = 2;
--- 回傳值如下時
name physical_name size
tempdev D:\MSSQL2019\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb.mdf 131072
templog D:\MSSQL2019\MSSQL15.MSSQLSERVER\MSSQL\DATA\templog.ldf 1024
temp2 D:\MSSQL2019\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndf 131072
temp3 D:\MSSQL2019\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_3.ndf 131072
temp4 D:\MSSQL2019\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_4.ndf 131072
temp5 D:\MSSQL2019\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_5.ndf 131072
temp6 D:\MSSQL2019\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_6.ndf 131072
temp7 D:\MSSQL2019\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_7.ndf 131072
temp8 D:\MSSQL2019\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_8.ndf 131072
temp9 D:\MSSQL2019\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_9.ndf 131072
temp10 D:\MSSQL2019\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_10.ndf 131072
temp11 D:\MSSQL2019\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_11.ndf 131072
temp12 D:\MSSQL2019\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_12.ndf 131072由於 TempDB 隨時都有系統在背景使用，最乾淨且保證成功的移除方式是「先清空、後移除」。請依照以下步驟在 SSMS 中執行：
步驟一：將檔案內的資料移回其他主要檔案
在刪除檔案前，必須使用 DBCC SHRINKFILE 搭配 EMPTYFILE 參數，強制將這 4 個檔案裡面的所有暫存資料移到前面的 tempdev ~ temp8 檔案中。
請在 SSMS 執行以下腳本：
SQL
USE [tempdb];
GO
-- 1. 將第 9 到 12 個檔案內容清空 (這不會影響正在執行的查詢，資料會自動轉移)
DBCC SHRINKFILE (N'temp9', EMPTYFILE); -- 註：請確認您的邏輯名稱
DBCC SHRINKFILE (N'temp10', EMPTYFILE);
DBCC SHRINKFILE (N'temp11', EMPTYFILE);
DBCC SHRINKFILE (N'temp12', EMPTYFILE);
GO步驟二：正式從資料庫中移除檔案
當步驟一的清空指令成功執行完畢後（視窗下方會顯示執行成功），即可直接執行 ALTER DATABASE REMOVE FILE 將它們徹底移除：
SQL
-- 2. 正式從系統中剔除這 4 個檔案
ALTER DATABASE [tempdb] REMOVE FILE [temp9];
ALTER DATABASE [tempdb] REMOVE FILE [temp10];
ALTER DATABASE [tempdb] REMOVE FILE [temp11];
ALTER DATABASE [tempdb] REMOVE FILE [temp12];
GO常見狀況與疑難排解
Q1：執行 EMPTYFILE 時噴錯，提示「無法清除檔案…因為它包含工作表」怎麼辦？
- 原因：這代表目前有某些使用者的複雜查詢、或者是未結束的交易（Transaction）正在死死鎖定這四個檔案裡的暫存資料，導致檔案無法完全清空。
- 解法：最快且最安全的方法是直接重啟 SQL Server 服務。因為 TempDB 每次重啟都會「完全重建」，重啟後它會是全空的。
- 重啟後的快速移除腳本：
在 SQL Server 服務剛重啟、還沒有大量連線進來時，立即執行步驟二的REMOVE FILE指令，就能 100% 成功移除。
Q2：SQL Server 裡移除了，硬碟裡的 .ndf 實體檔案會消失嗎？
- 當您成功執行
REMOVE FILE後，SQL Server 會自動將磁碟（例如 D 槽）底下的實體檔案刪除。如果不放心的話，可以在執行完後到 D 槽的資料夾下檢查，若檔案還在且資料庫已顯示移除，手動將其刪除即可。
NUMA Node（非統一記憶體存取節點）
NUMA Node（非統一記憶體存取節點） 是多處理器電腦或伺服器中，將一組 CPU 核心與其 專屬本地記憶體（Local Memory） 結合在一起的獨立硬體資源區塊。
NUMA Node 的運作特性
- 本地存取（Local Access）： 當 CPU 核心存取 同一個 NUMA Node 內的記憶體時，路徑最短，速度最快。
- 遠端存取（Remote Access）： 當 CPU 核心需要存取 其他 NUMA Node的記憶體時，必須透過節點間的高速互聯通道（Interconnect），這會產生額外的延遲，速度較慢。
為什麼要有 NUMA Node？
解決效能瓶頸： 在傳統的 UMA（一致性記憶體存取）架構中，所有 CPU 都透過同一條總線搶佔相同的記憶體，當核心數量增加時，訪問衝突也會隨之激增。
提升擴充性： NUMA 將資源分散管理，允許伺服器裝載更多處理器與記憶體，同時維持良好的平行處理效率。
如何管理與查詢？
在 Linux 系統中，作業系統核心會自動針對硬體的 NUMA 配置進行記憶體調配。你也可以透過指令來檢視與管理：
lscpu：查看系統中有多少個 NUMA Node 與 CPU 的對應關係。numactl：查詢系統 NUMA 狀態或設定記憶體存取策略。numastat：檢查記憶體訪問的命中率與遠端跨節點存取的情況。
