在数据库日常开发中，SQL存储过程搭配动态SQL是非常常见的组合，能够适配多变的查询条件，减少重复编写存储过程的工作量。但动态SQL的灵活性往往会带来执行计划缓存失效的问题，导致数据库频繁编译SQL语句，最终影响整体性能。
动态SQL与缓存执行计划的核心原理
动态SQL是指在运行时拼接、生成SQL语句的方式，常见的实现方式是通过字符串拼接后调用EXEC或者sp_executesql执行。而缓存执行计划是数据库为了提升查询效率，将编译后的SQL执行逻辑存储起来，下次遇到相同结构的SQL时直接复用，避免重复编译。
动态SQL的问题在于，如果每次拼接的SQL语句字符串不完全一致，数据库会认为这是不同的SQL，不会复用已有的执行计划，只能重新编译，这就是性能损耗的核心来源。
动态SQL导致缓存执行计划失效的常见原因
- 直接拼接字符串作为SQL内容，每次传入的参数不同，生成的SQL字符串完全不同，无法匹配已缓存的执行计划
- 在动态SQL中拼接不必要的空格、换行或者注释，导致SQL字符串的哈希值不同，无法命中缓存
- 使用
EXEC执行动态SQL时，没有参数化查询，数据库无法识别参数化的执行计划模板
动态SQL性能优化与缓存执行计划的具体方法
1. 优先使用sp_executesql替代EXEC执行动态SQL
sp_executesql支持参数化查询，数据库可以为参数化的动态SQL生成可复用的执行计划模板，只要SQL的结构不变，仅参数值不同，就可以复用执行计划。
以下是错误和正确的写法对比：
-- 错误写法：直接拼接字符串，每次SQL都不同，无法缓存执行计划
CREATE PROCEDURE Get_User_By_Condition_Wrong
@user_name NVARCHAR(50),
@age INT
AS
BEGIN
DECLARE @sql NVARCHAR(1000)
SET @sql = N'SELECT * FROM users WHERE 1=1 '
IF @user_name IS NOT NULL
SET @sql = @sql + N' AND user_name = ''' + @user_name + ''''
IF @age IS NOT NULL
SET @sql = @sql + N' AND age = ' + CAST(@age AS NVARCHAR(10))
EXEC(@sql)
END
GO
-- 正确写法：使用sp_executesql参数化，可缓存执行计划
CREATE PROCEDURE Get_User_By_Condition_Right
@user_name NVARCHAR(50),
@age INT
AS
BEGIN
DECLARE @sql NVARCHAR(1000)
DECLARE @params NVARCHAR(500)
SET @sql = N'SELECT * FROM users WHERE 1=1 '
SET @params = N'@user_name NVARCHAR(50), @age INT'
IF @user_name IS NOT NULL
SET @sql = @sql + N' AND user_name = @user_name'
IF @age IS NOT NULL
SET @sql = @sql + N' AND age = @age'
-- 参数化传入，数据库会缓存该结构的执行计划
EXEC sp_executesql @sql, @params, @user_name, @age
END
GO
2. 固定动态SQL的基础结构，减少字符串拼接差异
拼接动态SQL时，尽量保持基础SQL字符串的结构一致，仅通过参数控制条件是否生效，避免因为拼接内容不同导致SQL字符串哈希值变化。
例如查询用户时，不管是否传入筛选条件，都保持SQL的框架不变，通过参数判断条件是否生效：
CREATE PROCEDURE Get_User_Optimized
@user_name NVARCHAR(50),
@age INT,
@status INT
AS
BEGIN
DECLARE @sql NVARCHAR(1000)
DECLARE @params NVARCHAR(500)
-- 固定SQL结构，后续仅拼接条件，不修改基础框架
SET @sql = N'SELECT id, user_name, age, status FROM users WHERE 1=1 '
SET @params = N'@user_name NVARCHAR(50), @age INT, @status INT'
-- 条件生效时拼接对应片段，保持整体结构一致
IF @user_name IS NOT NULL
SET @sql = @sql + N' AND user_name = @user_name '
IF @age IS NOT NULL
SET @sql = @sql + N' AND age = @age '
IF @status IS NOT NULL
SET @sql = @sql + N' AND status = @status '
EXEC sp_executesql @sql, @params, @user_name, @age, @status
END
GO
3. 避免动态SQL中出现不必要的可变内容
不要在动态SQL中拼接当前时间、随机值、或者是每次执行都会变化的变量内容，这类内容会导致SQL字符串每次都不同，无法命中缓存。如果必须使用这类内容，可以将其作为参数传入，而不是直接拼接到SQL字符串中。
4. 定期清理无效的执行计划缓存
如果数据库中已经存在大量无法复用的动态SQL执行计划，会占用缓存空间，影响其他有效执行计划的命中率。可以定期清理过期的执行计划缓存，或者针对性清理特定存储过程相关的缓存。
清理特定存储过程执行计划缓存的示例：
-- 查询存储过程对应的执行计划句柄 SELECT plan_handle, st.text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st WHERE st.text LIKE N'%Get_User_Optimized%' GO -- 根据plan_handle清理对应的执行计划缓存 DBCC FREEPROCCACHE (对应的plan_handle值) GO
优化效果验证方法
可以通过以下系统视图查询动态SQL的执行计划缓存情况，验证优化是否生效：
-- 查询缓存的执行计划，查看对应存储过程的缓存次数和使用次数
SELECT
st.text AS sql_text,
cp.usecounts AS 执行计划使用次数,
cp.size_in_bytes AS 缓存大小,
cp.cacheobjtype AS 缓存对象类型
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE st.text LIKE N'%Get_User_Optimized%'
ORDER BY cp.usecounts DESC
GO
如果优化生效，相同结构的动态SQL对应的usecounts数值会随着执行次数增加，说明执行计划被成功复用，不需要重复编译。