这几篇写下来索引Hint、连接Hint、RECOMPILE这类常见家伙都聊了个遍。今天这第八个常用Hint也是大家平时讨论最多、翻车最频繁的一个话题参数化查询下的计划缓存与参数嗅探。主角包括OPTIMIZE FOR、OPTIMIZE FOR UNKNOWN以及经常和它们搭配使用的语句级RECOMPILE。很多做SQL Server性能和故障排查的同学应该都有这种经历某条存储过程在测试环境跑得飞快一上生产就慢成蜗牛早上执行还正常下午同样的参数突然就出了个烂计划。这种问题绝大多数都和参数嗅探有关而这三兄弟恰恰是控制这类行为的关键工具。这篇我把它们的使用逻辑、适用边界、排查方法一次性讲透。1. 先把问题说清楚参数化查询为什么会“魂穿”参数嗅探1.1 优化器为什么要缓存执行计划SQL Server执行一条查询时会先把语句文本通过哈希算法生成一个key然后去计划缓存里找有没有可复用的执行计划。找到了就直接用省掉编译环节找不到就从头开始做基数估计、生成执行计划再缓存起来备后续使用。这个机制本身是好的因为编译是非常消耗CPU和内存的行为一个大型系统每秒可能收到成千上万条查询如果每条都重新编译CPU早就被打满了。参数化的意义也在这里。存储过程天然是参数化查询应用里常见的SqlCommand传参数也属于参数化。这类查询经过一次编译后后续不管参数值怎么变只要语句文本一致、SET选项一致、数据库上下文一致都能命中同一个计划。问题恰恰出在这个“复用”上第一次编译时使用的参数值决定了这棵计划树长什么样而后续所有参数值都只能硬着头皮去用这个旧计划。举个最常见的例子订单表里状态字段的分布是严重歪斜的历史“已完成”订单占99%而“待支付”和“待发货”加起来不到1%。如果一条存储过程第一次被一个查询“已完成”订单的请求触发编译优化器根据当时的参数值估算行数很可能选择全表扫描或某个差劲的索引策略因为当时返回的行数确实很多。之后真正要处理“待发货”这批极少量数据时理想计划应该是走窄索引查找加书签查找可由于旧计划已经被缓存优化器根本不会重新评估每次都拿着一张烂计划去查高选择性数据响应时间自然崩盘。但这里要替优化器说一句公道话参数嗅探不是bug反而是优化器按设计正常工作的表现。它本来就该利用“当前参数值”来产生更精准的基数估计。问题出在“一个计划服务所有参数”这件事上本质是“数据分布倾斜 计划长时间复用”两个条件同时满足后才出现的现象。1.2 参数嗅探在哪里“兴风作浪”参数嗅探的本质是优化器在编译时读取传入参数的值用它来评估谓词选择性。如果这个值对应的统计信息直方图步数表现正常优化器就能产出一个合理计划如果这个值在一个密度极高或极低的区间估算出来的行数会和真实执行行数出现巨大偏差。我处理过很多所谓“慢查询”案例最后定位下来都是参数嗅探。典型特征非常明显同一条SQL在SSMS里用字面量执行很快一带上参数就慢或者同一存储过程某个参数值超快换一个参数值就超慢。这种“看参数脸色”的现象基本就是参数嗅探的实锤。需要特别注意参数嗅探并不只出现在数据倾斜场景。即使在分布均匀的列上如果统计信息过期、索引缺失或者查询涉及JOIN顺序复杂第一次选择的计划也可能不是好计划。尤其是有多个谓词、多个表连接时参数值的细微变化在优化器眼中可能被放大成完全不同的连接策略。1.3 如何确认你的系统正在被参数嗅探困扰动手加Hint之前先确认问题确实是参数嗅探。我见过不少开发同学上来就为所有存储过程补一个WITH RECOMPILE最后CPU暴涨、线上故障扩大。定位参数嗅探其实不复杂核心是看计划缓存里同一查询是否出现了多份不同的执行计划。SELECT qs.query_hash, qs.query_plan_hash, qs.execution_count, qs.creation_time, qs.last_execution_time, st.text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st WHERE st.text LIKE %你的存储过程名% ORDER BY qs.query_hash, qs.creation_time;如果返回结果里同一query_hash下出现了query_plan_hash不同、creation_time不同的多行说明系统里已经在为不同参数值分别生成并缓存计划了。注意如果只有一行且execution_count很高那么所有参数都复用了同一个计划这时候去看执行计划里估计行数和实际行数的偏差就能判断这个被复用的计划是不是烂计划。还有一种更直接的验证方式用DBCC FREEPROCCACHE清掉那条语句的缓存然后分别用两个不同参数值执行对比性能表现。生产环境千万小心不要全量清缓存应该先拿到plan_handle只清指定计划DBCC FREEPROCCACHE (plan_handle);这一步做完如果发现冷启动后两个参数值执行速度差异巨大那就确认了参数嗅探正在影响你的业务。2. 常用Hint的核心OPTIMIZE FOR 的使用边界2.1 语法与具体位置OPTIMIZE FOR是查询级别的Hint写作OPTION (OPTIMIZE FOR (...))必须放在单个查询的末尾。它不会改变查询实际传入的参数值只影响优化器在编译时做基数估计所采用的参数值。也就是说查询该返回什么数据还是返回什么数据但优化器“眼里”看到的参数值被我们人为指定了。语法格式SELECT * FROM dbo.Orders WHERE OrderStatus OrderStatus OPTION (OPTIMIZE FOR (OrderStatus Pending));多个参数时可以用逗号分隔OPTION (OPTIMIZE FOR (OrderStatus Pending, CustomerID 10086));如果你希望优化器只对某一参数使用固定值其他参数照常使用运行时的实际值那就只写需要固定的那个参数即可剩余参数不受影响。这个灵活性让OPTIMIZE FOR成为很多存储过程“定向纠偏”的首选工具。有一点我要强调OPTIMIZE FOR是语句级Hint不能在EXEC 存储过程时直接附加。如果你需要让整个存储过程里的多条语句都采用某个代表值来做基数估计你必须在每条语句后面分别加OPTION子句或者使用计划指南Plan Guide来批量应用。后者使用门槛高、维护成本大大多数场景我更倾向于直接在关键语句上逐条加。2.2 用“代表性值”纠正计划OPTIMIZE FOR实际要回答的问题是既然一个计划要服务所有参数值那么我该让优化器为“哪个值”来编译这个计划答案不是“随便选一个”而是选择一个业务上最具代表性、最值得优化的参数值。什么叫代表性值以刚才的订单状态为例。如果业务中90%的查询都是在查“待发货”这批最紧急的订单这些查询对性能要求也最高那么OPTIMIZE FOR (OrderStatus Pending)就是合理的选择。优化器会按“待发货”的选择性去生成计划走索引查找、返回少量行整体RT表现会非常理想。至于那些查“已完成”海量数据的查询虽然可能被这个计划坑一点但如果它们本来就是低频批量任务或者对延迟不敏感那这笔交易是划算的。选择代表性值的时候有几个经验可以参考先看业务权重选“数量最多、频次最高、最不能慢”的参数组合。参考统计信息里的直方图确认该值对应的谓词选择性处于哪个区间。如果代表性值的选择性跟其它高频值完全不在一个数量级你相当于把优化器从一个坑换到另一个坑。如果应用存在多个同样重要的业务分支你需要评估是继续用OPTIMIZE FOR还是切换到RECOMPILE方案。2.3 存在的约束与风险OPTIMIZE FOR不是无害的补丁用了它等于告诉优化器“你不用管实际参数就按我给你的值估行数”。万一业务形态变了当初那个代表性值已经不再是高频值你等于把系统锁死在旧计划上。比如电商大促期间平时“按单个商品ID”查详情的代表性查询是合理的但大促期间大量请求按“品牌ID”聚合查询代表性组合改变了。如果你之前已经用OPTIMIZE FOR固定了单商品ID的计划大促期间就可能出现灾难性慢查询。这也是为什么我一直强调凡是加了OPTIMIZE FOR的存储过程都要在监控里打标签定期复盘这些“硬编码值”是否仍然符合当前业务分布。另外还要注意NULL值问题。如果你固定了一个非NULL值而某个请求实际传入了NULL优化器按非NULL值做基数估计但实际谓词Column NULL永远不会匹配任何行除非用IS NULL语义都变了计划更不可能对。所以做参数嗅探处理时要先把NULL分支拆开不要让NULL参杂进同一个查询里。3. 没有魔法的“平均水平”OPTIMIZE FOR UNKNOWN3.1 UNKNOWN到底在告诉优化器什么OPTIMIZE FOR UNKNOWN是OPTIMIZE FOR的一个变体写法为OPTION (OPTIMIZE FOR UNKNOWN)。它的含义不是告诉优化器“有个神秘的未知值”而是告诉优化器不要使用当前传入参数的具体值做基数估计改用统计信息中的密度估计来推算预期行数。密度是统计信息中反映“平均选择性”的一个指标。假设列上有100万个不同的值平均密度大约就是百万分之一。优化器拿到这个密度后估算出来的行数会趋向于整个数据集的平均水平生成的计划也更偏向“中庸策略”。这个行为实际上等价于关闭了本次编译的参数嗅探让计划不偏向任何特定值。有人会问这和DBCC FREEPROCCACHE再冷编译有什么区别区别很大。前者是不使用参数值、用平均密度估算后者仍然会使用参数值、只是多编译一次。OPTIMIZE FOR UNKNOWN是主动选择“平均主义”而清缓存是让优化器“再用当前参数赌一把”。如果当前参数值是高频或极低频值清完缓存照样可能生成偏向性计划。3.2 什么时候该用UNKNOWNOPTIMIZE FOR UNKNOWN最适合数据分布相对均匀、且业务上无法确定哪个参数值更重要的查询。比如一个多租户后台日志查询接口所有租户的数据量差异不大任何租户的查询响应时间都在可接受范围内。这时候你并不需要为某个租户做极致优化只需要避免某一次极端参数值把计划带偏就可以用 UNKNOWN 来“磨平”计划波动。还有一个典型场景查询中的参数只是“粘合”条件实际查询结果集大小主要由其他常量或JOIN逻辑决定。此时参数值对基数估计的影响本来就小用 UNKNOWN 反而能避免参数嗅探引入的不稳定波动让执行计划长期保持稳定。在使用 UNKNOWN 前强烈建议用直方图确认数据分布。先用下面这条命令查看目标列的统计信息DBCC SHOW_STATISTICS(dbo.Orders, IX_Orders_OrderStatus) WITH HISTOGRAM;如果直方图里各个步数的RANGE_HI_KEY对应的EQ_ROWS差异在5倍以内用 UNKNOWN 是合理的。如果相差百倍千倍那还是回到OPTIMIZE FOR固定代表值或者用RECOMPILE更靠谱。3.3 UNKNOWN的坏处统计过期、类型不匹配、NULLOPTIMIZE FOR UNKNOWN最大的坏处是它把“纠偏”的希望全部寄托在统计信息质量上。它告诉优化器“别用参数值用平均密度”但平均密度本身是从统计信息里来的。如果表里的数据已经大幅变化、统计信息还没更新那么平均密度也是错的UNKNOWN 并不能挽救一个过期统计下的烂计划。更隐蔽的问题是类型隐式转换。如果列类型是varchar应用传入的参数是nvarchar优化器在判断谓词时会做隐式转换可能影响到基数估计的准确性。UNKNOWN 模式下这种隐式转换带来的干扰会变得更难辨认因为你不是基于某个具体值去排查而是对着一个模糊的平均密度猜测。此外如果谓词包含LIKE % keyword %这种模糊匹配优化器本身就无法靠参数值估算出好的选择性UNKNOWN 也一样无能为力因为统计信息里根本没有关键字前缀/子串分布的密度数据。遇到这种查询更务实的方案是考虑全文索引或调整查询模式而不是指望Hint。4. 让每次执行都重新“算一次命”RECOMPILE 的正确姿势4.1 RECOMPILE 到底是干什么的RECOMPILE是另一个特别常用的Hint。它分两种形态语句级和存储过程级。语句级写在查询末尾SELECT * FROM dbo.Orders WHERE OrderStatus OrderStatus OPTION (RECOMPILE);存储过程级写在调用方EXEC dbo.GetOrdersByStatus OrderStatus Pending WITH RECOMPILE;它的核心作用是执行完后不缓存当前语句/存储过程的执行计划下次执行时重新编译。需要注意的是语句级OPTION (RECOMPILE)会在每次执行该语句时都重新编译而且只影响该语句存储过程级WITH RECOMPILE则会让整个存储过程中的所有语句都不缓存计划每一次调用都全部重编译。开销显然比语句级大得多所以日常优化我更倾向于在存储过程内部的关键慢语句后面只加OPTION (RECOMPILE)而不是一竿子插到底给整个存储过程加WITH RECOMPILE。4.2 哪些场景受益最大RECOMPILE适合那些参数值分布极不均匀、且你无法找到一个固定代表值的场景。典型的就是报表系统里的“高级筛选”同一张宽表用户可能按日期、按城市、按部门、按金额段任意组合条件每次组合的基数估计差别极大。任何固定的计划都无法同时满足所有组合这时候用RECOMPILE让优化器每次都根据“当前参数组合”重新算一次是最务实的解法。还有一类场景是数据倾斜随时在变的批处理系统。比如每日凌晨从上游同步大量数据后表的统计信息快速老化白天跑的查询如果晚上还在用凌晨缓存下来的计划命中率大概率是灾难。给这些低频但耗时的批处理查询加RECOMPILE虽然会增加少量编译CPU但换来的是“每次执行都拿到当前统计信息下的最优计划”整体收益远大于编译开销。关键判断标准很简单查询单次执行时间越长重编译的性价比越高执行频率越高重编译的负担越重。一个执行耗时30秒的报表查询多花30毫秒编译完全无所谓一个每秒执行几百次的订单查询多花1毫秒编译就可能导致CPU明显上升还要小心并发下的编译锁竞争。4.3 组合这个系列的精髓OPTIMIZE FOR RECOMPILE很多人以为OPTIMIZE FOR和RECOMPILE是互斥方案其实它们经常成对出现。组合的含义是用OPTIMIZE FOR指定一个业务代表值来稳定编译计划形状用RECOMPILE保证每次执行都重新编译、从而及时纳入最新统计信息。SELECT * FROM dbo.Orders WHERE OrderStatus OrderStatus OPTION (OPTIMIZE FOR (OrderStatus Pending), RECOMPILE);这种组合特别适合数据在不断更新统计信息准确度在变化、但业务高频查询集中在某一类参数值上的场景。每次执行按“Pending”的选择性生成计划同时统计信息又是最新的双管齐下。什么时候只取其一如果查询执行频率很高每次重编译的CPU成本你承受不起那就去掉RECOMPILE只用OPTIMIZE FOR靠计划缓存扛住高频查询的压力。如果参数组合非常复杂、固定任何代表值都会伤害一部分查询那就放弃OPTIMIZE FOR只用RECOMPILE让每次编译跟着实际参数走。组合使用还有一个隐藏好处可以避免优化器因为数据小版本更新而自行选择“重新编译”时突然产生一个走偏的自动计划。因为OPTIMIZE FOR已经把基数估计钉死在你指定的值上即便触发了自动重编译计划形状也基本可控。5. 实战排查如何判断该不该上Hint5.1 锁定坏计划排查参数嗅探第一步永远是找到那条“偶发慢”的SQL。用前面提到的sys.dm_exec_query_stats找到有多个query_plan_hash的语句后把它正在使用的执行计划抓出来SELECT qs.execution_count, qs.total_worker_time / qs.execution_count AS avg_cpu_ms, qs.total_logical_reads / qs.execution_count AS avg_reads, qs.total_elapsed_time / qs.execution_count AS avg_duration_ms, qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp WHERE qs.query_hash IN ( 先在上一步查询中定位到的query_hash ) ORDER BY qs.execution_count DESC;拿到计划后打开图形化执行计划关注两个核心点Estimated Number of Rows估计行数和 Actual Number of Rows实际行数。如果两列数字差两个数量级以上就说明基数估计出了问题。再对比同一query_hash下的不同计划哪个好哪个坏一目了然。5.2 冷启动对比验证确认问题后最干净的验证方法是冷启动对比。取两个参数值一个是经常慢的值一个是经常快的值。先把对应查询的缓存计划清掉分别执行一次记录耗时和IO。然后加上候选Hint再试一轮。我一般会做个简单的对照组表格场景参数值是否带Hint耗时逻辑读计划中关键算子现状Pending无5.2s85000全表扫描当下Completed无0.8s800索引查找OPTIMIZE FORPending无0.2s900索引查找RECOMPILEPending无0.2s900索引查找有了这张表你就能很直观地判断Hint是否有效。注意一点测试时不要只测慢参数也要测其他高频参数防止“按下葫芦浮起瓢”。5.3 上线前必须做的三项验证OPTIMIZE FOR这类Hint一旦写进存储过程影响是长期的。上线前至少要做三项测试第一多参数值回归。把业务里能枚举到的主要参数值都跑一遍确认没有哪个高频场景被明显劣化。之前我遇到过有人对“查询全部订阅用户”的场景固定了一个“单租户”代表值结果所有租户总览页面全部走错索引IO翻了几十倍。第二统计信息变更模拟。Hint上线后后面统计信息更新可能让当前Hint计划的形状不再最优。测试时可以考虑强制更新统计或修改数据量观察Hint是否还能保持一个可接受的下限。如果不稳定考虑组合RECOMPILE或者干脆选UNKNOWN。第三并发编译压力测试。如果加了语句级RECOMPILE要模拟高并发调用观察CPU和等待类型。编译本身要消耗CPU还会占用编译内存并发起来很可能出现RESOURCE_SEMAPHORE_QUERY_COMPILE等待这意味着系统编译内存不足是一件很棘手的事。6. 大家踩过的坑关于Hint的误解清单6.1 误区一一有问题就清空计划缓存不少DBA遇到性能异常时的第一反应是执行DBCC FREEPROCCACHE。这在某些时候确实见效快但代价极大所有语句的缓存全部清空接下来一段时间系统会经历一次“编译风暴”CPU冲高、查询性能整体下降。而且如果根本原因没有找到几分钟后慢查询又会卷土重来你只是把症状推迟了一点点。正确处理是只清除出问题的那一条语句的plan_handle如果担心影响面可以先记录下慢SQL文本、执行计划和性能指标再用较小粒度的手段验证。清缓存永远是“复位”动作不是“修复”动作。6.2 误区二加了OPTIMIZE FOR就一劳永逸业务是会变的。上个月的高频参数值下个月不一定是高频统计信息里的热点区域也会迁移。如果加了OPTIMIZE FOR就扔在那里不管等于给系统埋了一个定时炸弹。我建议在运维侧给所有包含OPTIMIZE FOR的存储过程加一个清单每季度或每半年重新跑一遍统计信息直方图对比看看当初固定值的EQ_ROWS是否还处于业务热点上。如果热点偏移及时调整Hint参数值。6.3 误区三UNKNOWN对所有参数化查询都安全OPTIMIZE FOR UNKNOWN有时候被当作“不会出错”的保守选项实际上它只是把选择权交给了统计信息的“平均密度”。在数据严重倾斜的列上平均密度给出的结果对高频和低频查询都不讨喜。高频查询需要的是窄索引查找可平均密度会告诉优化器“这个列没什么选择性”于是它宁可选择扫描低频查询也许希望大范围扫描以配合并行平均密度又可能给出一个不高不低的行数估计选了一个两头不讨好的中间计划。更别说统计信息本来就不新鲜的情况。UNKNOWN 并不能解决统计过期的问题统计过期时密度值也是过期的。所以它不是“安全牌”只是“中庸牌”。6.4 误区四只盯着Hint忽略了索引、统计和SQL重写这是最核心的一条经验Hint是最后的手段不是第一选择。我见过不少开发同学对一条烂SQL不做任何索引分析上来就加OPTIMIZE FOR或RECOMPILE最后只是把问题从“慢”变成“稳定地慢”。正确的优化顺序应当是先看缺失索引建议检查统计信息最后更新时间再考虑改写SQL逻辑比如拆分为多条语句、消除不必要的笛卡尔积、把OR改写为UNION ALL如果这些都做完了仍无法稳定控制计划再上Hint。Hint的本质是绕过优化器也就等于放弃了优化器在统计信息变化后自动调整计划的能力。你手动接过方向盘就要对整条路况负责。最后分享一个我个人的排查小技巧遇到参数化查询性能波动时不要只盯着执行计划看先跑一遍DBCC SHOW_STATISTICS看直方图再对比计划里估计行数和实际行数。大部分时候你会发现问题不在优化器的参数嗅探而是统计信息该更新了但没更新。这时候一条简单的UPDATE STATISTICS就能解决问题远比加Hint更干净。真正需要上Hint的场景是统计数据本身没问题、业务分布也确实复杂、且你试过了所有常规优化手段后的最后一步。牢记这一点你使用Hint的姿势会比绝大多数人都要稳。