Avaliar o desempenho das consultas com planos de execução e DMVs

  • 10 minutos

Quando uma consulta corre mais devagar do que o esperado, o primeiro passo é perceber como o motor da base de dados a executa. Os planos de execução mostram-lhe os operadores exatos, métodos de acesso aos dados e custos de recursos que o otimizador escolheu para uma consulta. As visualizações de gerenciamento dinâmico (DMVs) complementam isso ao expor dados de desempenho durante a execução em todas as consultas da base de dados, para que possa encontrar as mais caras antes de avançar para qualquer plano individual.

Leia os planos de execução

Um plano de execução é o conjunto de instruções que o otimizador de consultas produz para recuperar e processar dados. Define quais as tabelas a aceder primeiro, se deve usar índices ou tabelas de varredura, e como juntar, filtrar, ordenar e agregar resultados. O otimizador avalia vários planos candidatos e seleciona aquele com o custo estimado mais baixo.

Captura de ecrã de um plano gráfico real de execução no SQL Server Management Studio mostrando operadores, setas indicando o fluxo de dados e percentagens de custos para cada etapa.

Existem dois tipos de planos de execução:

  • Plano de execução estimado: Gerado sem executar a consulta. Mostra os operadores planeados e as contagens estimadas de linhas com base em estatísticas. Use planos estimados para uma análise rápida sem afetar a base de dados.
  • Plano de execução real: Capturado durante a execução da consulta. Inclui o plano estimado mais contagens reais de linhas, tempos reais de execução, concessões de memória e avisos. O plano real revela discrepâncias entre o que o otimizador esperava e o que realmente aconteceu.

Para mostrar um plano estimado, execute SET SHOWPLAN_XML ON antes da consulta ou selecione Mostrar Plano de Execução Estimado no SQL Server Management Studio (SSMS). Para capturar um plano real, execute SET STATISTICS XML ON ou selecione Incluir Plano de Execução Real no SSMS antes de executar a consulta.

Embora os planos estimados e reais sejam semelhantes, as métricas de tempo de execução do plano são cruciais para diagnosticar problemas de desempenho. Por exemplo, se a contagem estimada de linhas para uma varredura de tabela for 100 mas a contagem real de linhas for 10.000, isso pode indicar estatísticas desatualizadas que levam a uma má escolha de plano. O otimizador compila o plano com base em estatísticas na primeira vez que encontra uma consulta. Se essas estatísticas não refletirem a distribuição atual dos dados, o plano pode ter um desempenho fraco.

Identificar questões comuns nos planos de execução

Os planos de execução são lidos da esquerda para a direita, de cima a baixo. Os primeiros operadores acedem às tabelas base, e o operador final produz o resultado da consulta. Identifique estes problemas comuns:

Os tipos de operador dizem-lhe como o motor acede aos dados. Existem muitos tipos de operadores, cada um representando um método diferente de recuperação ou processamento de dados. Por exemplo, um operador Index Seek representa um método altamente eficiente que direciona linhas específicas usando chaves de índice. Um operador de varrimento de tabela ou varrimento de índices , por outro lado, representa um método menos eficiente que lê todas as linhas. Se vires uma varredura numa tabela grande, provavelmente precisas de um índice. Por exemplo, se a aplicação de comércio eletrónico consultar encomendas por data e o plano mostrar uma Verificação de Índice Agrupado na Orders tabela, adicionar um índice não agrupado na OrderDate coluna pode transformar essa verificação numa pesquisa. Note que nem todos os scans são negativos. Se uma tabela for pequena, ou se a condição de pesquisa devolver a maioria das linhas numa tabela, uma varredura pode ser o método de acesso mais eficiente. Considera sempre o contexto da consulta e o tamanho dos dados. Conheça os seus dados e use planos de execução para confirmar se o método de acesso faz sentido.

