Início / dba / Perguntas / 270270
Accepted
Frederik Vanderhaegen
Asked: 2020-07-03 06:16:39 +0800 CST

Histograma mal formado causa estimativas ruins no loop aninhado

  • 772

Temos uma consulta em um SQL Server 2016 SP2 CU12 Enterprise em que o Query Optimizer estima que apenas 1 linha sairia do operador Nested Loops Join , na realidade 108501 linhas voltaram. Isso fez com que o Sortoperador vazasse para o TempDB.

As estimativas na entrada interna (busca de índice) e externa (busca de índice) da junção de loops aninhados estão corretas.

Adicionei os sinalizadores de rastreamento 2363 (Cálculo de Seletividade) e 3604 (redirecione a saída para a janela de mensagens) e aqui descobri que havia um histograma mal formado:

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

Acima é apenas uma parte da saída, o texto completo pode ser encontrado aqui

Quando atualizei o histograma mal formado com fullscan, as estimativas estão corretas (sem fullscan, esse problema não é resolvido).

Mas assim que um registro é inserido na tabela, o histograma está mal formado novamente.

O plano de consulta (com histograma mal formado) pode ser encontrado aqui e aqui você pode encontrar o plano de consulta após a atualização das estatísticas.

Nenhuma correção do otimizador de consulta está habilitada. Quando habilito o estimador de cardinalidade original para esta consulta, usando o sinalizador de rastreamento 9481, estou obtendo o mesmo plano de consulta após as estatísticas de atualização.

O que pode causar o histograma mal formado?

Existe uma maneira de resolver este problema?

Tentei a PERSIST_SAMPLE_PERCENTopção mas não fez diferença, o histograma também fica mal formado.

sql-server-2016 query-performance

1 respostas

  1. Best Answer

    O que pode causar o histograma mal formado?

    Isso é um bug, claro. Ocorre quando o SQL Server dimensiona o histograma inexato e encontra uma coluna exclusiva com aparentemente mais de um valor por chave. Isso foi parcialmente resolvido para o SQL Server 2016, mas parece-me que a correção completa está presente apenas no SQL Server 2017 (testei no CU21).

    Existe uma maneira de resolver este problema?

    Use FULLSCANestatísticas, o CE original, atualize para o SQL Server 2017 ou aguarde a portabilidade da correção completa.

    Demonstração

    Usando meu script de criação de tabela padrão do 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);

    Atualize as estatísticas a serem amostradas:

    UPDATE STATISTICS dbo.Numbers;

    Consulta trivial (solicitar apenas um plano estimado):

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

    A saída de rastreamento inclui:

    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

    Validado no Microsoft SQL Server 2016 (SP2-CU14).

    O histograma estatístico obtido via:

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

    tem entradas como:

    histograma de 2016

    no SQL Server 2017 o histograma é:

    histograma de 2017

    Observe que as EQ_ROWSetapas são pré-escalonadas em vez de depender do CE para fazê-lo.

    • 3

relate perguntas