Entender os planos de consulta

  • 11 minutos

É útil ter um entendimento básico de como os otimizadores de banco de dados funcionam antes de aprofundar-se 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 ele tem sobre as colunas que estão sendo utilizadas e nos possíveis índices que podem ser usados em cada operação em cada plano de consulta. Com base nessas informações, ele cria um custo total para cada plano. Algumas consultas complexas têm milhares de planos de execução possíveis. O otimizador não avalia todos os planos possíveis, mas usa heurística para determinar os planos que provavelmente têm bom desempenho. O otimizador escolherá o plano de custo mais baixo de todos os planos avaliados para determinada consulta.

Como o otimizador de consulta é baseado em custo, é importante que ele tenha boas informações para a tomada de decisão. As estatísticas do SQL Server usadas para acompanhar a distribuição de dados em colunas e índices precisam ser mantidas atualizadas; do contrário, poderão gerar planos de execução de qualidade inferior. O SQL Server atualiza suas estatísticas automaticamente como alterações de dados em uma tabela; no entanto, poderão ser necessárias atualizações mais frequentes para alterar rapidamente os dados. O mecanismo usa muitos fatores quando cria um plano, incluindo os níveis de compatibilidade do banco de dados, as estimativas de linha com base em estatísticas e os índices disponíveis.

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

  1. A consulta é analisada quanto à sintaxe adequada, e uma árvore de análise de objetos de banco de dados será gerada se a sintaxe estiver correta.
  2. A árvore de análise da etapa 1 é usada como entrada para um componente do mecanismo de banco de dados chamado Algebrizer para associação. Esta etapa valida que as colunas e os objetos na consulta existem e identifica os tipos de dados que estão sendo processados para determinada consulta. Ela gera uma árvore do processador de consultas, que está na entrada da etapa 3.
  3. Como a otimização de consulta é 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 planos. Se um plano para determinada consulta já existir, esse plano será recuperado do cache. As consultas cujos planos são armazenados em cache terão, cada um, um valor de hash gerado com base no T-SQL na consulta. Esse valor é referido como query_hash. O mecanismo vai gerar um query_hash para a consulta atual e verificar se ele corresponde a alguma consulta existente no cache de planos.
  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, as tabelas e os índices que são usados na consulta, conforme descrito acima. O resultado desta etapa é um plano de execução de consulta.
  5. Em seguida, a consulta é executada usando um plano de execução que é extraído do cache de planos ou um novo plano gerado na etapa 4. A saída desta etapa é os resultados da consulta.

Observação

Para saber mais sobre como o processador de consultas funciona, consulte o Guia da Arquitetura de Processamento de Consultas

Vejamos um exemplo. Considere a consulta a seguir:

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 de planos 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 nenhum plano com um hash correspondente, ele examinará as estatísticas disponíveis nas colunas OrderDate e ShipDate. A cláusula WHERE que faz referência à coluna ShipDate é conhecida como o predicado nessa 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 os da recuperação dos dados do índice clusterizado. O otimizador escolherá o plano de custo mais baixo dentre os 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 capturam 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 das estatísticas. Se o SQL Server achar que um operador vai retornar 10 milhões de linhas, uma quantidade muito maior de memória será concedida à consulta quando ele estiver retornando apenas 100. Uma concessão de memória acima do necessário pode causar um problema duplo. Primeiro, a consulta pode encontrar uma espera de RESOURCE_SEMAPHORE, o que indica que a consulta está aguardando que o SQL Server aloque a ela uma grande quantidade de memória. O SQL Server passa ao padrão de aguardar 25 vezes o custo da consulta (em segundos) antes da execução, até o total de 24 horas. Em segundo lugar, quando a consulta é executada, se não houver memória suficiente disponível, a consulta será despejada em tempdb, o que é muito mais lento do que operar na memória.

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

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

A maioria dos DBAs prefere ver os planos graficamente, já que um plano gráfico permite a você ver o plano como um todo, inclusive o que é chamado de formato do plano, facilmente. Há várias maneiras de exibir e salvar planos de consulta gráficos. 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. Também há ferramentas de terceiros que dão suporte à exibição de planos de execução gráficos.

Há três tipos diferentes de planos de execução que podem ser exibidos.

Plano de execução estimado

Este tipo é o plano de execução gerado pelo otimizador de consulta. Os metadados e o tamanho da concessão de memória de consulta baseiam-se 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 conjunto de comandos SET SHOWPLAN_ALL ON em 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 defina como DESATIVADO.

Plano de Execução Real

Esse tipo é o mesmo que o plano estimado, mas ele contém o contexto de execução da consulta, que inclui as contagens de linhas real e estimada, os avisos de execução, se houver, o grau real de paralelismo (número de processadores usado) e tempos de CPU e decorrido durante a execução. Para ver um plano real baseado em texto, execute o conjunto de comandos SET STATISTICS PROFILE ON em antes de executar a consulta. A consulta será executada e você obterá o plano e os resultados.

Estatísticas de Consulta ao Vivo

Essa opção de exibição de plano combina os planos estimado e real em um plano animado que mostra o progresso da execução por meio dos operadores no plano. Ela é atualizada a cada segundo e mostra o número real de linhas que flui pelos operadores. O outro benefício das Estatísticas de Consulta Ativa é que elas mostram a entrega de operador a operador, o que pode ser útil para solucionar alguns problemas de desempenho. Como o tipo de plano é animado, ele só fica disponível como um plano gráfico.