O Query Optimizer cria mais de um plano de execução possível. Como posso ver todos os planos gerados antes da escolha do executado?
O Otimizador de Consultas deve analisar os planos possíveis e escolher aquele com o menor custo estimado.
Por favor, note que não estou falando de Execution Plan Caching e Reuse , estou falando de planos de execução candidatos, aqueles que foram gerados mas não selecionados para serem executados.
De acordo com o artigo de Benjamin Nevarez The SQL Server Query Optimizer :
A geração de planos de execução candidatos é realizada dentro do Query Optimizer usando regras de transformação, e o uso de heurísticas limita o número de escolhas consideradas para manter o tempo de otimização razoável. Os planos candidatos são armazenados na memória durante a otimização, em um componente chamado Memo.
Seria possível visualizar os planos candidatos renderizados da forma como podemos exibir um plano de execução real ?
O otimizador tem heurísticas para reduzir o tempo de busca descartando planos e planos parciais antes que eles sejam totalmente explorados se acreditar que eles são piores do que as melhores soluções atuais que viu até agora na busca. Portanto, na verdade, ele não gera totalmente todas as alternativas. Além disso, a representação XML é realmente algo que é gerado no final da pesquisa - a representação interna é um pouco diferente. Infelizmente, não há nenhum recurso público atual para você explorar outros planos possíveis para a mesma consulta. Com um depurador podemos ver a estrutura que contém tudo isso durante o processo de otimização. Chama-se Memorando. Você pode ler mais sobre a estrutura Cascades na qual o otimizador do SQL é baseado aqui
Até onde sei, não há como ver o plano de execução gráfico/XML para planos candidatos que acabaram sendo descartados. Uma abordagem, se você esperava uma determinada forma de plano, é usar dicas e comparar os custos para ver por que o plano final foi escolhido em vez do plano sugerido.
Por exemplo, você pode esperar que um determinado índice não clusterizado seja usado para uma parte de uma consulta, mas o plano final verifica o índice clusterizado. Adicionando uma
WITH (INDEX (IX_Your_Index))dica de tabela à consulta, você pode mostrar como as coisas ficam com esse índice. Existem muitas dicas que podem ser usadas para esse fim. Você pode ver muitas opções com índices de dicas e junções.Deixando de lado os planos de execução completos, existem maneiras de ver mais detalhes sobre o processo de otimização, como uma consulta é transformada pelo otimizador e diferentes fragmentos de planos que são considerados e escolhidos ou descartados. A série Query Optimizer Deep Dive de Paul White entra em detalhes sobre isso.
Se você quiser visualizar como a "árvore lógica" de uma consulta é transformada e simplificada (como discutido na parte 1 da série de Paul), existe uma ferramenta gratuita para isso: Visualizador de árvore de consulta do SQL Server
A parte 3 da série fala sobre planos "intermediários" e como visualizar algumas informações sobre eles por meio das estruturas de dados de memorando do otimizador.
Eu sei que nada disso é particularmente direto, mas espero que ajude no seu objetivo de entender por que alguns planos podem ser descartados em detrimento de outros! E, independentemente disso, acho todos os artigos vinculados acima bastante interessantes de qualquer maneira ?