主页 / dba / 问题 / 270270
Accepted
Frederik Vanderhaegen
Asked: 2020-07-03 06:16:39 +0800 CST

格式错误的直方图导致对嵌套循环的错误估计

  • 772

我们在 SQL Server 2016 SP2 CU12 Enterprise 上有一个查询，其中查询优化器估计只有 1 行会来自嵌套循环连接运算符，实际上有 108501 行返回。这导致Sort操作员溢出到 TempDB。

对 Nested Loops Join 的内部（索引搜索）和外部输入（索引搜索）的估计是正确的。

我添加了跟踪标志 2363（选择性计算）和 3604（将输出重定向到消息窗口），在这里我发现有一个格式错误的直方图：

Plan for computation:

  CSelCalcExpressionComparedToExpression( QCOL: [Object1].Column1 x_cmpEq QCOL: [Object3].Column18 )

Loaded histogram for column QCOL: [Object1].Column1 from stats with id 1  *** WARNING: badly-formed histogram ***

Loaded histogram for column QCOL: [Object3].Column18 from stats with id 9

Selectivity: 1.07973e-009

Stats collection generated: 

  CStCollJoin(ID=4, CARD=1 x_jtLeftSemi)

      CStCollBaseTable(ID=1, CARD=5.01133e+007 TBL: Schema1.Table2 AS TBL: AA)

      CStCollFilter(ID=3, CARD=108210)

          CStCollBaseTable(ID=2, CARD=2.00511e+006 TBL: Schema1.Table1 AS TBL: A)

End selectivity computation

以上只是部分输出，全文可以看这里

当我用全扫描更新格式错误的直方图时，估计是正确的（没有全扫描，这个问题没有解决）。

但是，只要在表中插入一条记录，直方图就会再次形成错误。

查询计划（带有错误直方图）可以在这里找到，在这里您可以找到更新统计信息后的查询计划。

未启用查询优化器修复。当我为此查询启用原始基数估计器时，使用跟踪标志 9481，我得到与更新统计信息后相同的查询计划。

什么会导致直方图格式不正确？

有没有办法解决这个问题？

我尝试了该PERSIST_SAMPLE_PERCENT选项，但没有任何区别，直方图的格式也很糟糕。

sql-server-2016 query-performance

1 个回答

  1. Best Answer

    什么会导致直方图格式不正确？

    这当然是一个错误。当 SQL Server 缩放不精确的直方图并遇到每个键显然具有多个值的唯一列时，就会发生这种情况。这在SQL Server 2016 中得到了部分解决，但在我看来，完整的修复只存在于 SQL Server 2017 上（我在 CU21 上测试过）。

    有没有办法解决这个问题？

    使用FULLSCAN统计信息，原始 CE，升级到 SQL Server 2017，或等待完整修复被反向移植。

    演示

    使用我的标准 Numbers 表创建脚本：

    DROP TABLE IF EXISTS dbo.Numbers;
GO
-- Itzik Ben-Gan's row generator
WITH
  L0   AS (SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
SELECT
    -- Destination column type integer NOT NULL
    ISNULL(CONVERT(integer, N.n), 0) AS n
INTO dbo.Numbers
FROM Nums AS N
WHERE N.n >= 1
AND N.n <= 10 * 1000 * 1000
OPTION (MAXDOP 1);
GO
ALTER TABLE dbo.Numbers
ADD CONSTRAINT PK_Numbers_n
PRIMARY KEY CLUSTERED (n)
WITH (SORT_IN_TEMPDB = ON, MAXDOP = 1, FILLFACTOR = 100);

    更新要采样的统计信息：

    UPDATE STATISTICS dbo.Numbers;

    琐碎查询（仅请求估计计划）：

    SELECT * 
FROM dbo.Numbers AS N1
WHERE EXISTS
(
    SELECT * 
    FROM dbo.Numbers AS N2
    WHERE N2.n = N1.n
)
OPTION (QUERYTRACEON 3604, QUERYTRACEON 2363);

    跟踪输出包括：

    Begin selectivity computation

Input tree:

  LogOp_LeftSemiJoin
      CStCollBaseTable(ID=1, CARD=1e+007 TBL: dbo.Numbers AS TBL: N1)
      CStCollBaseTable(ID=2, CARD=1e+007 TBL: dbo.Numbers AS TBL: N2)
      ScaOp_Comp x_cmpEq
          ScaOp_Identifier QCOL: [N2].n
          ScaOp_Identifier QCOL: [N1].n

Plan for computation:
  CSelCalcExpressionComparedToExpression( QCOL: [N1].n x_cmpEq QCOL: [N2].n )

Loaded histogram for column QCOL: [N1].n from stats with id 1
    *** WARNING: badly-formed histogram ***

Selectivity: 1

    在 Microsoft SQL Server 2016 (SP2-CU14) 上验证。

    通过以下方式获得的统计直方图：

    DBCC SHOW_STATISTICS 
    (Numbers, [PK_Numbers_n])
    WITH HISTOGRAM;

    有如下条目：

    2016年直方图

    在 SQL Server 2017 上，直方图为：

    2017年直方图

    请注意，这些EQ_ROWS步骤是预先调整的，而不是依靠 CE 来完成。

    • 3

相关问题