SQL Server 2008 R2资源控制器配置与CPU内存治理

简介：《SQL Server 2008 R2 CPU 与内存最大优化分配》是一份面向数据库管理员与解决方案供应商的专题文档，聚焦多数据库共用同一台服务器时CPU与内存争抢这一常见难题。内容围绕资源控制器展开：先对比SQL Server 2005依赖独立实例与处理器亲和性、以及借服务器虚拟化做隔离等旧做法的局限，再讲解资源池与工作负载组的划分逻辑，说明最小值如何保障池内负载的固定配额、最大值如何抑制单一负载过度占用，并解释可能出现短暂CPU满载高峰的原因；同时介绍按请求特性分类投递到不同工作负载组的思路，例如给查询类操作更多CPU时间，也提示配置需编写脚本的技术门槛与MSDN参考资料。压缩包内为1个docx文档，约84KB，篇幅紧凑，便于随时查阅或在团队内传阅。目前已有1355人学习，适合需要为多实例环境规划资源配额、排查CPU峰值与内存分配失衡的中高级运维人员参考。

1. SQL Server 2008 R2 装完就跑满 CPU：资源控制器这套东西不配等于白装

上周帮人看一台还跑着 SQL Server 2008 R2 的物理机，症状很典型：装完之后没做任何资源治理，四个业务库挤在一个默认实例里，白天报表一跑，CPU 直接顶到 95%，内存被 buffer pool 吃满，其他库连个简单查询都要排队。用户第一反应是「是不是装的时候选错版本了」——其实不是，SQL Server 2008 R2 的 CPU 和内存最大优化分配，从来不在安装向导那几个勾选框里，而是在装完之后的资源控制器（Resource Governor）里。这套东西在 2005 时代只能靠多实例加处理器亲和度硬切，切出去就借不回来；2008 R2 换成了资源池加工作负载组的模型，但默认只给你系统池和默认池，不配就是所有请求挤在默认池里抢同一份资源。这篇记录的是我在 Windows Server 2008 R2 环境上把 SQL Server 2008 R2 装好、再把资源池和工作负载组配到能用的全过程，适合还在维护这套老库的运维和开发者，新手照着能走通，熟手可以直接看参数取舍和踩坑那几段。

2. 装前环境确认：版本、补丁与实例规划

2.1 版本与补丁：为什么一定要 SP1 以上

SQL Server 2008 R2 的初始发行版（RTM，10.50.1600）里资源控制器是存在的，但分类函数在高并发下的行为有几个已知的修正，实际生产上我一般不在 RTM 上直接配资源池。判断当前实例版本不用进图形界面，开 SSMS 连上去跑一句就行：

-- 查看实例版本、版本类型、是否已打补丁 SELECT SERVERPROPERTY('ProductVersion') AS 产品版本, -- 期望 10.50.2500 及以上 SERVERPROPERTY('ProductLevel') AS 补丁级别, -- 期望 SP1 / SP2 / SP3 SERVERPROPERTY('Edition') AS 版本类型, -- Enterprise / Datacenter 等 SERVERPROPERTY('IsIntegratedSecurityOnly') AS 仅Windows身份验证;

ProductVersion返回 10.50.1600 说明是 RTM，10.50.2500 是 SP1，10.50.4000 是 SP2，10.50.6000 是 SP3。资源控制器这个功能本身在 Standard 及以上版本都有，但资源池数量有上限差异，Enterprise 能建更多池，这也是我建议先查Edition的原因。补丁级别不对就先打 SP，别急着配池——踩过这个坑，RTM 上配好的分类函数在升级 SP 之后行为会变，等于白配一遍。

2.2 实例与内存的预分配：装之前就要定下来

安装向导走到「实例配置」那一步时，如果这台机器只跑一个实例，用默认实例最省事；如果确实要按业务分实例，命名实例的名字别用中文也别带空格。真正影响后续资源分配的是「数据库引擎配置」里的排序规则和身份验证模式，这两项装完再改代价很大，我一般按业务系统的要求一次定死。

内存这块，安装时不用管，装完在实例属性里调。原则是给操作系统留出至少 2GB 到 4GB，其余留给 SQL Server 的 max server memory。一句话记法：物理内存减去系统预留，就是实例的内存天花板。

这张表不是公式，是经验值。虚拟机上还要再往下压，因为宿主层也要吃内存。这里定的是整个实例的上限，资源控制器里的池最大值是在这个上限内部再切分，两层是叠加关系，很多人会搞混。

2.3 装完先做的三件事

