Como o SQL Server estima a cardinalidade na busca de índice de loops aninhados

como o servidor SQL apresenta a estimativa na busca do índice? Ele não pode saber a reputação de accountId 22547 no tempo de compilação, pois a estatística de ID da conta não mostra isso, portanto, não pode realizar uma pesquisa no histograma para IX_PostId.

Nesse caso específico, o SQL Server não deriva uma estimativa de cardinalidade para a busca de índice na tabela Comments porque não precisa . Deixe-me explicar um pouco essa afirmação:

O processo de compilação sempre envolve uma rodada inicial de estimativa de cardinalidade, onde as estimativas são derivadas com base na forma inicial da representação lógica da consulta após a simplificação. A árvore lógica em seu exemplo é:

LogOp_Join
    LogOp_Select
    LogOp_Get TBL: dbo.Users(alias TBL: U)
        ScaOp_Comp x_cmpEq
            ScaOp_Identifier [U].AccountId
            ScaOp_Const Value=22547
    LogOp_Get TBL: dbo.Comments(alias TBL: C)
        ScaOp_Comp x_cmpEq
        ScaOp_Identifier QCOL: [C].PostId
        ScaOp_Identifier QCOL: [U].Reputation

Duas estimativas iniciais são necessárias:

1. Filtro Users.AccountId = 22547 ( LogOp_Select)
2. Junção interna em Comments.PostId = Users.Reputation ( LogOp_Join)

Deixando de lado os detalhes desses cálculos por um momento, o fato é que o SQL Server deriva uma seletividade estimada (e cardinalidade) para ambos, de alguma forma. Digamos que a estimativa de cardinalidade após o filtro seja C ₁ e a após a junção seja C ₂ .

Durante a otimização baseada em custo posterior, o SQL Server considera diferentes maneiras de implementar a junção. Por exemplo, pode derivar custos estimados para uma junção de mesclagem , junção de hash , junção de loops aninhados ou aplicação (junção de loops correlacionados).

Quando se trata de considerar um Apply (usando uma regra como JNtoIdxLookup), o otimizador já tem uma estimativa para a entrada superior da junção, C ₁ . Também já conhece a cardinalidade do resultado da junção, C ₂ . Digamos que C ₁ seja 10 e C ₂ seja 250 para fins de argumentação.

Não há necessidade de produzir uma nova estimativa para a entrada inferior do Apply . Sabemos que será executado 10 vezes (uma vez por linha de entrada superior) e a junção produzirá 250 linhas no total. Cada iteração da entrada inferior, portanto, precisa produzir 25 linhas para fazer a soma dos números, 10 * 25 = 250.

A resposta simples para sua pergunta é que, neste caso , o otimizador não produz uma estimativa de cardinalidade para a busca de índice - ela é derivada diretamente de estimativas de junção e filtro já existentes, por uma regra que considera a implementação de uma junção lógica como um Apply com pesquisa de índice .

Outros detalhes

Não há como fugir do fato de que os cálculos de seletividade podem ser extremamente complexos. Eu dei a você uma resposta simples acima porque isso parece responder à sua pergunta. Outros podem querer um pouco mais de detalhes.

Não posso descrever toda a estrutura de estimativa aqui porque não conheço todos os detalhes e, mesmo que conhecesse, seriam necessários vários livros para cobrir. Dito isso, há algumas coisas que vale a pena dizer e alguns outros recursos para vincular ao leitor interessado.

AccountId no banco de dados de exemplo Stack Overflow é uma chave não declarada — é exclusiva por usuário. Essas informações devem ser impostas e comunicadas ao otimizador com uma restrição ou índice exclusivo.

As estatísticas amostradas são úteis para evitar tempos de compilação excessivos, mas podem apresentar uma imagem enganosa. A menos que você esteja procurando entender o mundo altamente envolvido da amostragem estatística, você deve criar ou atualizar as estatísticas com uma varredura completa para obter resultados repetíveis e de alta qualidade.

O SQL Server tenta transformar subconsultas e aplica-se a junções antes da estimativa inicial. Isso nem sempre é possível, portanto, há ocasiões em que uma estimativa para uma aplicação (possivelmente com uma busca de índice interno) é derivada diretamente. Isso geralmente é modelado como uma série de pesquisas de ponto. Se você reescrever sua consulta para um formulário de aplicação que não pode ser transformado pelo SQL Server em uma junção, obterá estimativas diferentes usando um método diferente. Esta é apenas a natureza da besta.

SELECT
    U.DisplayName,
    C.PostId,
    C.[Text]
FROM dbo.Users AS U
CROSS APPLY
(
    SELECT
        C1.PostId,
        C1.[Text]
    FROM dbo.Comments AS C1
    WHERE
        C1.PostId = U.Reputation
) AS C
WHERE
    U.AccountId = 22547
OPTION 
(
    -- Don't transform the APPLY to a join
    QUERYTRACEON 9114
);

Derivar estimativas é um processo caro, então o SQL Server procura evitá-lo sempre que possível. Em geral, não há razão específica para favorecer uma estimativa em detrimento de outra. É perfeitamente possível chegar a 'n' estimativas diferentes usando 'n' métodos diferentes (mas igualmente lógicos). Planos de execução complexos às vezes contêm estimativas aparentemente contraditórias porque partes diferentes da árvore usaram abordagens diferentes em momentos diferentes. Novamente, este é o caminho das coisas.

O SQL Server pode não derivar uma nova estimativa para o lado interno da aplicação em seu exemplo, mas executa alguns cálculos relacionados para estimar o custo da busca do lado interno e quantas vezes ele seria rebobinado ou rebobinado se um carretel fosse introduzido. Cálculos semelhantes são executados ao estimar a economia de custos para uma sugestão de 'índice ausente'.

Perguntas e respostas relacionadas e leitura adicional (por mim, salvo indicação em contrário):

• Qual método/fórmula um operador de loops aninhados usa para estimativa de linha?
• Como o otimizador do SQL Server estima o número de linhas em uma tabela unida?
• Aplicar problema de estimativa de cardinalidade no SQL Server
• Internos de estimativa de junção no SQL Server por Dmitry Piliugin
• Assunção de contenção de junção e variação de modelo CE no SQL Server por Dmitry Piliugin
• Estimativa de união do SQL Server usando o alinhamento grosso do histograma
• Estimativa de Cardinalidade: Combinando Estatísticas de Densidade
• Estimativa de Cardinalidade para um Predicado em uma Expressão COUNT
• Otimizando seus planos de consulta com o estimador de cardinalidade do SQL Server 2014 por Joseph Sack
• Estimativa de Cardinalidade para Predicados Múltiplos
• Estimativa de Cardinalidade para Predicados Disjuntivos (OR) no SQL Server 2014 em diante
• A suposição de contenção de junção no novo estimador de cardinalidade degrada o desempenho da consulta (Microsoft)