优化重点是保障执行计划稳定复用：查plan_generation_num确认是否频繁重编译，禁用EXEC(@sql)改用sp_executesql参数化，用局部变量或OPTIMIZE FOR缓解参数嗅探，统一SET选项确保上下文一致。
SQL Server 2022 的存储过程执行计划缓存本身没变坏，变的是你调用它的方式、参数的取值分布，以及有没有无意中破坏了缓存复用的前提条件。优化重点不在“清缓存”或“强制重编译”，而在于让 SQL Server 愿意且能够稳定复用同一个高质量计划。
查 plan_generation_num 确认是不是真在反复重编译
别靠“这次比上次慢”下结论——得看 SQL Server 实际有没有丢弃旧计划。关键指标是 plan_generation_num：
- 运行
SELECT qs.plan_generation_num, qs.execution_count, st.text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st WHERE st.text LIKE '%YourProcName%' ORDER BY qs.last_execution_time DESC -
plan_generation_num > 1表示该计划被重建过；如果每次执行都涨 1，说明几乎没缓存住 - 配合
SET STATISTICS XML ON看执行计划里是否有Recompile节点，这是最直接证据
避免 EXEC(@sql) 彻底杀死缓存复用
动态 SQL 不是不能缓存，而是你写的那句 EXEC(@sql) 让它永远进不了缓存。SQL Server 把每次拼出来的字符串当全新语句处理：
- ❌ 错误写法：
SET @sql = N'SELECT * FROM Orders WHERE Status = ' + CAST(@status AS VARCHAR); EXEC(@sql) - ✅ 正确写法：
EXEC sp_executesql N'SELECT * FROM Orders WHERE Status = @status', N'@status TINYINT', @status = 1 - 排序字段、表名、列名不能参数化，必须用
QUOTENAME()+ 白名单校验，否则既不安全也破坏缓存
用局部变量缓解参数嗅探，比 OPTION(RECOMPILE) 更稳
OPTION (RECOMPILE) 是高开销操作，每次执行都触发完整编译，在 OLTP 场景下容易拖垮 CPU。更轻量的做法是“断开嗅探链”：
- 在存储过程开头声明同类型局部变量：
DECLARE @local_status TINYINT = @status - 后续所有查询只用
@local_status，优化器无法感知原始参数值，转而基于统计信息做平均估算 - 对已知典型值的场景，可用
OPTIMIZE FOR (@status = 1)；若完全不可预测，用OPTIMIZE FOR (@status UNKNOWN)
统一 SET 选项，否则缓存形同虚设
哪怕两个存储过程逻辑一模一样，只要一个会话开了 SET ARITHABORT ON、另一个没开，SQL Server 就视为不同上下文，各自编译、各自缓存——白占内存，还增加编译压力：
- 检查当前连接默认选项：
DBCC USEROPTIONS - 在存储过程开头显式设置一致的选项，例如统一加
SET ARITHABORT ON; SET ANSI_NULLS ON; - 应用层调用时也要确保连接字符串或 ORM 配置未悄悄改掉这些值
真正难的不是写出能跑的存储过程，而是让它的执行计划在不同参数、不同时间、不同数据量下都保持可预期。最容易被忽略的点是：你以为在复用计划，其实每次都在生成新计划；你以为加了 OPTION(RECOMPILE) 就安全了，其实只是把性能问题从“慢”换成了“CPU 高”。盯住 plan_generation_num 和 execution_count，才是看清缓存真实状态的唯一方式。
