SQL Server计划缓存被即席查询挤爆？从原理到优化实践

做DBA最怕半夜看监控邮件。前阵子某业务库的内存告警邮件又躺在我收件箱里登录上去用惯例的几条视图一查计划缓存占了将近5GB其中七成以上是use_count为1的即席计划。这不是个案是“特定查询”长期积累把缓冲池堆爆的典型症状。很多人一遇到这种情况就DBCC FREEPROCCACHE但那是拆东墙补西墙问题不出几天还会回来。这篇就围绕“计划缓冲及优化”这个系列主题把特定查询尤其是即席查询在计划缓存里的行为、定位手段和优化策略完整讲一遍适合数据库运维同学、应用开发者也适合任何被“缓存漂移”和“内存虚高”困扰过的人。1. 特定查询是如何悄悄吃光整个计划缓存的1.1 先重新认识计划缓存的工作原理数据库执行一条SQL时第一件事不是编译而是去计划缓存里找现成的执行计划。哈希匹配、命中就直接复用不命中才做语法解析、权限校验、统计信息探查、成本估算最后把生成好的执行计划放回缓存。这套机制设计初衷很朴素让高频率的常用查询省掉反复编译的成本。问题就出在“特定查询”身上。什么叫特定查询就是每次执行时因为参数值不同、拼接串不同生成了一条“独一无二”文本的SQL。比如应用里按订单号查详情有的人写WHERE order_id 9527下次写WHERE order_id 9528数据库按整条文本做哈希匹配这两条完全被当作不同的查询来处理。同一个业务操作因为参数从1到10000变化就能在计划缓存里留下上万个几乎一模一样的计划副本。生活化一点理解就是图书馆本来按书名精确存放图书结果管理员连书里多了一个空格都当成新书入库。同一本书被存了几千个“版本”书架当然不够用。计划缓存的空间是从整个缓冲池里分出来的它越大留给数据页、索引页的余地就越小最终表现为缓存命中率下降、磁盘IO上升乃至内存压力告警。1.2 特定查询带来的不只是空间浪费空间占用只是表象。每次一条新文本没有命中缓存数据库必须走一次完整编译流程。10张表以上的大查询编译代价完全可能在几十毫秒量级单看一次没什么频繁出现就是纯粹烧CPU。还有一个容易被忽略的连锁反应内存吃紧时数据库会按策略逐出旧计划。越老的、使用次数越少的缓存计划最先被淘汰。这就造成刚刚积累起来的那些库表访问计划也被“殃及”真正的高频查询反而反复重新编译形成“编译-淘汰-再编译”的恶性循环。从运维角度这套循环是很有迷惑性的。它表现为CPU偶发尖峰、查询计划不稳定、部分语句时快时慢如果只看表面现象很容易误判成索引失效或者统计信息过期折腾一圈回来缓存问题还在原地。1.3 什么场景最容易出现这类问题根据我接触过的环境典型高危场景集中在三类报表平台和BI前端这种系统天然会由图形界面拼出大量不同组合条件的SQL。后台管理系统筛选项很多开发者图方便直接用字符串拼接参数。定时任务或批量脚本里循环体内从表查询动态结果后拼接下一句SQL。这些系统往往业务逻辑本身并不复杂单个查询也不慢但服务器就是莫名有压力。定性判断和定量验证才是调整的开始。后面两节会给出直接可用的定位SQL和判断口径。2. 三类“特定查询”的缓存行为差异别指望一招通吃2.1 从缓存键到复用条件先把对象分清很多人把“特定查询”当成一个大筐什么SQL都往里扔。实际上从计划缓存视角至少要区分三类对象即席查询Adhoc SQL、参数化语句Prepared/sp_executesql、存储过程。它们的缓存键构成完全不同优化手段也各异。类型缓存键基础复用条件典型来源缓存风险即席查询完整文本哈希文本一字不差才复用拼串SQL、BI前端高参数变化即产生新计划参数化语句参数化模板哈希模板一致且参数类型一致sp_executesql、PreparedStatement中模板数量可控即可存储过程对象ID过程对象不变即复用应用调存储过程低内部重编译另算这张表值得收藏起来。绝大多数计划缓存膨胀案例根因都在第一行“即席查询”。而优化动作要么是把即席查询变成参数化语句要么是给它套一个稳定的对象壳让缓存键不再随参数变化。2.2 自动参数化其实救不了复杂语句SQL Server等数据库引擎有“简单参数化”能力会把一些准入语句自动参数化。比如WHERE col 123这种极简单语句系统可能自动转成WHERE col param从而让不同字面值复用同一个计划。但自动参数化有两个天然限制一是只对非常简单、无歧义的语句生效二是即使生效数据库也不保证一定参数化它有自己的成本判断。一旦SQL里出现OR、IN列表、多个查询条件、子查询自动参数化几乎必然放弃。大部分人踩的坑就在这以为“数据库会自动优化”结果线上语句全是裸奔的字面值。2.3 同一存储过程也不是一定安全存储过程按对象ID缓存看起来最安全但它内部如果拼接了动态SQL并执行缓存键实际又回到了“即席查询”逻辑。还有参数嗅探问题存储过程首次编译时按首个参数值生成计划后续不同参数可能不适配数据库会重编译。所以正确的姿势是把“对象类型”和“执行方式”拆开看——表头是存储过程表尾也可能是即席查询。缓存里查询objtype和cacheobjtype两列远比看SQL文本来自哪个模块更可靠。3. 定位元凶的四段SQL不用猜数据会说话3.1 先看全局分布谁占用了多少内存我给团队定过一个规矩任何优化动作之前先跑下面这段SQL看计划缓存里的对象类型分布。这条用sys.dm_exec_cached_plans按类型聚合能给出整体画像。SELECT cacheobjtype, objtype, COUNT(*) AS plan_count, SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS memory_mb, SUM(usecounts) AS total_usecounts FROM sys.dm_exec_cached_plans GROUP BY cacheobjtype, objtype ORDER BY memory_mb DESC;输出里最值得警惕的是cacheobjtype Compiled Plan AND objtype Adhoc这一行。如果它的plan_count很高而total_usecounts / plan_count接近1说明大量缓存计划只用过一次这就是典型的“特定查询缓存堆积”可以毫无悬念地定性了。3.2 圈出单次计划它们就是内存浪费的直接证据全局分布确认方向后第二段SQL把单次执行计划单拉出来看具体有多浪费。SELECT COUNT(*) AS one_use_plan_count, SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS one_use_memory_mb FROM sys.dm_exec_cached_plans WHERE usecounts 1 AND cacheobjtype Compiled Plan;这里提醒一个细节usecounts 1不只是“缓存后还没复用”也可能被逐出后再重新编译进来所以usecounts会被重置。更严谨的复现判断还要结合计划创建时间和最后执行时间。但作为快速评估单次计划占比已经完全够用——无论被重置与否内存里躺着大量低复用计划本身就是优化信号。3.3 揪出元凶语句按缓存内存Top N查看具体文本知道浪费规模以后必须找到具体是哪条“特定查询”在不停产生新计划。第三段SQL按单个缓存项的内存占用倒序排列并关联出语句文本。SELECT TOP 20 cp.plan_handle, cp.objtype, cp.usecounts, cp.size_in_bytes / 1024 AS size_kb, qs.execution_count, SUBSTRING(st.text, (qs.statement_start_offset / 2) 1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) 1) AS statement_text FROM sys.dm_exec_cached_plans cp OUTER APPLY sys.dm_exec_sql_text(cp.plan_handle) st LEFT JOIN sys.dm_exec_query_stats qs ON qs.plan_handle cp.plan_handle WHERE cp.cacheobjtype Compiled Plan ORDER BY cp.size_in_bytes DESC;结果里你会看到熟悉的规律同一段文本反复出现区别只在IN列表长度或者右侧的数字不同。这基本可以锁定到具体业务模块——是报表页面还是后台列表一目了然。把这些语句收集下来后面改造和优化就有据可依了。3.4 再看重编译压力评估CPU消耗的辅助证据最后补一段重编译视角的检查。sys.dm_exec_query_stats里plan_generation_num的变化能反映计划重生成次数。数值越高说明这条SQL反复被重新编译对CPU的贡献越大。SELECT qs.execution_count, qs.plan_generation_num, qs.max_elapsed_time, SUBSTRING(st.text, 1, 200) AS sql_text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st WHERE qs.plan_generation_num 10 ORDER BY qs.plan_generation_num DESC;结合实际案例这里见过一个典型病症某查询文本里有多个可选筛选条件应用层对所有分支都拼字面值导致可能的组合有几十种每种组合多次重新编译。这条SQL可以快速把“重编译之王”找出来为方案设计提供决策依据。4. 有效优化该怎么做六种手段的适用边界和副作用4.1 “优化即席工作负载”选项压内存但不减编译很多文章推荐开启OPTIMIZE FOR AD HOC WORKLOADS。这个选项真正的机制是当一条即席SQL第一次编译时数据库只存一个极小的计划存根而不是完整计划只有等到同一文本第二次执行才转成完整的编译计划。相当于给“一次性的书”只留个书签不占书架实格。ALTER DATABASE [YourDatabase] SET OPTIMIZE_FOR_AD_HOC_WORKLOADS ON;注意一个容易误解的点它不节省首次编译CPU因为第一次还是要完整编译它只是把“不再复用的完整计划”在内存里占用的空间大幅降低。对即席查询占比很高的库这个开关效果立竿见影内存占用能肉眼可见地掉下来。但对所有查询都被完整编译过、打开之前效果就不明显的库也别指望它能解决一切——它治的是“存储浪费”不是“编译次数”。4.2 强制参数化能统一模板但可能伤及计划质量如果把“即席查询产生的无数模板”换成“唯一参数化模板”缓存压力会自然缓解。数据库层可以做全局强制参数化ALTER DATABASE [YourDatabase] SET PARAMETERIZATION FORCED;强制参数化会让数据库把符合条件的字面量自动提升为参数。听起来很美实际风险藏在“计划质量”里。很多复杂查询在参数化后可能因为参数嗅探导致只按第一个参数生成通用计划后续参数分布不同时执行计划未必最优。典型反面案例是LIKE % keyword %这类模糊查询参数化后可能放弃索引扫描选择全表扫描性能不升反降。所以我的建议是强制参数化可以作为短期压缓存的手段但绝不能无脑开启。开启前要找几条最重的语句对比计划开启后在监控中观察响应时间是否漂移。更精准的替代方案是配合计划指南只对特定的“高缓存占用语句”做参数化而不是全库一把梭。4.3 应用层改造把拼接SQL换成参数化语句根治问题真正的根治手段永远是让SQL保持参数化。如果业务代码用字符串拼接字面值无论数据库侧怎么做缓存利用率都会很低。改造方向很简单使用预编译语句接口例如T-SQL的sp_executesql。EXEC sp_executesql NSELECT * FROM orders WHERE order_id id AND status status, Nid INT, status TINYINT, id 9527, status 1;这条语句的缓存键不再是9527这个数字而是整个带参数的模板。只要模板不变后续任何参数值的执行都能复用同一计划。应用侧配合预编译接口使用效果一致。不少开发觉得改造工作量巨大其实优先级可以分层。先只针对命中“计划缓存Top N”的语句做改造其他高频但缓存占用小的语句可以慢慢来。这个顺序很重要优化资源应该砸在最痛的地方。4.4 存储过程封装用对象稳定性换取计划稳定把高频即席查询包装成存储过程也是经典做法。存储过程拥有独立对象生命周期计划缓存以对象ID为键天然稳定。对于报表后台一类逻辑固化场景这可能是比sp_executesql更优雅、更易维护的方案。但要注意前一节说过的边界存储过程内部的动态拼接仍是即席查询。如果只是把拼串逻辑从业务层挪到存储过程里内容还是EXEC(sql)那缓存情况不会好转。改造时必须同时保证过程体是静态SQL用参数传入查询条件CREATE PROCEDURE dbo.GetOrders order_id INT NULL, status TINYINT NULL AS BEGIN SELECT * FROM orders WHERE order_id order_id AND status status; END即使order_id和status存在空值组合的问题也可以用ISNULL或OPTION (RECOMPILE)平衡但至少缓存键完全可控。4.5 计划指南动不了代码时的补丁手段有些老系统你根本没有改代码的机会业务方也不允许改存储过程。这时候计划指南可以用来“指示”数据库对特定查询采用参数化或固定计划。EXEC sp_create_plan_guide name NPG_Orders_Query, stmt NSELECT * FROM orders WHERE order_id 9527, type NSQL, module_or_batch NULL, params Norder_id INT, hints NOPTION (PARAMETERIZATION FORCED);计划指南本质上是在语句文本和缓存行为中间插一层规则。它能解决“某个怪物SQL死活不参数化”的场景但维护成本不可忽视每个指南都要显式创建监控模板匹配是否正确。迁移数据库、升级版本时也可能失效。我的态度是把它当补丁而不是长久方案。业务能改代码优先改代码。4.6 清缓存操作方向对了也要把范围控制住关于DBCC FREEPROCCACHE我已经数不清见过多少运维同学在月初、每周甚至每天执行它。这里态度必须明确这是一个“全库计划缓存大清除”级别的操作执行后所有查询都要重新编译瞬时CPU会冲高线上往往会抖一下。它解决的只是当下内存紧张问题而根因——即席查询堆积——一个都没解决。如果确实需要定向清除一条语句的缓存请直接按plan_handle来DBCC FREEPROCCACHE (0x060006001C0B9300B0B79B580000010000000000000000000000000000000000000000000000000000000);这个能力在验证“某个具体计划是不是问题计划”时很好用。比如你怀疑某条语句参数嗅探导致计划走偏可以先刷新它的缓存让它在自然执行时重新编译一次观察是否恢复。这种情况下越精准影响面越小。5. 实测复盘一次报表系统计划缓存整治全过程5.1 现场症状与初步取证某个综合管理平台的报表模块最近内存监控持续告警。接到问题我先跑了第3章里的“全局分布”SQL看到结果是全库计划缓存约4.9GB其中Compiled Plan类型里Adhoc对象占了将近3.6GB单次执行计划数量更是占全部计划数的68%。CPU不算特别高但内存已经快触到调度阈值随时可能触发计划逐出风暴。再看Top语句发现同一张报表页面生成了几百种文本组合核心差异就是筛选条件动态拼串。每条计划平均只执行1到2次却又各自占用内存。系统性堆积无疑。5.2 制定优化策略不是“非黑即白”的选择我没有选择全库强制参数化原因很简单报表查询的条件复杂度高、部分语句带LIKE模糊匹配强制参数化之后计划质量容易失控。最终方案是组合拳开启OPTIMIZE_FOR_AD_HOC_WORKLOADS先压制未来新增即席计划的存储开销。针对缓存占用最大的那批报表语句协调应用团队把拼串逻辑改为sp_executesql参数化执行。审计所有核心报表查询能落存储过程的原则上落存储过程。保留“按plan_handle定向清理”能力过渡期逐条处理已经积压的高占用缓存项。关键一点开启新选项之前不执行全库清理避免一边压囤积一边又新增数据对比的时候说不清楚效果。5.3 执行动作与观察周期OPTIMIZE_FOR_AD_HOC_WORKLOADS设置为ON是即时生效的不要求重启。应用侧改造分批上线第一批只覆盖Top 20语句。我用三天作为观察窗口每天记录计划缓存总大小、单次计划数量、内存页数和平均响应时间。头24小时内存下降并不明显因为存量老计划还没被逐出这是正常预期——新开关只影响“以后新增的计划”老计划仍然占着内存。第二到三天效果开始显现内存逐步回落。再配合把积压的、明显无价值的单次计划用DBCC FREEPROCCACHE(plan_handle)做定点清理后整体内存占用明显改善。5.4 数据对比和结论下面是优化前后同一时段的观察数据示例数据来自某模拟系统仅作趋势参考指标优化前优化后一周计划缓存总占用4.9GB1.2GB即席计划数120002600单次执行计划占比68%21%平均编译CPU占比12%左右5%左右报表页平均响应时间1.8s1.5s有意思的是响应时间提升不完全来自缓存优化一部分来自系统内存压力释放后数据页缓存不再反复被逐出。这印证了前面说的连锁反应——计划缓存挤占数据缓冲区最终影响的是所有查询。所以整治特定查询缓存收益往往不只是“内存数字变好看”而是全库整体稳定性的回升。6. 实战中容易踩的坑和一些经验沉淀6.1 参数嗅探与“计划缓存清理”经常被混淆碰见线上SQL一会儿快一会儿慢很多人第一反应还是清缓存。但如果根因是参数嗅探导致计划漂移清缓存只是让下一个参数重新编译一次很快又回到老样子。正确先做的是对比不同参数下的实际执行计划识别哪一种参数组合生成了最差的计划。如果确实需要压住最差计划也优先使用OPTION (RECOMPILE)或计划指南而不是无差别清缓存。缓存和计划质量是两个维度的问题混在一起处理最容易翻车。6.2 强制参数化的后坐力会出现在索引上有个真实教训某系统开启强制参数化后一条原来能命中索引的语句因为参数被未知化优化器选择了并行全表扫描CPU直接飙升。原因是参数化后的语句无法准确估算选择性基数估计偏了就带动整个计划劣化。这是第4章强调“先测计划再开启”的重要原因。开强制参数化之前一定要拿缓存占用最大的10条语句在测试环境分别以参数化和非参数化跑一遍做成本对比。6.3 计划指南会过期版本升级和统计信息变更都可能让它失效计划指南是用文本匹配来“贴”到目标SQL上的。一旦应用升级改了SQL格式——哪怕只是换行符、空格调整——匹配就可能失败。加上统计信息变化、版本优化器行为变化原指南绑定的固定计划可能变得不是最优。这块维护成本往往被低估。我的经验是能通过应用改造解决的问题不要用指南强扭指南保留给那些明确不能动代码且语句结构长期不变的存量系统。6.4 “先分类、再优化”是把经验复制给团队的捷径这个系列写到这里我最想沉淀的一句话是计划缓存的优化没有银弹但一定有可复用的方法论。对任何新环境第一步永远是看缓存里对象类型分布第二步看单次计划占比第三步看Top SQL文本第四步才能谈优化手段。任何人跳步直接清理缓存或者开强制参数化都是在赌运气。我自己的习惯还会给监控系统加一条“计划缓存健康度”指标单次计划占比超过30%就报警。这比内存达到阈值再处理前移了一步既避免业务已经卡顿才介入也让团队能从小步快跑的优化中积累经验。毕竟缓存这东西不是清理一次就完事的它每天都在生长只有持续观察和及时调整才能真正稳住。最后再补一句如果你也正面对一个计划缓存快速膨胀的库先别急着执行任何清缓存命令。把前面三类查询的分布图拿出来给每条优化的优先级按“内存占用、复用次数、业务影响”三个维度排一下序大概率你会收获比我这次更干净的复盘曲线。