SQL Server 2008 R2 資源管理器配置實戰：限制特定用戶 CPU 使用率

需求背景

在多租戶或混合負載環境中，防止特定登入帳號佔用過多 CPU 資源，影響其他關鍵業務。SQL Server Resource Governor（資源管理器）提供精細的資源配額控制能力。

配置步驟詳解

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

-- 限制 CPU 上限 25%
CREATE RESOURCE POOL pMAX_CPU_PERCENT_25
WITH (MAX_CPU_PERCENT = 25);
GO
CREATE WORKLOAD GROUP gMAX_CPU_PERCENT_25
USING pMAX_CPU_PERCENT_25;
GO

-- 限制 CPU 上限 35%
CREATE RESOURCE POOL pMAX_CPU_PERCENT_35
WITH (MAX_CPU_PERCENT = 35);
GO
CREATE WORKLOAD GROUP gMAX_CPU_PERCENT_35
USING pMAX_CPU_PERCENT_35;
GO

2. 定義分類器函數

根據登入帳號導向對應工作負載群組：

CREATE FUNCTION dbo.rgclassifier_MAX_CPU() RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name AS sysname;
    IF (SUSER_NAME() = 'u01')
        SET @grp_name = 'gMAX_CPU_PERCENT_25';
    ELSE IF (SUSER_NAME() = 'u02')
        SET @grp_name = 'gMAX_CPU_PERCENT_35';
    ELSE
        SET @grp_name = 'default';
    RETURN @grp_name;
END;
GO

3. 啟用資源管理器

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rgclassifier_MAX_CPU);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
ALTER RESOURCE GOVERNOR RESET STATISTICS;
GO

驗證成效查詢

以下查詢可即時監控會話所屬工作負載群組與資源消耗：

SELECT 
    s.session_id AS [Session ID],
    s.login_name AS [Login],
    DB_NAME(p.dbid) AS [Database],
    s.program_name AS [Application],
    s.cpu_time AS [Total CPU (ms)],
    s.host_name AS [Host Name],
    c.client_net_address AS [Net Address],
    g.name AS [Workload Group]
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
LEFT JOIN sys.sysprocesses p ON s.session_id = p.spid
LEFT JOIN sys.dm_resource_governor_workload_groups g ON g.group_id = s.group_id
WHERE s.login_name IN ('u01','u02')
ORDER BY s.session_id;

關鍵觀察指標

• Workload Group：確認會話正確歸類至對應群組
• Total CPU (ms)：觀察 CPU 累計使用量是否受限
• Wait Type / Wait Time：若出現 RESOURCE_GOVERNOR_IDLE 等待，表示已觸發限制

注意事項

• 資源管理器僅限 Enterprise/Developer 版本使用
• 分類器函數必須使用 SCHEMABINDING，且不可參考臨時表
• MAX_CPU_PERCENT 為軟性上限，當系統閒置時仍可突破；若需硬性限制請搭配 CAP_CPU_PERCENT
• 變更配置後務必執行 RECONFIGURE 才會生效