人人都是 DBA（III）SQL Server 調度器

標籤：style   blog   http   io   ar   color   os   使用   sp   

在 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_schedulersWHERE scheduler_id < 255GROUP BY is_online    ,[status];

預設的 Affinity Mask 是 0，也就是所有 Scheduler 均為 ONLINE。

SELECT *FROM sys.configurationsWHERE [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.configurationsWHERE [name] LIKE ‘%worker%‘;

預設值為 0，即允許 SQL Server 根據 CPU 和版本情況進行自動設定最大線程數量。

Number of CPUs	32-bit Computor	64-bit Computor
<= 4 processors	256	512
8 processors	288	576
16 processors	352	704
32 processors	480	960
64 processors	736	1472

可以使用 sp_configure 來配置該選項。

EXEC sp_configure ‘show advanced options‘, 1;GORECONFIGURE;GOEXEC sp_configure ‘max worker threads‘, 900;GORECONFIGURE;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_schedulersWHERE scheduler_id < 255    AND is_online = 1GROUP 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_schedulersWHERE 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_idFROM sys.dm_exec_sessions AS sINNER JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_idINNER JOIN sys.dm_os_tasks AS t ON r.task_address = t.task_addressINNER JOIN sys.dm_os_workers AS w ON t.worker_address = w.worker_addressINNER JOIN sys.dm_os_schedulers AS u ON t.scheduler_id = u.scheduler_idWHERE s.is_user_process = 0ORDER 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》由 Dennis Gao 發表自部落格園個人技術部落格，未經作者本人同意禁止任何形式的轉載，任何自動或人為的爬蟲轉載或抄襲行為均為耍流氓。

人人都是 DBA（III）SQL Server 調度器