sp_executesql的参数化不能解决所有动态批处理性能问题，因其仅对完全相同的SQL文本（含空格/换行）且所有可变部分均通过参数或白名单映射时才复用执行计划；拼接列名、表名、ORDER BY等会导致sql_handle变化而失效。

为什么 sp_executesql 的参数化不能解决所有动态批处理性能问题

很多人以为只要用了 sp_executesql 就自动缓存执行计划、性能就稳了，其实不然。当动态 SQL 中包含拼接的 ORDER BY、TOP、WHERE 列名或表名时，哪怕只改一个排序字段，SQL Server 就认为是全新语句——sql_handle 变了，缓存失效。真正复用的前提是：语句文本（不含空格/换行差异）完全一致，且所有可变部分都走参数或白名单映射。

• 拼接 @orderby → 每次生成不同字符串 → sql_handle 不同 → 无法复用
• 用 CASE WHEN @sortCol = 'name' THEN name END 替代拼接列名 → 文本固定 → 缓存有效
• 表名/列名必须用 QUOTENAME() + 白名单校验（如 IF @table NOT IN ('Orders','Customers') THROW...），否则既不安全又破坏缓存
• 局部变量（如 @localFlag）直接出现在动态 SQL 字符串里，会被内联为常量 → 计划无法复用，应改用 sp_executesql 参数传入

SQL Server 2022 批模式执行对动态 SQL 的实际影响

SQL Server 2022 默认启用批模式自适应内存授予（Batch Mode on Rowstore），但它**只对静态查询或参数化程度高的动态 SQL 生效**。如果你的动态 SQL 因列名拼接导致计划不重用，批模式根本不会触发——因为每次都在编译新计划，连“是否启用批模式”的决策都没机会做。

• 确认是否启用：查 sys.database_scoped_configurations 中 BATCH_MODE_ON_ROWSTORE 是否为 ON
• 验证是否生效：在执行计划 XML 中搜索 BatchMode="1"；若没出现，说明优化器回退到了行模式
• 关键前提：目标表需有至少 10 万行（默认阈值），且查询涉及聚合、窗口函数或大范围扫描才倾向启用批模式
• 动态 SQL 中避免 SELECT *，显式列出列名并确保类型稳定（如不用 CONVERT(VARCHAR, x) 隐式转类型），否则批模式可能被禁用

如何验证动态 SQL 执行计划是否真实复用

别只看有没有报错或返回结果，得进系统视图里数次数。缓存复用不是“写了 sp_executesql”就自动发生的，而是要实打实看到 execution_count 递增。

Ainee: AI Notetaker

一款AI办公效率工具，主要用于Your ultimate AI-powered notetaking and learning companion.，适合需要提升相关任务效率的用户。

下载

• 运行一次动态 SQL 后，立即查：

  SELECT plan_handle, sql_handle, execution_count 
  FROM sys.dm_exec_query_stats 
  WHERE sql_handle IN (
    SELECT sql_handle FROM sys.dm_exec_sql_text 
    WHERE text LIKE '%SELECT%Orders%WHERE%status%'
  )

• 再调用同一存储过程 3 次，重新运行上面查询——如果 execution_count 是 4，说明复用成功；如果是 1 或每次不同，说明语句文本不稳定
• 注意区分：plan_handle 相同但 sql_handle 不同 → 完全不同的语句；只有 sql_handle 相同才代表文本一致
• 如果 execution_count 始终为 1，重点检查是否用了 QUOTENAME() 拼表名、是否把变量直接拼进字符串、是否漏了参数声明

动态批处理中容易被忽略的 CPU 瓶颈点

动态 SQL 本身不慢，慢的是反复编译+参数嗅探+低效谓词。尤其在 SQL Server 2022 中，如果动态语句触发了“重编译风暴”，CPU 会迅速拉满，而你可能还在查磁盘 IO。

• 用 sys.dm_exec_requests 查当前高 CPU 请求：WHERE command = 'EXECUTE' 且 cpu_time > 5000（毫秒）
• 结合 sys.dm_exec_cached_plans 查是否有大量 usecounts = 1 的计划，这是重编译泛滥的典型信号
• 避免在循环中反复调用 sp_executesql 执行相似语句——合并成单条批量操作，比如用 INSERT ... SELECT 替代多次 INSERT ... VALUES
• 对高频动态查询，考虑用 OPTION (RECOMPILE) 显式控制，反而比依赖首次参数生成的计划更稳定（尤其当参数分布极不均衡时）

真实复用的关键不在语法多漂亮，而在每次生成的语句字符串是否一字不差。表名、列名、排序逻辑这些“结构层”变动，必须靠白名单+QUOTENAME() 控制；而数据层变动，才交给参数。漏掉任何一层，缓存就断了。