参数嗅探是sql server默认行为，本质是执行计划错配；最稳解法是用同类型局部变量替代原始参数，因编译期未知变量值，优化器只能依赖统计信息估算，从而绕过参数值绑定。
参数嗅探不是 bug，是 SQL Server 默认行为；问题出在执行计划“错配”，而不是 SQL 写错了或索引坏了。最稳、改动最小、兼容性最好的解法是用同类型局部变量替代原始参数。
为什么局部变量能断开参数嗅探
SQL Server 在编译时能“看到”存储过程参数（如 @status）的具体值，于是据此生成并缓存一个高度特化的执行计划；但本地变量（如 @local_status）在编译期是未知的，优化器只能退回到统计信息估算行数，自然就绕过了参数值绑定。
这不是 hack，是利用编译期可见性规则的正向用法。效果等同于 OPTION (OPTIMIZE FOR UNKNOWN)，还不用加查询提示。
容易踩的坑：
- 类型不严格匹配：比如
@status CHAR(1)赋给@local_status VARCHAR(1)，隐式转换可能让索引失效 - 声明和赋值没写成一行：
DECLARE @local_status CHAR(1) = @status;才可靠；用SET在某些旧版本中仍可能被推导 - WHERE 中混用：只替换了部分参数，漏掉的
@id仍在被嗅探
OPTION (RECOMPILE) 应该加在哪儿
只加在具体慢的那条 SELECT / INSERT 语句末尾，不是整个存储过程头上。
适用场景：
- 参数分布极不均匀（比如查“近 1 天订单” vs “全量历史订单”，行数差上千倍）
- 执行频次低（每天一次的日终报表、后台任务）
- 语句本身轻量（编译耗时远小于执行耗时）
错误做法：
- 在
CREATE PROCEDURE ... WITH RECOMPILE—— 整个过程每次调都重编译，CPU 白烧 - 在子查询里加了
OPTION (RECOMPILE)却漏掉主查询 —— 嗅探链没真正切断 - 对高频 OLTP 查询滥用 —— 编译开销反成瓶颈
怎么确认真是参数嗅探导致的慢
别猜，直接查缓存行为：
- 运行
sys.dm_exec_procedure_stats，看avg_elapsed_time和last_elapsed_time差异是否超 5 倍（比如平均 150ms，最近一次 1200ms） - 用
sys.dm_exec_query_plan拿到慢那次的执行计划，对比快那次：是否一个走索引查找、另一个走聚集扫描？ - 执行
DBCC FREEPROCCACHE后立刻重跑，如果变快了，基本锁定是计划缓存问题，而非数据或索引本身
注意：SSMS 里快、应用里慢，大概率不是参数嗅探，而是 ARITHABORT 或 SET NOCOUNT 等会话选项不一致，导致计划被当成两个不同缓存项。
OPTIMIZE FOR UNKNOWN 和 OPTIMIZE FOR (@p = ...) 别搞混
OPTIMIZE FOR UNKNOWN 是通用解法：让优化器忽略参数实际值，基于列统计直方图的平均密度估算，生成“中庸但稳定”的计划。适合高频调用、响应时间一致性要求高的场景。
OPTIMIZE FOR (@p = '20230101') 是危险操作：它把计划固化成针对某个固定值的“偏科”方案，一旦业务逻辑变化（比如那个值不再高频），性能会断崖下跌。
两者不能混用，且都只作用于加了提示的那一条语句 —— 不会影响存储过程中其他语句的计划复用。
真正容易被忽略的点：局部变量法虽稳，但会让优化器失去精确基数估算能力。如果某列严重倾斜（比如 99% 值是 'A'，1% 是 'B'），而你查的是 'B'，原本能走索引查找，现在可能降级为扫描。这时就得结合覆盖索引或语句级 RECOMPILE 来兜底。
