Início / dba / Perguntas / 306708
Accepted
SEarle1986
Asked: 2022-01-28 15:11:23 +0800 CST

Criação de bitmap no plano de execução causa estimativa ruim na verificação de índice clusterizado

  • 772

Dada a seguinte consulta simples no banco de dados StackOverflow2010:

SELECT  u.DisplayName,
        u.Reputation
FROM    Users u
        JOIN Posts p
            ON u.id = p.OwnerUserId
WHERE   u.DisplayName = 'alex' AND
        p.CreationDate >= '2010-01-01' AND
        p.CreationDate <= '2010-03-01'

Eu estava tentando entender por que criar um índice

CREATE INDEX IX_CreationDate ON Posts
(
    CreationDate
)
INCLUDE (OwnerUserId)

fornece uma estimativa melhorPosts.CreationDate

Quando executo a consulta sem índices, recebo Plan 1 . Nesse plano, o SQL Server estima 298.910 linhas saindo da varredura de CI em postagens e, na verdade, 552 retornam - essa estimativa está longe.

Depois de adicionar o índice, recebo o Plano 2 , o que causa uma busca de índice e uma estimativa muito mais precisa.

Fiquei curioso para saber por que adicionar um índice causaria uma estimativa melhor, já que as estatísticas são criadas quando uma coluna é usada em um WHEREpredicado, independentemente de estar indexada ou não.

Em uma inspeção mais aprofundada, posso ver que o predicado Posts.CreationDateé diferente no plano 1 versus o plano 2:

Plano 1 predicado

[StackOverflow2010].[dbo].[Posts].[CreationDate] as [p].[CreationDate]>='2010-01-01 00:00:00.000' AND [StackOverflow2010].[dbo].[Posts].[CreationDate] as [p].[CreationDate]<='2010-03-01 00:00:00.000' AND PROBE([Bitmap1002],[StackOverflow2010].[dbo].[Posts].[OwnerUserId] as [p].[OwnerUserId],N'[IN ROW]')

Plano 2 predicado

Seek Keys[1]: Start: [StackOverflow2010].[dbo].[Posts].CreationDate >= Scalar Operator('2010-01-01 00:00:00.000'), End: [StackOverflow2010].[dbo].[Posts].CreationDate <= Scalar Operator('2010-03-01 00:00:00.000')

Assim, posso ver que o Plano 2 usará apenas o histograma para encontrar o número de linhas entre as duas datas, mas o Plano 1 tem um predicado um pouco mais complicado envolvendo uma sonda de bitmap.

Isso (eu acho) explica por que a estimativa na busca é mais precisa, mas agora estou me perguntando o que é a sonda de bitmap? Eu posso ver no plano que há um bitmap criado dos IDs de usuário que correspondem ao predicado Alex e é isso que está sendo testado.

Eu me perguntei "sem o índice, por que o Plano 1 não seria o mesmo que o Plano 2, a única diferença sendo uma varredura de CI em vez de uma busca de índice em CreationDate?"

Fiz alguns testes adicionais e descobri que, se eu executar a consulta sem o índice, mas forçar o plano a ser serial, usando OPTION (MAXDOP 1)recebo o Plano 3 , que tem uma estimativa melhor em CreationDate, apesar de agora fazer uma CI Scan em Posts. Se eu olhar para o predicado, posso ver que o teste agora se foi e o bitmap não está mais no plano, então isso me leva a acreditar que o bitmap tem algo a ver com o plano paralelo.

Então, minha pergunta é - por que um bitmap é criado quando o plano é paralelo e por que causa uma estimativa tão ruim Posts.CreationDate?

sql-server execution-plan

1 respostas

  1. Best Answer

    Vários fatores em jogo:

    O índice vem com estatísticas de varredura completas. Os criados automaticamente foram amostrados.

    Diferentes modelos de estimativa de cardinalidade e modos de execução tratam o cálculo de forma diferente. Você pode estar mais feliz com a estimativa usando o modelo CE original neste caso:

    USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')

    Bitmaps só aparecem em planos paralelos de modo de linha . Eu escrevi sobre os detalhes no Bitmap Magic (ou… como o SQL Server usa filtros de bitmap)

    Bitmaps são possíveis em planos de modo de lote serial e paralelo. Você tem seu banco de dados definido para o modo de compatibilidade 130, portanto , o modo de lote no rowstore não está disponível para você. Observação lateral: talvez você queira aplicar a CU mais recente para 2019 - você ainda está no RTM.

    A fórmula de estimativa varia, mas geralmente tem suas raízes na estimativa de uma semijunção entre as linhas filtradas no lado de compilação da junção de hash e a tabela de destino usando histogramas. Às vezes é um palpite . Às vezes, o bitmap não é considerado:

    Nos planos de modo de linha paralela, existem dois tipos de bitmap . O tipo original de bitmap foi adicionado heuristicamente após a conclusão da otimização da consulta. Como não está presente durante a otimização, não tem efeito nas estimativas de cardinalidade. Esses bitmaps são nomeados Bitmapxxxx. O seu é um destes:

    Bitmap

    É mais difícil ver porque o efeito do bitmap está misturado com o predicado CreationDate . Podemos separá-los com o sinalizador de rastreamento não documentado 9130:

    SELECT
    U.DisplayName,
    U.Reputation
FROM dbo.Users AS U
JOIN dbo.Posts AS P
    ON U.id = P.OwnerUserId
WHERE
    U.DisplayName = N'alex' 
    AND P.CreationDate >= CONVERT(datetime, '2010-01-01', 121)
    AND P.CreationDate <= CONVERT(datetime, '2010-03-01', 121)
OPTION 
(
    USE HINT ('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_130'),
    QUERYTRACEON 9130
);

    O bitmap ainda é aplicado em linha na varredura, mas o predicado em CreationDate está em um operador Filter posterior:

    Estimativa de digitalização com TF 9130

    Estimativa de filtro com TF 9130

    A estimativa na varredura é a cardinalidade completa da tabela base, apesar do bitmap ainda ser aplicado lá:

    Bitmap aplicado na verificação

    Se estiver interessado em ver um plano sem o bitmap para comparar estimativas, você pode habilitar o sinalizador de rastreamento não documentado 9498.

    O segundo tipo de bitmap de modo de linha é o chamado bitmap otimizado . Eles são avaliados como parte da otimização baseada em custo, portanto, afetam as estimativas de cardinalidade e a forma final do plano. Esses bitmaps são nomeados Opt_Bitmapxxx.

    Eu escrevi sobre os detalhes do modo de lote em Bitmaps de modo de lote no SQL Server .

    • 9

relate perguntas