Compreender os planos de consulta

  • 11 minutos

É útil ter uma compreensão básica de como os otimizadores de banco de dados funcionam antes de se aprofundar nos detalhes do plano de execução. O SQL Server usa o que é conhecido como otimizador de consulta baseado em custo. O otimizador de consulta calcula um custo para vários planos possíveis com base nas estatísticas que tem nas colunas que estão sendo utilizadas e nos índices possíveis que podem ser usados para cada operação em cada plano de consulta. Com base nessas informações, chega-se a um custo total para cada plano. Algumas consultas complexas podem ter milhares de planos de execução possíveis. O otimizador não avalia todos os planos possíveis, mas usa heurísticas para determinar os planos que provavelmente terão um bom desempenho. O otimizador escolherá então o plano de menor custo de todos os planos avaliados para uma determinada consulta.

Como o otimizador de consulta é baseado em custos, é importante que ele tenha boas entradas para a tomada de decisões. As estatísticas que o SQL Server usa para controlar a distribuição de dados em colunas e índices precisam ser mantidas atualizadas ou podem gerar planos de execução abaixo do ideal. O SQL Server atualiza automaticamente suas estatísticas à medida que os dados são alterados em uma tabela; no entanto, podem ser necessárias atualizações mais frequentes para alterar rapidamente os dados. O mecanismo usa muitos fatores ao construir um plano, incluindo o nível de compatibilidade do banco de dados, estimativas de linha com base em estatísticas e índices disponíveis.

Quando um usuário envia uma consulta para o mecanismo de banco de dados, o seguinte processo acontece:

  1. A consulta é analisada quanto à sintaxe adequada e uma árvore de análise de objetos de banco de dados é gerada se a sintaxe estiver correta.
  2. A árvore de análise da Etapa 1 é tomada como entrada para um componente do mecanismo de banco de dados chamado Algebrizer para vinculação. Esta etapa valida se existem colunas e objetos na consulta e identifica os tipos de dados que estão sendo processados para uma determinada consulta. Esta etapa gera uma árvore do processador de consultas, que está na entrada da etapa 3.
  3. Como a otimização de consultas é um processo relativamente caro em termos de consumo de CPU, o mecanismo de banco de dados armazena em cache os planos de execução em uma área especial da memória chamada cache de plano. Se já existir um plano para uma determinada consulta, esse plano será recuperado do cache. As consultas cujos planos são armazenados em cache terão um valor hash gerado com base no T-SQL na consulta. Esse valor é chamado de query_hash. O mecanismo gerará um query_hash para a consulta atual e, em seguida, verificará se ele corresponde a quaisquer consultas existentes no cache do plano.
  4. Se o plano não existir, o Otimizador de Consulta usará seu otimizador baseado em custo para gerar várias opções de plano de execução com base nas estatísticas sobre as colunas, tabelas e índices usados na consulta, conforme descrito acima. A saída desta etapa é um plano de execução de consulta.
  5. A consulta é então executada usando um plano de execução que é extraído do cache do plano ou um novo plano gerado na etapa 4. A saída desta etapa é o resultado da sua consulta.

Nota

Para saber mais sobre como o processador de consultas funciona, consulte Guia de arquitetura de processamento de consultas

Vamos ver uma instância. Considere a seguinte consulta:

SELECT orderdate,
        AVG(salesAmount)
FROM FactResellerSales
WHERE ShipDate = '2013-07-07'
GROUP BY orderdate;

Neste exemplo, o SQL Server verificará a existência das colunas OrderDate, ShipDate e SalesAmount na tabela FactResellerSales. Se essas colunas existirem, ele gerará um valor de hash para a consulta e examinará o cache do plano em busca de um valor de hash correspondente. Se houver um plano para uma consulta com um hash correspondente, o mecanismo tentará reutilizar esse plano. Se não houver um plano com um hash correspondente, ele examinará as estatísticas disponíveis nas colunas OrderDate e ShipDate . A WHERE cláusula que faz referência à coluna ShipDate é o que é conhecido como o predicado nesta consulta. Se houver um índice não clusterizado que inclua a coluna ShipDate, o SQL Server provavelmente incluirá isso no plano, se os custos forem menores do que a recuperação de dados do índice clusterizado. O otimizador escolherá o plano de menor custo dos planos disponíveis e executará a consulta.

