Explicar os planos de consulta estimados e reais
O tópico de planos de execução reais versus estimados pode ser confuso. A diferença é que o plano real inclui estatísticas de tempo de execução que não são capturadas no plano estimado. Os operadores utilizados, e ordem de execução será a mesma que o plano estimado em quase todos os casos. A outra consideração é que, para capturar um plano de execução real, a consulta tem que ser executada, o que pode ser demorado ou não possível. Por exemplo, a consulta pode ser uma UPDATE instrução que só pode ser executada uma vez. No entanto, se você precisar ver os resultados da consulta e o plano, precisará usar uma das opções reais do plano.
Como mostrado acima, você pode gerar um plano estimado no SSMS clicando no botão apontado pela caixa de plano de consulta estimado (ou usando o comando de teclado Control+L). Você pode gerar o plano real clicando no ícone mostrado (ou usando o comando de teclado Control+M) e, em seguida, executando a consulta. Os dois botões de opção funcionam de forma um pouco diferente. O botão Incluir Plano de Consulta Estimado responde imediatamente a qualquer consulta realçada (ou a todo o espaço de trabalho, se nada estiver realçado), em oposição ao botão Incluir Plano de Consulta Real.
Há sobrecarga para executar uma consulta e gerar um plano de execução estimado, portanto, a visualização dos planos de execução deve ser feita com cuidado em um ambiente de produção.
Normalmente, você pode usar o plano de execução estimado enquanto escreve sua consulta, para entender suas características de desempenho, identificar índices ausentes ou detetar anomalias de consulta. O plano de execução real é melhor usado para entender o desempenho em tempo de execução da consulta e, mais importante, as lacunas nos dados estatísticos que fazem com que o otimizador de consulta faça escolhas subótimas com base nos dados disponíveis.
Ler um plano de consulta
Os planos de execução mostram quais tarefas o mecanismo de banco de dados está executando ao recuperar os dados necessários para satisfazer uma consulta. Vamos mergulhar no plano.
Primeiro, a consulta em si é mostrada abaixo:
SELECT [stockItemName]
,[UnitPrice] * [QuantityPerOuter] AS CostPerOuterBox
,[QuantityonHand]
FROM [Warehouse].[StockItems] s
JOIN [Warehouse].[StockItemHoldings] sh ON s.StockItemID = sh.StockItemID
ORDER BY CostPerOuterBox;
Esta consulta está unindo a tabela StockItems à tabela StockItemHoldings onde os valores na coluna StockItemID são iguais. O mecanismo de banco de dados precisa primeiro identificar essas linhas antes de poder processar o restante da consulta.
Cada ícone no plano mostra uma operação específica, que representa as várias ações e decisões que compõem um plano de execução. O mecanismo de banco de dados do SQL Server tem mais de 100 operadores de consulta que podem compor um plano de execução. Você notará que, sob cada ícone de operador, há uma porcentagem de custo em relação ao custo total da consulta. Mesmo uma operação que apresenta um custo de 0% ainda representa algum custo. Na verdade, 0% é geralmente devido a arredondamentos, porque os custos do plano gráfico são sempre mostrados como números inteiros, e a porcentagem real é algo inferior a 0,5%.
O fluxo de execução em um plano de execução é da direita para a esquerda e de cima para baixo, portanto, no plano acima, a operação Clustered Index Scan no índice clusterizado StockItemHoldings.PK_Warehouse_StockItemHoldings é a primeira operação na consulta. As larguras das linhas que conectam os operadores são baseadas no número estimado de linhas de dados que fluem para o próximo operador. Uma seta grossa é um indicador de transferência de operador grande para operador e pode ser indicativo de uma oportunidade para ajustar uma consulta. Você também pode segurar o mouse sobre um operador e ver informações adicionais em uma dica de ferramenta, conforme mostrado abaixo.
A dica de ferramenta destaca o custo e as estimativas para o plano estimado, e para um plano real incluirá as comparações com as linhas e custos reais. Cada operador também tem propriedades que mostrarão mais do que a dica de ferramenta. Se você clicar com o botão direito do mouse em um operador específico, poderá selecionar a opção Propriedades no menu de contexto para ver a lista completa de propriedades. Essa opção abrirá um painel Propriedades separado no SQL Server Management Studio, que, por padrão, fica no lado direito. Quando o painel Propriedades estiver aberto, clicar em qualquer operador preencherá a lista Propriedades com propriedades para esse operador. Como alternativa, você pode abrir o painel Propriedades clicando em Exibir no menu principal do SQL Server Management Studio e escolhendo Propriedades.
O painel Propriedades inclui algumas informações adicionais e mostra a lista de saída, que fornece detalhes das colunas que estão sendo passadas para o próximo operador. Essas colunas podem indicar que um índice não clusterizado é necessário para melhorar o desempenho da consulta quando analisado com a verificação de índice clusterizado. Como uma operação de verificação de índice clusterizado está lendo a tabela inteira, nesse cenário, um índice não clusterizado na coluna StockItemID em cada tabela pode ser mais eficiente.
Criação de perfis de consulta leve
Como mencionado acima, a captura de planos de execução reais, seja usando o SSMS ou a infraestrutura de monitoramento de Eventos Estendidos, pode ter uma grande quantidade de sobrecarga e, normalmente, só é feita em esforços de solução de problemas no local ao vivo. A sobrecarga do observador, como é conhecida, é o custo de monitorar um aplicativo em execução. Em alguns cenários, esse custo pode ser de apenas alguns pontos percentuais de utilização da CPU, mas em outros casos, como a captura de planos de execução reais, pode diminuir significativamente o desempenho de consultas individuais. A infraestrutura de criação de perfil herdada no mecanismo do SQL Server pode produzir até 75% de sobrecarga para capturar informações de consulta, enquanto a infraestrutura de criação de perfil leve tem uma sobrecarga máxima de cerca de 2%.
Na primeira versão da criação de perfil leve, ele coletava informações de contagem de linhas e utilização de E/S (o número de leituras e gravações lógicas e físicas realizadas pelo mecanismo de banco de dados para satisfazer uma determinada consulta). Além disso, um novo evento estendido chamado query_thread_profile foi introduzido para permitir que os dados de cada operador em um plano de consulta sejam inspecionados. Na versão inicial da criação de perfil leve, o uso do recurso requer que o sinalizador de rastreamento 7412 seja habilitado globalmente.
Em versões mais recentes (SQL Server 2016 SP2 CU3, SQL Server 2017 CU11 e SQL Server 2019), se a criação de perfil leve não estiver habilitada globalmente, você poderá usar a dica de consulta com QUERY_PLAN_PROFILE para habilitar a USE HINT criação de perfil leve no nível de consulta. Quando uma consulta com essa dica conclui a execução, um evento query_plan_profile estendido é gerado, o que fornece um plano de execução real. Você pode ver um exemplo de uma consulta com esta dica:
SELECT [stockItemName]
,[UnitPrice] * [QuantityPerOuter] AS CostPerOuterBox
,[ QuantityonHand]
FROM [Warehouse].[StockItems] s
JOIN [Warehouse].[StockItems] sh ON s.StockItemID = sh.StockItemID
ORDER BY CostPerOuterBox
OPTION(USE HINT ('QUERY_PLAN_PROFILE'));
Estatísticas dos planos da última consulta
O SQL Server 2019 e o Banco de Dados SQL do Azure oferecem suporte a dois aprimoramentos adicionais na infraestrutura de criação de perfil de consulta. Primeiro, a criação de perfil leve é habilitada por padrão no SQL Server 2019 e no Banco de Dados SQL do Azure e na instância gerenciada. A criação de perfil leve também está disponível como uma opção de configuração com escopo de banco de dados, chamada LIGHTWEIGHT_QUERY_PROFILING. Com a opção de escopo do banco de dados, você pode desabilitar o recurso para qualquer um dos seus bancos de dados de usuários independentes uns dos outros.
Em segundo lugar, há uma nova função de gerenciamento dinâmico chamada sys.dm_exec_query_plan_stats, que pode mostrar o último plano de execução de consulta real conhecido para um determinado identificador de plano. Para ver o último plano de consulta real conhecido através da função, você pode ativar o sinalizador de rastreamento 2451 em todo o servidor. Como alternativa, você pode habilitar essa funcionalidade usando uma opção de configuração com escopo de banco de dados chamada LAST_QUERY_PLAN_STATS.
Você pode combinar essa função com outros objetos para obter o último plano de execução para todas as consultas em cache, conforme mostrado abaixo:
SELECT *
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps;
GO
Essa funcionalidade permite que você identifique rapidamente as estatísticas de tempo de execução para a última execução de qualquer consulta em seu sistema, com sobrecarga mínima. A imagem abaixo mostra como recuperar o plano. Se você selecionar o XML do plano de execução, que será a primeira coluna de resultados, ele exibirá o plano de execução mostrado na segunda imagem abaixo.
Como você pode ver nas propriedades da verificação de índice Columnstore abaixo , o plano recuperado do cache tem o número real de linhas recuperadas na consulta.
