简介：这份文档面向SQL Server数据库管理员与解决方案供应商，聚焦SQL Server 2008 R2中CPU与内存资源的分配优化问题。相比2005版依赖独立实例与处理器亲和度的做法，2008 R2引入资源控制器，通过资源池与工作负载组实现更灵活的管控。文档系统讲解了资源池最小值和最大值的含义与配置原则，说明如何按请求特性将负载分发到不同工作组，并指出设置最大值时可能出现的短暂CPU高峰属正常现象，同时提醒分类转发请求需编写大量脚本、可参考微软MSDN文章完成配置。资源包为1个docx文件，约84KB，内容紧凑、主题集中，适合需要理解资源控制器机制、规划多数据库资源配额的读者查阅。目前已有1350人学习，可作为SQL Server 2008 R2资源分配方案设计与排错时的参考材料。
1. SQL Server 2008 R2 的 CPU 与内存分配：为什么默认配置总让服务器“吃不饱”
一台 32GB 内存、16 核的物理机装完 SQL Server 2008 R2，默认状态下往往只用到 4GB 左右内存，CPU 也常年趴在 15% 以下，可业务查询还是慢。这不是硬件不行，而是 SQL Server 2008 R2 的默认资源策略偏保守：内存上限不设，操作系统和数据库抢页；CPU 亲和与最大工作线程数全按老年代默认值走，高并发下线程调度反而成了瓶颈。这个标题要解决的就是把 CPU 和内存这两块资源从“自动挡”切到“手动挡”，让数据库实例在可控范围内吃满该吃的资源。适合还在维护 SQL Server 2008 R2 的运维和 DBA，尤其是那些机器配置不低、但数据库响应始终上不去的场景。下面按“先定内存、再调 CPU、最后避坑”的顺序拆开讲。
2. 内存分配：先给操作系统留够，再锁死上限
2.1 最大服务器内存到底该设多少
SQL Server 2008 R2 默认不限制最大服务器内存，只要查询压力上来，它会把几乎所有物理内存都吃进缓冲池。问题在于 Windows 本身、备份进程、杀毒软件也需要内存，一旦物理内存被 SQL Server 占满，操作系统就开始把页面往磁盘上换，整个机器的响应会突然变慢。所以第一步是设一个明确的上限。
常见做法是：如果这台机器只跑 SQL Server，给操作系统留 4GB 到 8GB，其余全给数据库。比如 32GB 物理内存，最大服务器内存设 24576MB（24GB）；64GB 物理内存，设 57344MB（56GB）。如果机器上还跑着应用服务或备份代理，操作系统预留要加到 8GB 到 12GB。
设置方式有两种，图形界面和 T-SQL 命令。图形界面在 SSMS 里右键实例 → 属性 → 内存 → 最大服务器内存，填数字即可。命令行更适合批量或脚本化操作：
-- 将最大服务器内存设为 24576MB（24GB） EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'max server memory (MB)', 24576; RECONFIGURE;逻辑说明：show advanced options打开后才能看到max server memory这个高级选项。RECONFIGURE让修改立即生效，不需要重启实例。参数说明：max server memory (MB)的单位是 MB，设成 24576 就是 24GB。注意不要设得太低，低于 4GB 会导致缓冲池频繁抖动，查询计划缓存也被压缩，反而更慢。
2.2 最小服务器内存要不要设
最小服务器内存控制的是 SQL Server 启动后至少保留多少内存。默认值是 0，意味着启动时只占很少，随着查询逐步增长。对于专用数据库服务器，建议把最小服务器内存设成最大服务器内存的 50% 到 75%，比如最大 24GB，最小设 12GB 到 18GB。这样实例启动后就能快速拿到足够内存，避免刚重启那段时间频繁读磁盘。
-- 最小服务器内存设为 12288MB（12GB） EXEC sp_configure 'min server memory (MB)', 12288; RECONFIGURE;参数说明：min server memory (MB)同样以 MB 为单位。设得太高会挤压操作系统，设得太低则起不到预热效果。我一般会按最大值的 50% 来设，留出弹性空间。
2.3 开启 AWE 还是锁定内存页
SQL Server 2008 R2 是 64 位版本的话，AWE 已经不需要了，因为 64 位地址空间足够大。但“锁定内存页”这个权限值得开。开启后，SQL Server 的缓冲池不会被操作系统换出到页面文件，减少磁盘 I/O 抖动。操作步骤：在 Windows 的本地安全策略里，找到“锁定内存页”权限，把 SQL Server 服务账户加进去。然后重启 SQL Server 服务。
注意：开启锁定内存页后，任务管理器里 SQL Server 的内存占用会显得很“死”，不会随负载大幅波动，这是正常现象。如果发现内存占用一直不降，先检查是不是最大服务器内存没设，而不是怀疑锁定内存页出了问题。
2.4 缓冲池扩展与 max worker threads 的关系
SQL Server 2008 R2 没有缓冲池扩展功能，那是 2014 之后才有的。所以内存优化只能靠 max server memory 和 min server memory 这两个参数。但内存和 CPU 是联动的：如果 max worker threads 设得太高，每个线程都要占用一定内存（约 2MB 到 4MB 栈空间），线程数过多会吃掉大量内存，反而压缩缓冲池。所以内存调完后，下一步必须调 CPU 相关参数。
3. CPU 分配：最大工作线程数与并行度的配合
3.1 最大工作线程数默认值为什么不够用
SQL Server 2008 R2 在 64 位系统上，最大工作线程数默认是 0，表示由系统自动配置。对于 16 核 CPU，自动配置大约是 704 个线程。听起来很多，但在高并发短查询场景下，线程池会频繁创建和销毁，上下文切换开销明显。更麻烦的是，如果某个查询发生阻塞，线程被占住不放，后续请求排队，CPU 利用率反而上不去。
我一般会把最大工作线程数显式设成 CPU 核数的 32 倍左右。比如 16 核，设 512。这样既够用，又不会因为线程过多导致内存被栈空间吃掉。
-- 最大工作线程数设为 512 EXEC sp_configure 'max worker threads', 512; RECONFIGURE;参数说明：max worker threads的有效范围是 128 到 32767。设得太低会导致请求排队，设得太高会浪费内存。对于 8 核以下的机器，建议不超过 256；16 核以上可以到 512 或 768。改完后用SELECT * FROM sys.dm_os_sys_info查看实际线程数。
3.2 并行度阈值与开销阈值怎么调
SQL Server 2008 R2 默认的并行度阈值是 5，意思是只要查询开销超过 5，就可能走并行计划。在 OLTP 系统里，这会导致大量小查询被并行化，CPU 瞬间飙高，但单个查询并没快多少。常见做法是把并行度阈值提高到 30 到 50，让只有真正的大查询才走并行。
-- 并行度阈值设为 40 EXEC sp_configure 'cost threshold for parallelism', 40; RECONFIGURE;参数说明：cost threshold for parallelism的单位是查询开销估算值，不是秒数。设成 40 意味着估算开销超过 40 的查询才考虑并行。对于 OLTP 为主、偶尔有报表查询的库，40 到 50 比较平衡。如果全是报表查询，可以降到 20 左右。
3.3 MAXDOP 到底设几
MAXDOP 控制单个查询最多用几个 CPU 核。默认是 0，表示不限制，有多少核用多少核。在 16 核机器上，一个并行查询可能占满所有核，其他查询只能等。我一般会把 MAXDOP 设成 8 或 4，具体看业务：OLTP 系统设 4，混合系统设 8，纯报表系统可以设 0 或 16。
-- MAXDOP 设为 8 EXEC sp_configure 'max degree of parallelism', 8; RECONFIGURE;参数说明：max degree of parallelism设成 1 表示完全禁用并行，设成 0 表示不限制。对于 NUMA 架构的机器，MAXDOP 不要超过单个 NUMA 节点的核数，否则跨节点访问内存会拖慢查询。改完后用SELECT * FROM sys.dm_exec_query_stats观察并行查询的实际执行情况。
3.4 CPU 亲和掩码要不要动
CPU 亲和掩码可以把 SQL Server 绑定到特定 CPU 核上，减少操作系统和其他进程的干扰。但在虚拟化环境或 NUMA 机器上，乱设亲和掩码会导致性能下降。我一般只在物理机、且操作系统和其他服务混跑的情况下才考虑设亲和掩码。设置命令：
-- 将 SQL Server 绑定到 CPU 0-7（掩码 0xFF） EXEC sp_configure 'affinity mask', 255; RECONFIGURE;参数说明：affinity mask是位掩码，255 对应二进制 11111111，表示使用 CPU 0 到 7。设之前先用SELECT * FROM sys.dm_os_schedulers查看当前调度器分布。注意：设错掩码可能导致 SQL Server 启动失败，改之前先记下原值。
4. 避坑与排查：那些让优化白做的操作
4.1 现象：内存设了上限，但任务管理器里 SQL Server 还是占满内存
原因：最大服务器内存只限制缓冲池，不限制 SQL Server 的其他组件，比如 CLR、扩展存储过程、链接服务器提供程序。这些组件可能额外占用几百 MB 到几 GB。另外，如果开了锁定内存页，任务管理器显示的是工作集，可能包含共享内存。
解决：用SELECT * FROM sys.dm_os_process_memory查看实际物理内存占用，用SELECT * FROM sys.dm_os_buffer_descriptors看缓冲池用了多少。如果缓冲池没超，但总内存超了，检查是否有第三方组件在吃内存。
4.2 现象：改了 MAXDOP 后，某些查询反而更慢
原因：MAXDOP 设得太低，原本能并行的大查询被迫串行执行，执行时间变长。或者 MAXDOP 设成 1 后，所有查询都单线程，CPU 利用率上不去。
解决：不要全局设 MAXDOP 1。可以用查询提示OPTION (MAXDOP 4)对特定查询单独控制。改完全局 MAXDOP 后，用 SQL Server Profiler 或扩展事件抓取执行时间超过 5 秒的查询，对比改前改后的 CPU 时间和执行时间。
4.3 现象：并行度阈值调高后，报表查询变慢
原因：报表查询的估算开销可能刚好在阈值附近，调高后不再走并行，单线程跑大表扫描自然慢。
解决：对报表查询用OPTION (RECOMPILE)或OPTION (QUERYTRACEON 8649)强制并行。或者把并行度阈值设成 30 而不是 50，给报表查询留出并行空间。
4.4 现象：最大工作线程数调高后，内存反而更紧张
原因：每个线程默认占用 2MB 栈空间，512 个线程就是 1GB。如果 max server memory 设得比较紧，这 1GB 会从缓冲池里扣。
解决：先算账。16 核机器，512 线程约 1GB 内存开销，max server memory 要相应留出这部分。如果内存实在紧张，把最大工作线程数降到 256，或者把 max server memory 再调低 1GB 给线程用。
4.5 现象：优化后性能提升不明显
原因：CPU 和内存调优只是基础，如果查询本身缺索引、统计信息过期、或者存在阻塞，资源再多也白搭。
解决：先看等待类型。用SELECT * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC查前 10 个等待。如果是CXPACKET为主，说明并行度有问题；如果是PAGEIOLATCH_SH为主，说明内存还是不够；如果是LCK_M_XX为主，说明是阻塞问题，跟 CPU 内存无关。
5. 用性能计数器验证调优效果：别只看任务管理器
调完参数不算完，得用数据验证。Windows 性能计数器里，跟 SQL Server 2008 R2 内存和 CPU 最相关的几个指标是：SQLServer:Buffer Manager\Page life expectancy（页生命周期，低于 300 秒说明内存不足）、SQLServer:Buffer Manager\Buffer cache hit ratio（缓冲命中率，低于 95% 要警惕）、SQLServer:SQL Statistics\Batch Requests/sec（每秒批请求数，看吞吐）、Processor(_Total)\% Processor Time（CPU 总利用率，持续高于 80% 要查原因）。
我一般会建一个数据收集器集，每 15 秒采一次，跑一整天。然后对比调优前后的曲线。如果页生命周期从 200 秒升到 800 秒，缓冲命中率从 92% 升到 99%，说明内存分配到位了。如果 CPU 利用率从 40% 升到 70%，但批请求数也翻倍，说明 CPU 调优有效。反过来，如果 CPU 利用率升到 90% 但批请求数没变，说明并行度或 MAXDOP 设错了，得回退。
还有一个容易忽略的点：SQL Server 2008 R2 的sys.dm_os_ring_buffers里有资源监控记录，可以查最近的内存和 CPU 压力事件。
-- 查看最近的内存压力记录 SELECT TOP 10 record_id, timestamp, CONVERT(XML, record) AS record_xml FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR' ORDER BY timestamp DESC;逻辑说明：RING_BUFFER_RESOURCE_MONITOR记录内存和 CPU 的压力状态，record_xml里能看到MemoryNode、MemoryAvailable等字段。参数说明：TOP 10取最近 10 条，时间戳是毫秒级。如果看到MemoryAvailable长期低于 200MB，说明操作系统内存吃紧，得把 max server memory 再调低。
最后说个我自己的习惯：每次改完 CPU 或内存参数，至少观察 48 小时再下结论。SQL Server 2008 R2 的缓冲池需要时间预热，统计信息也需要重新编译。急着看效果，往往会被短期波动误导。希望帮到你。
本文还有配套的精品资源，点击获取
