SQL Server 2014 資源調控器 IOPS 限流實戰：隔離維護作業，保障生產效能

SQL Server 2014 資源調控器重大增強：支援依 **IOPS** 限制查詢資源消耗。本文實戰示範如何建立專屬 DBA 資源池、分類函數、動態調整 MAX_IOPS_PER_VOLUME，將索引重建、DBCC CHECKDB 等維護作業的 I/O 壓力壓制在可接受閾值，避免衝擊 7×24 生產負載。

背景痛點

資源調控器自 2008 版僅限制 CPU 與查詢記憶體。維護作業（索引重建、統計更新、一致性檢查）常產生爆發式 IOPS，擠佔儲存頻寬，導致生產查詢延遲飆升。

實作步驟完整腳本

1. 建立 DBA 專用資源池與工作負載群組

-- 資源池：初始設高上限建立基準線
CREATE RESOURCE POOL DbaPool WITH (MAX_IOPS_PER_VOLUME = 100000);
GO
CREATE WORKLOAD GROUP DbaGroup USING DbaPool;
GO

2. 定義分類函數：依登入帳號導流

CREATE FUNCTION dbo.MyClassifierFunction() RETURNS SYSNAME WITH SCHEMABINDING AS
BEGIN
    DECLARE @GroupName SYSNAME;
    IF SUSER_NAME() = 'DbaUser' SET @GroupName = 'DbaGroup';
    ELSE SET @GroupName = 'default';
    RETURN @GroupName;
END;
GO

3. 註冊並重新配置資源調控器

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.MyClassifierFunction);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

4. 驗證 Session 歸屬

SELECT session_id, login_name, group_id FROM sys.dm_exec_sessions WHERE login_name = 'DbaUser';

5. 建立測試索引並執行重建（無限流基準）

CREATE NONCLUSTERED INDEX idx_DateKey ON FactOnlineSales(DateKey);
GO
ALTER INDEX idx_DateKey ON FactOnlineSales REBUILD;
GO

效能監視器計數器 SQL Server:Resource Pool Stats:Disk Write IO/Sec 顯示 ~100 IOPS。

6. 動態限流至 50 IOPS

ALTER RESOURCE POOL DbaPool WITH (MAX_IOPS_PER_VOLUME = 50);
GO
-- 再次執行 REBUILD，觀察 IOPS 被壓制在 ~50
-- 計數器 Disk Write IO Throttled/sec 顯示被限流次數

等待類型變化分析

限流前後的等待統計對比：

• 無限流：PAGEIOLATCH_EX、SOS_SCHEDULER_YIELD 正常。
• 限流後：PAGEIOLATCH_EX 大幅增加、SOS_SCHEDULER_YIELD 明顯上升，查詢在 RUNNABLE 隊列等待 CPU 處理被節流的 I/O 完成。

**關鍵洞察**：資源調控器本身**不引入新等待類型**，而是透過延長現有 PAGEIOLATCH_* 等待時間間接實現限流。

監控與運維建議

• 持續監控 sys.dm_resource_governor_resource_pools、sys.dm_exec_requests 觀察 io_throttling 狀態。
• 搭配 Disk Write IO Throttled/sec 計數器設定告警。
• 維護視窗內可暫時提高上限；平峰期嚴格限流。
• 注意：MIN_IOPS_PER_VOLUME 可保障關鍵負載最低 I/O 權益。

總結

SQL Server 2014 資源調控器的 IOPS 限流功能，為多租戶、混合負載環境提供了精細的儲存資源治理能力。善用分類函數與動態調整，即可在不停機、不修改應用程式的前提下，優雅地平衡維護作業與生產查詢的 I/O 爭用。