1. 背景

某次正式環境使用者抱怨網頁速度極慢，卡了好幾分鐘，我連上 SSMS 查詢也一樣卡。追查後發現另一個專案的排程在跑，瘋狂吃 CPU 和記憶體，把整台 SQL Server 拖垮。理想解法是切獨立主機，但沒有資源時，Resource Governor 是次佳選項——不需重建環境，直接在現有執行個體限制特定資料庫的耗用量。

版本限制（重要）： Resource Governor 在 SQL Server 2022 及以前僅支援 Enterprise 與 Developer Edition，Standard Edition 不支援。若在 Standard 上執行下列指令會直接報錯，請先用 SELECT SERVERPROPERTY('Edition') 確認版本再繼續。

2. Resource Governor 的核心概念

Resource Governor 管理的單位有三層：

  • **資源池 (Resource Pool)**：定義 CPU 與記憶體的用量上限，可建立多個池子分別對應不同優先級的工作負載。
  • **工作負載群組 (Workload Group)**：關聯到某個資源池，細分同一個池子內的工作分類。
  • **分類函數 (Classifier Function)**：每個連線進來時由 SQL Server 呼叫此函數，回傳應該進入哪個工作負載群組的名稱。

重要：MAX 與 CAP 的差異

資源池有兩種 CPU 上限，語意完全不同，常見混淆：

參數 行為
MAX_CPU_PERCENT 軟性上限：只在多個池子互搶 CPU 時才強制執行。伺服器 CPU 空閒時，該資源池可超過此值，甚至用到 100%。
CAP_CPU_PERCENT 硬性上限：不論伺服器是否空閒，該資源池永遠不能超過此值。

若你的目的是「無論如何不讓這個資料庫超過某個 CPU 比例」，必須用 CAP_CPU_PERCENT。只設 MAX_CPU_PERCENT 在離峰時段根本沒有限制效果。

記憶體的 MAX_MEMORY_PERCENT 同樣是軟性上限，邏輯相同。

3. 設定步驟

以下以限制 AdhocDatabase 這個資料庫為例，執行順序：建立資源池 → 建立工作負載群組 → 建立分類函數 → 啟用。

3.1 建立資源池

1
2
3
4
5
6
7
8
9
10
-- MAX：壓力下最多使用 20% CPU，空閒時可超過
-- CAP：硬性不超過 30% CPU（依實際情況調整）
CREATE RESOURCE POOL PoolAdhocDB
WITH (
    MIN_CPU_PERCENT    = 0,
    MAX_CPU_PERCENT    = 20,
    CAP_CPU_PERCENT    = 30,
    MIN_MEMORY_PERCENT = 0,
    MAX_MEMORY_PERCENT = 25
);

MIN_CPU_PERCENT = 0 表示不保證最低量；若此資料庫有關鍵查詢需要保底資源才需要調高。

3.2 建立工作負載群組

1
2
CREATE WORKLOAD GROUP GroupAdhocDB
USING PoolAdhocDB;

3.3 建立分類函數

分類函數有幾個強制規則：

  • 必須建立在 master 資料庫
  • 必須是純量函數，回傳 sysname
  • 必須加 WITH SCHEMABINDING（Resource Governor 的強制要求）
  • 函數內不能存取使用者資料表，只能用系統函數（否則 RECONFIGURE 時報錯）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
USE master;
GO

CREATE FUNCTION dbo.fn_WorkloadClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    RETURN CASE
               WHEN DB_NAME() = 'AdhocDatabase' THEN 'GroupAdhocDB'
               ELSE 'default'
           END;
END;
GO

若要同時限制多個資料庫，或依登入帳號分流，可在 CASE 裡加條件：

1
2
3
4
5
6
RETURN CASE
           WHEN DB_NAME() = 'AdhocDatabase'   THEN 'GroupAdhocDB'
           WHEN DB_NAME() = 'ReportingDB'     THEN 'GroupReporting'
           WHEN SUSER_SNAME() = 'batch_user'  THEN 'GroupBatch'
           ELSE 'default'
       END;

3.4 啟用設定

