主页 / dba / 问题 / 302480
Accepted
Aleksey Vitsko
Asked: 2021-11-12 07:36:25 +0800 CST

查询无限期运行 - 根本原因是什么？（不问如何解决）

  • 772

我有一个简单的分组查询，效果很好，直到我再添加一个联接：

select 
   [ca].Value,
   [c].cID, 
   [c].Name

from ReportingDB..Table1 [t1]

join MainDB..Companies [c] on
    [t1].CompanyID = [c].cID
    and [c].cID not in (1)

join MainDB..CompanyAttributes [ca] on -- this is the join that causes trouble
    [t1].CompanyID = caCID
    and caAttr = 26

group by [ca].Value, [c].cID, [c].Name

信息：

Companies 表是一个“查找”表，有 2254 行，cID是 PK
CompanyAttributes与多对一关系Companies并有 4055 行
Table1与表有多对一关系Companies，有 3,485,150 行和

估计执行计划看起来并不异常。

  • 当我尝试运行查询时，它没有完成，1 小时后我停止它，所以看不到实际执行计划发生了什么
  • 实时查询统计使我的 SSMS 挂起
  • 如果删除“group by”子句，它会非常快地开始毫无问题地获取行。或者当最后一个连接被删除时，它也可以正常工作 - 使用分组
  • 服务器不忙，有足够的资源，启动查询时我没有看到明显的 CPU 上升
  • 看sys.dm_exec_requests，wait_type 为 NULL，cpu_time并且logical_reads继续增长，对于运行查询的会话

原始查询运行超过 1 小时而未完成的根本原因是什么？

我自己解决了性能瓶颈（请参阅我的答案），但不明白究竟是什么导致原始查询运行 1 小时并且没有在体面的服务器上完成，查询的表并不大。预计原始查询将在不到 1 分钟的时间内完成。

sql-server query-performance

3 个回答

  1. Best Answer

    显示的估计执行计划的主要问题是 Table1 的聚集索引扫描上方的 Top 运算符。扫描有一个残差谓词：

    [ReportingDB].[dbo].[Table1].[CompanyID]=[MainDB].[dbo].[CompanyAttributes].[cacID]

    优化器尝试估计在通过该测试之前需要从扫描中读取多少行。它使用的逻辑是通用的，在我看来并不是特别合理。特别是，如果没有匹配，扫描将运行完成，检查所有 3,514,200 行。

    更重要的是，此扫描将对公司属性扫描返回的每一行重复，乘以公司搜索返回的行数。这就是嵌套循环连接的工作方式。

    优化器对在 Table1 的每次扫描中查找匹配项非常乐观。这导致该计划形状具有所考虑的替代方案的最低估计成本。其根本原因是 Top 运算符引入的行目标。

    如果您对 Top 的来源（不在您的查询文本中）感到好奇，请查看与我密切相关的文章Row Goals, Part 4: The Anti Join Anti Pattern。

    简而言之：优化器引入了本地（部分）聚合作为其计划搜索的一部分。该聚合结果在逻辑上是多余的，并被替换为等效的 Top。Top 的一个不幸的副作用是引入了行目标，从而显着降低了扫描的估计成本。

    带有残差谓词的扫描上方的顶部（尤其是在重复扫描的情况下）是非常需要注意的反模式。

    • 4

  2. 我相信您遇到的情况类似于我最近在DBA.StackExchange 问题中提到的几次经历。我绝不是正在发生的事情的专家，但会尝试总结我的理解。

    当您将两个表连接在一起时，SQL 引擎会利用数据统计信息（基于谓词中的字段）来确定实际将数据连接在一起时使用的最有效操作。这在很大程度上取决于这些谓词的基数，换句话说，它们预计会返回多少数据。

    SQL 引擎可以选择使用三种主要操作（内部连接类型），具体取决于它希望您的连接返回多少行：嵌套循环连接、合并连接和哈希连接。还有一个内部指标，通常称为“临界点”，它是 SQL 引擎使用的截止点（基于基数）来确定何时使用前面提到的连接操作之一来为您的查询提供服务。

    嵌套循环连接通常在将两个小数据集连接在一起时最有效，而哈希连接在将大数据集连接在一起时更有效。（当一个比另一个性能更高时，还有一些其他特征，例如数据是否已经在连接谓词上排序。）从过程编程语言的角度来看，将嵌套循环视为正在处理的外部循环它内部的另一个内部循环并将内部循环的每个值与外部循环的当前值进行比较，而 Hash Joins 执行顾名思义，并对谓词的数据进行散列以在加入时进行散列查找。

    您可能遇到的问题（如果类似于我上面链接的问题中的问题）是，由于某种原因，SQL 引擎认为您的数据没有超过临界点阈值，该阈值保证它从嵌套循环连接操作升级到合并或哈希联接操作。我的猜测是通过使用连接提示来强制执行哈希连接，因为您Table1有将近 350 万行，并且您估计的执行计划在最终连接到CompanyAttributes.

    我想当您执行您提到的有助于提高性能的少数事情之一时，如果您分析实际的执行计划，您可能会注意到特定的嵌套循环操作现在已替换为合并或哈希联接。

    Erik 提到的“行目标”问题让我找到了一些您可能会感兴趣的相关资源：

    1. 行目标流氓 - 巴特邓肯- 这讨论了为什么嵌套循环连接可能比哈希连接更受青睐（以满足行目标）的原因。

    2. 在执行计划中设置和识别行目标 - Paul White - 更深入地研究行目标，什么可以触发它们，并进一步讨论了嵌套循环优于散列连接的原因。

    当我有更多时间时，我会尝试进一步详细说明和改进这个答案，但我想最后提一下，连接提示通常不是一个理想的解决方案，实际上应该只在没有其他选择的情况下使用完成性能调整。但它们肯定有助于尝试调试性能问题的原因，例如您当前遇到的问题。

    • 2

  3. 发现了三种解决此性能瓶颈的方法，并在 1 秒内完成查询：

    1. 只需在 select 子句中将 [c].cID 替换为 [t1].CompanyID，无需创建索引

    或者

    1. 在 Table1 上的 [t1].CompanyID 上添加非聚集索引 - 正如 Erik 建议的那样。并且无需替换 select 子句中的列

    或者

    1. 将您对 CompanyAttributes 的联接更改为内部哈希联接 MainDB..CompanyAttributes [ca]（感谢 JD）

    我不明白的是为什么原始查询（以及没有 NC 索引时）没有完成。
    好的，我希望它会进行聚集索引扫描，并在几分钟或更短的时间内完成，Table1 不是超级大（350 万行）。

    但它运行 1 小时，没有完成。因此，如果有人可以解释原始查询运行超过 1 小时且未完成的根本原因，我将接受其他答案

    • 1

相关问题