Entender os planos de consulta
Entender como os otimizadores de banco de dados funcionam é essencial antes de se aprofundar nos detalhes do plano de execução. O SQL Server usa um otimizador de consulta baseado em custo, que calcula o custo de vários planos possíveis com base nas estatísticas que ele tem nas colunas que estão sendo usadas e nos índices potenciais para cada operação no plano de consulta. Essas informações ajudam o otimizador a determinar o custo total de cada plano. Consultas complexas podem ter milhares de planos de execução possíveis, mas o otimizador não avalia cada uma delas. Em vez disso, ele usa heurística para identificar planos que provavelmente têm um bom desempenho e, em seguida, seleciona o plano de menor custo daqueles avaliados.
Como o otimizador de consulta é baseado em custo, é crucial fornecer entradas precisas para a tomada de decisões. O SQL Server depende de estatísticas para acompanhar a distribuição de dados em colunas e índices, e essas estatísticas devem ser mantidas atualizadas para evitar a geração de planos de execução abaixo do ideal. Embora o SQL Server atualize automaticamente suas estatísticas à medida que os dados são alterados em uma tabela, atualizações mais frequentes podem ser necessárias para alterar rapidamente os dados. O otimizador considera muitos fatores ao criar 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 ao mecanismo de banco de dados, ocorre o seguinte processo:
- A consulta é analisada para sintaxe adequada e, se correta, uma árvore de análise de objetos de banco de dados é gerada.
- Em seguida, a árvore de análise é inserido em 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. A saída é uma árvore de processadores de consulta, que serve como entrada para a próxima etapa.
- A otimização de consulta é intensiva em CPU, portanto, o mecanismo de banco de dados armazena em cache planos de execução em uma área de memória especial chamada cache de plano. Se já existir um plano para a consulta, ele será recuperado do cache. Cada consulta no cache tem um valor de hash gerado com base no T-SQL na consulta, conhecida como query_hash. O mecanismo gera um query_hash para a consulta atual e verifica se há correspondências no cache do plano.
- Se nenhum plano existir, o Otimizador de Consulta usará seu otimizador baseado em custo para gerar várias opções de plano de execução com base em estatísticas sobre as colunas, tabelas e índices usados na consulta. A saída é um plano de execução de consulta.
- A consulta é executada usando um plano de execução do cache de planos ou um novo plano gerado na etapa anterior. A saída são 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 verifica a existência das colunas OrderDate, ShipDate e SalesAmount na tabela FactResellerSales . Se essas colunas existirem, o SQL Server gerará um valor de hash para a consulta e examinará o cache de planos para obter um valor de hash correspondente. Se um valor de hash correspondente for encontrado, o mecanismo tentará reutilizar o plano. Se nenhum valor de hash correspondente for encontrado, o SQL Server examinará as estatísticas disponíveis nas colunas OrderDate e ShipDate .
A WHERE cláusula que faz referência à coluna ShipDate é conhecida como o predicado nesta consulta. Se houver um índice não clusterizado que inclua a coluna ShipDate , o SQL Server provavelmente a incluirá no plano, desde que os custos sejam menores do que a recuperação de dados do índice clusterizado. Em seguida, o otimizador escolhe o plano de custo mais baixo das opções disponíveis e executa a consulta.
Os planos de consulta combinam uma série de operadores relacionais para recuperar dados e capturar informações, como contagens de linhas estimadas. Outro elemento do plano de execução é a memória necessária para operações como junção ou classificação de dados, conhecida como concessão de memória. A concessão de memória destaca a importância das estatísticas. Se o SQL Server estimar que um operador retorna 10.000.000 linhas quando ele realmente retorna 100, uma concessão de memória maior é alocada para a consulta. Uma concessão de memória excessivamente grande pode causar dois problemas. Primeiro, a consulta pode encontrar uma RESOURCE_SEMAPHORE espera, indicando que está aguardando o SQL Server alocar 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, se não houver memória suficiente disponível quando a consulta for executada, ela será derramada no tempdb, que é mais lento do que operar na memória.
O plano de execução também armazena outros metadados sobre a consulta, como o nível de compatibilidade do banco de dados, o grau de paralelismo e os parâmetros fornecidos se a consulta for 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 se aplicam somente à conexão atual. Esses planos podem ser exibidos em qualquer lugar em que você possa executar consultas T-SQL.
A maioria dos DBAs prefere planos gráficos porque permitem que você veja o plano como um todo, incluindo a forma do plano. Há várias maneiras de exibir e salvar planos de consulta gráfica. A ferramenta mais comum para essa finalidade é o SQL Server Management Studio. Além disso, há ferramentas de terceiros que dão suporte à exibição de planos de execução gráfica.
Há três tipos diferentes de planos de execução.
Plano de execução estimado
Esse tipo de 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 presentes 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 permanece em vigor até que você a defina OFF.
Plano de Execução Real
Esse tipo de plano é o mesmo que o plano estimado; no entanto, ele também inclui o contexto de execução da consulta. Esse contexto contém 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 é executada e você obtém o plano e os resultados.
Estatísticas de Consulta ao Vivo
Essa opção de exibição de plano combina os planos estimados e reais em um plano animado que exibe o progresso da execução por meio dos operadores. Ela é atualizada a cada segundo e mostra o número real de linhas que flui pelos operadores. Outro benefício das Estatísticas de Consulta Dinâmica é que ela mostra a entrega de operador para operador, o que pode ser útil na solução de problemas de desempenho. Como esse tipo de plano é animado, ele só está disponível como um plano gráfico.