ar.fId = ar.fId
产生的估计值为1行。但是,这在逻辑上是不一致的估计:ar
有20,608
行,而fId
只有一个不同的值(准确地反映在统计信息中)。因此,此联接产生行(~424MM
行)的完整叉积,导致查询运行几个小时。我很难理解为什么SQL Server会得出以下估计:可以很容易地证明与统计数据不一致。有任何想法吗?
初步调查和其他详细信息
基于Paul在这里的回答,似乎用于估计联接基数的SQL 2012和SQL 2014启发式方法都应该轻松应对情况在这里需要比较两个相同的直方图。
我从跟踪标志2363的输出开始,但无法轻松理解。下面的代码片段是否表示SQL Server正在比较
fId
和bId
的直方图,以便估计仅使用fId
的联接的选择性?如果是这样,那显然是不正确的。还是我误读了跟踪标志的输出?Plan for computation:
CSelCalcExpressionComparedToExpression( QCOL: [ar].fId x_cmpEq QCOL: [ar].fId )
Loaded histogram for column QCOL: [ar].bId from stats with id 3
Loaded histogram for column QCOL: [ar].fId from stats with id 1
Selectivity: 0
请注意,我想出了几种解决方法,这些方法都包含在完整的repro脚本中,并将此查询简化为毫秒。这个问题的重点是了解行为,如何在以后的查询中避免它,以及确定它是否应与Microsoft一起提交。
这里是完整的repro脚本,这里是完整的跟踪标志2363的输出,以下是查询和表定义,以防您想快速查看它们而无需打开完整的脚本:
WITH cte AS (
SELECT ar.fId,
ar.bId,
MIN(CONVERT(INT, ar.isT)) AS isT,
MAX(CONVERT(INT, tcr.isS)) AS isS
FROM #SQL2014MinMaxAggregateCardinalityBug_ar ar
LEFT OUTER JOIN #SQL2014MinMaxAggregateCardinalityBug_tcr tcr
ON tcr.rId = 508
AND tcr.fId = ar.fId
AND tcr.bId = ar.bId
GROUP BY ar.fId, ar.bId
)
SELECT s.fId, s.bId, s.isS, t.isS
FROM cte s
JOIN cte t
ON t.fId = s.fId
AND t.isT = 1
CREATE TABLE #SQL2014MinMaxAggregateCardinalityBug_ar (
fId INT NOT NULL,
bId INT NOT NULL,
isT BIT NOT NULL
PRIMARY KEY (fId, bId)
)
CREATE TABLE #SQL2014MinMaxAggregateCardinalityBug_tcr (
rId INT NOT NULL,
fId INT NOT NULL,
bId INT NOT NULL,
isS BIT NOT NULL
PRIMARY KEY (rId, fId, bId, isS)
)
#1 楼
我很难理解为什么SQL Server会提出一个容易被证明与统计数据不一致的估计。
一致性
没有通用的保证一致性。可以使用不同的统计方法在不同时间对不同(但在逻辑上等效)子树进行估算。
逻辑上没有错,即将这两个相同的子树连接起来应该产生叉积,但是存在同等的价值。没有什么可以说推理的选择比其他任何选择都更合理。
初始估计
在您的特定情况下,联接的初始基数估计不会在两个相同的子树上执行。当时的树形为:
LogOp_Join LogOp_GbAgg LogOp_LeftOuterJoin LogOp_Get TBL: ar LogOp_Select LogOp_Get TBL: tcr ScaOp_Comp x_cmpEq ScaOp_Identifier [tcr].rId ScaOp_Const Value=508 ScaOp_Logical x_lopAnd ScaOp_Comp x_cmpEq ScaOp_Identifier [ar].fId ScaOp_Identifier [tcr].fId ScaOp_Comp x_cmpEq ScaOp_Identifier [ar].bId ScaOp_Identifier [tcr].bId AncOp_PrjList AncOp_PrjEl Expr1003 ScaOp_AggFunc stopMax ScaOp_Convert int ScaOp_Identifier [tcr].isS LogOp_Select LogOp_GbAgg LogOp_LeftOuterJoin LogOp_Get TBL: ar LogOp_Select LogOp_Get TBL: tcr ScaOp_Comp x_cmpEq ScaOp_Identifier [tcr].rId ScaOp_Const Value=508 ScaOp_Logical x_lopAnd ScaOp_Comp x_cmpEq ScaOp_Identifier [ar].fId ScaOp_Identifier [tcr].fId ScaOp_Comp x_cmpEq ScaOp_Identifier [ar].bId ScaOp_Identifier [tcr].bId AncOp_PrjList AncOp_PrjEl Expr1006 ScaOp_AggFunc stopMin ScaOp_Convert int ScaOp_Identifier [ar].isT AncOp_PrjEl Expr1007 ScaOp_AggFunc stopMax ScaOp_Convert int ScaOp_Identifier [tcr].isS ScaOp_Comp x_cmpEq ScaOp_Identifier Expr1006 ScaOp_Const Value=1 ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [ar].fId ScaOp_Identifier QCOL: [ar].fId
第一个连接输入的未投影聚合已简化,第二个连接输入的谓词
t.isT = 1
推到其下方,其中t.isT
为MIN(CONVERT(INT, ar.isT))
。尽管如此,isT
谓词的选择性计算仍可以在直方图上使用CSelCalcColumnInInterval
:CSelCalcColumnInInterval Column: COL: Expr1006 Loaded histogram for column QCOL: [ar].isT from stats with id 3 Selectivity: 4.85248e-005 Stats collection generated: CStCollFilter(ID=11, CARD=1) CStCollGroupBy(ID=10, CARD=20608) CStCollOuterJoin(ID=9, CARD=20608 x_jtLeftOuter) CStCollBaseTable(ID=3, CARD=20608 TBL: ar) CStCollFilter(ID=8, CARD=1) CStCollBaseTable(ID=4, CARD=28 TBL: tcr)
(正确的)期望是该谓词将20,608行减少为1行。 >联接估计
现在的问题是,来自另一个联接输入的20,608行将如何与这一行匹配:
LogOp_Join CStCollGroupBy(ID=7, CARD=20608) CStCollOuterJoin(ID=6, CARD=20608 x_jtLeftOuter) ... CStCollFilter(ID=11, CARD=1) CStCollGroupBy(ID=10, CARD=20608) ... ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [ar].fId ScaOp_Identifier QCOL: [ar].fId
通常,有几种不同的估算联接的方法。例如,我们可以:在每个子树中的每个计划运算符处派生新的直方图,在连接处对齐它们(必要时插值步长),并查看它们如何匹配;或
对直方图进行更简单的“粗略”对齐(使用最小值和最大值,而不是逐步进行);或
单独为连接列计算单独的选择性(从基表中进行,无需任何过滤),然后添加非连接谓词的选择性效果。
...
根据使用的基数估计量和一些启发式方法,可以使用任何这些(或变体)。有关更多信息,请参见Microsoft白皮书《使用SQL Server 2014基数估计器优化查询计划》。
错误?
现在,如问题中所述,在这种情况下,“简单”单列联接(在
fId
)使用CSelCalcExpressionComparedToExpression
计算器:Plan for computation: CSelCalcExpressionComparedToExpression [ar].fId x_cmpEq [ar].fId Loaded histogram for column QCOL: [ar].bId from stats with id 2 Loaded histogram for column QCOL: [ar].fId from stats with id 1 Selectivity: 0
此计算评估将20608行与1个过滤后的行连接在一起将具有零选择性:没有行将匹配(在最终计划中报告为一行)。错了吗是的,这里的新CE可能存在错误。有人可能会争辩说1行将匹配所有行或不匹配任何行,因此结果可能是合理的,但我们有理由相信。
细节实际上是相当棘手的,但是对估计的期望是基于未过滤的通过过滤器的选择性修改的
fId
直方图给出了20608 * 20608 * 4.85248e-005 = 20608
行是非常合理的。进行此计算将意味着使用计算器
CSelCalcSimpleJoinWithDistinctCounts
而不是CSelCalcExpressionComparedToExpression
。没有记录的方法可以执行此操作,但是如果您感到好奇,可以启用未记录的跟踪标志9479:请注意,最终联接会从两个单行输入中产生20,608行,但不应这样做令人惊讶。这与原始CE根据TF 9481制定的计划相同。
我提到细节是棘手的(调查非常耗时),但据我所知,问题的根本原因与谓词
rId = 508
,选择性为零。该零估计值以通常的方式上升到一行,当它考虑输入树中的较低谓词时,这似乎有助于所讨论的连接处的零选择性估计(因此,bId
的加载统计信息)。允许外部联接保持零行内部估算(而不是提高到一行)(因此所有外部行都合格),可以使用任一计算器提供“无错误”的联接估算。如果您对此感兴趣,则未记录的跟踪标志为9473(单独):
还可以修改
CSelCalcExpressionComparedToExpression
的联接基数估计的行为,以不考虑bId
的另一个未记录的变化标志(9494)。我提到所有这些是因为我知道您对这些事情感兴趣。不是因为他们提供了解决方案。除非您将问题报告给Microsoft,然后由他们解决(或不解决),否则以不同的方式表达查询可能是最好的解决方法。不管行为是不是故意的,他们都应该对回归有兴趣。最后,要整理复制脚本中提到的另一件事:问题计划中Filter的最终位置是基于成本的探索的结果
GbAggAfterJoinSel
将聚合和过滤器移到联接上方,因为联接输出的行数很少。如您所料,该过滤器最初位于联接下方。