sql-server – SQL Server 2014：对不一致的自加入基数估计的任

发布时间：2020-12-12 06:15:07 所属栏目：MsSql教程来源：网络整理

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

请考虑SQL Server 2014中的以下查询计划：

在查询计划中,自联接ar.fId = ar.fId产生的估计为1行.但是,这是一个逻辑上不一致的估计：ar有20,608行,只有一个不同的fId值(准确反映在统计中).因此,此连接生成行的完整交叉积(~424MM行),导致查询运行几个小时.

我很难理解为什么SQL Server会提出一个可以很容易地证明与统计数据不一致的估计.有任何想法吗？

初步调查和其他细节

基于Paul的answer here,似乎用于估计连接基数的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提交的错误.

这是一个full repro script,这里是full output from trace flag 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,isS BIT NOT NULL
    PRIMARY KEY (rId,fId,bId,isS)
)

解决方法

I am having a hard time understanding why SQL Server would come up with an estimate that can be so easily proven to be inconsistent with the statistics.

一致性

一致性没有一般保证.可以使用不同的统计方法在不同时间在不同(但逻辑上等效)的子树上计算估计值.

加入这两个相同的子树应该产生一个交叉产品的逻辑没有任何问题,但同样没有什么可以说推理的选择比任何其他更合理.

初步估计

在您的特定情况下,连接的初始基数估计不会在两个相同的子树上执行.那时的树形是：

  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白皮书Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator.

错误？

现在,如问题中所述,在这种情况下,“简单”单列连接(在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

此计算评估将20,608行与1个已过滤的行连接将具有零选择性：没有行匹配(在最终计划中报告为一行).这是错的吗？是的,可能这里新CE中存在一个错误.有人可能会争辩说,1行将匹配所有行或没有,所以结果可能是合理的,但有理由相信.

细节实际上相当棘手,但是估计的期望是基于未经过滤的fId直方图,由滤波器的选择性修改,给出20608 * 20608 * 4.85248e-005 = 20608行是非常合理的.

计算之后意味着使用计算器CSelCalcSimpleJoinWithDistinctCounts而不是CSelCalcExpressionComparedToExpression.没有记录的方法可以做到这一点,但如果你很好奇,你可以启用未记录的跟踪标志9479：

请注意,最终连接从两个单行输入生成20,但这不应该是一个惊喜.这是原始CE根据TF 9481制定的相同计划.

我提到细节很棘手(并且需要花费很长时间来研究),但据我所知,问题的根本原因与谓词rId = 508有关,选择性为零.这个零估计以正常方式提升到一行,当它在输入树中考虑较低谓词时(因此加载bId的统计数据),这似乎有助于所讨论的连接处的零选择性估计.

允许外连接保持零行内侧估计(而不是提高到一行)(因此所有外行都符合条件)使用任一计算器给出“无错误”连接估计.如果您有兴趣探索这个,那么未记录的跟踪标志是9473(单独)：

使用CSelCalcExpressionComparedToExpression的连接基数估计的行为也可以被修改为不考虑具有另一个未记录的变化标志的“bId”(9494).我提到所有这些因为我知道你对这些事情感兴趣;不是因为他们提供了解决方案.在您向Microsoft报告此问题之前,他们会对此进行解决(或不解决),以不同的方式表达查询可能是最好的方法.无论行为是否有意,他们都应该有兴趣了解回归.

最后,整理再现脚本中提到的另一件事：过滤器在问题计划中的最终位置是基于成本的探索的结果GbAggAfterJoinSel将聚合和过滤器移动到连接之上,因为连接输出具有这样的结果行数不多.过滤器最初位于连接下方,如您所料.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!