请考虑以下SQL Server 2014中的查询计划:在查询计划中,自联接ar.fId = ar.fId产生的估计值为1行。但是,这在逻辑上是不一致的估计:ar20,608行,而fId只有一个不同的值(准确地反映在统计信息中)。因此,此联接产生行(~424MM行)的完整叉积,导致查询运行几个小时。

我很难理解为什么SQL Server会得出以下估计:可以很容易地证明与统计数据不一致。有任何想法吗?

初步调查和其他详细信息

基于Paul在这里的回答,似乎用于估计联接基数的SQL 2012和SQL 2014启发式方法都应该轻松应对情况在这里需要比较两个相同的直方图。

我从跟踪标志2363的输出开始,但无法轻松理解。下面的代码片段是否表示SQL Server正在比较fIdbId的直方图,以便估计仅使用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.isTMIN(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将聚合和过滤器移到联接上方,因为联接输出的行数很少。如您所料,该过滤器最初位于联接下方。