As contagens de linhas estimadas versus reais revelam se as suposições do otimizador correspondem à realidade. O otimizador baseia o seu plano em estatísticas, metadados que descrevem a distribuição e densidade dos dados nas suas tabelas. Se essas estatísticas estiverem obsoletas, as contagens estimadas e reais de linhas divergem. Quando o otimizador subestima o número de linhas, pode escolher uma junção de loop aninhada (que processa uma linha de cada vez a partir da tabela interna de uma junção) quando uma junção de hash (que constrói uma tabela de hash em memória para consultas rápidas) seria mais rápida, ou alocar pouca memória para uma operação de ordenação. As estatísticas podem tornar-se obsoletas após alterações significativas nos dados, por isso atualizar estatísticas com UPDATE STATISTICS ou ativar atualizações automáticas pode ajudar o otimizador a tomar melhores decisões.

Os operadores de Pesquisa de Chave aparecem quando o motor encontra linhas através de um índice não agrupado, mas necessita de colunas extra do índice agrupado. Para cada linha correspondente, o motor faz uma ida e volta extra até ao índice agrupado para recuperar essas colunas. Se o filtro devolve muitas linhas, essas pesquisas extra acumulam-se rapidamente. Por exemplo, se a aplicação de comércio eletrónico filtrar as encomendas por CustomerID mas também selecionar OrderDate, TotalAmount, e ShippingAddress, e o índice não agrupado em CustomerID não incluir essas colunas, o plano mostra uma Pesquisa de Chave para cada ordem correspondente. Pode eliminar Consultas de Chave adicionando as colunas em falta como colunas incluídas no índice. Tenha em mente que as colunas incluídas aumentam o tamanho do índice, o que pode atrasar as escritas, por isso pondere o benefício no desempenho de leitura em relação ao sobrecusto de escrita.

Setas grossas entre operadores representam o número de linhas que fluem entre eles. Uma seta inesperadamente grossa no início do plano (lendo da esquerda para a direita, de cima para baixo) muitas vezes significa que um filtro ou índice em falta está a deixar passar demasiadas linhas.

Sugestões de índice em falta aparecem como texto verde destacado no topo do plano gráfico de execução no SSMS. Quando o otimizador deteta que um índice pode reduzir significativamente o custo de uma consulta, apresenta uma recomendação diretamente no plano. Clique com o botão direito na sugestão e selecione Detalhes do Índice em Falta para gerar uma CREATE INDEX declaração que possa rever e executar. Estas sugestões são uma das vitórias mais fáceis que pode obter ao ler um plano de execução.

Os avisos aparecem como um triângulo amarelo com um ponto de exclamação (⚠) no operador afetado. Cada aviso aponta para uma oportunidade de otimização. Avisos comuns incluem:

  • Estatísticas em falta: O otimizador não conseguiu encontrar estatísticas para uma coluna, por isso adivinhou o número de linhas em vez de usar a distribuição real dos dados. Para resolver este problema, crie estatísticas nas colunas usadas nas suas consultas ou atualize estatísticas existentes se estiverem obsoletas.
  • Concessão excessiva de memória: A consulta pediu mais memória do que precisava, desperdiçando recursos que outras consultas poderiam usar. Este problema ocorre frequentemente quando o otimizador sobrestima as contagens de linhas. Atualizar estatísticas ou reescrever a consulta para filtrar linhas mais cedo pode ajudar a reduzir as concessões de memória.
  • Sem Predicado de Junção: Duas tabelas são unidas sem uma condição adequada, produzindo um produto cartesiano que devolve todas as combinações possíveis de linhas. Verifique se a sua consulta tem uma cláusula em falta ou incorreta ON .
  • Conversão implícita: Uma incompatibilidade de tipo de dado obriga o motor a converter valores em tempo de execução, o que pode transformar uma busca de índice numa varredura. Por exemplo, se uma WHERE cláusula comparar um nvarchar parâmetro a uma varchar coluna, o motor converte todas as linhas da coluna para nvarchar antes de comparar. Para corrigir conversões implícitas, compare os tipos de dados nos seus parâmetros de consulta com as definições das colunas.
  • Ordenação ou transbordo de hash: Uma operação de ordenação ou de hash esgotou a memória concedida e transbordou resultados intermédios para o tempdb. Estas operações são o segundo fator mais comum do uso mais elevado de CPU após as verificações. Se vir um aviso de derrame, o otimizador provavelmente subestimou as contagens de linhas e solicitou memória insuficiente. Executar UPDATE STATISTICS para atualizar as estatísticas da tabela ou reescrever a consulta para reduzir o número de linhas antes da ordenação pode muitas vezes eliminar o extravasamento.

