简介这份文档面向SQL Server数据库管理员与解决方案供应商聚焦SQL Server 2008 R2中CPU与内存资源的分配优化问题。相比SQL Server 2005依赖独立实例与处理器亲和性的做法2008 R2引入的资源控制器可通过SQL Server Management Studio定义资源池与工作负载组为多数据库共存环境提供更灵活的资源管控手段。文档系统讲解了资源池最小值和最大值的含义与配置原则说明如何按请求特性将负载分配到不同资源池并指出脚本编写门槛较高这一实际难点同时提示可借助MSDN文章完成配置。资源包为1个docx文件约84KB内容紧凑适合作为DBA优化服务器资源分配的参考笔记。目前已有1350人学习下载可帮助读者理解资源控制器的核心机制掌握CPU与内存按需分配、避免数据库间资源争抢的实践思路。1. 资源控制器SQL Server 2008 R2 里被低估的 CPU 与内存分配方案如果你还在用 SQL Server 2005 那套「一个数据库一个实例 处理器亲和度」的老办法来隔离多库资源大概率遇到过这种场景A 库跑报表把 CPU 吃满B 库的订单写入被拖到超时而 A 库闲下来时那些绑死的核心又白白空转。SQL Server 2008 R2 引入的资源控制器Resource Governor就是冲着这个痛点来的——它把 CPU 和内存从「实例级硬绑定」下沉到「连接级动态分配」同一实例内不同来源的连接可以走不同的资源池各自有最小值和最大值兜底。这套机制适合谁手上有一台物理机或虚拟机跑着多个业务库、负载峰谷差异大、又不想拆实例的 DBA 和解决方案供应商。它不是什么点几下鼠标就能配好的管理工具分类函数和工作负载组都得靠 T-SQL 脚本落地但一旦跑通收益是实打实的。2. 资源池与工作负载组先搞懂三层模型再动手2.1 资源控制器到底管什么资源控制器在 SQL Server 2008 R2 里的定位是介于「操作系统调度」和「SQLOS 内部调度」之间的一层策略引擎。它不直接抢 CPU 时间片也不直接锁内存页而是通过三个对象把请求归类、限流、兜底资源池Resource PoolCPU 和内存的配额容器定义MIN_CPU_PERCENT、MAX_CPU_PERCENT、MIN_MEMORY_PERCENT、MAX_MEMORY_PERCENT四个核心参数。工作负载组Workload Group资源池内部的子分组一个池可以挂多个组组内可以设IMPORTANCELow / Medium / High来区分同池内的优先级。分类函数Classifier Function一个返回 sysname 的标量函数在登录时执行根据HOST_NAME()、APP_NAME()、SUSER_NAME()等把连接打到对应的工作负载组。默认安装完有两个池internal系统内部专用不可改和default所有未分类连接落这里。internal池的最小值固定占用一部分资源这是很多人算配额时漏掉的一块。2.2 最小值和最大值的真实语义最小值不是「预留但闲置」而是「保底可用」。假设你有三个池最小值分别设 20%、30%、10%总和 60%剩下 40% 是共享池谁需要谁抢。当池 A 空闲时它那 20% 并不会被锁死池 B 可以临时用超过 30% 的资源只要不突破自己的最大值。最大值也不是硬天花板。原文特别提到池可能触发短暂的 CPU 100% 高峰这是正常行为。原因是最大值约束的是「调度器层面的平均占用」不是逐毫秒的硬截断。如果你看到监控图上某个池瞬间冲到 100%别急着改参数先看持续时间——持续超过几秒才需要排查。内存这边更微妙。MIN_MEMORY_PERCENT和MAX_MEMORY_PERCENT控制的是缓冲池的目标区间不是查询执行时的内存授予。也就是说一个池即使内存配额很小跑一个大排序查询时仍可能从系统申请到超出配额的内存授予只是缓冲池的页面生命周期会受影响。这一点在规划时容易被忽略。2.3 建池、建组、写分类函数下面这套脚本是我在测试环境反复跑过的模板改一下百分比和分类条件就能用。注意顺序先建池再建组最后建并启用分类函数。-- 1. 创建两个资源池OLTP 池和报表池 CREATE RESOURCE POOL pool_oltp WITH ( MIN_CPU_PERCENT 30, MAX_CPU_PERCENT 70, MIN_MEMORY_PERCENT 30, MAX_MEMORY_PERCENT 70 ); GO CREATE RESOURCE POOL pool_report WITH ( MIN_CPU_PERCENT 20, MAX_CPU_PERCENT 60, MIN_MEMORY_PERCENT 20, MAX_MEMORY_PERCENT 60 ); GO -- 2. 在每个池下创建工作负载组 CREATE WORKLOAD GROUP wg_oltp WITH ( IMPORTANCE High, REQUEST_MAX_MEMORY_GRANT_PERCENT 25 ) USING pool_oltp; GO CREATE WORKLOAD GROUP wg_report WITH ( IMPORTANCE Low, REQUEST_MAX_MEMORY_GRANT_PERCENT 15 ) USING pool_report; GOIMPORTANCE只在同一资源池内部生效跨池不比较。REQUEST_MAX_MEMORY_GRANT_PERCENT限制单个查询能拿到的内存授予上限按池的MAX_MEMORY_PERCENT百分比算不是按服务器总内存算——这个参数是防大查询拖垮同池其他连接的关键。分类函数决定了连接进来时走哪个组。下面这个函数按应用名区分报表工具走报表池其余走 OLTP 池。-- 3. 分类函数按应用程序名分流 CREATE FUNCTION dbo.fn_classifier() RETURNS sysname WITH SCHEMABINDING AS BEGIN DECLARE grp sysname; IF APP_NAME() LIKE %Reporting% SET grp Nwg_report; ELSE SET grp Nwg_oltp; RETURN grp; END; GO -- 4. 绑定分类函数并启用资源控制器 ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION dbo.fn_classifier); GO ALTER RESOURCE GOVERNOR RECONFIGURE; GO分类函数有几个硬限制必须是WITH SCHEMABINDING不能查表、不能调存储过程、不能有副作用。它只在登录时执行一次之后连接的工作负载组就固定了。如果你需要中途切换只能断开重连。ALTER RESOURCE GOVERNOR RECONFIGURE是让配置生效的关键一步很多人建完池忘了执行然后纳闷为什么没效果。2.4 验证配置是否生效配完之后别急着上生产先用系统视图确认状态-- 查看资源池当前配置和实际使用 SELECT pool_id, name, min_cpu_percent, max_cpu_percent, min_memory_percent, max_memory_percent, -- 以下两列反映实际压力 cpu_percent, memory_percent FROM sys.dm_resource_governor_resource_pools; -- 查看工作负载组统计 SELECT group_id, name, pool_id, importance, total_request_count, total_queued_request_count FROM sys.dm_resource_governor_workload_groups;total_queued_request_count如果持续大于 0说明该组有请求在排队等资源要么调大池的MAX_CPU_PERCENT要么降低同池其他组的IMPORTANCE。cpu_percent和memory_percent是瞬时值多采样几次看趋势别拿单次快照下结论。3. 参数怎么设从负载特征反推百分比3.1 CPU 百分比的计算逻辑设最小值之前先算清楚internal池占了多少。internal池的MIN_CPU_PERCENT默认是 0但它实际会占用一部分调度资源尤其在系统连接活跃时。保守做法是给所有用户池的最小值总和留出 20% 余量别贴着 100% 配。假设服务器 8 核OLTP 业务日均 CPU 峰值约 50%报表业务日均峰值约 30%两者高峰时段重叠约 2 小时。那么池MIN_CPUMAX_CPU理由pool_oltp3070保底 30% 应对日常写入峰值允许抢到 70%pool_report2060保底 20% 保证报表不饿死峰值 60% 留余量共享余量50—两者都闲时可互相借用最小值总和 50%远低于 100%这样两个池在对方空闲时都能突破自己的最小值。如果你把最小值总和设到 90%那共享空间只剩 10%动态调配的意义就没了。3.2 内存百分比的坑内存比 CPU 更难调因为 SQL Server 的缓冲池是「有多少用多少」的模型。MIN_MEMORY_PERCENT设了 30%不代表这个池只占 30% 内存而是说当内存压力出现时这个池至少能保住 30% 的缓冲池页面不被踢出。实际配置时我一般按「业务库数据量 索引大小」估算热数据占比再留 10% 浮动。比如 OLTP 库热数据约 40GB报表库热数据约 20GB服务器总内存 128GBSQL Server 最大内存设 112GB那么OLTP 池MIN_MEMORY_PERCENT 40/112 ≈ 35%报表池MIN_MEMORY_PERCENT 20/112 ≈ 18%两者之和 53%剩余 47% 作为共享缓冲MAX_MEMORY_PERCENT我通常设得比MIN高 20~30 个百分点给突发查询留空间。但注意如果某个池的MAX_MEMORY_PERCENT设得太低大查询可能频繁触发内存授予等待RESOURCE_SEMAPHORE等待类型会飙升。3.3 工作负载组的细粒度参数除了池级别的百分比工作负载组还有几个值得调的参数-- 调整已有工作负载组的参数 ALTER WORKLOAD GROUP wg_report WITH ( IMPORTANCE Low, REQUEST_MAX_MEMORY_GRANT_PERCENT 10, REQUEST_MEMORY_GRANT_TIMEOUT_SEC 30, MAX_DOP 4, GROUP_MAX_REQUESTS 20 ); GO ALTER RESOURCE GOVERNOR RECONFIGURE; GOREQUEST_MEMORY_GRANT_TIMEOUT_SEC查询等内存授予的超时秒数默认 0 表示不超时一直等。报表类查询设 30 秒比较合理等不到就失败别拖着。MAX_DOP该组内查询的最大并行度。报表池设 4 可以防止单个大查询吃满所有调度器。GROUP_MAX_REQUESTS组内同时活跃请求数上限超出的排队。这个参数对防止连接风暴有用但设太小会导致正常请求也被排队建议先观察total_request_count的峰值再定。4. 避坑与排查资源控制器落地时的五个血泪教训4.1 分类函数返回了不存在的组名现象连接进来后落到default组预期的工作负载组统计里total_request_count一直是 0。原因分类函数返回的字符串和实际组名大小写或拼写不一致。SQL Server 的sysname比较在默认排序规则下不区分大小写但如果你的数据库排序规则是CS区分大小写Nwg_report和NWG_REPORT会被当成两个不同的组。解决分类函数里用变量拼组名时统一用UPPER()或LOWER()包一层或者直接从sys.dm_resource_governor_workload_groups里查出准确名称再硬编码。改完记得ALTER RESOURCE GOVERNOR RECONFIGURE。4.2 忘了给 internal 池留资源现象配完资源池后系统连接如备份、复制、监控采集响应变慢甚至出现登录超时。原因internal池承载系统任务它的资源需求不参与你的百分比计算。如果你把用户池的最小值总和设到 95%internal池能用的资源被挤压系统任务排队。解决用户池最小值总和控制在 70% 以内给internal和共享池留至少 30%。已经出问题的临时把某个池的MIN_CPU_PERCENT调低RECONFIGURE后观察sys.dm_os_wait_stats里RESOURCE_GOVERNOR相关等待是否下降。4.3 最大值设太低导致 CPU 100% 高峰被误判现象监控告警显示某池 CPU 冲到 100%但业务反馈正常。原因原文明确说了池可能触发短暂的 CPU 100% 高峰这是调度器的工作方式不是配置错误。最大值约束的是平均占用不是瞬时截断。解决把监控阈值从「瞬时 100%」改成「持续 5 秒以上超过 90%」。用sys.dm_resource_governor_resource_pools的cpu_percent每 10 秒采样一次连续三次超过MAX_CPU_PERCENT的 90% 才告警。4.4 分类函数里查了系统视图现象CREATE FUNCTION时报错「无法绑定到系统对象」或「分类函数不能包含数据访问」。原因分类函数要求WITH SCHEMABINDING且不能访问任何表、视图、存储过程。有人想在里面查sys.dm_exec_sessions拿登录信息直接翻车。解决只能用HOST_NAME()、APP_NAME()、SUSER_NAME()、SUSER_SNAME()这几个内置函数。需要更复杂的分类逻辑就在应用连接串里加Application Name参数分类函数按APP_NAME()分流。4.5 改完配置没执行 RECONFIGURE现象脚本跑完没报错但资源池行为跟改之前一样。原因CREATE和ALTER只是把配置写进元数据ALTER RESOURCE GOVERNOR RECONFIGURE才是让配置生效的开关。这个设计是为了让你批量改完再一次性生效但很容易忘。解决养成习惯每个改资源控制器的脚本末尾都加上ALTER RESOURCE GOVERNOR RECONFIGURE;。可以用SELECT * FROM sys.dm_resource_governor_configuration确认is_reconfigure_pending是否为 0。5. 进阶技巧用 DMV 做持续验证和动态调参资源控制器配好只是开始真正的功夫在持续验证。我一般会建一套轻量的监控查询每天跑一次看趋势而不是看单点。-- 资源池压力趋势对比配置值和实际值 SELECT rp.name AS pool_name, rp.min_cpu_percent, rp.max_cpu_percent, rp.cpu_percent AS actual_cpu, rp.min_memory_percent, rp.max_memory_percent, rp.memory_percent AS actual_memory, wg.name AS group_name, wg.total_request_count, wg.total_queued_request_count, wg.max_request_cpu_time_ms, wg.max_request_memory_grant_kb FROM sys.dm_resource_governor_resource_pools rp JOIN sys.dm_resource_governor_workload_groups wg ON rp.pool_id wg.pool_id ORDER BY rp.name, wg.name;重点看三列total_queued_request_count持续大于 0 说明资源不够max_request_cpu_time_ms突然飙升说明有大查询混进了不该进的组max_request_memory_grant_kb接近REQUEST_MAX_MEMORY_GRANT_PERCENT换算出的上限说明该调大或优化查询。另一个技巧是用sys.dm_exec_requests关联工作负载组实时看哪个连接在哪个组里跑-- 实时查看活跃请求的资源组归属 SELECT r.session_id, r.status, r.command, r.cpu_time, r.total_elapsed_time, wg.name AS workload_group, rp.name AS resource_pool FROM sys.dm_exec_requests r JOIN sys.dm_resource_governor_workload_groups wg ON r.group_id wg.group_id JOIN sys.dm_resource_governor_resource_pools rp ON wg.pool_id rp.pool_id WHERE r.session_id 50; -- 排除系统会话这个查询在排查「为什么某个查询变慢了」时特别有用。如果发现报表查询跑到了 OLTP 组里说明分类函数的APP_NAME()匹配条件没覆盖到那个报表工具的实际应用名——很多报表工具连接串里的Application Name是默认值不是你以为的那个名字。调参的节奏我一般是这样上线第一周每天看一次 DMV记录total_queued_request_count和cpu_percent的峰值第二周根据峰值调整MIN_CPU_PERCENT和MAX_CPU_PERCENT每次调整幅度不超过 10 个百分点稳定后每月复查一次。记住一个原则资源控制器的参数是「策略声明」不是「性能旋钮」调得太频繁反而会让 SQLOS 的调度器反复重新计算配额引入额外开销。从那以后我每次配完资源控制器都强制走一遍「分类函数测试 → DMV 采样 → 压力验证」三步确认total_queued_request_count在正常负载下为 0 才敢交给业务。希望帮到你。本文还有配套的精品资源点击获取