在 SQL Server 中，當數據庫啟動后，SQL Server 會為每個物理 CPU（包括 Physical CPU 和 Hyperthreaded）創建一個對應的任務調度器（Scheduler），Scheduler 可以看作為邏輯 CPU（Logical CPU）。
根據 Affinity Mask 選項的配置，Scheduler 的狀態被設置為 ONLINE 或 OFFLINE。使用下面的 SQL 來查詢當前環境中 Scheduler 的狀態。
SELECT is_online ,[status] ,COUNT(*) AS [count] FROM sys.dm_os_schedulers WHERE scheduler_id < 255 GROUP BY is_online ,[status];
默認的 Affinity Mask 是 0，也就是所有 Scheduler 均為 ONLINE。
SELECT * FROM sys.configurations WHERE [name] LIKE '%affinity%';
例如，如果把 Affinity Mask 設置為 3，即 00000011，則意味著只有 0 和 1 號 CPU 可以使用。
假設有 64 個 CPU，則常用的 Affinity Mask 值有：
- 255 -> 0xFF
- 65280 -> 0xFF00
- 16711680 -> 0xFF0000
- 4278190080 -> 0xFF000000
- 4294967040 -> 0xFFFFFF00
- -256 -> 0xFFFFFF00
下面的 VM 的配置為 4 * 8 = 32 Logical CPUs 情況。
設置 NumaNode0 上的 8 個 CPU 用于 I/O，其他 3 個節點用于 Processor。
Scheduler 負責根據需求創建和銷毀 Worker，一個 Worker 即可是一個 Thread 也可以是一個 Fiber，可以通過 Max Worker Threads 和 Use Windows Fibers 配置項來進行設置。
Max Worker Threads 選項負責限制線程池（Threading Pool）中線程的最大數量。
SELECT * FROM sys.configurations WHERE [name] LIKE '%worker%';
默認值為 0，即允許 SQL Server 根據 CPU 和版本情況進行自動配置最大線程數量。
可以使用 sp_configure 來配置該選項。
EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'max worker threads', 900; GO RECONFIGURE; GO
使用如下 SQL 查詢來查看當前數據庫環境中的 Max Workers Count 和 Current Workers Count。
SELECT max_workers_count FROM sys.dm_os_sys_info; SELECT SUM(current_workers_count) AS current_workers_count FROM sys.dm_os_schedulers;
Worker 直接使用 Scheduler，每個 Worker 只會關聯到 1 個 Scheduler，Worker 不能從一個 Scheduler 轉移到另一個 Scheduler 上。
Worker 處理的工作單元可以是一個 Request，也可以是一個 Task。比如批處理 Request 可能被分解成多個 Task。當 Scheduler 接收到新的 Request 或 Task 請求時，如果當前沒有空閑 Worker（Idle Worker），則根據配置開始創建新的 Worker，而 Request 或 Task 將被綁定到該 Worker 上。
SELECT is_idle ,COUNT(*) AS [count] FROM sys.dm_os_schedulers WHERE scheduler_id < 255 AND is_online = 1 GROUP BY is_idle;
sys.dm_os_schedulers 中的 scheduler_id < 255 則為常規查詢，如果 scheduler_id >= 255 則為系統內調度。
如果 Worker 已經空閑了至少 15 分鐘以上，或者 SQL Server 檢測到有內存壓力時，空閑的 Worker 可能被銷毀。
- 在 32 位機上，1 個 Worker 至少占用 0.5M 的內存。
- 在 64 位機上，1 個 Worker 至少占用 2M 的內存。
所以，銷毀空閑的 Worker 以釋放內存可以立即改善系統對內存的迫切需求。
SQL Server 設計了非常高效的 Worker Pool，所以即使有大量的并發在訪問數據庫，可能 Worker Pool 的大小仍遠小于配置的 Max Worker Threads 的值。但盡管如此，如果 Worker 中處理的 Task 發生了鎖定或者等待 IO 完成等阻塞操作，Worker 即會被阻塞，Worker 不會其他任何請求直到阻塞條件解除。
SELECT AVG(current_workers_count) AS [avg_current_workers_count] ,AVG(active_workers_count) AS [avg_active_workers_count] ,MAX(current_workers_count) AS [max_current_workers_count] ,MAX(active_workers_count) AS [max_active_workers_count] ,SUM(current_workers_count) AS [total_current_workers_count] ,SUM(active_workers_count) AS [total_active_workers_count] ,SUM(pending_disk_io_count) AS [total_pending_disk_io_count] FROM sys.dm_os_schedulers WHERE scheduler_id < 255 AND is_online = 1;
SQL Server 中的 Session 實際上只描述了建立連接后的通道，通過該通道可以發送 Request，通道也可以保持空閑。所以 Session 不會與特定的 Scheduler 進行綁定。
SELECT s.session_id ,r.command ,r.[status] ,r.wait_type ,r.scheduler_id ,w.is_preemptive ,t.task_state ,u.cpu_id FROM sys.dm_exec_sessions AS s INNER JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id INNER JOIN sys.dm_os_tasks AS t ON r.task_address = t.task_address INNER JOIN sys.dm_os_workers AS w ON t.worker_address = w.worker_address INNER JOIN sys.dm_os_schedulers AS u ON t.scheduler_id = u.scheduler_id WHERE s.is_user_process = 0 ORDER BY r.scheduler_id;
當 Session 建立后，會將當前負載最低的 Scheduler 分配給該 Session。然后，當 Session 中有新的 Request 抵達時，SQL Server 會將最近處理過該 SPID 中 Request 的 Scheduler 作為推薦的調度器（Preferred Scheduler）優先調度。盡管如此，當 Session 中抵達的 Request 開始排隊時，SQL Server 會計算每個 Scheduler 的 Load Factor，尋找負載最低的 Scheduler 來處理任務。
《人人都是 DBA》系列文章索引：
本系列文章《人人都是 DBA》由 Dennis Gao 發表自博客園個人技術博客，未經作者本人同意禁止任何形式的轉載，任何自動或人為的爬蟲轉載或抄襲行為均為耍流氓。
文章列表
留言列表
