主页 / dba / 问题 / 136736
Accepted
Shoeless
Asked: 2016-04-28 05:53:57 +0800 CST

存储过程的执行计划不一致

  • 772

我们有一个业务关键型存储过程，通常每天凌晨 2 点从生产数据库服务器上的计划作业（在 SSIS 包中）运行。15 分钟后，第二个作业从另一个服务器上运行的 SQL 代理作业调用相同的过程/程序包（作为紧急故障转移，以防第一个作业出现任何问题）。

该过程定义为 WITH RECOMPILE。

该过程通常在大约 45 秒内执行。上周三和今天早上（也是周三，巧合？！？），凌晨 2 点的程序执行了 90 分钟。在执行过程中，凌晨 2:15 的作业开始运行，执行时间通常为 45 秒。

我有两种情况的执行计划。有一些表变量过程应该包括 200K 行附近的估计行数。错误的计划报告了这些表变量，估计有 1300 亿行。[旁注：基于此讨论，我已经重写了代码以使用临时表而不是表变量，并将在不久的将来将其移至生产环境]

我们的监控软件 (Solar Winds DPA) 报告 CXPACKET 等待凌晨 2 点执行过多。这似乎表明并行性存在问题，并且可能与过程中使用的表变量有关。

在此期间服务器上仍有用户活动和一些预定作业，但我看不到任何会影响此过程或其执行计划的情况。索引维护作业在凌晨 2:30 运行。

我知道执行计划执行不佳与临时表有关，但为什么在 15 分钟后执行的相同过程会有如此截然不同的执行计划（为什么凌晨 2 点的执行在本周余下时间运行良好？）

以下是 .sqlplan 文件的链接：坏计划和好计划。

sql-server stored-procedures

1 个回答

  1. Best Answer

    对于“好”计划，所有表变量基数估计都是 1 行。这是使用表变量时最常见的结果，除非启用跟踪标志 2453，或者发生语句级重新编译（例如，因为OPTION (RECOMPILE)使用了，或者查询中的常规表之一已超过其重新编译阈值。

    对于“坏”计划，表变量基数是准确的，这意味着上述条件之一在起作用。这似乎违反直觉，因为更好的信息通常会导致更好的计划，但表变量不支持统计信息，因此额外的信息相当有限。优化器知道有“x”行，但不知道这些行中值的分布。也许是另一种不完整的信息，但仍然如此。

    无论如何，当假设表变量包含一行时构建的计划恰好产生了良好的性能。这不仅仅是一点点运气。除非你喜欢调试罕见的计划回归，否则我会避免过分依赖运气。

    具体细节

    错误的计划报告了这些表变量，估计有 1300 亿行。

    您所指的计划部分是：

    性能假脱机计划片段

    如您所见，表假脱机估计会产生约 1300 亿行；表变量仅发出 198,411。

    排序和假脱机组合旨在通过缓存嵌套循环连接的一次迭代的结果并在相关参数未更改的情况下在下一次迭代中重播保存的结果来优化重复扫描。排序确保任何潜在的重复项一起到达，因为假脱机仅缓存最近的结果。来自假脱机的估计是总行数（来自表变量的 198,411 * 653,969 次迭代）。

    将排序中的行与表变量相关联的有用谓词卡在嵌套循环左外连接迭代器上：

    连接谓词

    结合表变量的输出列来看这一点，我们可以得出结论，表变量PatientID, FirstTestDate上的索引几乎肯定会消除这个问题。

    对sub_PSTRules的分析可以删除那里看到的索引和表假脱机，尽管这些在现阶段对性能没有太大影响：

    索引假脱机片段

    然而，让 SQL Server 每次都建立一个临时的非聚集索引，然后在最后丢弃它是一种浪费。丢失的（过滤后的）索引可能是：

    CREATE INDEX give_me_a_good_name
ON dbo.sub_PSTRules
    (SubscriberSID, CinicSID, OfficeSID)
INCLUDE
    (PSTQuestionGroupSID)
WHERE
    OfficeSID IS NULL;
    • 4

相关问题