简介：面向SQL Server 2008 R2数据库管理员与解决方案供应商的资源管理文档，聚焦CPU和内存的最大优化与分配。文档系统梳理了SQL Server 2005按实例和处理器亲和性分配资源、虚拟化隔离开销高等旧方案的局限，并重点讲解2008 R2资源控制器的使用方法：通过Management Studio定义系统资源池与默认资源池，为每个资源池设置CPU和内存的最小/最大百分比，配合负载工作组按请求特性分类；同时说明了各资源池最小值之和不超过100%、未声明最小值的资源可跨池借用、短时达到100%峰值属正常行为等要点。包内为1个docx文档，压缩包约84KB，便于快速阅读、检索和摘录脚本配置思路。已有1350人学习，适合需要掌握多数据库资源隔离与动态分配方法的中高级数据库运维人员，可直接用作性能调优与方案设计参考。

1. SQL Server 2008 R2 的 CPU 和内存最大优化分配：不是全给，而是先定上限再加锁

一台 64 核、128GB 内存的服务器，跑着 SQL Server 2008 R2，让 DBA 做“最大优化分配”。急性子的人第一反应是把最大服务器内存拉满、MAXDOP 设成 0、再打开 AWE……结果通常是：SQL 服务起来了，Windows 越用越卡，查询从 2 秒变成 20 秒。标题里的 CPU 和内存最大优化分配，本质上是一组边界和取舍：你能给多少、该给多少、以及如何确保给出去的资源真正用在查询上而不是互相打架。这个版本的 SQL 虽然已经老旧，但在不少传统企业里仍是核心库。说句公道话：“最大优化分配”不是“无上限”，恰恰相反，它是先算清楚物理内存、版本限制、系统预留，再通过 max server memory、Locked Pages、MAXDOP 和 affinity mask 把这些资源明确钉住。我一般按“分配上限 → 锁住物理资源 → 验证运行时是否兜住”的顺序处理，下面把每一步讲透。

2. 内存最大化分配：版本上限、系统预留与 max server memory 的落地

2.1 先看版本上限：2008 R2 的内存与 CPU 能“最大”到哪里

动手之前，先打开已安装实例的版本，确认是 Standard 还是 Enterprise。SQL Server 2008 R2 的“最大化”不是机器有多大就能吃多少，版本许可先画了一条线。这是我做过不少老库优化后最先确认的事，不然你在配置里填了 120GB，SQL Server 实际只能用到 64GB，剩下的数字只是写在配置里的心理安慰。

以 Standard 版为例，一台物理机装了 128GB 内存，你把 max server memory 配成 118000MB，SQL Server 也不会突破 64GB 这条版本线。这个现象在排查“为什么内存分配没生效”时非常常见，排了半天发现是版本限制。所以第一步不是开 SSMS，而是执行一条版本查询确认身份。

SELECT SERVERPROPERTY('Edition') AS edition, SERVERPROPERTY('ProductVersion') AS version, SERVERPROPERTY('EngineEdition') AS engine_edition;

这个查询输出实例版本号和大版本类别。2008 R2 的版本号基数是 10.50，如果看到 10.50.xxxx，说明补丁已经打了一部分。确认版本后，再根据 Standard 或 Enterprise 来决定内存上限，避免把大量时间耗在无意义的配置上。

2.2 系统预留：内存的“最大”不是物理内存的 100%

“最大优化分配”听起来像要把所有物理内存都放进 SQL Server，实际上必须留出 Windows 和周边进程的口粮。SQL Server 的缓冲池一旦吃进去，不会因为其他程序申请内存就主动吐出来，这是一个“请神容易送神难”的分配模型。如果你把 max server memory 设成物理内存总额，等 Windows 自身的页面缓存、杀毒软件、备份代理都被挤到虚拟内存时，整个服务器会比没做优化前还慢。

我一般在专用数据库服务器上按这个经验值做预留：

这组数字背后有个简单公式：物理内存减去 4GB 到 8GB 的操作系统保留，再扣掉同机运行的其他常驻程序。比如 64GB 机器上还跑了监控 Agent、备份客户端，就预留 6GB 左右；如果这台机器干干净净只跑 SQL Server，预留 4GB 也算够用。公式不是死板的，关键是记住“给系统留出呼吸空间”这个原则。

min server memory 也有说法。单实例、专职服务的机器，最小内存可以设一个 4GB 左右的底，防止 SQL Server 在内存紧张时把执行计划缓存和缓存区过度压缩；如果是多实例共存，最小内存就要谨慎，每个实例都设一个比较大的最小值，反而会让 Windows 在物理内存不足时开始交换，得不偿失。

2.3 用 sp_configure 落地内存分配：三个命令与参数说明

