用 Resource Governor 限制报表负载（资源池、工作负载组、分类器）

命令

USE master;
-- 1) Pool and workload group:
CREATE RESOURCE POOL [pool_report]
WITH (MIN_CPU_PERCENT = 0, MAX_CPU_PERCENT = 30, CAP_CPU_PERCENT = 40,
      MIN_MEMORY_PERCENT = 0, MAX_MEMORY_PERCENT = 25);
CREATE WORKLOAD GROUP [wg_report]
WITH (IMPORTANCE = LOW, REQUEST_MAX_MEMORY_GRANT_PERCENT = 15,
      MAX_DOP = 2, REQUEST_MAX_CPU_TIME_SEC = 600)
USING [pool_report];
GO
-- 2) Classifier function (in master, SCHEMABINDING required):
CREATE FUNCTION dbo.fn_rg_classifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp sysname = N'default';
    IF SUSER_SNAME() = N'rapor_user' OR APP_NAME() LIKE N'Power BI%'
        SET @grp = N'wg_report';
    RETURN @grp;
END;
GO
-- 3) Enable:
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fn_rg_classifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;

-- 4) Which session landed in which group:
SELECT s.session_id, s.login_name, s.program_name, wg.name AS workload_group, rp.name AS pool
FROM   sys.dm_exec_sessions s
  JOIN sys.dm_resource_governor_workload_groups wg ON wg.group_id = s.group_id
  JOIN sys.dm_resource_governor_resource_pools rp  ON rp.pool_id = wg.pool_id
WHERE  s.is_user_process = 1;

-- Rollback:
-- ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL); ALTER RESOURCE GOVERNOR DISABLE;

作用

在连接时将来自特定登录名或应用的会话路由到单独的工作负载组；通过限制该组的 CPU、内存授予、DOP 和每请求 CPU 时间来保护 OLTP 负载。

适用场景

报表/BI 查询挤占 OLTP、ETL 与业务时间负载重叠，或多租户服务器需要资源共享时。

注意

分类器函数在每个新连接上运行；函数缓慢或出错会拖慢或中断每次登录（函数中不要有查询或表访问）。若出现异常，通过 DAC 连接并设置 CLASSIFIER_FUNCTION = NULL。分类只在连接时进行；现有会话不受影响。CAP_CPU_PERCENT 是硬上限，即使 CPU 空闲也会生效。MAX_CPU_PERCENT 仅在 CPU 争用时生效。Resource Governor 是 Enterprise/Developer 功能。