动态SQL执行慢主因是执行计划未缓存;EXEC(@sql)完全不缓存,必须用sp_executesql并参数化所有变量,结构可变部分用白名单+CASE,对象名用QUOTENAME()校验,避免局部变量截断参数嗅探,慎用OPTION(RECOMPILE),统一连接选项以确保缓存复用。

动态SQL执行慢,大概率不是语法问题,而是执行计划没缓存
SQL Server 对 EXEC(@sql) 完全不缓存执行计划——每次拼字符串、每次执行,都走完整编译流程。哪怕语句逻辑一模一样,只要字符串内容有微小差异(比如空格、换行、大小写),就视为全新语句,sys.dm_exec_query_stats 里 execution_count 永远是 1。
实操建议:
- 必须用
sp_executesql替代EXEC,且所有可变值都得进参数列表,不能拼进字符串,例如:EXEC sp_executesql @sql, N'@status TINYINT', @status = 1 - 排序字段、分页方向等“结构可变”部分,不能直接拼接,得用白名单 +
CASE映射,例如:ORDER BY CASE @sortCol WHEN 'name' THEN name END - 表名、列名等无法参数化的部分,必须用
QUOTENAME()包裹,并提前校验是否在允许列表内,否则既不安全,也破坏缓存
为什么加了 sp_executesql 还是慢?检查参数是否被局部变量截断
常见错误:在存储过程里把输入参数先赋给局部变量,再传给 sp_executesql,例如:DECLARE @status_local INT = @status; EXEC sp_executesql @sql, N'@s INT', @s = @status_local。这会导致 SQL Server 在编译时看不到原始参数值,失去参数嗅探能力,生成的计划偏向“平均分布”,对极端选择性参数(如 99% vs 1%)反而更差。
正确做法:
- 让
sp_executesql的参数直接绑定存储过程入参,跳过中间变量 - 如果必须用局部变量(比如要做类型转换或默认值处理),确保变量声明后不再修改,且类型与参数严格一致,避免隐式转换导致参数化失败
- 验证是否真缓存:查
sys.dm_exec_query_stats中对应语句的execution_count,多次调用后大于 1 才算成功复用
OPTIMIZE FOR 和 OPTION (RECOMPILE) 别乱用
OPTION (RECOMPILE) 放在动态SQL末尾,等于主动放弃缓存——每次执行都重编译,CPU 压力翻倍,只适合低频、参数跨度极大、且单次执行耗时远高于编译开销的报表类场景。
更实用的控制方式:
- 对固定模式的动态SQL(如按不同状态查订单),优先用
OPTIMIZE FOR (@status = 1),指定一个典型高频值,让计划稳定适配主流流量 - 避免
OPTIMIZE FOR UNKNOWN:SQL Server 2016+ 中它实际行为接近“强制平均估算”,在数据倾斜严重时效果不如显式指定典型值 - 若分支极多(如 10 种状态),考虑拆成多个专用存储过程,比在一个过程里堆
IF/ELSE+ 动态SQL 更易命中缓存
真正卡住的地方,往往藏在调用链路里
动态SQL本身不慢,但容易掩盖上游问题:比如调用它的存储过程开头用了 SET ARITHABORT OFF,而其他过程或应用连接串默认是 ON,导致同一段 sp_executesql 被当成两个上下文,各自编译、各自缓存。
排查重点:
- 确认所有调用方连接选项一致,特别是
ARITHABORT、QUOTED_IDENTIFIER、ANSI_NULLS - 查
sys.dm_exec_cached_plans+sys.dm_exec_plan_attributes,看相同 SQL 文本是否对应多个 plan_handle - 临时加
SET STATISTICS XML ON,观察执行计划里是否出现重复的“Recompile”标记,定位具体哪一层触发了重编译
最常被忽略的是:动态SQL里的表变量和临时表选择不当。数据量大时用表变量会误导优化器低估行数,导致嵌套循环变慢;数据量小时用临时表又多一次磁盘 IO。这个权衡点不在动态SQL本身,而在它所依赖的基础结构。

