配置入口有两个：SSMS 实例属性里的“内存”页，或者 sp_configure。图形界面看起来直观，但要对多台服务器做批量收编时，脚本更可靠。下面这组脚本是 128GB 物理内存、专职数据库服务器的常见配置。需要先打开“show advanced options”，才看得到 min/max server memory。

-- 开启高级选项，否则找不到 min server memory 和 max server memory EXEC sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; GO -- 128GB 物理内存，系统预留 10GB，设置最小 4GB，最大 118GB EXEC sp_configure 'min server memory (MB)', 4096; EXEC sp_configure 'max server memory (MB)', 118000; RECONFIGURE WITH OVERRIDE; GO

有几个参数细节需要说明。单位永远是 MB，不是 GB，填 118GB 前先在纸上换算出 MB；sp_configure 里的 max server memory 约束的是 SQL Server 缓冲池，不限制所有内部线程和 CLR 消耗；RECONFIGURE WITH OVERRIDE 在这条语句里不是可有可无的后缀，当配置项处于 advanced 级别时，普通 RECONFIGURE 可能拒绝命令。

设置 max server memory 后通常不需要重启服务，它会逐渐生效。但如果你是从一个已经吃满内存的状态往下调，SQL Server 并不会马上释放内存，需要观察一段时间，或者在业务允许时重启 SQL 服务。这个“配置生效但内存还在”的现象，属于 SQL Server 内存管理的常态，不是故障。

2.4 加上 Locked Pages：确保分配的内存不被换出

内存分配写进配置后，下一步是让 Windows 不要把 SQL Server 的物理页换到磁盘。这一步就叫“锁定内存中的页面”，也就是 Locked Pages in Memory。它和旧教程里 32 位时代的 AWE 不是一回事，在 64 位的 2008 R2 上，AWE 基本没有存在意义，而 Locked Pages 才是真正把内存钉在物理内存里的机制。

启用方式不在 SQL Server 里，而在 Windows 本地安全策略：找到“锁定内存中的页面”，把 SQL Server 服务账号加进去。改完策略后重启 SQL Server 服务，然后通过下面的 DMV 确认是否生效。

SELECT sql_memory_model_desc, locked_page_allocations_kb / 1024 AS locked_page_mb FROM sys.dm_os_sys_info;

如果 sql_memory_model_desc 显示为 LOCK_PAGES，说明服务账号已经有锁定权限且 SQL Server 正在使用该特性；显示 CONVENTIONAL 则说明没生效，回到组策略检查账号加没加对。locked_page_allocations_kb 是锁定页面的字节量，单位也是 KB，这个数字越大，说明 SQL Server 使用的内存越稳定，不会被换出到磁盘。对“最大化分配”这个目标来说，这一步价值很高，因为它让前面设置的上限真正有了物理内存兜底，而不是只停留在配置数字上。

3. CPU 最大化分配：MAXDOP、affinity mask 与 max worker threads 的取舍

3.1 怎样才算 CPU 分配被“拉到最大”

CPU 侧的“最大化”比内存更绕。默认情况下，SQL Server 会自动使用所有可用的逻辑处理器，你不需要做任何事，它就已经在用全部 CPU 了。真正困扰我们的是另外三件事：并行查询的调度开销、NUMA 跨节点访问、以及多实例并存时的 CPU 归属。

先做一个基本判断：这台机器跑的是 OLTP 还是 OLAP。OLTP 的查询短平快，追求并发；OLAP 的大查询依赖并行，追求吞吐。CPU 最大化分配，在这两种负载下的最优解完全相反。一个通用配置打天下，在 2008 R2 的多核服务器上是危险的。所以 CPU 分配不是“把每个核都点亮”这种表面动作，而是把并行的口子开到合适程度，让每个核都在干正事。

3.2 MAXDOP 设 0 ≠ 最大化，0 只是“无限制并行”

MAXDOP 全称 max degree of parallelism，控制单个 SQL 语句执行时最多能用几个 CPU。默认值是 0，很多人以为是“不限制”，从字面上理解没错，但这个“不限制”在实践中非常危险。16 核机器上用默认的 0，一条大查询可能分裂出 16 个并行分支，然后每个分支再去扫描表，CPU 瞬间被打满，其他小查询只能在后面排队。

我处理过的优化分配场景，几乎都会把 MAXDOP 从 0 改成具体数字。OLTP 系统建议 2 到 4，OLAP 报表库可以到 4 到 8，超过 8 基本没有线性收益，跨 NUMA 的开销反而比并行收益更明显。下面命令把整个实例的 MAXDOP 设为 4。

EXEC sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'max degree of parallelism', 4; RECONFIGURE WITH OVERRIDE; GO