Os planos de consulta combinam uma série de operadores relacionais para recuperar os dados e também capturar informações sobre os dados, como contagens de linhas estimadas. Outro elemento do plano de execução é a memória necessária para executar operações como unir ou classificar dados. A memória necessária para a consulta é chamada de concessão de memória. A concessão de memória é um bom exemplo da importância da estatística. Se o SQL Server acha que um operador vai retornar 10.000.000 linhas, quando ele está retornando apenas 100, uma quantidade muito maior de memória é concedida à consulta. Uma concessão de memória maior do que o necessário pode causar um problema duplo. Primeiro, a consulta pode encontrar uma RESOURCE_SEMAPHORE espera, o que indica que a consulta está aguardando que o SQL Server aloque uma grande quantidade de memória. O padrão do SQL Server é aguardar 25 vezes o custo da consulta (em segundos) antes de ser executado, até 24 horas. Em segundo lugar, quando a consulta é executada, se não houver memória suficiente disponível, a consulta será derramada para tempdb, que é muito mais lento do que operar na memória.

O plano de execução também armazena outros metadados sobre a consulta, incluindo, entre outros, o nível de compatibilidade do banco de dados, o grau de paralelismo da consulta e os parâmetros fornecidos se a consulta for parametrizada.

Os planos de consulta podem ser visualizados numa representação gráfica ou num formato baseado em texto. As opções baseadas em texto são invocadas com comandos SET e aplicam-se apenas à conexão atual. Os planos baseados em texto podem ser visualizados em qualquer lugar onde você possa executar consultas T-SQL.

A maioria dos DBAs prefere olhar para os planos graficamente, porque um plano gráfico permite que você veja o plano como um todo, incluindo o que é chamado de forma do plano, facilmente. Há várias maneiras de visualizar e salvar planos de consulta gráfica. A ferramenta mais comum usada para essa finalidade é o SQL Server Management Studio, mas os planos estimados também podem ser exibidos no Azure Data Studio. Existem também ferramentas de terceiros que suportam a visualização de planos de execução gráfica.

Existem três tipos diferentes de planos de execução que podem ser visualizados.

Plano de execução estimado

Esse tipo é o plano de execução gerado pelo otimizador de consulta. Os metadados e o tamanho da concessão de memória de consulta são baseados em estimativas das estatísticas existentes no banco de dados no momento da compilação da consulta. Para ver um plano estimado baseado em texto, execute o comando SET SHOWPLAN_ALL ON antes de executar a consulta. Ao executar a consulta, você verá as etapas do plano de execução, mas a consulta NÃO será executada e você não verá nenhum resultado. A opção SET permanecerá em vigor até que você a desative.

Plano de Execução Real

Este tipo é o mesmo plano que o plano estimado; no entanto, esse plano também contém o contexto de execução para a consulta, que inclui as contagens de linhas estimadas e reais, quaisquer avisos de execução, o grau real de paralelismo (número de processadores usados) e os tempos decorridos e de CPU usados durante a execução. Para ver um plano real baseado em texto, execute o comando SET STATISTICS PROFILE ON antes de executar a consulta. A consulta será executada e você obterá o plano e os resultados.

Estatísticas de consulta em tempo real

Esta opção de visualização de plano combina os planos estimados e reais em um plano animado que exibe o progresso da execução através dos operadores no plano. Ele é atualizado a cada segundo e mostra o número real de linhas que fluem através dos operadores. O outro benefício do Live Query Statistics é que ele mostra a transferência de operador para operador, o que pode ser útil na solução de alguns problemas de desempenho. Como o tipo de plano é animado, ele só está disponível como um plano gráfico.