SQL Server sys.dm_os_waiting_tasks 實戰解析（下）：並行執行內幕與 Task/Worker/Scheduler 模型

本系列第三篇，深入剖析 sys.dm_os_waiting_tasks 在並行查詢中揭示的任務調度內幕。透過雙表鎖定實驗，驗證並行掃描順序；詳細解釋 Scheduler、Worker、Task、Yielding 四大核心概念與關係圖；並提供持續捕捉等待鏈的監控腳本。

實驗：雙表同時鎖定，觀察並行等待分佈

-- Session 1：鎖定兩張表
BEGIN TRAN;
UPDATE t1 SET b = GETDATE();
UPDATE t2 SET b = GETDATE();
-- 不提交，保持鎖定

-- Session 2：並行聯結查詢
SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a OPTION (QUERYTRACEON 8649);

查詢 sys.dm_os_waiting_tasks 發現 21 條等待記錄，其中 4 條為 LCK_M_S (共享鎖等待)，資源描述指向 t2 的 Hobt ID，證實執行計劃先掃描 t2，而非同時掃描兩表。

核心結論：並行是「運算子級」多執行緒，非「表級」同時掃描

• 執行計劃為管線樹，每個物理運算子 (Index Scan、Hash Match、Stream Aggregate 等) 可獨立並行化。
• 並行度 (DOP) 決定每個運算子最多使用多少 Worker 同時執行。
• 上游運算子 (如 Hash Match Build) 需等待下游 (Index Scan) 產出資料，產生 CXPACKET 等待。
• 因此 Union All 等多輸入運算子無法同時啟動各分支，受限於單一執行緒調度邏輯。

SQLOS 任務調度四大核心物件

1. Scheduler (排程器) — 邏輯 CPU 抽象

• 每個邏輯 CPU 對應一個 Scheduler (含超執行緒)。
• 同一時刻僅一個 Worker 持有 Scheduler 執行，其餘排隊於 Runnable Queue。
• 可透過 sp_configure 'affinity mask' 綁定物理 CPU。

2. Worker (工作者) — 執行實體

• 1:1 對應 Windows Thread (或 Fiber)。
• 固定綁定一個 Scheduler，生命週期由 SQLOS 管理 (閒置 15 分鐘回收)。
• 最大 Worker 數 = max worker threads (預設依 CPU 核心數自動配置：4 核 512、8 核 576、16 核 704、32 核 960，64 位元環境)。

3. Task (任務) — 最小工作單元

• 一個 Batch (GO 分隔) = 一個 Task。
• Task 佔用 Worker 直到完成或主動進入等待 (如鎖、I/O、CXPACKET)。
• 等待時 Worker 不釋放，導致執行緒阻塞 (非搶占式調度)。

4. Yielding (主動讓步) — 非搶占式調度核心

Worker 主動歸還 Scheduler 的關鍵時機：

1. 每次讀取資料頁前檢查：已執行 > 4ms → Yield。
2. 每排序 64KB 結果 → Yield。
3. 編譯過程中頻繁 Yield。
4. 用戶端消費結果過慢 → Yield。
5. Batch 內每條陳述式結束 → Yield。

此機制避免單一 Task 長期佔用 CPU，降低 Context Switch 開銷。

關係圖解

• 1 Scheduler ↔ 多 Worker (上限可配置)
• 1 Worker → 1 Thread/Fiber
• 1 Worker 執行 1 Task (當下)
• 並行查詢：1 Query → 多 Task (DOP × 運算子數) → 多 Worker → 多 Scheduler

並行等待狀態機分析 (4 DOP 範例)

• 9 個 Task (去重 Task Address)：4 個掃描 Producer + 4 個消費者 + 1 個協調者。
• 4 個 Scheduler 各跑 2 Worker (一生產一消費) + 協調者輪流。
• CXPACKET 等待 = 消費者等待生產者資料 (Exchange Port)。
• LCK_M_S = 掃描運算子等待鎖釋放。

持續監控腳本：捕捉完整並行等待鏈

-- 建立暫存表
CREATE TABLE #waiting_log (
    grp INT, waiting_task_address VARBINARY(8), session_id SMALLINT,
    exec_context_id INT, wait_duration_ms INT, wait_type NVARCHAR(60),
    resource_address VARBINARY(8), blocking_task_address VARBINARY(8),
    blocking_session_id SMALLINT, blocking_exec_context_id INT
);

DECLARE @grp INT = 0;
WHILE 1=1
BEGIN
    INSERT INTO #waiting_log
    SELECT @grp, * FROM sys.dm_os_waiting_tasks WHERE session_id > 50;
    SET @grp += 1;
    WAITFOR DELAY '00:00:00.100'; -- 100ms 採樣
END

-- 分析 (另開視窗執行並行查詢後)
SELECT
    grp, wait_type, COUNT(*) AS cnt,
    STRING_AGG(CONCAT(session_id,':',exec_context_id), ',') AS participants
FROM #waiting_log
GROUP BY grp, wait_type
ORDER BY grp, cnt DESC;

待解謎題：Union All 分支為何無法真正並行？

推測原因：Exchange 運算子僅支援單一生產者流，Union All 為多輸入單一輸出，需序列化拉取各分支資料。期待大神補充官方文件或原始碼層面的解釋。

本系列三篇總結：sys.dm_os_waiting_tasks 能見度遠超 sys.dm_exec_requests，能精準定位並行查詢中每個 Worker 的真實等待類型與阻塞鏈，是 SQL Server 效能調優的「核磁共振」。