这条配置是实例级别的，对 2008 R2 不重启即可生效，但不影响正在执行的查询。设 4 不是万能答案，它表示任何单条语句最多用 4 个 CPU。如果业务里有一种特定大报表可以从 8 个 CPU 获益但也别整库改，可以在语句上加 OPTION (MAXDOP 8)，保持实例级设置稳定。这个“实例级收着点，语句级放开点”的做法，比整个库库乱改 MAXDOP 稳妥。

3.3 affinity mask 与 affinity I/O mask：多实例场景才需要动的手脚

affinity mask 是让 SQL Server 只使用指定范围内的逻辑处理器，默认 0 表示全部使用。单实例、物理机、没有任何资源隔离需求的场景，不要动它。真正用到 affinity mask 的场景，是一台物理机上同时跑两个以上 SQL Server 实例，比如测试环境加生产实例共存。此时默认配置会让两个实例抢同一批 CPU，查询调度互相干扰。

2008 R2 上设置亲和性最简单的方法是打开 SQL Server 配置管理器，进入 SQL Server 服务的“处理器”选项卡，勾选要给当前实例使用的 CPU。图形界面的好处是不用手算掩码。如果用脚本，就要指定掩码值。比如一个 16 逻辑处理器的机器，A 实例使用 CPU 0 到 7，掩码是 0xFF 即 255；B 实例使用 CPU 8 到 15，掩码是 0xFF00 即 65280。

-- 实例 A 使用前 8 个逻辑处理器 EXEC sp_configure 'affinity mask', 255; RECONFIGURE WITH OVERRIDE; GO

注意不要把 affinity I/O mask 与 affinity mask 混在一起。affinity mask 管的是 CPU 调度，affinity I/O mask 管的是磁盘 I/O 完成端口绑定的 CPU。在 2008 R2 上，I/O 掩码默认 0，绝大多数业务不需要修改。I/O 掩码一旦误设，可能让 SQL Server 无法处理预期之内的磁盘 I/O 中断，磁盘等待时间飙升。这个参数“经典但危险”的名头不是白来的。

3.4 max worker threads：保留 0，让 SQL Server 自己算

max worker threads 是另一个容易被误解成“最大化”的参数。它控制 SQL Server 工作线程池的最大数量。有些教程会把它的值设成 CPU 数乘以某个系数，说这样能让 CPU 利用率更高。实际经验告诉我，2008 R2 上这个参数默认值为 0，SQL Server 会在启动时根据当时的 CPU 数量自动计算一个合理值，并且能在运行中动态调整。

如果你在做“最大化分配”时强行把它调大，线程池里堆太多空闲工作线程，上下文切换的开销会吃掉部分 CPU 资源。把它调小，高并发场景下查询又可能因为线程池排队而出现等待。排查时看到 SQL 错误日志里有“task allocation failure”时再去动它也不迟。下面这句命令把控制权交回给 SQL Server 自己。

EXEC sp_configure 'max worker threads', 0; RECONFIGURE WITH OVERRIDE; GO

把 0 理解成“系统自动判断”，而不是“不设置”。这是我遇到的最稳妥的做法。在 2008 R2 这种老版本上，任何“手动指定一个更大的线程数”的想法，最终都会在并发高峰期变成一台卡住的服务器。CPU 最大化分配，本质上是让 SQL Server 按需创建线程，而不是一次性塞给它一大堆等待被调度的线程。

4. 最大化分配时必然会遇到的 5 个 CPU/内存坑

4.1 把 max server memory 直接填成物理内存总大小

现象：128GB 内存的机器，max server memory 配成 131072MB，几天后 Windows 开始频繁读写页面文件，服务器整体响应变慢，SQL Server 自身查询没崩但客户端连接超时。

原因：SQL Server 缓冲池一旦占住内存，不会因为有其他进程申请物理内存就主动归还。Windows 想满足其他程序的内存申请，只能把一部分分页写入磁盘，最终双方的性能一起崩掉。

解决：按物理内存减去操作系统预留 4GB 到 8GB 重新计算。128GB 专机我通常设 118000MB，也就是留 10GB 给系统。如果机器上还跑着备份软件、监控 Agent，再往下减 2GB。

4.2 多实例共存时每个实例都做“最大化”

现象：一台 64GB 机器上跑两个 SQL Server 2008 R2 实例，分别把自己的 max server memory 都设成 50GB，运行一段时间后两个实例都出现明显的内存等待，查询变慢。

原因：每个实例都只考虑了自己，不考虑同机邻居。两者合计的上限超过了物理内存可分配范围，操作系统被迫交换页面，反而谁都没得到好处。

解决：多实例场景先统计整体物理内存和系统预留，再按业务权重拆分总配额。比如 64GB 机器留 8GB 给系统，剩下 56GB 按 7:3 分，一个实例拿 39GB，另一个拿 17GB。max server memory 必须写绝对的 MB 数，不能靠默认值。

