简介针对SQL Server 2008 R2多数据库环境下CPU与内存分配难题这份文档围绕资源控制器展开面向需要优化服务器性能的数据库管理员与运维人员。文档先梳理SQL Server 2005依赖独立实例与处理器亲和力的旧方案短板再讲解虚拟机分配资源的局限随后重点解析2008 R2资源控制器的设计思路通过默认资源池与系统资源池定义CPU和内存的最小、最大百分比确保关键负载获得保留资源同时允许空闲资源跨池流动内容还说明了工作负载组如何按请求特性分类转发以及设置最大值后短暂CPU冲高仍属正常现象避免管理员误判。资源共1个docx文件压缩包仅84KB轻量易读。已有1350人学习浏览适合希望快速理解资源池、工作负载组配置逻辑的SQL Server使用者。文档还提示了资源控制器依赖脚本配置的缺点并指引MSDN相关文章节省自行摸索时间。1. 装完不调等于白装SQL Server 2008 R2 的 CPU 与内存优化分配到底在解决什么网上还在大量搜 sql server 2008 r2 安装包下载、安装教程的人大多把系统跑起来就不再管了可问题恰恰从这里开始。SQL Server 2008 R2 安装完默认不限制任何资源max degree of parallelism 为 0意思是并行查询能吃满所有核max server memory 默认接近无上限意思是缓冲池会把物理内存吸干。于是最常见的一幕就是白天几个大查询把 CPU 打满、晚上内存被 Buffer Pool 占满导致 Windows 开始换页最后只能重启实例。这个标题背后做的事就是不动硬件、不改代码靠 sp_configure、DMV 和性能计数器把并行度、内存边界、CPU 亲和性调成贴合业务的状态。适合刚接手老实例的 DBA、运维以及准备给 2008 R2 做资源巡检的开发。2. 先摸清家底用 DMV 确认 2008 R2 当前的 CPU 与内存分配状态2.1 一条 SQL 看懂当前 CPU 与物理内存调参之前最怕的就是拿 感觉 当依据。比如一台物理 8 核 16 线程的机器你在任务管理器里看到 16 个小格就以为 SQL Server 有 16 个调度器实际要看 SQLOS 自己的视角。SQL Server 2008 R2 里最直接能查这些信息的是sys.dm_os_sys_info它记录的是当前实例启动时识别的硬件参数比任务管理器靠谱得多。-- 查看 SQL Server 2008 R2 当前识别的 CPU 与物理内存 SELECT cpu_count AS [逻辑CPU数], hyperthread_ratio AS [物理核/超线程比], physical_memory_kb / 1024.0 AS [物理内存MB] FROM sys.dm_os_sys_info;这段查询输出三个关键值cpu_count是 SQLOS 看到的逻辑处理器数量对应调度器个数hyperthread_ratio是物理核与逻辑核的换算关系比如 8 物理核 16 逻辑线程时它是 16physical_memory_kb是操作系统报告给 SQL Server 的物理内存。注意在虚拟机里cpu_count显示的是 vCPU 数而不是宿主机的物理核数后续设 maxdop 必须按这个值来不能按宿主机配置理解。很多 2008 R2 实例是从 2005 或 2000 升级上来的这台机器可能经历过加内存、改 vCPU 配额但 SQL Server 的配置一直是当年装机时的默认值。所以这条 SQL 的意义不是看热闹而是把「SQL Server 眼中的硬件」和「你以为的硬件」对齐。查询结果如果cpu_count是 32但你知道这台机器只有 12 物理核就要意识到虚拟化层开了超线程或者给了超额 vCPU后面的并行度设置就要小心。2.2 把六个资源相关的配置项一次性列出来摸清硬件后接着看实例层配置。SQL Server 2008 R2 把资源类配置存在sys.configurations里但很多人只查value列就下结论忘记了value_in_use才是真正生效的值。如果某次 sp_configure 改完没执行 RECONFIGURE两列就会不一致这时候按value判断就会踩坑。-- 查看当前资源分配相关的实例配置 SELECT name, value AS [配置值], value_in_use AS [运行值], minimum, maximum, is_dynamic AS [是否动态生效] FROM sys.configurations WHERE name IN (max degree of parallelism, min server memory (MB), max server memory (MB), affinity mask, affinity64 mask, awe enabled) ORDER BY name;min server memory (MB)的默认值是 0max server memory (MB)的默认值是 2147483647也就是不设上限max degree of parallelism默认 0表示使用全部逻辑处理器affinity mask和affinity64 mask默认也都是 0表示不固定调度器到特定 CPU。is_dynamic为 1 表示这个配置修改后不用重启 SQL Server 服务就生效2008 R2 里的内存和并行度选项基本都是动态的但没有执行 RECONFIGURE 之前value_in_use不会变这是新手最容易忽略的操作链。把查询结果和默认值比对就能快速判断这台实例是不是「裸奔状态」maxdop 为 0、max server memory 是 2147483647、affinity mask 为 0三件事凑齐基本可以确认资源分配从没被认真调过。接下来第 3 章和第 4 章的所有改动都是在为这种裸奔状态装上边界。3. 把 CPU 优化分配做实maxdop 与亲和性的调参路径3.1 maxdop 的设置逻辑先分清楚负载是 OLTP 还是 OLAPmax degree of parallelism控制的是单个并行查询最多能占用多少个调度器。SQL Server 2008 R2 的查询优化器发现某个语句适合并行执行时会按这个上限去分配 worker 线程。设成 0 等于不设限这在数据量小的机器上没有感觉但一旦出现几条大查询并发就会出现所有 CPU 被并行扫描占满、连登录请求都排队的场面。对正在跑业务的 OLTP 系统来说这比查询本身慢几秒更致命。先确定这台实例承载的业务类型再定值不要照抄别人的配置。我的做法分三档纯 OLTP 系统maxdop 设置在 4 以内保证有足够调度器处理小查询混合负载设置在 4 到 8 之间给报表查询留并行空间但也留出余量纯 OLAP 或数据仓库且并发用户数不多可以设置在 8 到 16。16 核以下的机器经验推荐不要超过核数的一半。负载类型逻辑 CPU 数maxdop 建议纯 OLTP82~4混合负载164~8纯 OLAP / 报表168~16虚拟化环境vCPU 超配8~322~4设置命令如下注意两个前提先打开高级选项改完必须 RECONFIGURE。-- 1. 开放高级配置选项 EXEC sp_configure show advanced options, 1; RECONFIGURE; GO -- 2. 把并行度上限设为 4按上面表格按需调整 EXEC sp_configure max degree of parallelism, 4; RECONFIGURE; GO -- 3. 确认改动已生效 EXEC sp_configure max degree of parallelism;这里解释几个参数细节。show advanced options必须最先设置为 1否则 max degree of parallelism 这类选项会被拒绝RECONFIGURE 的作用是把配置值写入运行态缺少这一步会出现第 5 章要讲的「改了不生效」问题。maxdop 是动态配置执行后不需要重启服务但如果实例上有正在运行的并行查询新值只对后续编译的语句生效已经跑起来的语句不会中断。改完后建议顺手看一个等待类型CXPACKET。它表示并行查询在等待子线程同步少量存在是正常的但如果优化前 CXPACKET 排在sys.dm_os_wait_stats的前三名说明并行度过高反而制造了大量协同开销。maxdop 调低后这个等待通常明显下降这是验证调整是否有效的直观指标。3.2 affinity什么时候真的需要绑核什么时候千万别碰affinity mask决定 SQLOS 的调度器允许跑在哪些逻辑 CPU 上本质是把 SQL Server 线程和特定 CPU 绑定。很多刚接触性能调优的人看到服务器上还有空闲核就想用 affinity 把实例隔离开这其实是用错了场景。对单实例部署而言绑核很少带来收益反而可能让 SQL Server 在 CPU 0 上和其他系统进程抢资源。真正该用 affinity 的场景是单机跑多个 SQL Server 实例或者 SQL Server 和其他高负载应用混部需要硬性划分 CPU 边界。SQL Server 2008 R2 提供了两种做法老的 sp_configure 里的 affinity mask32 位用 affinity mask超过 32 逻辑 CPU 用 affinity64 mask以及更直观的 ALTER SERVER CONFIGURATION。64 位实例上更推荐用后者它支持 CPU 0 TO 7 这样的区间写法不用自己做二进制到十进制的换算。-- 多实例隔离场景把当前实例绑定到 CPU 0~7 ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU 0 TO 7; GO -- 查看当前 CPU 亲和性 EXEC sp_configure affinity mask; GO -- 取消绑定恢复自动调度 ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU AUTO;参数说明CPU 0 TO 7 表示逻辑 CPU 编号 0 到 7闭区间共 8 个核CPU AUTO 取消手动绑定。执行 ALTER SERVER CONFIGURATION SET PROCESS AFFINITY 后SQL Server 会自动把位掩码同步到 affinity mask 配置项所以用 sp_configure 查看也能确认。如果服务器是 NUMA 架构绑核最好按 NUMA 节点划分比如第一个节点 0~7、第二个节点 8~15不要让一个实例的调度器横跨两个节点还同时访问远端内存。单实例 无其他高负载进程时保持默认 0 就是最优解。现在新 CPU 都在讲智能核心调度但 SQL Server 2008 R2 的 SQLOS 并不认识这套机制它只认逻辑处理器数量。给它绑死几个核反而把系统调度器的灵活性废掉了。4. 把内存优化分配做实max server memory 的计算与内存去向排查4.1 计算 max server memory给操作系统和其他进程留够余量SQL Server 的内存机制有个特性只要 max server memory 不设限它就会不断把空闲物理内存吸入 Buffer Pool因为它默认「内存闲着也是浪费不如用来缓存数据页」。对数据库本身这没错但服务器不只是 SQL Server 一个人的。操作系统需要内存做文件缓存和驱动缓冲Windows Defender 这类安全软件的 Antimalware Service Executable 进程也会随时占用几百 MB 到 1 GB 内存再加上备份代理、监控 agent内存被截胡后 Windows 就开始疯狂换页。计算 max server memory 的常见做法是按物理内存留出固定余量8GB 以内的小机器给 OS 留 2GB8GB 到 64GB 的机器留 4GB超过 64GB余量建议控制在 8GB 左右同时观察任务管理器里非 SQL 进程的实际占用。如果这台机器上还跑着其他业务进程要额外减掉它们的内存配额。只留 2GB 给 64GB 内存的服务器看似合理但一旦安全软件扫描仓库文件就可能触发内存压力。-- 以物理内存 64GB 为例SQL Server 上限设为 56320MB约 55GB EXEC sp_configure min server memory (MB), 2048; EXEC sp_configure max server memory (MB), 56320; RECONFIGURE; GO -- 验证内存配置 EXEC sp_configure max server memory (MB);这里要解释 min server memory 的作用它经常被误解。min server memory (MB)不是启动时立刻抢占 2GB而是表示 SQL Server 在内存压力下会努力让 Buffer Pool 保持在这个水位以上。换句话说它是在告诉内存管理器低于这个值就别把缓存页交出去。真正决定内存上限的是 max server memorySQL Server 启动时只吃少量内存随着数据页加载逐步增长到这个硬边界不会再突破。配置里的单位是 MB很多人第一次改会把它当 GB 填导致设了一个超大的值然后发现 SQL Server 内存还在继续涨以为配置没生效。实际上 56320 MB 就是 55GB如果直接填 56320 还嫌大说明把单位理解反了。改完内存配置后观察任务管理器里的 sqlservr.exe 工作集会缓慢上升直到接近上限这才是正常现象。4.2 内存都去哪了用 sys.dm_os_memory_clerks 追内存吃相内存上限设好以后还要知道内存是被谁吃掉的。SQL Server 进程内的内存并不只有 Buffer Pool 一块查询编译缓存、锁管理器、CLR、连接池都要占内存。2008 R2 里可以通过sys.dm_os_memory_clerks按类型汇总一眼看出哪些内存吃相异常。-- 按内存 clerk 类型汇总进程内内存占用 SELECT type, SUM(pages_kb) AS [物理内存KB], SUM(virtual_allocated_kb) AS [虚拟内存KB] FROM sys.dm_os_memory_clerks GROUP BY type ORDER BY [物理内存KB] DESC;结果里最常看到的几类MEMORYCLERK_SQLBUFFERPOOL是数据页缓冲池通常占比最大属于正常MEMORYCLERK_SQLCACHE是缓存计划的内存如果业务里有大量不重用的 ad-hoc 查询这一项会异常大OBJECTSTORE_LOCK_MANAGER是锁内存它暴涨往往说明有长时间未提交事务或锁升级风暴。如果 Buffer Pool 之外的项目加起来超过总内存的 20%先不要急着加大 max server memory而是先处理异常的 clerk。更直接判断内存压力的指标是Memory Grants Pending它表示有多少查询在等待内存授权。这个计数器长期大于 0说明内存不够分查询在 RESOURCE_SEMAPHORE 等待上排队。注意这个计数器是累积值不是瞬时值要看趋势不能只看某一秒的结果。-- 查看内存授权等待数量持续上涨说明内存候选不够 SELECT cntr_value AS [Memory Grants Pending] FROM sys.dm_os_performance_counters WHERE object_name LIKE %Memory Manager% AND counter_name Memory Grants Pending;如果Memory Grants Pending明显上涨同时第 4.1 节的余量已经留够优先检查有没有查询请求了超大的 sort/hash 内存。一次几百万行的排序可能就要 5GB 内存授权。用sys.dm_exec_query_memory_grants可以追到具体是哪条语句在申请内存这类问题靠调 max server memory 解决不了得改语句或加索引。顺带提一句排查进程内内存泄漏时很多人习惯用 poolmon 查内核池但 SQL Server 的内存是在进程内管理的用 DMV 定位比 poolmon 有效得多。4.3 别忘了 32 位实例的 AWE 边界2008 R2 是还能碰到 32 位实例的最后一个版本如果你维护的机器是 32 位系统内存分配的逻辑完全不同。32 位进程的用户模式地址空间通常只有 2GB 到 4GB即使物理内存有 16GBSQL Server 默认也够不着。部分 32 位版本支持 AWEAddress Windowing Extensions扩展内存寻址但需要在 sp_configure 里开启并给 SQL Server 服务账户授予 Lock Pages in Memory 权限。-- 仅 32 位实例需要启用 AWE64 位实例开启没有意义 EXEC sp_configure awe enabled, 1; RECONFIGURE WITH OVERRIDE; GO -- 确认实例位数与版本 SELECT SERVERPROPERTY(Edition) AS [版本], SERVERPROPERTY(InstanceName) AS [实例名];这里特别说明 RECONFIGURE WITH OVERRIDE 的用法。awe enabled 属于高风险选项普通 RECONFIGURE 可能被拒绝需要带上 WITH OVERRIDE 强制生效。但这个选项只在 32 位实例上有意义64 位实例里开启 AWE 不会带来任何内存扩展效果反而可能引起不必要的误解。判断实例是 32 位还是 64 位看 Edition 输出里有没有 x86 字样或者直接看任务管理器里 sqlservr.exe 路径x86 目录就是 32 位。对 32 位实例最稳妥的优化建议不是开 AWE而是尽早计划迁移到 64 位实例。AWE 分配的内存在数据页缓冲上有效但查询编译缓存、连接内存等仍然受限内存配置的天花板就在那里。第 5 章的避坑内容里我会展开讲一个典型现象32 位实例把 max server memory 调大后实际用不满。5. 避坑SQL Server 2008 R2 资源分配最常见的 5 个翻车现场5.1 现象maxdop 调小以后报表查询反而更慢一位同事把 maxdop 从默认 0 调到 4OLTP 业务是稳了但第二天业务方反馈月底报表跑得比原来慢了一半。查看实际执行计划发现一条几千万行的事实表聚合原本走 8 路并行扫描现在被限制成 4 路并行分区扫描的收益直接被砍掉一半。原因maxdop 是全局配置它对所有查询一视同仁OLTP 需要限制并行度避免小查询被挤兑但大型报表恰恰需要并行度来缩短响应时间。全局配置不可能同时满足这两种诉求。解决分清负载主体。全局 maxdop 按 OLTP 需求设 4对少数需要大并行的报表语句单独加查询提示。SQL Server 2008 R2 支持OPTION (MAXDOP 8)查询提示让这一条语句突破全局限制。注意 2008 R2 的查询提示写法要放在语句末尾SELECT COUNT(*) AS [记录数] FROM dbo.FactSale WITH (NOLOCK) WHERE SaleDate 2024-01-01 OPTION (MAXDOP 8);5.2 现象内存配置没动Windows 突然卡死事件日志报 17890服务器 64GB 内存SQL Server 一直是默认配置运行半年后某天上午业务高峰期 Windows 突然卡死远程桌面都敲不动命令。重启后查看系统事件日志发现多条 17890 事件提示 SQL Server 正在对 Buffer Pool 执行内存回收。再配合性能监视器一看 Available MBytes 几乎归零页面文件使用率冲高。原因max server memory 保持默认 2147483647SQL Server 把 64GB 物理内存几乎全部吸入 Buffer PoolWindows 和其他进程需要内存时只能靠换页。尤其是 Antimalware Service Executable 这类安全进程在扫描文件时会突然申请大块内存直接触发系统级内存压力。解决立刻把 max server memory 调到合理值比如 64GB 物理内存设 56320MB然后重启 SQL Server 服务让 Buffer Pool 收缩。这里注意即使 SQL Server 支持动态调整释放已占用的内存也需要重启服务或等待内存压力逐步回收生产环境不能等直接计划维护窗口重启。5.3 现象32 位实例max server memory 设了 16GB进程却只用到 2GB一台 32 位 SQL Server 2008 R2 的物理内存有 16GBDBA 把 max server memory 设成 16384MB想着给数据库多分点内存结果 perfmon 里 sqlservr.exe 的工作集始终在 2GB 附近Buffer Cache Hit Ratio 也上不去。原因32 位进程默认地址空间只有 2GB/3GB 开关后可到 3GB 左右普通内存模式根本够不到 16GB。AWE 没有开启服务账户也没有 Lock Pages in Memory 权限设再大的 max server memory 也只是个数字。解决先用SELECT SERVERPROPERTY(Edition)确认是不是企业版非企业版的 32 位实例无法使用 AWE 超过 4GB如果是企业版开启 awe enabled 并到本地安全策略里给 SQL Server 服务账户授予「锁定内存页」权限之后重启服务。长期方案只有一个迁移到 64 位实例。32 位上的 AWE 只是补丁不是出路。5.4 现象配了 affinity mask 后 CPU 出现一条平线业务反而变慢某服务器为了隔离 SQL Server 和其他应用用 sp_configure 把 affinity mask 设为 15也就是只允许 CPU 0 到 3 跑 SQL Server。改完后任务管理器里出现明显两极分化CPU 0 到 3 打满CPU 4 到 15 全是绿油油的空闲业务响应时间却变长了。原因SQL Server 绑在 0 到 3 号核上但 Windows 的时钟中断、网卡中断、防病毒扫描也大部分落在这些低编号核上。SQLOS 明明看到 4 个核很忙却碰不到 CPU 4 到 15 的空闲资源大量线程在调度器队列里排队。CPU 看起来有富余实际上 SQL Server 能用的只有那 4 个核。解决单实例别用 affinity这是最直接的结论。多实例隔离属于例外场景但 2008 R2 上应该用ALTER SERVER CONFIGURATION来绑定而不是手工算二进制掩码。如果已经踩坑执行ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU AUTO退回自动调度。改完后重启一下 SQL Server 服务让调度器重新分布。5.5 现象sp_configure 改完不生效重启后配置回滚有 DBA 执行了EXEC sp_configure max server memory (MB), 40960没有报错但第二天查看配置发现 value_in_use 还是 2147483647。还有一些情况是改完 min server memory 之后 SQL Server 服务直接起不来。原因分两种。第一是改完没执行 RECONFIGURE所有 sp_configure 的改动在 RECONFIGURE 之前只停留在 value 列没有进入运行态报错都没有。第二是 min server memory 设得过大、超出物理内存导致 SQL Server 启动时尝试提交内存失败。这种情况常见于物理内存只有 8GB 却把 min 设为 16384MB。解决养成「改完必 RECONFIGURE、查必看 value_in_use」的习惯。如果服务已经起不来用命令行方式以最小配置模式启动 SQL Server 实例把 min server memory 降回安全值# 以前台最小配置方式启动 SQL Server 实例适合单机救援 sqlservr -f -s MSSQLSERVER等实例起来后立刻执行 sp_configure 把 min server memory 改回 0 或合理值再恢复正常服务启动方式。这个操作属于灾后救援尽量选在维护窗口做因为单用户模式下业务不可用。配置类的后悔药就在这一步改坏了不要慌最小配置模式是第一选择。6. 验证优化效果用 DMV 写一份 2008 R2 的资源分配验收脚本6.1 三个最该看的资源健康信号调完参数不能只看任务管理器要回到 SQL Server 自己的性能计数器。最核心的三个信号Buffer Cache Hit Ratio 反映缓存命中率通常建议长期在 95% 以上Page Life Expectancy 反映数据页在缓冲池里的平均存活秒数低于 300 说明内存压力大或者缓存被频繁冲刷Processor Queue Length 如果持续高于每个核心 2说明 CPU 已经排队。-- 缓存命中率0~100 SELECT cntr_value AS [BufferHitPercent] FROM sys.dm_os_performance_counters WHERE object_name LIKE %Buffer Manager% AND counter_name Buffer cache hit ratio; -- 数据页平均存活秒数 PLE低于 300 需要警惕 SELECT cntr_value AS [PLE_秒] FROM sys.dm_os_performance_counters WHERE object_name LIKE %Buffer Manager% AND counter_name Page life expectancy;6.2 我的验收节奏基线先行改完 30 分钟后看趋势我的习惯是改配置前先跑一轮基线存成一张表调完 30 分钟后再抓一轮。不要盯着瞬时值趋势才是真实结果。内存的变更通常要跑一天才能看出稳定水位maxdop 的变更则在业务高峰最能体现。这些年调过的 2008 R2 实例多了我给自己定了一条规矩任何一次资源分配变更都必须当场记录下行和恢复方案顺便把 baseline 数据放到一张维护表里备忘。不然三个月后同事问起「这台机器的内存上限是谁定的」现场又是一阵玄学排查。这篇方案只针对 2008 R2 的逻辑放在现在的新版本依然通用换汤不换药。希望帮到你。本文还有配套的精品资源点击获取