参数嗅探导致计划复用失配：首次用@id=1生成嵌套循环计划，后续@id=999999仍硬套该计划引发性能骤降；正确应对需严格类型局部变量、慎用recompile、合理使用optimize for。
这不是“后续变慢”，而是首次执行用了一个参数生成了计划，后续所有调用都硬套这个计划——哪怕参数值已经从查 10 行变成查 50 万行。根本问题不在执行次数，而在计划复用时的参数失配。
为什么 EXEC YourSP @id = 1 快，@id = 999999 就卡住
SQL Server 第一次编译 YourSP 时，“嗅探”到 @id = 1，发现只返回少量数据，于是选了嵌套循环 + 索引查找；第二次传 @id = 999999，优化器却照搬原计划，结果对 50 万行反复做索引查找，逻辑读飙升、CPU 拉满。
- 典型信号：
sys.dm_exec_query_stats中同一plan_handle的avg_logical_reads波动超过 10 倍 - SSMS 手动粘贴 SQL 很快，但
EXEC YourSP @id = ...就慢 → 基本锁定是参数嗅探，不是语句本身问题 -
DBCC FREEPROCCACHE后首次变快，过几分钟又慢 → 计划缓存被重新填入，且再次被边缘参数带偏
局部变量绕过参数嗅探：怎么写才真正生效
声明并赋值必须一步完成，且类型严格一致，否则隐式转换会让索引失效。
- ✅ 正确：
DECLARE @local_id INT = @id;（@id是INT，直接赋值） - ❌ 错误：
DECLARE @local_id VARCHAR(10) = @id;（触发隐式转换，索引列id被函数化，无法走查找） - ❌ 错误：
DECLARE @local_id INT; SET @local_id = @id;（两步分离，SQL Server 仍可能在编译阶段“看到”原始@id） - WHERE 子句中必须用
@local_id，不能混用@id，否则前功尽弃
OPTION (RECOMPILE) 不是万能解药：什么时候该用、什么时候别碰
它让每次执行都重走完整优化流程，代价是 CPU 升高、计划缓存命中率归零。
- ✅ 适合：
@id取值跨度极大（如 1～1000 万），且 WHERE 条件几乎全依赖它，统计信息无法覆盖分布差异 - ❌ 慎用：高频 OLTP 场景（如每秒调用 50+ 次），
OPTION (RECOMPILE)带来的 CPU 开销可能比跑错计划还糟 - ❌ 白用：WHERE 中有固定过滤（如
AND status = 1），参数主导性被削弱，重编译意义不大 - 只加在关键语句后，比如
SELECT ... FROM t WHERE id = @id OPTION (RECOMPILE)，不要整个存储过程都加
OPTIMIZE FOR 更轻量：绕过“第一次就踩坑”的陷阱
不用每次都重编译，而是告诉优化器：“按这个典型值来估算”，运行时仍用真实值。
-
OPTIMIZE FOR (@id = 5000)→ 让优化器假装@id是 5000 去算行数和连接方式 -
OPTIMIZE FOR (@id UNKNOWN)→ 完全忽略参数值，纯靠统计信息平均估算，适合参数完全不可预测的场景 - ⚠️ 避免
OPTIMIZE FOR (@id = NULL)：NULL 在直方图里常单独成桶，容易触发全索引扫描而非查找 - 必须加在具体语句末尾，如
SELECT * FROM orders WHERE customer_id = @cid OPTION (OPTIMIZE FOR (@cid = 5000))
最容易被忽略的是：局部变量写法看似简单，但类型不一致或分步赋值会让它彻底失效；而 OPTION (RECOMPILE) 在高并发下可能把 CPU 推到瓶颈，反而放大问题。参数嗅探不是 bug，是 SQL Server 的默认行为，应对思路得匹配实际负载特征，不是堆方案。