装完不要直接开始建池，先按顺序把三件事做完：第一，确认 SQL Server 服务和 SQL Server Browser 服务的启动类型是手动还是自动，按需设；第二，在 SSMS 里连上实例，确认能查到sys.dm_os_sys_info；第三，把上面的版本查询再跑一遍，确认补丁级别。这三步是后面所有配置的地基，任何一步不对，后面的池配出来都是假的。

-- 确认实例能看到系统资源视图，同时看当前 CPU 和内存的真实盘子 SELECT cpu_count AS 逻辑CPU数, physical_memory_kb / 1024 / 1024 AS 物理内存GB, committed_target_kb / 1024 / 1024 AS 当前目标提交内存GB FROM sys.dm_os_sys_info;

cpu_count是 SQL Server 能看到的逻辑处理器数量，committed_target_kb是当前实例打算用到的内存目标值。如果这里的 CPU 数和物理机对不上，说明有亲和度设置或者 BIOS 层的限制，得先解决掉，不然资源池的百分比算出来全是错的。

3. 配置资源池：最小值和最大值到底怎么填

3.1 资源池的模型：池里装组，组里装请求

资源控制器的结构是三层：资源池（Resource Pool）持有 CPU 和内存的额度，工作负载组（Workload Group）挂在池下面，分类函数（Classifier Function）在连接进来的那一刻决定这个会话进哪个组。默认存在两个池，internal是系统池，装的是 SQL Server 自己的后台任务，改不了也删不掉；default是默认池，所有没被分类函数命中的请求都落在这里。

建池的语法本身不复杂，关键在参数。下面这段是我在测试实例上用的建池脚本，两个池对应两类业务：报表类和分析类，各自给不同的额度。

-- 报表池：给报表查询用，CPU 留足下限，内存给中等额度 CREATE RESOURCE POOL pool_report WITH ( MIN_CPU_PERCENT = 30, -- 保底 30% CPU，负载高时不被饿死 MAX_CPU_PERCENT = 70, -- 上限 70%，防止报表把整机吃满 MIN_MEMORY_PERCENT = 20, -- 保底 20% 内存 MAX_MEMORY_PERCENT = 50 -- 内存上限 50% ); GO -- 写入池：给批量写入用，CPU 下限低，内存需求大 CREATE RESOURCE POOL pool_write WITH ( MIN_CPU_PERCENT = 10, MAX_CPU_PERCENT = 40, MIN_MEMORY_PERCENT = 10, MAX_MEMORY_PERCENT = 40 ); GO

四个参数的逻辑要分开看。MIN_CPU_PERCENT和MIN_MEMORY_PERCENT是保底值，意思是哪怕整机被打满，这个池也能拿到这么多资源，但所有池的保底值加起来不能超过 100%，这是硬约束，超了直接报错。MAX_CPU_PERCENT和MAX_MEMORY_PERCENT是天花板，防止某个池失控。特别说明一点：CPU 的最大值不是硬顶，短时间冲过上限是正常现象，别看到瞬时 100% 就以为配置没生效；内存的最大值则相对严格。

3.2 工作负载组：真正决定请求去哪的一层

池建好只是有了容器，请求要落进去还得靠工作负载组。一个池下面可以挂多个组，组里才是具体的会话。我一般按「业务类型 + 优先级」来切组，不按具体数据库切，这样扩库的时候不用改配置。

-- 报表池下挂一个高优先级组，用于核心报表 CREATE WORKLOAD GROUP wg_report_core WITH ( IMPORTANCE = HIGH, -- 同池内优先调度 REQUEST_MAX_MEMORY_GRANT_PERCENT = 25, -- 单查询最多拿池内存的 25% MAX_DOP = 4 -- 单查询并行度不超过 4 ) USING pool_report; GO -- 报表池下再挂一个普通组，用于临时取数 CREATE WORKLOAD GROUP wg_report_adhoc WITH ( IMPORTANCE = LOW, REQUEST_MAX_MEMORY_GRANT_PERCENT = 10, MAX_DOP = 2 ) USING pool_report; GO

IMPORTANCE有三个取值：LOW、MEDIUM、HIGH，只在同一个池内部起作用，跨池没有意义，这点很容易理解错。REQUEST_MAX_MEMORY_GRANT_PERCENT控制单条查询能申请到的内存授予上限，报表类查询最容易在这上面翻车——一条大查询把池内存吃掉，后面的查询全在等内存授予。把它压到 25% 以内，牺牲一点单查询速度换整体稳定。MAX_DOP是并行度，老机器上核数不多的话，限到 2 到 4 比放开跑更稳。

3.3 分类函数：会话进门那一刻的判定逻辑