Os planos de execução são uma ferramenta poderosa para compreender o desempenho das consultas. Mostram-lhe exatamente como o motor executa uma consulta e onde estão os gargalos. Ao aprender a ler planos de execução de forma eficaz, pode identificar e corrigir rapidamente problemas de desempenho nas suas consultas à base de dados.

Consultar os DMVs para dados de desempenho em tempo de execução

Os DMVs expõem dados de desempenho em tempo real e acumulados do motor de base de dados. A base de dados Azure SQL requer VIEW DATABASE STATE permissão para os consultar. Enquanto os planos de execução mostram como é executada uma única consulta, os DMVs mostram o que está a acontecer em todas as consultas, o que o ajuda a encontrar primeiro as mais caras.

Encontre as consultas mais caras

O tempo de CPU, as leituras lógicas e a contagem de execuções são as métricas mais comuns para identificar consultas dispendiosas. Um tempo elevado de CPU ou leituras lógicas indica que uma consulta é intensiva em recursos, enquanto um elevado número de execuções significa que mesmo uma consulta moderadamente dispendiosa pode ter um grande impacto no desempenho global. Comece por rever as principais consultas por tempo médio de CPU ou leituras lógicas para encontrar candidatos à otimização.

sys.dm_exec_query_stats devolve estatísticas agregadas de desempenho para planos de consulta em cache. Una-o com sys.dm_exec_sql_text para ver o texto da consulta e sys.dm_exec_query_plan para recuperar o plano de execução.

A consulta seguinte encontra as 10 principais consultas por tempo médio de CPU:

SELECT TOP 10
    qs.total_worker_time / qs.execution_count AS avg_cpu_time,
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset) / 2) + 1) AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY avg_cpu_time DESC;

Este script ajuda-te a identificar quais as consultas que merecem a tua atenção. Um valor elevado avg_logical_reads em relação ao tamanho do conjunto de resultados aponta frequentemente para índices em falta ou planos ineficientes. No entanto, tenha cautela ao interpretar estes resultados. Uma consulta com tempo médio de CPU elevado que só é executada uma vez por dia pode importar menos do que uma consulta moderada que corre milhares de vezes por hora. Considera sempre tanto o custo médio como o número de execuções quando priorizas. Também pode ordenar por avg_logical_reads para encontrar consultas que utilizam intensivamente E/S, o que muitas vezes indica ausência de índices ou métodos de acesso ineficientes.

Verifique as consultas em execução atualmente

Enquanto a consulta anterior mostra-te as consultas históricas mais caras no cache do plano, sys.dm_exec_requests dá-te uma imagem de cada pedido que está a ser executado. Inclui colunas para tempo de CPU, leituras, escritas, tipo de espera, tempo de espera e ID de sessão de bloqueio. Utilize esta visão para identificar consultas ativas que consomem demasiados recursos ou que estão presas à espera de bloqueios. Esta vista é uma das mais importantes DMVs para o monitoramento e resolução de problemas de desempenho em tempo real.

SELECT
    r.session_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    r.cpu_time,
    r.logical_reads,
    t.text AS query_text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id > 50
ORDER BY r.cpu_time DESC;

