Explicar os planos de consulta estimados e reais
Os planos de execução reais e estimados podem ser confusos. A diferença é que o plano real inclui estatísticas de tempo de execução que não estão refletidas no plano estimado. Os operadores usados e a ordem de execução serão iguais ao plano estimado em quase todos os casos. Outra consideração é que capturar um plano de execução real requer que a consulta seja executada, o que pode ser demorado ou não possível. Por exemplo, uma instrução UPDATE 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 de plano reais.
Conforme mostrado, você pode gerar um plano estimado no SSMS selecionando o botão indicado pela caixa de plano de consulta estimada (ou usando o comando de teclado Control+L). Você pode gerar o plano real selecionando o ícone mostrado (ou usando o comando de teclado Control+M) e executando a consulta. Os dois botões de opção funcionam de forma diferente. O botão Incluir Plano de Consulta Estimado responde imediatamente a qualquer consulta realçada (ou a todo o workspace, se nada estiver realçado), enquanto o botão Incluir Plano de Consulta Real requer que a consulta seja executada.
Há uma sobrecarga em executar uma consulta e gerar um plano de execução estimado, ou seja, a exibição de 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 ao escrever sua consulta para entender suas características de desempenho, identificar índices ausentes ou detectar anomalias de consulta. O aproveitamento do plano de execução real é maior para entender o desempenho do tempo de execução da consulta e, o mais importante, as lacunas nos dados estatísticos que fazem com que o otimizador de consulta faça escolhas de qualidade inferior 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 atender a uma consulta. Vamos nos aprofundar no plano.
SELECT [stockItemName]
,[UnitPrice] * [QuantityPerOuter] AS CostPerOuterBox
,[QuantityonHand]
FROM [Warehouse].[StockItems] s
JOIN [Warehouse].[StockItemHoldings] sh ON s.StockItemID = sh.StockItemID
ORDER BY CostPerOuterBox;
Essa consulta está unindo a tabela StockItems à tabela StockItemHoldings em que 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 representa uma operação específica, que corresponde às 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 fazer parte de um plano de execução. Em cada ícone de operador, há uma porcentagem de custo relativa ao custo total da consulta. Mesmo uma operação mostrando um custo de 0% ainda representa algum custo. Na verdade, 0% se deve ao arredondamento, pois os custos do plano gráfico são sempre mostrados como números inteiros, e o percentual real é algo menor que 0,5%.
O fluxo de execução em um plano de execução é da direita para a esquerda e de cima para baixo, portanto, neste plano, a operação de escaneamento de índice clusterizado no índice clusterizado StockItemHoldings.PK_Warehouse_StockItemHoldings é a primeira operação da consulta. As larguras das linhas que conectam os operadores são baseadas no número estimado de linhas de dados que fluem em direção ao operador seguinte. Uma seta grossa é indicador de uma transferência grande de operador para operador e pode ser uma indicação de 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.
A dica de ferramenta realça o custo e as estimativas do plano estimado e, para um plano real, inclui comparações com as linhas e os custos reais. Cada operador também tem propriedades que fornecem mais detalhes do que a dica de ferramenta. Clicando com o botão direito do mouse em um operador específico, você pode selecionar a opção Propriedades no menu de contexto para ver a lista de propriedades completa. Essa opção abre um painel de Propriedades separado no SQL Server Management Studio, que por padrão está no lado direito. Depois que o painel Propriedades estiver aberto, selecionar qualquer operador preencherá a lista Propriedades com detalhes para esse operador. Como alternativa, você pode abrir o painel Propriedades selecionando em Exibir no menu principal do SQL Server Management Studio e escolhendo Propriedades.
O painel de Propriedades inclui informações adicionais e mostra a lista de saída, detalhando as 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 uma verificação de índice clusterizado. Como uma operação de verificação de índice clusterizado lê a tabela inteira, um índice não clusterizado na coluna StockItemID em cada tabela pode ser mais eficiente nesse cenário.
Criação de perfil de consulta leve
Quando você gera planos de execução reais, seja usando o SSMS ou a infraestrutura de monitoramento de Eventos Estendidos, ele pode introduzir uma sobrecarga significativa. Portanto, esse processo normalmente é reservado para esforços de solução de problemas de site 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 apenas alguns pontos percentuais de utilização da CPU, mas em outros casos, como a captura de planos de execução reais, pode reduzir significativamente o desempenho de consultas individuais. A criação de perfil herdada no mecanismo do SQL Server pode produzir até 75% de sobrecarga para capturar informações de consulta, enquanto a criação de perfil leve tem uma sobrecarga máxima de cerca de 2%.
Na primeira versão da criação de perfil leve, ela coletava informações de contagem de linhas e de utilização de E/S (o número de leituras e gravações lógicas e físicas executadas pelo mecanismo de banco de dados para atender a uma determinada consulta). Além disso, um novo evento estendido chamado query_thread_profile foi introduzido para permitir que 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 esteja habilitado globalmente.
Se a criação de perfil leve não estiver habilitada globalmente, você poderá usar a dica de consulta USE HINT co QUERY_PLAN_PROFILE para habilitar a criação de perfil leve no nível da consulta. Quando uma consulta com essa dica conclui a execução, um evento estendido query_plan_profile é gerado, fornecendo um plano de execução real. Aqui está 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 da última consulta de planos
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 no escopo do banco de dados, chamada LIGHTWEIGHT_QUERY_PROFILING. Com a opção no escopo do banco de dados, você pode desabilitar o recurso para todos os seus bancos de dados de usuário, independentemente uns dos outros.
Além disso, há uma 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 usando a função, você pode habilitar o sinalizador de rastreamento 2451 em todo o servidor. Como alternativa, você pode habilitar essa funcionalidade usando uma opção de configuração no escopo do 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 armazenadas em cache:
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 identificar 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 a seguir 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 na imagem a seguir, o plano obtido do cache tem o número real de linhas obtidas na consulta.