分类函数是整个资源控制器里唯一需要写代码的部分，也是原文提到的「需要编写大量脚本」的地方。它的本质是一个返回 sysname 的标量函数，在登录时执行一次，返回组名就把这个会话塞进对应的组。

-- 分类函数：按登录名和应用程序名分流 USE master; GO CREATE FUNCTION dbo.fn_classifier() RETURNS sysname WITH SCHEMABINDING AS BEGIN DECLARE @group sysname; -- 报表服务账号统一进核心报表组 IF SUSER_SNAME() = 'report_svc' SET @group = 'wg_report_core'; -- 应用程序名带 adhoc 的进临时取数组 ELSE IF APP_NAME() LIKE '%adhoc%' SET @group = 'wg_report_adhoc'; -- 其余全部落默认组 ELSE SET @group = 'default'; RETURN @group; END; GO

函数的限制要记牢：必须WITH SCHEMABINDING，返回值只能是已有的工作负载组名，函数里不能查表、不能有副作用、不能用GETDATE()这类非确定性函数。判定依据常用的是SUSER_SNAME()（登录名）、APP_NAME()（客户端程序名）、HOST_NAME()（主机名），三个够用了。写完函数要把它注册给资源控制器并启用，否则函数存在但不会被执行：

-- 注册分类函数并启用资源控制器 ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fn_classifier); GO ALTER RESOURCE GOVERNOR RECONFIGURE; GO -- 验证是否已启用，以及分类函数挂的是哪个 SELECT is_enabled AS 是否启用, classifier_function AS 分类函数 FROM sys.resource_governor_configuration;

ALTER RESOURCE GOVERNOR RECONFIGURE这句是必须的，改了池、组、函数的任何一项都要重新跑一遍，不跑就等于没改。sys.resource_governor_configuration这个视图里的is_enabled为 0 说明整个功能还没开，这时候前面所有配置都是挂起状态。

4. 内存配额与实例内存的联动：两层天花板别打架

4.1 资源池内存和 max server memory 的关系

这是最容易配错的地方。实例属性里的 max server memory 是整个实例的总天花板，资源池的MAX_MEMORY_PERCENT是在这个天花板之内的百分比切分。举个具体例子：物理内存 32 GB，max server memory 设 26 GB，某个池的MAX_MEMORY_PERCENT = 50，那这个池最多能拿到约 13 GB，不是 16 GB。很多人按物理内存算百分比，结果实际能用的内存远小于预期，然后怀疑资源控制器没生效。

反推的时候也一样：如果想让某个池稳定拿到 8 GB，在 26 GB 的实例上限下，百分比要设到 30 左右。这种换算写在脚本注释里，半年后回来看才不会忘。

-- 调整已有池的内存上限，改成 30%（对应实例上限下的约 8 GB） ALTER RESOURCE POOL pool_report WITH (MAX_MEMORY_PERCENT = 30); GO ALTER RESOURCE GOVERNOR RECONFIGURE; GO

ALTER RESOURCE POOL只改指定的参数，没写的参数保持原值。改完同样要RECONFIGURE。这里再强调一次，资源池的内存最小值之和也受 100% 约束，和 CPU 是一样的规则，别只盯着 CPU 忘了内存。

4.2 CPU 亲和度还要不要配

SQL Server 2008 R2 里 CPU 亲和度仍然存在，但和资源控制器是两套独立机制。我的做法是：整机只跑一个实例时，不开亲和度，全部交给资源控制器；一台机器上确实有多个实例时，先用亲和度给实例划核，再在实例内部用资源池细分。两套一起上不是不行，但排错的时候会很难判断到底是哪一层在限流，这就是血泪经验了。

-- 查看当前 CPU 亲和度配置，确认没有意外绑核 SELECT affinity_type_desc AS 亲和类型, scheduler_count AS 调度器数量 FROM sys.dm_os_schedulers WHERE scheduler_id < 255;

如果这里的调度器数量和预期不符，先去检查实例的处理器亲和度设置，别在资源池上找原因。

4.3 配完先不动业务，跑一轮基线

配置落地之后不要立刻放业务进池。我一般先让分类函数把监控账号和测试账号分到一个独立的小池，跑一两天看数据。基线数据靠两个 DMV：sys.dm_resource_governor_resource_pools看各池的实际用量，sys.dm_resource_governor_workload_groups看组级的排队情况。

-- 各资源池的实时 CPU 与内存使用情况 SELECT pool_id, name, cpu_usage_percent AS CPU使用率, used_memory_mb AS 已用内存MB, target_memory_mb AS 目标内存MB FROM sys.dm_resource_governor_resource_pools;

