根本原因是存储过程体过大、逻辑分支过多、涉及对象太多，导致优化器认为执行计划“太脆弱”而频繁放弃缓存并重新生成；拆分为多个按数据生命周期和执行确定性划分的独立子过程（如用sp_executesql驱动、TVP传参）是高效低风险解法。
SQL Server 存储过程重编译频繁，根本原因往往不是“缓存被踢出”或“参数嗅探失控”，而是单个存储过程体过大、逻辑分支过多、涉及对象太多——导致优化器每次执行都不得不放弃已缓存的执行计划，重新生成。拆分大过程为多个子过程（sp_executesql 驱动的独立小过程），是见效快、风险低、DBA 和开发都能快速落地的解法。
为什么大存储过程容易触发重编译
SQL Server 对单个存储过程的执行计划缓存有隐式限制：当过程内引用的对象超过一定数量（如 50+ 张表/视图/函数）、条件分支嵌套过深（CASE + IF + 动态拼接混用）、或包含大量临时表定义/修改时，优化器会认为该计划“太脆弱”，倾向于每次执行都走完整优化流程。这不是 bug，是设计取舍——它宁可多花点 CPU 重编译，也不愿用一个可能已失效的计划去查错数据。
- 常见现象：
sys.dm_exec_query_stats中该过程的execution_count很高，但plan_generation_num更高（远大于 1） - 执行计划 XML 中反复出现
Reason="SchemaChanged"或"StatisticsChanged"，但实际表结构和统计信息没变——本质是过程内部对象依赖链太长，让 SQL Server 误判为“不稳定” - 过程里用
SELECT INTO #tmp创建临时表后又立刻CREATE INDEX，这种模式极易触发重编译，因为索引创建会改变临时对象元数据
拆分子过程的关键原则
不是简单把代码 Ctrl+X/Ctrl+V 切开，而是按“数据生命周期”和“执行确定性”切分：输入确定、输出明确、不跨事务边界、不共享未声明临时表。
- 把“准备中间结果”的逻辑（如多表 JOIN 汇总、窗口计算）抽成独立子过程，用表值参数（
@tvp）或只读临时表（##global_tmp）传递结果，避免在主过程中反复建删#local_tmp - 把“条件分支强相关”的代码块单独封装，比如按
@mode = 'EXPORT'/'VALIDATE'分成usp_export_data和usp_validate_data，而不是在一个过程里堆满IF...ELSE IF... - 禁止子过程之间互相调用形成环路；主过程只调用子过程，子过程不反向调用主过程或其它兄弟过程
- 所有子过程必须显式
SET NOCOUNT ON，否则结果集计数干扰缓存稳定性
临时表 vs 表值参数：选哪个传数据
传中间结果时，别无脑选 #temp。临时表在跨过程时行为复杂：本地临时表（#t）无法被子过程访问；全局临时表（##t）能跨过程但需手动清理、有并发风险；而表值参数（TVP）才是安全首选。
- 用 TVP：适合结果集结构固定、行数
- 慎用
##t：仅当子过程需对中间数据建索引或多次扫描时才考虑，且必须在主过程末尾DROP TABLE ##t，否则残留表会污染后续执行 - 绝对不用
#t跨过程：SQL Server 明确不支持，子过程中访问会报Invalid object name '#t' - 性能对比：同一批 5 万行数据，TVP 传参耗时约 8ms，
##t创建+插入+索引+清理约 42ms，且后者每次都会引发重编译
拆分后仍要检查的隐藏坑
拆分本身不能自动解决所有问题。最容易被忽略的是权限继承和上下文切换。
- 子过程默认以调用者上下文运行，但如果主过程用
EXECUTE AS OWNER，子过程不会自动继承——必须在每个子过程中也显式写EXECUTE AS OWNER，否则可能因权限不足触发重编译（错误码Msg 916） - 如果原大过程里用了
SET ARITHABORT ON，而子过程没设，客户端连接设置不一致会导致缓存分裂——所有子过程开头统一加SET ARITHABORT ON - 拆分后，原过程的
WITH RECOMPILE选项必须移除，否则等于白拆；同时检查是否误把RECOMPILE写进了子过程的EXEC sp_executesql @sql, N'@p int', @p语句里 - 上线前务必查
sys.dm_exec_procedure_stats，确认各子过程的plan_generation_num稳定为 1，且last_execution_time后的缓存命中率 > 95%
真正难的不是拆，而是判断哪一段该拆、拆完要不要加索引、以及如何让应用层不感知变化。这些细节一旦漏掉，重编译次数可能不降反升。
