Para uma consulta moderadamente complexa que estou tentando otimizar, notei que a remoção da TOP ncláusula altera o plano de execução. Eu teria imaginado que, quando uma consulta inclui TOP no mecanismo de banco de dados, executaria a consulta ignorando a TOPcláusula e, no final, apenas reduziria o resultado definido para o número n de linhas solicitadas. O plano de execução gráfico parece indicar que este é o caso -- TOPé a "última" etapa. Mas parece que há mais coisas acontecendo.
Minha pergunta é: como (e por que) uma cláusula TOP n afeta o plano de execução de uma consulta?
Aqui está uma versão simplificada do que está acontecendo no meu caso:
A consulta está correspondendo linhas de duas tabelas, A e B.
Sem a TOPcláusula, o otimizador estima que haverá 19.000 linhas da tabela A e 46.000 linhas da tabela B. O número real de linhas retornadas é 16.000 para A e 13.000 para B. Uma correspondência de hash é usada para unir esses dois conjuntos de resultados para um total de 69 linhas (então uma classificação é aplicada). Esta consulta acontece muito rapidamente.
Quando adiciono TOP 1001o otimizador não usa uma correspondência de hash; em vez disso, ele primeiro classifica os resultados da tabela A (mesma estimativa/real de 19k/16k) e faz um loop aninhado na tabela B. O número estimado de linhas para a tabela B agora é 1, e o estranho é que TOP nafeta diretamente o número estimado de execuções (busca de índice) contra B - parece ser sempre 2n+1 ou, no meu caso, 2003. Essa estimativa muda de acordo se eu mudar TOP n. Obviamente, como essa é uma junção aninhada, o número real de execuções é 16k (o número de linhas da tabela A) e isso torna a consulta mais lenta.
A consulta tem uma ORDER BYcláusula. Adicionar TOPalterações onde no plano esse tipo ocorre, mas estou mais preocupado em como isso afeta o número de execuções de buscas de índice em relação à tabela B.
O cenário real é um pouco mais complexo, mas captura a ideia/comportamento básico. Ambas as tabelas são pesquisadas usando buscas de índice. Esta é a edição SQL Server 2008 R2 Enterprise.
A maneira como o texto acima foi formulado me faz pensar que você pode ter uma imagem mental incorreta de como uma consulta é executada. Um operador em um plano de consulta não é uma etapa (onde o conjunto de resultados completo de uma etapa anterior é avaliado pela próxima.
O SQL Server usa um modelo de execução em pipeline , em que cada operador expõe métodos como Init() , GetRow() e Close() . Como sugere o nome GetRow() , um operador produz uma linha por vez sob demanda (conforme exigido por seu operador pai). Isso está documentado na referência dos operadores lógicos e físicos dos livros on-line , com mais detalhes no meu post de blog Por que os planos de consulta são executados ao contrário . Esse modelo linha por vez é essencial para formar uma boa intuição para a execução da consulta.
Algumas operações lógicas como
TOP, semi joins e aFAST ndica de consulta afetam a maneira como o otimizador de consulta custeia as alternativas do plano de execução. A ideia básica é que uma forma de plano possível pode retornar as primeiras n linhas mais rapidamente do que um plano diferente que foi otimizado para retornar todas as linhas.Por exemplo, junção de loops aninhados indexados geralmente é a maneira mais rápida de retornar um pequeno número de linhas, embora hash ou junção de mesclagem com varreduras possam ser mais eficientes em conjuntos maiores. A maneira como o otimizador de consulta raciocina sobre essas escolhas é definindo uma meta de linha em um ponto específico na árvore lógica de operações.
Uma meta de linha modifica a forma como as alternativas do plano de consulta são custeadas. A essência disso é que o otimizador começa calculando o custo de cada operador como se o conjunto de resultados completo fosse necessário, define uma meta de linha no ponto apropriado e, em seguida, trabalha de volta na árvore do plano, estimando o número de linhas que espera precisar examinar. para atingir o objetivo da linha.
Por exemplo, um lógico
TOP(10)define um objetivo de linha de 10 em um determinado ponto na árvore de consulta lógica. Os custos dos operadores que levam ao objetivo de linha são modificados para estimar quantas linhas eles precisam produzir para atingir o objetivo de linha. Esse cálculo pode se tornar complexo, por isso é mais fácil entender tudo isso com um exemplo totalmente trabalhado e planos de execução anotados. As metas de linha podem afetar mais do que a escolha do tipo de junção ou se as buscas e pesquisas são preferidas às varreduras. Mais detalhes sobre isso aqui .Como sempre, um plano de execução selecionado com base em um objetivo de linha está sujeito às habilidades de raciocínio do otimizador e à qualidade das informações fornecidas a ele. Nem todo plano com meta de linha produzirá o número necessário de linhas mais rapidamente na prática, mas, de acordo com o modelo de custeio, sim.
Quando um plano de meta de linha não for mais rápido, geralmente há maneiras de modificar a consulta ou fornecer informações melhores ao otimizador, de modo que o plano selecionado naturalmente seja o melhor. Qual opção é apropriada no seu caso depende dos detalhes do curso. O recurso de objetivo de linha geralmente é muito eficaz (embora haja um bug a ser observado quando usado em planos de execução paralelos).
Sua consulta e plano específicos podem não ser adequados para uma análise detalhada aqui (forneça um plano de execução real, se desejar), mas esperamos que as ideias descritas aqui permitam que você avance.
Quando você usa o TOP, o Optimizer vê uma oportunidade de fazer menos trabalho. Se você pedir 10 linhas, há uma boa chance de não precisar consumir todo o conjunto. Portanto, o operador TOP pode ser empurrado muito mais para a direita. Ele continuará solicitando linhas do próximo operador (à sua direita), até receber o suficiente.
Você aponta que sem TOP, a consulta classifica os dados no final. Se o mecanismo pudesse saber antecipadamente quantas linhas seriam satisfeitas pela junção, ele poderia optar por usar um plano semelhante, posicionando o TOP à esquerda. Mas como o esforço para fazer uma correspondência de hash é relativamente alto e, presumivelmente, sem opção para uma junção de mesclagem, o otimizador pode preferir filtrar o TOP mais à direita.
Quando a tabela B é consultada, ela busca uma única linha por vez. É por isso que a estimativa é 1. Ele também assume que só encontrará essa linha 50% das vezes. Então ele acha que vai precisar de 2n+1 buscas para encontrá-lo.