动态SQL - 长时间执行 - 仅首次运行

Question

我有一个存储过程，它根据输入参数构建动态SQL语句并执行。

其中一个查询导致超时，所以我决定检查一下。第一次执行出现问题的语句时很慢（30秒-45秒），但每次后续执行只需要1-2秒。

为了重现这个问题，我正在使用

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

我真的很困惑问题出在哪里，因为普通的 SQL 语句如果慢的话，一直都是慢的。现在，它只有第一次执行时才需要很长时间。

这可能是因为它本身很慢需要优化，还是因为其他原因导致的问题？

执行计划如下，但对我来说没有什么奇怪的地方：

- gotqn

---

清除缓存，然后使用SET STATISTICS IO ON运行查询；查看读取次数（特别是物理读取或预读取），然后在不清除缓存的情况下再运行一次并查看读取次数，如果之前有很多物理/预读取现在变成了逻辑读取，那么很可能SQL服务器只需要做大量IO来满足查询，在数据缓存在内存中后速度会更快。 - steoleary

@steoleary 你说得对。所有的物理读取都变成了逻辑读取。这就解释了为什么会出现这种情况。我想，唯一修复的方法就是尝试优化查询本身。 - gotqn

Answer 1

很简单，第一次执行需要更长时间，然后所有后续的执行都会相对较快。这个谜团背后的原因是“缓存执行计划”。

在处理存储过程时，SQL Server会执行以下步骤： 1）解析命令语法。 2）转换为查询树。 3）开发执行计划。 4）执行。

前两个步骤只在创建存储过程时发生。

第三个步骤只在第一次执行或者缓存计划已经从缓存内存中清除时发生。

第四个步骤在每次执行时都会发生，如果计划仍然在缓存内存中，则这是第一次执行后唯一发生的步骤。

在您的情况下，第一次执行需要很长时间，然后后面的执行就相对较快了。

为了重现“问题”，您执行了DBCC FREEPROCCACHE和DBCC DROPCLEANBUFFERS命令，这基本上刷新了缓存内存，并导致您的存储过程在下一次执行时创建一个新的执行计划。希望这能让您有所了解 :)

- M.Ali

---

是的，这很有道理，但在生产环境中，该过程执行了很多次，一切都正常。只有一个特定的输入会出现延迟。我正在使用“DBCC FREEPROCCACHE”来仅重现此问题。 - gotqn

除非你有非常非常充分的理由，否则请不要在生产服务器上执行这两个DBCC命令。 - M.Ali

那么在这种情况下，您需要查看具有减慢过程的特定输入的执行计划和普通输入，并查看计划的不同之处以及可以进行哪些更改以获得一致且高效的执行计划。 - M.Ali

好的，没问题。我正在本地备份上执行它们 :-) @steoleary 给我的建议解释了一切 - 第一次有很多物理读取，之后变成逻辑读取，所以问题在于查询本身。 - gotqn

Answer 2

从您对我的评论的回复中可以看出，第一次运行此查询时，它执行了很多物理读取或预读取，这意味着需要大量IO来将正确的页面读取到缓冲池中以满足此查询。

一旦页面被读入缓冲池（内存）中，它们通常会留在那里，以便不需要物理IO再次读取它们（您可以看到这是发生的，因为您指示第二次运行查询时物理读取转换为逻辑读取）。内存比磁盘IO快几个数量级，因此该查询的速度差异很大。

查看计划，我几乎可以看到每个读操作都是针对表的聚集索引完成的。由于聚集索引包含行的每个列，因此可能每行获取的数据比实际查询所需的数据更多。

除非您从每个表中选择每个列，否则我建议创建非聚集覆盖索引以满足此查询（尽可能窄），这将减少查询的IO需求并使其在第一轮中更少费用。

当然，这可能对您来说不可行，如果是这样，您应该只承受第一次运行的影响而不清空缓存，或者重写查询本身以更有效地执行并执行更少的读取。

- steoleary

Answer 3

一般情况下，当存储过程首次创建或其统计信息重置时，它将把传递给存储过程的第一个值作为存储过程的“默认”值。然后，它将尝试基于此进行优化。

为防止这种情况发生，您可以采取一些措施。您可以使用查询提示功能将特定变量标记为未知。例如，在存储过程末尾，您可以添加以下内容：

select * from foo where foo.bar = @myParam option (optimize for @myParam unknown)

作为另一种方法，您可以强制每次重新编译SQL计划-如果存储过程生成的SQL类型高度可变，则这可能是一个好主意。 您可以这样做：

select * from foo where foo.bar = @myParam option (optimize recompile)

希望这有所帮助。

- Winds of Change