SQL Server 2008 R2 CPU与内存调优：从默认配置到手动优化

简介这份文档面向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 物理内存最大服务器内存设 24576MB24GB64GB 物理内存设 57344MB56GB。如果机器上还跑着应用服务或备份代理操作系统预留要加到 8GB 到 12GB。设置方式有两种图形界面和 T-SQL 命令。图形界面在 SSMS 里右键实例 → 属性 → 内存 → 最大服务器内存填数字即可。命令行更适合批量或脚本化操作-- 将最大服务器内存设为 24576MB24GB 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。这样实例启动后就能快速拿到足够内存避免刚重启那段时间频繁读磁盘。-- 最小服务器内存设为 12288MB12GB 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 核以下的机器建议不超过 25616 核以上可以到 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 TimeCPU 总利用率持续高于 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 的缓冲池需要时间预热统计信息也需要重新编译。急着看效果往往会被短期波动误导。希望帮到你。本文还有配套的精品资源点击获取