option (recompile) 应用于参数嗅探导致执行计划错配的场景，如参数取值跨度极大且where条件高度依赖该参数；不应滥用在高频oltp或参数均匀、含固定过滤条件等场景。

SQL Server 中 OPTION (RECOMPILE) 什么时候该用、什么时候不该用

强制重编译不是万能加速器，它解决的是参数嗅探导致的执行计划“错配”，而不是计划本身低效。当存储过程首次执行用了一个小数据量的参数生成了嵌套循环计划，后续大范围查询还沿用这个计划，就会严重拖慢——这时 OPTION (RECOMPILE) 才有意义。

但滥用会带来明显开销：每次执行都走完整优化流程，CPU 升高、缓存命中率下降；尤其在高频调用（如每秒几十次）的 OLTP 场景下，可能比跑错计划还糟。

• 适合场景：@id 取值跨度极大（如从 1 到 1000 万）、且 WHERE 条件高度依赖该参数，统计信息无法覆盖分布差异
• 不适合场景：表结构稳定、参数取值均匀、或 WHERE 条件中含固定过滤（如 AND Status = 1）削弱了参数主导性
• 替代思路：先用 DBCC SHOW_STATISTICS 查看直方图是否过时；考虑拆分逻辑，对不同参数范围走不同分支（IF @id ）

用 OPTIMIZE FOR 提示绕过首次参数的“假代表”

SQL Server 默认用第一次传入的参数值生成并缓存计划，如果那次碰巧是边缘值（比如空字符串、0、NULL），后续正常值就全被带偏。此时不用全量重编译，而是告诉优化器“按这个典型值来算”更轻量。

OPTIMIZE FOR (@pid = 5000) 会让优化器假装 @pid 是 5000 去估算行数和选择连接算法，但运行时仍用真实值；而 OPTIMIZE FOR (@pid UNKNOWN) 则让优化器忽略参数值，纯靠统计信息平均估算——后者适合参数分布完全不可预测的场景。

• 必须注意：OPTIMIZE FOR 不影响实际执行逻辑，只影响计划生成阶段的估算依据
• 常见错误：写成 OPTIMIZE FOR (@pid = NULL) 却没意识到 NULL 在统计信息里通常单独成桶，容易触发索引扫描而非查找
• 验证方式：开启 SET STATISTICS XML ON，对比加提示前后“Estimated Number of Rows”是否更贴近真实业务值

USE PLAN 强制绑定执行计划的风险与适用边界

当你已经手工调优出一个稳定高效的 XML 执行计划，并想彻底锁定它，才考虑 USE PLAN。它不解决“怎么生成好计划”，只解决“不让系统换掉好计划”。但一旦底层变了（比如加了新索引、删了列、升级 SQL Server 版本），这个硬编码的计划很可能直接报错 Query processor could not produce a query plan because of the hints defined in this query。

• 仅建议用于核心报表类存储过程，且该计划已在线上稳定运行超 2 周、跨多个数据量级验证无误
• XML 计划必须来自同一兼容级别、同一数据库、同一 SET 选项环境（尤其是 ANSI_NULLS 和 QUOTED_IDENTIFIER 必须匹配）
• 不能和 RECOMPILE 共存；也不能在含临时表或表变量的语句中使用（因计划结构依赖运行时对象）

为什么 INDEX 提示经常失效、甚至变慢

显式指定索引（如 WITH (INDEX(IX_Order_Date))）看似精准，实则绕过了优化器的成本权衡。当数据量增长、统计信息滞后、或查询条件组合变化时，你“钦定”的索引可能早已不是最优路径——而优化器原本选的堆扫描或另一个复合索引反而更快。

更隐蔽的问题是：SQL Server 在遇到 INDEX 提示但目标索引不存在、被禁用、或列顺序不匹配时，不会报错，而是静默退回到默认计划，导致行为不可控。

• 真正需要它的情况极少：比如确认某个索引页分裂严重，临时切到另一个物理结构更健康的索引扛住高峰流量
• 调试阶段可用来对比性能，但上线前务必移除；生产环境若真要用，必须配套监控 sys.dm_exec_query_stats 中该语句的 execution_count 和 total_logical_reads 波动
• 优先尝试重建索引 + 更新统计信息，比硬写提示更能治本

执行计划不是越“可控”越好，而是越贴合数据实际分布和访问模式越好。所有强制手段都在把优化器从“智能助手”变成“听话木偶”，一旦现实变化，木偶就卡住不动了。