主页 / dba / 问题 / 300808
Accepted
variable
Asked: 2021-10-08 01:55:17 +0800 CST

为什么在使用 sp_executesql 时更改参数化样式会导致计划缓存中的计划减少？

  • 772

通常 sp_execute sql 将重用缓存计划，其中 EXEC 将为每个参数创建新计划。

在此示例中，两个查询都使用 sp_executesql - 第二个查询导致计划缓存中计划数量减少的原因是什么？

查询一：

在此处输入图像描述 在此处输入图像描述

查询 2：

在此处输入图像描述 在此处输入图像描述

sql-server plan-cache

2 个回答

  1. Best Answer

    这与您的查询构建相比，与execvs的关系较小。sp_executesql

    第一个查询实际上会生成 100 个不同的查询，如下所示，

    select foo from bar where param=1 # Query hash abc123
select foo from bar where param=2 # Query hash def456
select foo from bar where param=3 # Query hash efg789
...
select foo from bar where param=100 # Query hash zzz999

    正如您所指出的，这些最终会出现在计划缓存中。第二个查询生成一个参数化查询，如下所示，

    select foo from bar where param=@p # Query hash opq789

    所以第二件事是使用不同的参数值运行相同的查询，并且查询哈希保持不变。像这样，

    select foo from bar where param=@p # Query hash opq789, @p=1
select foo from bar where param=@p # Query hash opq789, @p=2
select foo from bar where param=@p # Query hash opq789, @p=3
...
select foo from bar where param=@p # Query hash opq789, @p=100

    根据经验，除非必须，否则不要使用。 exec它很容易发生 SQL 注入。

    编辑：根据您获得多个计划的原因。SQL Server 计算查询的哈希值，它将用于查找是否已提交相同的查询以及计划缓存中是否有可重用的计划。

    当查询使用硬编码值时，例如等param=1，param=2每个查询将获得不同的哈希值（除非发生冲突，但这与此处无关）。不同的哈希意味着 SQL Server 认为它以前没有见过查询，因此它创建并缓存它（除非针对临时工作负载进行优化设置，其中仅缓存计划存根以供第一次执行）。有强制参数化，可以用来强制参数是参数而不是硬编码值。

    当查询使用参数时，例如param=@value，查询哈希不包括将传入的任何值@value。因此，每当使用不同的参数值重新运行相同的查询时，它将使用相同的计划（除非查询优化器注意到该计划不再有效或计划缓存被清除。）

    • 6

  2. 在您的第一个示例中，您并没有真正使用 sp_executesql 的参数化功能，因为您每次都在构建一个新字符串，并且查询字符串中的任何更改都将导致一个新计划。

    在第二个示例中，查询字符串保持不变，您通过 sp_executesql 将外部参数传递给查询字符串中定义的内部参数。

    正如您所展示的，第二种方法通常更有效

    • 3

相关问题