存储过程在第一次运行时运行缓慢。

Question

我有一个每天运行并执行数十个存储过程的任务。

他们中的大多数都跑得很好，但其中有几个最近开始跑了一段时间(4-5分钟)。

当我早上来找他们时，他们只花了10-20秒，就像他们想的那样。

这种情况在过去10天左右一直在发生。没有对服务器进行任何更改(我们正在运行SQL 2012)。

我如何排除它，我能做些什么来解决这个问题？？

谢谢！！

Answer 1

您可以使用SQL提供的一些DMV(动态管理视图)来调查计划缓存。然而，结果可能有点吓人，没有一些背景，这可能是很难挖掘的结果。我建议调查一些DMV，如sys.dm_exec_query_stats和sys.dm_exec_cached_plans。来自SQLSkills.com的金伯利·特里普在Pluralsight上做了一些关于如何使用这些工具的很棒的课程，并通过在那些DMV上构建更高级的查询来获得一些令人敬畏的结果。

同样，这些DMV将返回一个plan_handle列，您可以将该列传递给另一个DMV，sys.dm_exec_query_plan(plan_handle)，以返回特定语句的执行计划。最困难的部分是挖掘dm_exec_cached_plans的结果，以找到引起问题的特定作业/存储过程。sys.dm_exec_sql_text(qs.[sql_handle])可以通过提供为该作业运行的SQL的快照来帮助您，但是您将从它中获得最大的好处(在我看来)，通过CROSS APPLY与我提到的其他一些DMV一起使用它。如果您能够识别Job/Proc/语句并查看计划，它可能会显示出Sean提到的参数嗅探问题的一些指示。

以防万一:参数嗅探是在运行查询/存储proc的第一个实例时，SQL查看传入的参数并基于它构建计划。从查询/proc的初始编译中生成的计划对于传入的特定参数是理想的，但对于其他参数可能不是理想的。想象一下一个高度倾斜的表格，其中所有的日期都是'01-01-2000'，除了一个'10-10-2015‘。

传递这两个参数将产生由于数据选择性而产生的非常不同的计划(读:数据有多独特？)如果其中一个计划被保存到缓存中，并在每次后续执行时调用，则可能(在某些情况下，很可能)对其他参数不太理想。

当您运行该命令时，您可能会看到该作业与您自己运行的命令在速度上的差异，因为当您运行它时，您正在运行Ad。作业不是，而是将它们作为存储过程运行，这意味着它们将使用不同的执行计划。

TL;DR:您为作业保存的执行计划没有优化。但是，当您手动运行它时，您可能会创建一个针对该特定运行优化的Ad计划。这是一条艰难的道路，深入计划缓存，看看发生了什么，但它是100%值得的。我强烈建议查阅金伯利特里普的博客，因为她有一些关于这方面的很棒的文章，也有一些关于Pluralsight的精彩课程。