Share via


Solucionar problemas de consultas de execução lenta no SQL Server

Versão original do produto: SQL Server
Número de KB original: 243589

Introdução

Este artigo descreve como lidar com um problema de desempenho que os aplicativos de banco de dados podem enfrentar ao usar SQL Server: desempenho lento de uma consulta específica ou grupo de consultas. A metodologia a seguir ajudará você a reduzir a causa do problema de consultas lentas e direcioná-lo para a resolução.

Localizar consultas lentas

Para estabelecer que você tem problemas de desempenho de consulta em sua instância SQL Server, comece examinando consultas pelo tempo de execução (tempo decorrido). Verifique se o tempo excede um limite definido (em milissegundos) com base em uma linha de base de desempenho estabelecida. Por exemplo, em um ambiente de teste de estresse, você pode ter estabelecido um limite para que sua carga de trabalho não seja mais de 300 ms, e você pode usar esse limite. Em seguida, você pode identificar todas as consultas que excedem esse limite, focando em cada consulta individual e sua duração de linha de base de desempenho pré-estabelecida. Em última análise, os usuários empresariais se preocupam com a duração geral das consultas de banco de dados; Portanto, o foco main está na duração da execução. Outras métricas, como tempo de CPU e leituras lógicas, são coletadas para ajudar a reduzir a investigação.

  • Para executar instruções no momento, marcar colunas total_elapsed_time e cpu_time no sys.dm_exec_requests. Execute a seguinte consulta para obter os dados:

    SELECT 
        req.session_id
        , req.total_elapsed_time AS duration_ms
        , req.cpu_time AS cpu_time_ms
        , req.total_elapsed_time - req.cpu_time AS wait_time
        , req.logical_reads
        , SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1, 
           ((CASE statement_end_offset
               WHEN -1
               THEN DATALENGTH(ST.text)  
               ELSE req.statement_end_offset
             END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 
          1, 512)  AS statement_text  
    FROM sys.dm_exec_requests AS req
        CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
    ORDER BY total_elapsed_time DESC;
    
  • Para execuções anteriores da consulta, marcar colunas last_elapsed_time e last_worker_time no sys.dm_exec_query_stats. Execute a seguinte consulta para obter os dados:

    SELECT t.text,
         (qs.total_elapsed_time/1000) / qs.execution_count AS avg_elapsed_time,
         (qs.total_worker_time/1000) / qs.execution_count AS avg_cpu_time,
         ((qs.total_elapsed_time/1000) / qs.execution_count ) - ((qs.total_worker_time/1000) / qs.execution_count) AS avg_wait_time,
         qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
         qs.total_logical_writes / qs.execution_count AS avg_writes,
         (qs.total_elapsed_time/1000) AS cumulative_elapsed_time_all_executions
    FROM sys.dm_exec_query_stats qs
         CROSS apply sys.Dm_exec_sql_text (sql_handle) t
    WHERE t.text like '<Your Query>%'
    -- Replace <Your Query> with your query or the beginning part of your query. The special chars like '[','_','%','^' in the query should be escaped.
    ORDER BY (qs.total_elapsed_time / qs.execution_count) DESC
    

    Observação

    Se avg_wait_time mostrar um valor negativo, será uma consulta paralela.

  • Se você puder executar a consulta sob demanda no SSMS (SQL Server Management Studio) ou no Azure Data Studio, execute-a com SET STATISTICS TIMEON e SET STATISTICS IOON.

    SET STATISTICS TIME ON
    SET STATISTICS IO ON
    <YourQuery>
    SET STATISTICS IO OFF
    SET STATISTICS TIME OFF
    

    Em seguida, em Mensagens, você verá a hora da CPU, o tempo decorrido e as leituras lógicas assim:

      Table 'tblTest'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
    
      SQL Server Execution Times:
        CPU time = 460 ms,  elapsed time = 470 ms.
    
  • Se você puder coletar um plano de consulta, marcar os dados das propriedades do plano de execução.

    1. Execute a consulta com Inclua Plano de Execução Real .

    2. Selecione o operador mais à esquerda no plano De execução.

    3. Em Propriedades, expanda a propriedade QueryTimeStats .

    4. Verifique ElapsedTime e CpuTime.

      Captura de tela da janela SQL Server propriedades do plano de execução com a propriedade QueryTimeStats expandida.

Executando vs. Esperando: por que as consultas são lentas?

Se você encontrar consultas que excedam seu limite predefinido, examine por que elas podem ser lentas. A causa de problemas de desempenho pode ser agrupada em duas categorias, executando ou aguardando:

  • ESPERA: As consultas podem ser lentas porque estão esperando por um gargalo por muito tempo. Confira uma lista detalhada de gargalos em tipos de Esperas.

  • RUNNING: As consultas podem ser lentas porque estão em execução (execução) por um longo tempo. Em outras palavras, essas consultas estão usando ativamente recursos da CPU.

Uma consulta pode estar em execução por algum tempo e aguardando algum tempo em seu tempo de vida (duração). No entanto, seu foco é determinar qual é a categoria dominante que contribui para seu longo tempo decorrido. Portanto, a primeira tarefa é estabelecer em qual categoria as consultas se enquadram. É simples: se uma consulta não estiver em execução, ela estará aguardando. Idealmente, uma consulta passa a maior parte do tempo decorrido em um estado em execução e muito pouco tempo esperando por recursos. Além disso, no melhor cenário, uma consulta é executada dentro ou abaixo de uma linha de base predeterminada. Compare o tempo decorrido e a hora da CPU da consulta para determinar o tipo de problema.

Tipo 1: associado à CPU (runner)

Se o tempo da CPU estiver próximo, igual ou maior do que o tempo decorrido, você poderá tratá-la como uma consulta vinculada à CPU. Por exemplo, se o tempo decorrido for 3000 milissegundos (ms) e o tempo da CPU for 2900 ms, isso significa que a maior parte do tempo decorrido é gasto na CPU. Então podemos dizer que é uma consulta vinculada à CPU.

Exemplos de consultas em execução (vinculadas à CPU):

Tempo decorrido (ms) Hora da CPU (ms) Leituras (lógica)
3200 3000 300000
1080 1000 20

Leituras lógicas – leitura de páginas de dados/índice no cache – são, com mais frequência, os drivers de utilização da CPU em SQL Server. Pode haver cenários em que o uso de CPU vem de outras fontes: um loop de tempo (em T-SQL ou em outros códigos como objetos XProcs ou SQL CRL). O segundo exemplo na tabela ilustra esse cenário, em que a maioria da CPU não é de leituras.

Observação

Se o tempo da CPU for maior que a duração, isso indicará que uma consulta paralela será executada; vários threads estão usando a CPU ao mesmo tempo. Para obter mais informações, confira Consultas paralelas – corredor ou garçom.

Tipo 2: Aguardando um gargalo (garçom)

Uma consulta aguarda um gargalo se o tempo decorrido for significativamente maior que o tempo da CPU. O tempo decorrido inclui o tempo de execução da consulta na CPU (hora da CPU) e o tempo de espera para que um recurso seja liberado (tempo de espera). Por exemplo, se o tempo decorrido for 2000 ms e o tempo da CPU for 300 ms, o tempo de espera será de 1700 ms (2000 - 300 = 1700). Para obter mais informações, consulte Tipos de Espera.

Exemplos de consultas de espera:

Tempo decorrido (ms) Hora da CPU (ms) Leituras (lógica)
2000 300 28000
10080 700 80000

Consultas paralelas - corredor ou garçom

Consultas paralelas podem usar mais tempo de CPU do que a duração geral. O objetivo do paralelismo é permitir que vários threads executem partes de uma consulta simultaneamente. Em um segundo do tempo de relógio, uma consulta pode usar oito segundos de tempo de CPU executando oito threads paralelos. Portanto, torna-se desafiador determinar uma consulta vinculada à CPU ou à espera com base no tempo decorrido e na diferença de tempo da CPU. No entanto, como regra geral, siga os princípios listados nas duas seções acima. O resumo é:

  • Se o tempo decorrido for muito maior que o tempo da CPU, considere-o um garçom.
  • Se o tempo da CPU for muito maior do que o tempo decorrido, considere-o um corredor.

Exemplos de consultas paralelas:

Tempo decorrido (ms) Hora da CPU (ms) Leituras (lógica)
1200 8100 850000
3080 12300 1500000

Representação visual de alto nível da metodologia

A captura de tela mostra uma representação visual de alto nível da metodologia para solucionar problemas de consultas lentas.

Diagnosticar e resolve consultas de espera

Se você estabeleceu que suas consultas de interesse são garçons, sua próxima etapa é se concentrar na resolução de problemas de gargalo. Caso contrário, vá para a etapa 4: diagnosticar e resolve executar consultas.

Para otimizar uma consulta que está aguardando gargalos, identifique quanto tempo a espera é e onde está o gargalo (o tipo de espera). Depois que o tipo de espera for confirmado, reduza o tempo de espera ou elimine completamente a espera.

Para calcular o tempo de espera aproximado, subtraia o tempo da CPU (tempo de trabalho) do tempo decorrido de uma consulta. Normalmente, o tempo de CPU é o tempo de execução real e a parte restante do tempo de vida da consulta está aguardando.

Exemplos de como calcular a duração aproximada da espera:

Tempo decorrido (ms) Hora da CPU (ms) Tempo de espera (ms)
3200 3000 200
7080 1000 6080

Identificar o gargalo ou a espera

  • Para identificar consultas históricas de longa espera (por exemplo, >20% do tempo decorrido geral é tempo de espera), execute a consulta a seguir. Essa consulta usa estatísticas de desempenho para planos de consulta armazenados em cache desde o início do SQL Server.

    SELECT t.text,
             qs.total_elapsed_time / qs.execution_count
             AS avg_elapsed_time,
             qs.total_worker_time / qs.execution_count
             AS avg_cpu_time,
             (qs.total_elapsed_time - qs.total_worker_time) / qs.execution_count
             AS avg_wait_time,
             qs.total_logical_reads / qs.execution_count
             AS avg_logical_reads,
             qs.total_logical_writes / qs.execution_count
             AS avg_writes,
             qs.total_elapsed_time
             AS cumulative_elapsed_time
    FROM sys.dm_exec_query_stats qs
             CROSS apply sys.Dm_exec_sql_text (sql_handle) t
    WHERE (qs.total_elapsed_time - qs.total_worker_time) / qs.total_elapsed_time
             > 0.2
    ORDER BY qs.total_elapsed_time / qs.execution_count DESC
    
  • Para identificar as consultas atualmente em execução com esperas superiores a 500 ms, execute a seguinte consulta:

    SELECT r.session_id, r.wait_type, r.wait_time AS wait_time_ms
    FROM sys.dm_exec_requests r 
       JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id 
    WHERE wait_time > 500
    AND is_user_process = 1
    
  • Se você puder coletar um plano de consulta, marcar waitStats das propriedades do plano de execução no SSMS:

    1. Execute a consulta com Inclua Plano de Execução Real .
    2. Clique com o botão direito do mouse no operador mais à esquerda na guia Plano de execução
    3. Selecione Propriedades e, em seguida, propriedade WaitStats .
    4. Verifique os WaitTimeMs e o WaitType.
  • Se você estiver familiarizado com cenários PSSDiag/SQLdiag ou SQL LogScout LightPerf/GeneralPerf, considere usar um deles para coletar estatísticas de desempenho e identificar consultas de espera em sua instância de SQL Server. Você pode importar os arquivos de dados coletados e analisar os dados de desempenho com o SQL Nexus.

Referências para ajudar a eliminar ou reduzir esperas

As causas e resoluções para cada tipo de espera variam. Não há um método geral para resolve todos os tipos de espera. Aqui estão artigos para solucionar problemas e resolve problemas comuns de tipo de espera:

Para obter descrições de muitos tipos de espera e o que eles indicam, consulte a tabela em Tipos de Espera.

Diagnosticar e resolve executar consultas

Se o tempo de CPU (trabalho) estiver muito próximo da duração total decorrido, a consulta passará a maior parte do tempo de execução. Normalmente, quando o mecanismo SQL Server impulsiona o alto uso da CPU, o alto uso da CPU vem de consultas que geram um grande número de leituras lógicas (o motivo mais comum).

Para identificar as consultas responsáveis pela atividade alta da CPU no momento, execute a seguinte instrução:

SELECT TOP 10 s.session_id,
           r.status,
           r.cpu_time,
           r.logical_reads,
           r.reads,
           r.writes,
           r.total_elapsed_time / (1000 * 60) 'Elaps M',
           SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,
           ((CASE r.statement_end_offset
                WHEN -1 THEN DATALENGTH(st.TEXT)
                ELSE r.statement_end_offset
            END - r.statement_start_offset) / 2) + 1) AS statement_text,
           COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) 
           + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,
           r.command,
           s.login_name,
           s.host_name,
           s.program_name,
           s.last_request_end_time,
           s.login_time,
           r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC

Se as consultas não estiverem usando a CPU no momento, você poderá executar a seguinte instrução para procurar consultas históricas associadas à CPU:

SELECT TOP 10  qs.last_execution_time, st.text AS batch_text,
    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 statement_text,
    (qs.total_worker_time / 1000) / qs.execution_count AS avg_cpu_time_ms,
    (qs.total_elapsed_time / 1000) / qs.execution_count AS avg_elapsed_time_ms,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    (qs.total_worker_time / 1000) AS cumulative_cpu_time_all_executions_ms,
    (qs.total_elapsed_time / 1000) AS cumulative_elapsed_time_all_executions_ms
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
ORDER BY(qs.total_worker_time / qs.execution_count) DESC

Métodos comuns para resolve consultas de longa duração e vinculadas à CPU