简介SQL Server 2008 R2引入的资源控制器为多数据库环境下CPU与内存争抢问题提供了全新解法。这份文档系统讲解了资源控制器的工作机制包括资源池与工作负载组的定义、默认资源池和系统资源池的区别以及如何设置CPU和内存的最小/最大百分比来保证关键业务的资源可用性。内容还对比了SQL Server 2005独立实例分配方式的缺陷并通过资源池和负载组的配置思路说明如何将不同数据库请求分类到对应资源池以便为查询、写入等操作分别设定资源上限与下限避免单库耗尽服务器资源。文档从问题背景切入逐步阐述配置方法适合数据库管理员、运维工程师以及需要深度调优服务器的解决方案供应商阅读。资源包为1个docx文档约84KB结构完整。已有1350人学习下载是处理多实例资源竞争、提升硬件利用率的实用参考。1. 资源控制器SQL Server 2008 R2 把 CPU 和内存分配从割地变成动态调度如果一台 SQL Server 上只跑一个数据库资源争抢基本不用操心可一旦多个库共用一台服务器CPU 和内存怎么分就成了日常扯皮。SQL Server 2005 时代的主流做法是一库一实例 处理器亲和性资源一经分配就像割地A 库闲到发慌也借不出 CPU 给满载的 B 库。SQL Server 2008 R2 引入的资源控制器Resource Governor把方案往前推了一大步CPU 和内存按资源池配置最小值和最大值池间资源可以按负载动态流动。这篇笔记适合正在做多实例合并、或者被多数据库互抢资源折腾过的 DBA 和运维先把原理和参数语义讲透再给一套能直接抄的配置脚本和踩坑记录。2. 资源池与工作负载组读懂最小值和最大值的真实语义2.1 从一库一实例到资源池CPU 亲和性方案的边界在哪SQL Server 2005 时代多数据库竞争资源时公认的做法是每个数据库一个独立实例再用 sp_configure 的 affinity mask处理器亲和性把不同的 CPU 核分给不同实例。这套方案能跑但有两个硬伤第一资源分配是静态的实例 A 分到 4 个核后即便完全空闲实例 B 忙到排队也不能借用第二内存池同样被物理切分无法按业务峰谷做动态调整。对于典型的解决方案供应商场景比如一台物理机上同时托管开发库、测试库和几个客户的共享库这种割地式分配会让硬件利用率很难看。服务器虚拟化是另一个尝试方向。每台虚拟机只托管一个 SQL Server 实例靠虚拟化平台做资源隔离。问题在于虚拟化层自身的 CPU 和内存开销这些开销本来可以直接给 SQL Server 用另外如果虚拟化软件只支持静态资源分配按需扩缩容的粒度根本到不了数据库层数据库的临时峰值依旧没法被同一台宿主机上的其他空闲资源吸收。SQL Server 2008 R2 的资源控制器选择在数据库引擎内部解决这件事。它引入资源池Resource Pool抽象层每个池配置 CPU 和内存的 MIN / MAX 百分比池下面挂工作负载组Workload Group请求按分类函数Classifier Function落到不同组。这样资源分配就从按实例切变成了按请求特性切查询函数多分 CPU、写操作少分这类细粒度控制在 2008 R2 里才成为可能。从 CPU 架构的角度看2008 R2 的调度器和 2005 没有本质区别多核下的线程调度逻辑一脉相承资源控制器真正改变的是资源的归属权——从实例独占变成池间共享。这点想清楚很多参数误设都能提前避开。2.2 默认的两个资源池internal 与 default 分别管什么装好 SQL Server 2008 R2 后资源控制器默认就有两个池internal系统资源池和 default默认资源池。internal 池留给 SQL Server 自身的系统任务比如死锁检测、统计信息更新、检查点这类后台线程default 池承接没有被分类函数显式指定的所有用户请求。两个池各自带一个同名的工作负载组新连接没被分类时全部走 default。注意internal 池的最小值和最大值不允许修改也不要把任何用户工作负载组塞进 internal 池否则系统任务可能拿不到足够的调度时间。这个默认结构的关键点在于default 池的最小 CPU 和内存默认是 0最大值是 100。也就是说如果不去动它全部用户负载可以消耗到整机资源上限。一旦新建了池并给它设了 MIN 大于 0这部分资源就变成预留给该池的default 池实际可用的最大值会相应收缩。理解这一点后面排查为什么设了池之后默认库变慢了才有方向。对比维度SQL Server 2005 独立实例 亲和性SQL Server 2008 R2 资源控制器资源隔离粒度实例级粗池 / 工作负载组级细空闲资源借用不支持割地后闲置支持未设 MIN 的资源池间可流动配置方式sp_configure 掩码重启生效T-SQL RECONFIGURE动态生效额外开销一库一实例内存和 CPU 被实例进程重复占用只在引擎内部做调度开销极小这张表是给决策用的如果现有架构里实例数已经很多合并实例加资源池通常比继续堆实例更划算如果只是临时解决一两个库的争抢不改架构直接用亲和性反而更省事。说到底资源控制器的收益来自动态代价是配置复杂度静态场景里它没有优势。2.3 最小值与最大值的真实语义MIN 是底线MAX 不是硬墙资源池的 MIN_MEMORY_PERCENT 和 MIN_CPU_PERCENT 表示该池至少能拿到的份额。设计上所有池的 MIN 总和不能超过 100并且系统会天然保留一部分给 internal 池。这里容易踩的第一个认知误区是设了 MAX 就万事大吉。MAX_CPU_PERCENT 表示池最多可用的 CPU 百分比但 SQL Server 2008 R2 的资源控制器文档里写得很明确池有可能出现短暂的 CPU 100% 高峰这是正常行为并不违反 max 设置。原因是调度器在负载波动时允许池间短时间借用资源避免请求无谓等待。同理MAX_MEMORY_PERCENT 限制的是缓冲池可分配给该池的最大页面数但池内大查询触发的内存授予Memory Grant可能让池实际占用短暂越过这个百分比。这不是配置失效而是资源控制器的调度粒度大于瞬时波动。把两个参数放在一起看MIN 是给关键业务的底线保障MAX 是限制长期占用的上限两者都不是物理闸门。配置时正确的思路是用 MIN 保底线用 MAX 限长尾而不是用 MAX 去模拟一堵墙。CPU 的 MIN 和内存的 MIN 语义也有细微差别——CPU 的最小值按时间片保证秒级窗口内可能达不到内存的最小值是物理预留从配置生效那一刻就要腾出这么多页面。资源控制器的思路说穿了就是数据库层的动态内存分配CPU 时间片和缓冲池页面都不再被某个实例锁定池间可以按负载实时流动。这是 2008 R2 相比 2005 亲和性方案最本质的进步——资源是共享的、可借调的而不是割完就定死的。2.4 工作负载组按请求特性再切一刀资源池管的是预算工作负载组管的是池内请求的调度细节。每个池默认有一个同名工作组你也可以在同一个池里创建多个组。工作负载组的核心参数有三个IMPORTANCELOW / MEDIUM / HIGH决定组内请求在 CPU 调度上的优先级REQUEST_MAX_MEMORY_GRANT_PERCENT 限制单个请求能拿到的内存授予上限REQUEST_CPU_TIME_SEC 之类则是超时控制。一个典型划分是把 OLTP 查询放进 IMPORTANCEHIGH 的组把报表查询放进 LOW 的组这样报表跑起来不会把 OLTP 的 CPU 抢光。资源控制器按组而不是按会话去累计资源用量所以同一个登录名下不同性质的连接只要分类函数写得够细就能分别落到不同组。比如 ERP 账号白天的短事务走 HIGH 组同一个账号凌晨的批处理走 LOW 组互不干扰。下面一章直接给生产可用的脚本。3. 落地配置从创建资源池到绑定登录名的完整脚本3.1 配置前先确认版本、场景和权限动手之前先用三关确认环境适不适合启用资源控制器。第一关是版本。资源控制器从 SQL Server 2008 企业版开始提供2008 R2 的 Standard 版不带这个功能。执行SELECT SERVERPROPERTY(Edition)先看一眼如果是 Standard后面全部白搭只能回到亲和性方案。第二关是场景。如果一台服务器上只跑一两个库且负载平稳资源控制器带来的收益有限配置成本反而高。真正适用的场景是多租户、多库混部、业务间有明确优先级差比如同一台机器上既有对外交易库又有内部报表库。判断标准很简单高峰时有没有库在抢资源里吃亏如果有资源池才有存在价值。第三关是权限。配置资源池和分类函数需要 ALTER RESOURCE GOVERNOR 权限属于 CONTROL SERVER 级别。上线前先在测试环境跑通整套脚本不要直接在生产的 SSMS 里拍脑袋改配置。三关都过了我推荐直接用脚本配置而不是全靠 SSMS 界面。SSMS 里的资源调控器节点能看图但 CREATE / ALTER 语句的版本管理和回滚能力比鼠标操作强得多生产环境变更需要留痕脚本是唯一的选择。3.2 创建资源池和工作负载组T-SQL 脚本下面这段脚本创建两个池一个给 ERP 业务一个给报表业务并分别挂上对应的工作负载组。-- 1. 创建 ERP 资源池CPU 最小 30%最大 60%内存最小 25%最大 50% CREATE RESOURCE POOL [Pool_ERP] WITH ( MIN_CPU_PERCENT 30, MAX_CPU_PERCENT 60, MIN_MEMORY_PERCENT 25, MAX_MEMORY_PERCENT 50 ); GO -- 2. 创建报表资源池CPU 最小 10%最大 40%内存最小 10%最大 30% CREATE RESOURCE POOL [Pool_Report] WITH ( MIN_CPU_PERCENT 10, MAX_CPU_PERCENT 40, MIN_MEMORY_PERCENT 10, MAX_MEMORY_PERCENT 30 ); GO -- 3. 在 ERP 池下创建工作负载组IMPORTANCE 设为 HIGH CREATE WORKLOAD GROUP [Group_ERP_OLTP] WITH ( IMPORTANCE HIGH, REQUEST_MAX_MEMORY_GRANT_PERCENT 20 ) USING [Pool_ERP]; GO -- 4. 在报表池下创建工作负载组IMPORTANCE 设为 LOW CREATE WORKLOAD GROUP [Group_Report_Query] WITH ( IMPORTANCE LOW, REQUEST_MAX_MEMORY_GRANT_PERCENT 30 ) USING [Pool_Report]; GO逻辑说明CREATE RESOURCE POOL 定义池层面的 CPU / 内存预算。MIN_CPU_PERCENT30 表示调度器保证 ERP 池至少获得 30% 的 CPU 时间MAX_CPU_PERCENT60 限制它的长期占用上限。MIN_MEMORY_PERCENT25 意味着缓冲池要物理预留 25% 的内存给 ERP 池这个预留是配置生效时就要兑现的。参数说明IMPORTANCE 只在多个池同时抢占资源时起作用HIGH 比 MEDIUM 优先获得 CPU 时间片。REQUEST_MAX_MEMORY_GRANT_PERCENT 是相对池内存的百分比上限ERP 池如果有超大查询且经常内存授予不足这个值要放宽到 40 左右但也要防止单个查询把池内内存吃穿。两个池的 MIN 总和是 40留了充足余量给 default 池这样的比例在生产环境里比较稳妥。3.3 分类函数把每个连接送进对应的工作负载组池和组建好之后还差最后一环决定一个连接走哪个组。这个决策函数叫分类函数Classifier Function连接建立时执行返回工作负载组名称。下面是一个按登录名分类的示例。-- 1. 创建分类函数必须使用 WITH SCHEMABINDING CREATE FUNCTION dbo.RG_Classifier() RETURNS SYSNAME WITH SCHEMABINDING AS BEGIN DECLARE GroupName SYSNAME; -- ERP 应用账号走 ERP 池 IF SUSER_SNAME() IN (erp_app_login, sa) SET GroupName NGroup_ERP_OLTP; -- 报表账号走报表池 ELSE IF SUSER_SNAME() report_login SET GroupName NGroup_Report_Query; -- 其他连接全部落到默认池防止兜底失败 ELSE SET GroupName Ndefault; RETURN GroupName; END; GO逻辑说明SUSER_SNAME() 返回当前会话的登录名分类函数在每次新连接建立时执行一次返回值必须是已存在的工作负载组名。WITH SCHEMABINDING 是强制要求这样函数引用的对象结构不会被随意改动。注意函数里不要做重活它跑在连接建立的路径上如果里面有复杂查询每次新建连接都会被拖慢影响面是所有应用。参数说明返回 default 是兜底逻辑强烈建议保留防止漏网之鱼被分到不存在的组导致连接被拒绝。分类函数的分支不要超过一屏复杂规则建议存到配置表里 JOIN这样业务加账号不用改函数改表就能生效维护成本低很多。然后注册分类函数并让整套配置生效-- 注册分类函数 ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION dbo.RG_Classifier); GO -- 应用所有待生效的配置 ALTER RESOURCE GOVERNOR RECONFIGURE; GO逻辑说明ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION ...) 把函数挂到资源控制器上RECONFIGURE 让池、组、分类函数整套配置生效。这两步的顺序有讲究新增池和组之后先 RECONFIGURE 应用一次再挂分类函数否则新连接执行分类函数时池配置还没就绪容易在连接阶段报错。这里有个特别容易翻车的点分类函数一旦挂载它引用的对象就被 SCHEMABINDING 锁住想改函数必须先摘分类函数。正确顺序是先 ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION NULL)再 ALTER FUNCTION最后重新挂载这个在第四章详细说。3.4 用系统视图确认配置实际生效配置完别急着收工先查两张视图-- 查看资源池的运行时配置 SELECT pool_id, name, min_cpu_percent, max_cpu_percent, min_memory_percent, max_memory_percent, consumed_cpu_percent, target_memory_kb FROM sys.dm_resource_governor_resource_pools; -- 查看工作负载组的历史请求统计 SELECT group_id, name, importance, total_request_count, total_cpu_usage_ms FROM sys.dm_resource_governor_workload_groups;逻辑说明sys.dm_resource_governor_resource_pools 返回的是运行时配置和实际消耗值。consumed_cpu_percent 表示该池当前实际消耗的 CPU 百分比target_memory_kb 是内存目标如果它长期低于 min_memory_percent 对应的值说明池配置没生效或者物理内存不足。工作负载组视图里 total_request_count 和 total_cpu_usage_ms 用来确认连接确实被分到了预期组如果某个组请求数一直为 0说明分类函数没有命中它。验证的关键不是看配置值而是看真实流量有没有按预期走。最简单的方法用 erp_app_login 登录执行一条耗 CPU 的查询同时查 sys.dm_exec_requests 的 group_id再用 report_login 做同样操作比对两个 group_id 是否分别落在对应的池和工作负载组上。都对了资源池才算真正跑起来。4. 避坑与排查资源控制器最容易翻车的五个现场资源控制器的配置语法并不复杂真正折磨人的是运行时的各种看起来对但实际没生效。下面五条踩坑记录都来自真实环境每一条都能在测试环境里复现不值得再亲历一遍。4.1 现象一RECONFIGURE 返回成功但配置一会儿就回滚现象执行 ALTER RESOURCE POOL 改大某个池的 MIN_CPU_PERCENT再 ALTER RESOURCE GOVERNOR RECONFIGURE语句都返回成功。过几分钟再查 sys.dm_resource_governor_resource_pools值还是旧的。原因资源控制器的配置变更是异步应用的。RECONFIGURE 把请求放进配置队列如果池里有长事务或者上一次 reconfiguration 还没跑完新的变更会被排队甚至被覆盖看起来就像回滚了。解决先查 sys.dm_resource_governor_configuration 的 reconfiguration_failure 字段有报错就清掉再执行一次 RECONFIGURE 强制应用。排查问题时还可以看 reconfiguration_count 是否在增长如果一直不涨说明配置请求根本没进入队列。最稳妥的办法是选在维护窗口做变更改完直接重启 SQL Server 服务让配置从元数据表干净加载。4.2 现象二改分类函数后所有新连接失败现象ALTER FUNCTION 改成新逻辑重新挂载后 SSMS 里新建查询就报错提示无法加载 classifier 或返回的组不存在严重时连 sa 都被挡在外面。原因两个常见诱因。一是函数返回了不存在的组名比如把 Group_ERP_OLTP 多敲了一个空格二是函数挂了 SCHEMABINDING但修改时引用的对象结构变了导致绑定失败。分类函数出错时SQL Server 会拒绝新连接建立老连接不受影响所以从外面看就是突然连不上数据库。解决分类函数挂载状态下不能直接改先执行 ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION NULL) 摘掉函数再 ALTER FUNCTION重新挂载前查一把 sys.resource_governor_workload_groups确认函数返回的每个组名都在列表里。万一已经被锁死连不上用 DAC 连接管理员专用连接进去执行上面的恢复步骤。我生产上吃过一次亏以后凡是改分类函数一律先在测试实例上跑一遍完整流程再上生产。4.3 现象三MIN 值设满默认池里的库被饿到超时现象两个业务池各设了 40% 的 MIN_CPU_PERCENT加上系统内部保留default 池实际能用的 CPU 只剩个位数。跑在 default 池里的库高峰时大量超时应用端响应时间飙到几十秒。原因所有池的 MIN 总和虽然没超过 100%但没把调度器的系统保留和内存预留算进去。MIN 总和越接近 100%default 池越没有腾挪空间任何未分类的连接都必须在极其有限的资源里挤。解决把两个池的 MIN 各降到 30%留出至少 30% 给 default 池。同时把核心 OLTP 库的登录名显式分类进带 MIN 的池而不是让它留在 default 池里听天由命。经验值是多个业务池的 MIN 总和控制在 70% 以内剩 30 交给默认池和系统任务这样即使分类函数漏了连接default 池也不会成为压垮骆驼的稻草。4.4 现象四MAX_MEMORY_PERCENT 被当成硬上限实际占用却超了现象报表池设了 MAX_MEMORY_PERCENT30报表跑到一半查 DMV发现实际内存占用到了 45%第一反应是配置失效。原因MAX_MEMORY_PERCENT 限制的是缓冲池buffer pool里可分配给该池的页面数但单个查询的内存授予query memory grant和排序、哈希等工作区内存不受池级上限的硬约束。也就是说池级 max 管的是常驻内存管不住瞬时授予内存。解决要控制单查询内存改工作负载组的 REQUEST_MAX_MEMORY_GRANT_PERCENT要控制并发大查询挤爆内存配合查询超时和资源信号量Resource Semaphore一起调。理解这个边界以后把 MAX_MEMORY_PERCENT 看成长期目标值而不是物理闸门才能设计出合理的容量规划否则很容易在深夜报表跑批时被内存授予把机器拖垮。4.5 现象五连接被分到了 internal 池和系统任务抢资源现象新建的池和组都正常但某类连接总是落到 internal 组这些连接的查询性能和系统任务互相干扰CPU 使用率起伏很大。原因分类函数返回的组名拼写错误或者大小写不一致SQL Server 找不到匹配组时默认落到 internal 池而不是 default 池。这是资源控制器最容易踩的坑因为分类函数的结果是字符串运行之前根本看不出来匹配不匹配。解决分类函数里加 ELSE 分支显式返回 default这是兜底的第一道防线。上线前用下面这条 SQL 把函数返回的所有组名和系统里的工作负载组名逐一比对-- 列出所有已注册的工作负载组供分类函数对照 SELECT name, pool_id FROM sys.resource_governor_workload_groups; -- 查看当前正在运行请求的分类结果 SELECT session_id, group_id, SUSER_SNAME() AS login_name FROM sys.dm_exec_requests;逻辑说明第一句确认组名拼写第二句在业务连接运行时实时查看它归属的 group_id。如果发现某类连接落在不期望的组优先检查分类函数的返回值而不是池配置——十有八九是字符串匹配出问题。服务器端不会因为这个拒绝连接而是默默把请求丢进 internal 池这种静默行为最坑人。5. 验证配置效果用统计信息和性能计数器确认资源分配真正生效配置上线不等于工作完成还要验证业务感知。资源池像黑匣子配置对了和配置错了从业务日志里不一定看得出来所以我每次配置完都盯两套指标资源池运行视图和性能计数器。-- 查看各池的 CPU 消耗占比和内存目标达成情况 SELECT rp.name AS pool_name, rp.min_cpu_percent, rp.max_cpu_percent, rp.consumed_cpu_percent, rp.min_memory_percent, rp.max_memory_percent, rp.target_memory_kb / 1024 / 1024 AS target_mem_mb, rp.used_memory_kb / 1024 / 1024 AS used_mem_mb FROM sys.dm_resource_governor_resource_pools AS rp WHERE rp.name NOT IN (internal, default);逻辑说明consumed_cpu_percent 反映该池实际吃到的 CPU如果长期低于 min_cpu_percent说明配置没生效或调度器没有按预期分配target_memory_kb 和 used_memory_kb 的差值可以看出内存预留是否达成。性能计数器方面Windows 性能监视器里的 SQLServer:Resource Pool Stats 对象按池提供 CPU usage % 和 Memory usage %配置前后各存一份采样对比才能确认改善幅度。性能计数器观察对象判断标准SQLServer:Resource Pool Stats\CPU usage %各资源池长期高于 MAX_CPU_PERCENT 说明池间借调频繁SQLServer:Resource Pool Stats\Memory usage %各资源池接近 100 需要排查内存授予和缓冲池压力SQLServer:Workload Group Stats\CPU usage %各工作负载组确认高频连接是否落在预期组配置之前先压一轮业务配置后再压一轮把两轮的数据放在一起比直观得多。比如 ERP 池的 CPU usage % 从压测前的 15% 涨到配置后的 30%说明 MIN_CPU_PERCENT 的预留确实生效了报表池的 Memory usage % 如果始终压在 30% 附近不再往下走说明 MAX_MEMORY_PERCENT 的长期限制也起作用了。验证完之后有个习惯值得固化把配置脚本存进版本库。资源池和分类函数都是元数据可以用一条 SELECT * FROM sys.resource_governor_configuration 导出当前配置快照再把 CREATE / ALTER 语句录入变更单做版本管理。我第一次上手资源控制器时吃过配置漂移的亏同事在测试环境改了分类函数没同步脚本生产上线时重建函数直接返工从晚上十点排查到凌晨两点。从那以后我每次变更都强制走一遍固定流程先导出配置快照再对比生产与版本库的差异最后才执行 ALTER 和 RECONFIGURE。这套流程多花十分钟但每次排查都少熬一个通宵。希望帮到你。本文还有配套的精品资源点击获取