target_memory_mb是池当前的目标内存，会随着负载浮动，和used_memory_mb对比着看能判断池是吃紧了还是宽裕。刚配完的一两天，主要看有没有池长时间贴着上限跑，贴着上限说明额度给低了或者分类函数分错了组。

5. 避坑与排查：五个真踩过的坑

5.1 现象：分类函数建好了，请求还是全在默认组

原因：函数建完只跑了ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = ...)，没跟RECONFIGURE，或者is_enabled一直是 0。还有一种情况是函数建在了错误的数据库里——分类函数必须建在 master 库。

解决：先查sys.resource_governor_configuration确认is_enabled为 1、分类函数名正确，再确认函数在 master 下。两个都对还不生效，检查会话是不是复用了旧连接，分类只在登录时执行一次，老连接不会重新分类。

5.2 现象：池的最小值怎么设都报错

原因：所有池的MIN_CPU_PERCENT之和超过了 100，或者内存最小值之超过了 100。系统池internal虽然不能改，但也占着额度，实际可用空间比想象的少。

解决：先用查询把现有池的下限列出来加总，再决定新池给多少。报错信息里一般会提示约束冲突，别硬试。

-- 统计所有资源池的最小值之和，确认没超 100% SELECT SUM(min_cpu_percent) AS CPU下限合计, SUM(min_memory_percent) AS 内存下限合计 FROM sys.dm_resource_governor_resource_pools;

5.3 现象：某个池的 CPU 使用率冲过了最大值

原因：CPU 的最大值不是硬限流，短时间的突发可以越过。这是设计行为，不是配置失效。

解决：看持续时间。短暂尖峰忽略，持续贴着甚至超过上限，说明该池里跑的东西本身负载过高，先查慢查询和并行度，再考虑调低该池的MAX_CPU_PERCENT。单纯把最大值压低不解决问题，只会把压力推到别人身上。

5.4 现象：应用连不上数据库，报登录失败

原因：分类函数里用了SUSER_SNAME()做判断，但某个应用的登录名不在任何分支里，函数返回了空的组名或者不存在的组名。

解决：函数最后一定要有一个ELSE兜底返回'default'，这是硬性写法。另外函数里不要抛异常，不要引用不存在的组名，组名拼写错一个字母整个登录就失败。

5.5 现象：改了配置，重连之后行为没变

原因：资源控制器的配置缓存有生效时机，池和组的改动需要RECONFIGURE才进配置表，分类函数的改动还要等新会话。

解决：改完统一跑一遍ALTER RESOURCE GOVERNOR RECONFIGURE，然后断开重连再测。线上改配置我一般放到低峰期，因为重配的瞬间可能有短暂的调度抖动。

6. 装完怎么验证：从 DMV 到一次真实压测

验证分三层，一层比一层接近真实。第一层看配置是否落地，第二层看分类是否正确，第三层看额度是否真的起作用。

配置层验证就是把前面几个视图再查一遍，写成一个检查脚本存下来，每次改完都跑：

-- 一屏看全：功能是否启用、池和组的数量、分类函数 SELECT (SELECT is_enabled FROM sys.resource_governor_configuration) AS 资源控制器已启用, (SELECT COUNT(*) FROM sys.dm_resource_governor_resource_pools) AS 资源池数量, (SELECT COUNT(*) FROM sys.dm_resource_governor_workload_groups) AS 工作组数量, (SELECT classifier_function FROM sys.resource_governor_configuration) AS 分类函数;

分类层验证要靠sys.dm_exec_sessions关联sys.dm_resource_governor_workload_groups，看具体会话落在哪个组：

-- 查看当前会话分别落在哪个工作负载组 SELECT s.session_id, s.login_name, s.program_name, g.name AS 工作组 FROM sys.dm_exec_sessions s JOIN sys.dm_resource_governor_workload_groups g ON s.group_id = g.group_id WHERE s.is_user_process = 1;

is_user_process = 1过滤掉系统会话，看到的结果里业务账号应该落在预期的组里。这一步能立刻暴露分类函数写错分支的问题。

第三层是压测。我做的时候不用专业工具，就在测试库上开两个会话，一个跑大查询循环，一个跑小查询，观察两边的响应时间和 DMV 里的cpu_usage_percent变化。大查询那侧应该被限制在自己的池里，小查询那侧的延迟不能明显恶化。这一轮跑完，配置才算真的可信。

从那以后我每次配完资源控制器，都强制走一遍这三层验证再交付，尤其是分类函数那一层，漏了就等于整个配置在裸奔。这套东西配起来确实不算轻松，但配完之后的稳定性，是老实例上少有的能靠配置换来的东西。希望帮到你。

本文还有配套的精品资源，点击获取