4.3 MAXDOP 设成 0 后 CPU 长期跑满

现象：16 核服务器 MAXDOP 为默认的 0，白天高峰期 CPU 持续 100%，但很多查询并不快，sys.dm_os_wait_stats 里 CXPACKET 等待居高不下。

原因：MAXDOP=0 允许单条语句使用全部 CPU。几个大查询同时并行分裂，每个查询都尝试抢够 16 个线程，CPU 调度器被并行分支塞满，普通小查询反而被挤在后面。

解决：先把实例级 MAXDOP 设为 4，观察峰值时段 CPU 是否回落。如果特定的大报表需要更多并行，单独在该查询语句加 OPTION (MAXDOP 8)，不要改全局配置。

4.4 在虚拟机上手动设置 affinity mask

现象：一台 VMware 虚机分配了 16 个 vCPU，DBA 把 SQL Server affinity mask 固定到 CPU 0 到 7，结果 SQL Server 的 CPU 使用率没上去，反而偶发等待 SOS_SCHEDULER_YIELD，业务反馈有时候慢得像卡住。

原因：虚拟机里的 vCPU 并不真正固定在某个物理核心上。宿主机的调度器会把不同 vCPU 分散到不同物理核心，手动限制 SQL Server 只用部分 vCPU，等于人为降低了并发调度能力，还增加了上下文切换。

解决：虚拟机环境不要设置 affinity mask 和 affinity I/O mask，保持默认 0。把精力放在 MAXDOP 和 max server memory 上，虚拟化层会自己调度物理核心。

4.5 照搬 32 位时代的 AWE 配置

现象：老教程要求启用“awe enabled”，照着在 x64 的 2008 R2 上开了它，内存配置没变得更充裕，反而出现奇怪的内存申请失败，重启后又正常一段时间。

原因：32 位时代通过 AWE 突破 4GB 内存限制的做法，到 x64 架构下已无意义。SQL Server 2008 R2 在 64 位系统上可以直接寻址更大的内存范围，再开 AWE 只会引入额外的地址窗口逻辑，属于画蛇添足。

解决：确认操作系统是 x64 后，保持 sp_configure 中 awe enabled 为 0。要让内存稳定，正确路径是启用 Windows 的 Locked Pages in Memory，而不是开 AWE。这中间的血泪经验是：老教程要看时代背景，2008 R2 的优化请以 x64 体系为前提。

5. 用 DMV 和性能计数器验证最大化分配的成果

5.1 内存侧：PLE 与 target/total server memory

配置做完不是结束，验证才是判断分配是否合理的唯一标准。我通常让服务器跑 24 小时业务，然后用下面这组 DMV 观察缓冲池健康度。Page life expectancy 也就是 PLE，表示数据页在缓冲池里平均存活多少秒，它是内存压力的直接反映。

SELECT counter_name, cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:Buffer Manager' AND counter_name IN ('Page life expectancy', 'Total Server Memory (KB)', 'Target Server Memory (KB)');

PLE 长期低于 300，说明内存压力很大，SQL Server 的缓冲页在不停被淘汰；高于 600 通常说明分配充足。Total Server Memory 等于 max server memory 时，要继续观察 PLE，如果 PLE 还是在掉，说明业务确实吃不满这个上限，要么加内存，要么优化查询。Target Server Memory 表示 SQL Server 根据负载期望达到的内存目标，如果它反复波动，说明负载不稳定。

5.2 CPU 侧：从调度器看是否真的“接得住”

CPU 分配是否合理，不只看利用率，还要看有没有排队。sys.dm_os_schedulers 能直接看到每个调度器上的任务排队情况。这个 DMV 在 2008 R2 上是个黑匣子透视镜，很多表面正常但内部排队的问题都能从这里看出来。

SELECT scheduler_id, cpu_id, current_tasks_count, runnable_tasks_count, work_queue_count FROM sys.dm_os_schedulers WHERE scheduler_id < 255 ORDER BY scheduler_id;

runnable_tasks_count 表示正在等待 CPU 调度的任务数。如果多个调度器上该值长期大于 0，说明 CPU 已经排队，核心数不够用或某些核心被不合理地占住了。work_queue_count 则能看到工作线程池堆积的情况，数值过高时意味着任务分配跟不上。再结合 sys.dm_os_wait_stats 里的 SOS_SCHEDULER_YIELD 和 CXPACKET，就能判断是 CPU 不够、MAXDOP 太高还是内存吃紧。我会把这些值按小时采样，持续看一个完整业务周期，而不是配置完看一眼就完事。最大化分配本来就是一个动态平衡的过程，配置只是起点，验证才是落地点。希望帮到你。

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