Por que minha junção de Nested Loops está mostrando estimativas de linha imprecisas no SQL Server?

1. Como a contagem de linhas é estimada para uma junção de Nested Loops no SQL Server?

Não é diferente de um hash ou merge join. A exibição no SSMS geralmente parece diferente para loops aninhados porque o lado interno é por execução (ao olhar para um plano de pré-execução (estimado)).

Fundamentalmente, você está perguntando como o SQL Server estima a seletividade de um join . A seletividade lógica permanece a mesma, qualquer que seja a implementação física (loops aninhados, apply, hash, merge) escolhida.

A resposta ampla para essa pergunta é que o SQL Server pode usar uma de várias técnicas baseadas em informações de histograma ou frequência (valores distintos) para as colunas de predicado de junção.

Não há uma fórmula única para estimativas baseadas em histograma ou frequência. Há diversas variações de modelagem que podem entrar em jogo dependendo dos predicados envolvidos, das informações disponíveis e da configuração do banco de dados.

Os fatores de configuração mais importantes são qual modelo de estimativa de cardinalidade (CE) você está usando ('padrão' ou 'legado'), a configuração do nível de compatibilidade do banco de dados e se você tem hotfixes do otimizador de consulta habilitados. Essa não é uma lista completa.

A questão é excepcionalmente leve em detalhes — nem mesmo fornece os predicados de junção — então não vou nem especular sobre uma causa específica aqui.

2. Que fatores poderiam levar a uma estimativa de linha tão imprecisa neste caso?

As estimativas são calculadas com base em informações estatísticas usando suposições e algoritmos de modelagem proprietários do SQL Server. Dadas estatísticas representativas, a causa mais frequente de estimativas imprecisas é uma desconexão entre os dados e as suposições de modelagem do SQL Server.

3. Há algo que eu possa fazer para melhorar ou corrigir a estimativa?

Primeiro, garanta que as estatísticas estejam atualizadas e de boa qualidade. Não confie somente em estatísticas amostradas padrão. Você pode precisar de uma taxa de amostragem mais alta ou até mesmo FULLSCANfornecer informações precisas o suficiente. Você também pode precisar criar estatísticas ou índices adicionais, dependendo da natureza exata da junção.

Uma vez que isso esteja estabelecido, as principais coisas que você pode tentar são:

1. Use um modelo de estimativa de cardinalidade diferente

   FORCE_DEFAULT_CARDINALITY_ESTIMATION
   FORCE_LEGACY_CARDINALITY_ESTIMATION
   

2. Use variações de modelo documentadas

    ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS
    ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES
    ASSUME_FULL_INDEPENDENCE_FOR_FILTER_ESTIMATES
    ASSUME_PARTIAL_CORRELATION_FOR_FILTER_ESTIMATES
   

3. Use um nível de compatibilidade do otimizador diferente se as estimativas costumavam ser melhores

    QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n
   

Todas as opções acima são especificadas como uma USE HINTdica de consulta .

Recursos

• Perguntas e respostas existentes: Qual método/fórmula um operador de loops aninhados usa para estimativa de linha ?

Se você estiver realmente interessado no processo de seletividade de junção subjacente ou em como vários predicados são combinados:

• Otimizando seus planos de consulta com o estimador de cardinalidade do SQL Server 2014 - Microsoft
• Junte-se a Estimation Internals por Dmitry Piliugin
• Junte-se à estimativa de cardinalidade usando o alinhamento grosseiro do histograma por mim
• Estimativa de cardinalidade para múltiplos predicados por mim
• Estimativa de cardinalidade para predicados disjuntivos (OR) por mim

O SQL Server mantém estatísticas sobre cardinalidades de coluna e histogramas de distribuição de valor, então ele é capaz de estimar com algum nível de precisão quantas linhas podem corresponder a um predicado particular contra uma coluna, ou um conjunto de colunas, de uma única tabela. Ele não sabe, entretanto, quantos valores em uma coluna podem corresponder a um certo valor em uma coluna de uma tabela diferente , ou seja, ele não tem as estatísticas de correlação de coluna.

Um possível mecanismo para gerar e usar tais estatísticas é permitir a coleta de estatísticas em visualizações simples (não materializadas e não indexadas). Você criaria então uma visualização unindo suas duas (ou mais) tabelas, e o mecanismo calcularia um histograma dos valores de coluna correlacionados.

O SQL Server não parece ter a capacidade de manter estatísticas em exibições regulares, embora permita que você gere estatísticas em uma exibição indexada , o que se espera que possa produzir um efeito semelhante, então você pode tentar isso. Haverá, é claro, uma penalidade de desempenho por ter que manter o índice físico correspondente, além da utilização adicional de armazenamento.

Para o operador Nested Loops, o "estimado" é por linha da tabela externa, ou seja, por "loop", e o "real" é o total em todos os loops.

Veja, por exemplo: https://kendralittle.com/2016/09/06/estimated-vs-actual-number-of-rows-in-nested-loop-operators/