简介面向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 的“最大化”不是机器有多大就能吃多少版本许可先画了一条线。这是我做过不少老库优化后最先确认的事不然你在配置里填了 120GBSQL Server 实际只能用到 64GB剩下的数字只是写在配置里的心理安慰。2008 R2 版本内存上限逻辑处理器上限Standard64GB16 个逻辑处理器Enterprise / Datacenter受操作系统支持通常可达数 TB 级别64 个逻辑处理器及以上以 Standard 版为例一台物理机装了 128GB 内存你把 max server memory 配成 118000MBSQL 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 自身的页面缓存、杀毒软件、备份代理都被挤到虚拟内存时整个服务器会比没做优化前还慢。我一般在专用数据库服务器上按这个经验值做预留物理内存建议 max server memory 起始值8GB6144MB16GB14336MB32GB28672MB64GB58368MB128GB118000MB这组数字背后有个简单公式物理内存减去 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 前先在纸上换算出 MBsp_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 到 4OLAP 报表库可以到 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 即 255B 实例使用 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 上这个参数默认值为 0SQL 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 等待居高不下。原因MAXDOP0 允许单条语句使用全部 CPU。几个大查询同时并行分裂每个查询都尝试抢够 16 个线程CPU 调度器被并行分支塞满普通小查询反而被挤在后面。解决先把实例级 MAXDOP 设为 4观察峰值时段 CPU 是否回落。如果特定的大报表需要更多并行单独在该查询语句加 OPTION (MAXDOP 8)不要改全局配置。4.4 在虚拟机上手动设置 affinity mask现象一台 VMware 虚机分配了 16 个 vCPUDBA 把 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 太高还是内存吃紧。我会把这些值按小时采样持续看一个完整业务周期而不是配置完看一眼就完事。最大化分配本来就是一个动态平衡的过程配置只是起点验证才是落地点。希望帮到你。本文还有配套的精品资源点击获取