SQL Server 執行緒與纖程架構深度解析：排程器、工作項與效能調校

理解 SQL Server 的執行緒模型，是診斷高併發阻塞、CPU 壓力、排程器壓力的前提。本文深入剖析 UMS（User Mode Scheduler）、Worker Thread、Fiber 模式、執行緒池配置與親和性遮罩，並提供實務調優指引。

核心概念對照

三種運作模式

1. Thread Mode (預設)： 每個連線/任務佔用 1 OS Thread。Thread 池由 max worker threads 控制（預設 0 = 自動，約 512 + 16 * CPU 核心數）。
2. Fiber Mode (輕量級共用)： 每 CPU 核心 1 Thread，其下掛載多個 Fiber。啟用：sp_configure 'lightweight pooling', 1。限制： 不支援 CLR、某些擴展預存程序、SQL Server Express/Standard 版本；Windows 9x 不支援。
3. SOS Scheduler (SQL OS 2005+)： 現代架構中，Scheduler 綁定邏輯 CPU，管理 Worker、Runnable Queue、Waiter List，實作協作式排程（非搶佔），大幅減少上下文切換。

Thread/Fiber 池運作機制

-- 查看目前 Worker 狀態
SELECT 
    scheduler_id,
    current_tasks_count,
    runnable_tasks_count,
    work_queue_count,
    load_factor
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE';

任務送入時：

1. 有閒置 Worker → 直接指派
2. 無閒置且未達上限 → 新建 Worker
3. 已達上限 → 任務進入 Runnable Queue 等待，產生 THREADPOOL 等待類型

關鍵配置選項

實務診斷查詢

-- 1. 偵測 THREADPOOL 等待（Worker 耗盡）
SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type = 'THREADPOOL';

-- 2. 排程器負載不均（NUMA 節點傾斜）
SELECT parent_node_id AS NUMA_Node, scheduler_id, current_tasks_count, runnable_tasks_count, load_factor
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE'
ORDER BY parent_node_id, load_factor DESC;

-- 3. 查詢並行度實際使用
SELECT 
    qs.max_dop, 
    qs.execution_count,
    qs.total_worker_time / qs.execution_count AS avg_cpu_ms,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_ms
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.max_dop > 1
ORDER BY avg_cpu_ms DESC;

調優決策樹

1. 出現 THREADPOOL 等待： 檢查長時間運行/阻塞查詢 → 優化索引/查詢 → 考慮調高 max worker threads（謹慎，會增加記憶體壓力）。
2. CPU 高但 runnable_tasks_count 低： 可能為鎖競爭、Latch、I/O 等待，非排程器問題。
3. NUMA 節點負載嚴重不均： 設定 affinity mask 將實例綁定特定 NUMA 節點，或啟用 trace flag 8048（SQL 2014+）將分區鎖提升至 NUMA 節點層級。
4. 平行查詢效能不升反降： 降低 MAXDOP、使用 OPTION (MAXDOP 1) 強制序列執行、優化統計資訊避免錯誤平行計畫。

版本與版本限制提醒

• SQL Server Express / Standard / Web / Developer 版不支援 Fiber 模式。
• 啟用 CLR 整合、In-Memory OLTP、Columnstore 索引、PolyBase 等特性時，不相容 Fiber 模式。
• 現代硬體（多核心、NUMA）下，Thread Mode + SOS Scheduler 已極度優化，Fiber 模式邊際效益極低，一般不建議啟用。

掌握執行緒架構，才能從根源解決「CPU 明明不高卻卡頓」、「連線數不多卻報 THREADPOOL」等反直覺現象。