Eu tenho uma consulta que une algumas tabelas e tem um desempenho muito ruim - as estimativas de linha estão muito (1000 vezes) erradas e a junção de loops aninhados é escolhida, resultando em várias varreduras de tabela. A forma da consulta é bastante simples, parecendo algo assim:
SELECT t1.id
FROM t1
INNER JOIN t2 ON t1.id = t2.t1_id
LEFT OUTER JOIN t3 ON t2.id = t3.t2_id
LEFT OUTER JOIN t4 ON t3.t4_id = t4.id
WHERE t4.id = some_GUID
Brincando com a consulta, notei que, quando sugiro usar uma junção de mesclagem para uma das junções, ela é executada muito mais rápido. Isso eu posso entender - Merge join é uma opção melhor para os dados que são unidos, mas o SQL Server simplesmente não estima corretamente escolhendo os Nested Loops.
O que não entendo completamente é por que essa dica de junção altera todas as estimativas para todas as operadoras de planos? Ao ler diferentes artigos e livros, presumi que as estimativas de cardinalidade são executadas antes de o plano ser criado, portanto, usar uma dica não alteraria as estimativas, mas diria explicitamente ao SQL Server para usar uma implementação de junção física específica.
O que vejo, no entanto, é que a sugestão de mesclagem faz com que todas as estimativas se tornem praticamente perfeitas. Por que isso acontece e existem técnicas comuns para fazer o otimizador de consulta fazer uma estimativa melhor sem uma dica - considerando que as estatísticas obviamente permitem isso?
UPD: planos de execução anônimos podem ser encontrados aqui: https://www.dropbox.com/s/hchfuru35qqj89s/merge_join.sqlplan?dl=0 https://www.dropbox.com/s/38sjtv0t7vjjfdp/no_hints_join.sqlplan?dl =0
Eu verifiquei as estatísticas usadas por ambas as consultas usando TF 3604, 9292 e 9204, e elas são idênticas. No entanto, os índices que são verificados/buscados diferem entre as consultas.
Além disso, tentei executar a consulta com OPTION (FORCE ORDER)- ela é ainda mais rápida do que usar merge join, escolhendo HASH MATCH para cada junção.
Não exatamente. Uma estimativa de cardinalidade inicial é derivada (após simplificações e outros trabalhos), o que influencia a ordem de junção inicial escolhida pelo otimizador.
No entanto, explorações subseqüentes (durante a otimização baseada em custo) podem resultar, e geralmente o fazem, no cálculo de novas estimativas de cardinalidade. Esses CEs posteriores podem ser mais ou menos 'precisos'. Se o resultado for subestimado, o otimizador pode escolher um plano que pareça mais barato, mas que na verdade funcione por muito mais tempo.
Em geral, não há garantia de que as estimativas de cardinalidade para subárvores semanticamente idênticas produzirão os mesmos resultados. Afinal, é um processo estatístico, e algumas operações têm um suporte de CE mais profundo do que outras.
No seu caso, parece haver outro fator - o otimizador introduz (ou move) um Top, que define um objetivo de linha na subárvore abaixo dele:
Se você habilitar o sinalizador de rastreamento 4138 (no 2008 R2 ou posterior), poderá encontrar as estimativas mais alinhadas com as expectativas ou talvez até mesmo que o otimizador não escolha mais loops aninhados.
Há um elemento de sorte envolvido aqui. As pessoas tendem a escrever consultas, ou pelo menos as junções, na ordem em que esperam que sejam executadas fisicamente. O uso de uma dica de junção vem com um implícito
FORCE ORDER, corrigindo assim a ordem de junção para corresponder à forma textual e desativando muitas regras de exploração do otimizador que podem levar à reestimativa de cardinalidade.Isso é o mesmo que sugerir uma junção, mas não restringe a escolha do operador de junção física. Novamente, se você escrever a ordem de junção da consulta de maneira lógica, é bem provável que obtenha um plano razoável. Claro, você perde muitas das habilidades do otimizador dessa maneira, o que pode não produzir resultados ideais em situações mais gerais.
Você provavelmente não vai querer usar com
FORCE ORDERmuita frequência porque é uma dica (diretiva) extremamente poderosa que tem efeitos mais amplos do que simplesmente forçar a ordem das junções; por exemplo, evita que o otimizador mova agregações e introduza agregações parciais. Eu desaconselho o uso dessa dica, exceto em circunstâncias muito excepcionais e por sintonizadores verdadeiramente experientes .Uma análise detalhada exigiria mais tempo do que tenho agora e acesso a uma cópia do banco de dados apenas com estatísticas.
O where nega a esquerda
Por que dificultar o otimizador?
Em 3 ou mais uniões, o otimizador tenderá a ficar na defensiva e entrar em loops, pois isso protege a memória
An ou condição na junção, ele também tenderá a entrar em uma junção de loop - tenho evidências concretas de que isso acontecerá sempre - não - ainda é uma realidade
Com várias junções, puxe as condições de onde para a junção quando você puder
Ou melhor ainda - aposto que isso vai atender ou vencer suas dicas ou força
O problema com as dicas é que elas são para dados em um estado específico. Escreva uma consulta limpa e deixe o otimizador fazer seu trabalho. Algumas vezes, só precisa de mais estatísticas para fazer a coisa certa, mas então travará.
Por que estimativas diferentes. Um planos diferentes. Comece com consultas que dão ao otimizador uma chance de lutar.