1
2
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fn_WorkloadClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;

RECONFIGURE 會立即套用，不需要重啟服務，現有連線不中斷，新進連線才走新的分類邏輯。

4. 驗證設定是否生效

光查系統檢視只能確認設定存在，看不出分類是否正確把連線路由過去。完整驗證流程如下：

步驟一：確認 Resource Governor 已啟用、分類函數已掛上

1
2
3
SELECT is_enabled, classifier_function_id
FROM sys.resource_governor_configuration;
-- is_enabled = 1 且 classifier_function_id 非 0 才算正常

步驟二：確認資源池與工作負載群組的設定值

1
2
3
4
5
6
7
SELECT name, max_cpu_percent, cap_cpu_percent, max_memory_percent
FROM sys.resource_governor_resource_pools
WHERE name = 'PoolAdhocDB';

SELECT name, pool_id
FROM sys.resource_governor_workload_groups
WHERE name = 'GroupAdhocDB';

步驟三：確認現有連線被分到正確的群組（最關鍵）

開一個連到 AdhocDatabase 的 session，然後在另一個視窗執行：

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    DB_NAME(s.database_id) AS database_name,
    wg.name               AS workload_group,
    rp.name               AS resource_pool
FROM sys.dm_exec_sessions s
JOIN sys.dm_resource_governor_workload_groups wg
    ON s.group_id = wg.group_id
JOIN sys.dm_resource_governor_resource_pools rp
    ON wg.pool_id = rp.pool_id
WHERE DB_NAME(s.database_id) = 'AdhocDatabase';

workload_group 欄位應該顯示 GroupAdhocDB，而不是 default。若還是 default，代表分類函數邏輯沒有命中，或函數沒有正確掛上。

5. 監控資源實際耗用

設定完不等於結束，需要持續觀察實際效果。以下 DMV 可以看資源池的累計耗用：

1
2
3
4
5
6
7
8
SELECT
    rp.name,
    rp.total_cpu_usage_ms,
    rp.total_cpu_active_ms,
    rp.out_of_memory_count,
    rp.total_memgrant_count
FROM sys.dm_resource_governor_resource_pools rp
WHERE rp.name = 'PoolAdhocDB';

若 out_of_memory_count 持續累積，代表記憶體上限設太低，該資源池的查詢在搶不到記憶體授權。調整 MAX_MEMORY_PERCENT 再 ALTER RESOURCE GOVERNOR RECONFIGURE 即可，不需重建資源池。

6. 常見問題

Q：RECONFIGURE 回報「找不到分類函數」
確認函數存在於 master 資料庫，且函數名稱含結構描述（dbo.fn_WorkloadClassifier）。

Q：RECONFIGURE 回報「分類函數不允許存取使用者資料表」
函數內部呼叫到了存取使用者資料表的程式碼。分類函數只能用 DB_NAME()、SUSER_SNAME() 等系統函數，無法查自訂資料表。

Q：設了 MAX_CPU_PERCENT = 20，但監控看到 CPU 超過 20%
這是預期行為。MAX_CPU_PERCENT 是軟性上限，伺服器 CPU 空閒時不限制。若需要硬限，改用 CAP_CPU_PERCENT。

Q：Standard Edition 能用嗎？
SQL Server 2022 以前不行，只有 Enterprise 與 Developer Edition 支援。

7. 清除設定

若實驗後要還原，依序執行：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 1. 解除分類函數綁定
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
ALTER RESOURCE GOVERNOR RECONFIGURE;

-- 2. 刪除工作負載群組（要先移出資源池）
DROP WORKLOAD GROUP GroupAdhocDB;
ALTER RESOURCE GOVERNOR RECONFIGURE;

-- 3. 刪除資源池
DROP RESOURCE POOL PoolAdhocDB;
ALTER RESOURCE GOVERNOR RECONFIGURE;

-- 4. 刪除分類函數
USE master;
GO
DROP FUNCTION dbo.fn_WorkloadClassifier;

順序不能反，Resource Governor 不允許刪除仍在使用中的池子或群組。

8. 延伸閱讀