Esta consulta filtra sessões do sistema (IDs de sessão 1-50) e ordens por tempo de CPU. Pode também ordenar por logical_reads para encontrar consultas que têm um uso intensivo de I/O. As colunas wait_type e wait_time ajudam a identificar se uma consulta está aguardando por bloqueios, I/O ou outros recursos.

Descobrir índices ausentes

Anteriormente, vimos como os planos de execução podem mostrar sugestões de índices ausentes para uma única consulta. Os DMVs de índices ausentes oferecem uma visão mais ampla dos índices que o otimizador usaria em todas as interrogações se existissem. Estas vistas são uma excelente forma de encontrar oportunidades de otimização que afetam múltiplas consultas. sys.dm_db_missing_index_details mostra a tabela, as colunas de igualdade e desigualdade, e as colunas incluídas. sys.dm_db_missing_index_group_stats fornece uma medida de melhoria que estima a redução de custos.

SELECT
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.avg_total_user_cost * migs.avg_user_impact *
        (migs.user_seeks + migs.user_scans) AS improvement_measure
FROM sys.dm_db_missing_index_groups AS mig
INNER JOIN sys.dm_db_missing_index_group_stats AS migs
    ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON mig.index_handle = mid.index_handle
ORDER BY improvement_measure DESC;

Esta consulta calcula um improvement_measure para cada recomendação de índice em falta, que é um produto do custo médio das consultas que beneficiariam do índice, da melhoria percentual média e do número de vezes que essas consultas foram executadas. Ordenar por esta medida ajuda-te a priorizar quais os índices em falta a criar primeiro. No entanto, lembre-se que estes resultados são apenas recomendações baseadas nas consultas atualmente no cache do plano. Revise sempre as colunas de índice sugeridas e teste o seu impacto tanto no desempenho da consulta como na sobrecarga de escrita antes de as adicionar à produção.

Observação

As recomendações em falta no índice são sugestões, não diretivas. Teste sempre o impacto de um novo índice tanto no desempenho das consultas como na sobrecarga de escrita antes de o adicionar à produção.

Monitorizar sessões ativas e tarefas de espera

sys.dm_exec_sessions dá-lhe informações sobre todas as sessões autenticadas, incluindo tempo de iniciação de sessão, nome do host, nome do programa e CPU e leituras acumuladas. Combine isso com sys.dm_os_waiting_tasks para ver quais tarefas estão à espera e em que recursos estão à espera. Estas opiniões tornam-se essenciais quando diagnostica bloqueios e disputas de recursos numa unidade posterior.

Ponha tudo junto

Planos de execução e DMVs oferecem uma visão completa do comportamento das consultas. Comece pelos DMVs para identificar as consultas mais dispendiosas. Depois, analisa os planos de execução para compreender por que motivo são caros. Será um índice em falta a causar uma varredura? Estatísticas desatualizadas estão a causar erros na estimativa de linhas? Uma Pesquisa de Chave que se pode eliminar? Esta abordagem sistemática, desde a perspetiva de todo o sistema até à análise individual de consultas, é a forma mais eficiente de encontrar e corrigir gargalos de desempenho.

Principais conclusões

Os planos de execução revelam a estratégia do otimizador para uma consulta, e os planos reais incluem métricas de execução que expõem discrepâncias entre o número estimado e o número real de linhas. Ao ler um plano, concentre-se nos tipos de operadores (procura vs. varredura), estimativas de contagem de linhas, avisos e operadores de Pesquisa de Chaves. Os DMVs fornecem dados de desempenho a nível de sistema: use sys.dm_exec_query_stats para encontrar as consultas mais caras, sys.dm_exec_requests para consultas em curso, e os DMVs de índices em falta para oportunidades de otimização. Comece de forma abrangente com as DMVs para identificar onde estão os maiores problemas e, em seguida, aprofunde-se nos planos de execução individuais para compreender o porquê.