我在内联表值函数中有一个递归 CTE。ITVF 返回一个 ID 列表,其中包含一个人的一长串祖先序列,它通常会循环大约 12 到 18 次才能到达末尾。它的速度相当快,但在许多人使用时,估计会出现错误,因此它变得非常慢。
CTE 看起来像这样
WITH ancestors AS (
SELECT
IndID,
AncestorID
FROM
dbo.persons
UNION ALL
SELECT
IndID,
AncestorID
FROM
ancestors a
INNER JOIN dbo.persons p ON p.IndID = a.AncestorID
)
SELECT IndID, AncestorID FROM ancestors
我有一千万行,所以这是一个相当大的表。当我请求一个 IndID 时,执行计划说它估计有 7 行,但实际得到了 1300 行。对于单个请求来说,它是可以接受的(运行时间不到一秒),但如果我将它加入到另一个请求中,那么它就会被调用,比方说 100 次,速度就会下降,因为估计越来越差。
需要明确的是,即使在 IVTF 之外也存在估计误差。我只是为了清楚地表明我不能只使用临时表。它需要保留在 IVTF 中,以便我可以将其加入更大、更复杂的请求中,并且它保持可并行性。我可以做什么来更好地估计行数?
更新:粘贴计划
更新 2:不太简化
我有点陷入两个问题之间。要么我使用 MSTVF 并且所有查询都无法并行化,要么我使用 ITVF 并希望 SQL 之神慷慨,不要严重低估行数,因此现在所有内容都在硬盘驱动器而不是 RAM 上交换。我希望这只是我的愚蠢,而且这是一个愚蠢的简单解决办法。
更新 3 据我所知回答所提出的问题。
uno)更新到最新的累积更新。没有按预期改变任何内容,但正如您所说,保持最新状态是件好事:)
dos)我们使用标准版,但我确实有一个列存储,但我不记得为什么这样做。它位于 IndID、FirstNameID、LastNameID 上。我会尝试删除它,今天我们只有 2 个数据库用户,如果它崩溃了其他问题,我们可以管理停机时间。
去掉ColumnStore后,确实节省了大约30秒!虽然还是慢,但已经好多了。我必须检查我的笔记以找出为什么要使用该列存储。
dos:第2部分)你所感受到的“动力不足的盒子”的感觉正是让我到现在为止的原因。我以为我们的机器动力不足,但与这里的 IT 人员交谈后,他们说我们没有使用超过 25% 的可用资源,因此瓶颈肯定是在 SQL 级别。因此,我上个月要求将 SQL 2017 更新到 2022,然后,现在我发现我的大多数繁重查询总是以序列化方式运行,所以开始优化,直到我遇到了这一点。我尝试过OPTION(USE HINT('DISALLOW_BATCH_MODE'), MAXDOP 8);
,但没有看到速度有任何变化。
tres) 该请求确实应该返回大约 1400 万行,所以这方面不用担心。但是,资源预留中只估计了 8 行,这难道不是它比应有的速度慢得多的原因吗?
更多上下文)我在本月所有工作之前使用的是 MSTVF,当我切换到 IVTF 时,速度更快,但如果您明白我的意思,则花费的时间与请求的行数的曲线是指数而不是线性的。我愿意重新思考这一切是如何完成的。
我在一个研究小组工作,我的部分工作是为研究人员提取数据集。我几乎是数据库上唯一的重度用户,我的同事更多地从事工作的“插入和清理数据”部分。所以我几乎可以用索引、函数等做我想做的事,只要表结构本身不改变太多。
更新 4 - 什么? 我不明白,我试图制作一个漂亮的图形来显示“花费的时间与询问的行数”指数曲线,所以我更改了查询以获得漂亮的平方数。
select
count(*)
FROM
(SELECT TOP 10000 * FROM individus.Individus WHERE AnneeNaissance > 1901 AND AnneeDeces < 1911) i CROSS APPLY
individus.GetAscendanceSimple(i.IndID) a
它在 10 秒内运行...即使尝试了 TOP 10,000,000 并且仍然很快,所以我只需要输入一个任意大的数字,这样我的所有案例都被覆盖,并且它运行得像我希望的那样快(TOP 很重要)。在将其作为解决方案之前,我一定是错的不是吗?如果我们需要做的就是修复规划,那么这真是一个愚蠢的修复。
我越看这个,关于查询计划的问题就越多。这可能无法回答您第一次复飞时的问题,但让我们开始吧。
乌诺
首先,计划 XML 表明您使用的是 SQL Server 2022 RTM (16.0.1000.6),这使您比当前版本有11 个累积更新。我并不是把这归咎于你的问题,但了解最新情况也没什么坏处。
DOS
其次,也是最令人困惑的:您使用的是标准版还是企业版 SQL Server,或者
Individus
列是否在某处存储索引?我问的原因是因为优化器决定您的计划中的一个运算符应该以批处理模式运行:
缺少列存储索引,这种情况仅发生在具有智能查询处理功能(行存储上的批处理模式)的企业版中。
更令人困惑的是,您的计划仅以并行度 (DOP) 2 运行。将批处理模式操作限制为 DOP 2 是只有标准版才会做的事情。
通常情况下,我会将其归因于您使用的机器功能确实不足,但查询计划还表明您有 12 个线程可用于并行查询。也许 DOP 被限制在优化器不知道的地方的两个。
在上面提到的哈希匹配聚合的情况下,这变得很不幸,因为一个线程在 HTBUILD 上等待大约 22 秒。
以下是哈希匹配聚合的实际时间统计数据。请注意较长的持续时间(挂钟时间)与非常低的 CPU 时间。
您应该尝试的一件事是以 结束查询
OPTION(USE HINT('DISALLOW_BATCH_MODE'), MAXDOP 8);
。特雷斯
除此之外,您的查询还有其他一些非常奇怪的等待。
查询执行时,PAGELATCH_SH和PAGELATCH_EX合计等待时间约为 49 秒。以下是您的计划中在批量模式哈希匹配聚合之前的大部分操作时间:
我用箭头指向两个使用 tempdb 进行存储的运算符,因为有时(但并非总是如此)我刚才讨论的那些 PAGELATCH_XX 等待与那里的活动相关。据我所知,在递归公用表表达式执行计划中无法绕过这些线轴。
读取操作员时间有点奇怪。在行模式下,父操作员不仅显示其时间,还显示子操作员花费的时间。在批处理模式下,每个操作员仅显示其花费的时间。
我在这里提出潜在的 tempdb 争用的一个原因是,您的惰性索引假脱机变得相当大,大约有 1400 万行,并且由于使用数据加载它是唯一会生成 PAGELATCH_EX 等待的事情,因此值得检查。
好吧
话虽这么说,基数估计并不是问题的根源,并且使用 MSTVF(它将使用表变量)可能对您的 tempdb 情况或基数估计情况没有帮助。
虽然较新版本的 SQL Server中的表变量可以获得表级基数估计,但即使添加索引,它们仍然无法获得为其构建的任何列级直方图。每当在其中修改数据(在您的情况下,在插入时)以及当外部查询读取返回的表变量时,它们还会在您的计划中导致串行区域。
请编辑您的问题以添加阅读本文的任何详细信息。我会在时间允许的情况下尽力跟上,相应地编辑我的答案。
夸特罗
我没有您的数据库可供试验,但我可以使用我的 StackOverflow 数据库副本进行合理的尝试。
为了让事情顺利进行,我添加了几个索引:
还有一个与你类似的函数:
然后是一个起始查询,该查询会生成一个惰性假脱机,该假脱机获取大约 1400 万行并对其进行更改。我在这里有一些提示,以更好地匹配您的环境。
这个查询对我来说大约需要 1 分钟。部分问题在于递归公用表表达式通常不符合并行计划的条件,并且涉及嵌套循环连接的并行计划实际上只是运行串行计划的 DOP 副本。因此,它们对并行线程上不均匀的行分布非常敏感。
带有 TOP 的查询运行速度如此之快的原因是,Top 运算符在查询计划中引入了串行区域,这需要 Distribute Streams Exchange 运算符将行移回计划的并行部分。
我可以通过复制您的查询来复制该行为:
对我来说,时间上只有大约 30 秒的差异,但原因在于你和我的查询计划。以下是两个新示例查询计划中并行行分布的一些屏幕截图:
当然,这个问题在您共享的原始查询计划中并不明显,因为标准版中的批处理模式将您的 DOP 限制为 2。在完全行模式计划中,您可以使用更高的 DOP ,问题就清楚了。
您可以在Adam Machanic 的关于排球目标的课程中了解更多相关信息。
由于您已经统治了 ITVF 之外的所有内容,因此您永远无法从中获得良好的基数估计。根本无法知道在编译时您将获得多少行。ITVF 限制排除了任何有用的技巧,例如
OPTION (RECOMPILE)
临时表。也就是说,我看不出您有任何理由排除多语句表值函数。您是否考虑过使用表变量 和OPTION (RECOMPILE)
?框架挑战:停止使用递归 CTE 来完成此任务。Hierarchyid是现代的解决方案。更好的是,如果可以的话,完全避免使用 SQL。这是图数据库的工作。