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.

     

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):

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.

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:

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:

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

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:

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:

• Entender e resolve problemas de bloqueio (LCK_M_*)
• Entender e resolve SQL do Azure problemas de bloqueio de banco de dados
• Solucionar problemas de desempenho lento SQL Server causados por problemas de E/S (PAGEIOLATCH_*, WRITELOG, IO_COMPLETION, BACKUPIO)
• Resolver a contenção de PAGELATCH_EX de inserção de última página no SQL Server
• Memória concede explicações e soluções (RESOURCE_SEMAPHORE)
• Solucionar problemas de consultas lentas resultantes de ASYNC_NETWORK_IO tipo de espera
• Solução de problemas de tipo de espera de alta HADR_SYNC_COMMIT com grupos de disponibilidade Always On
• Como funciona: CMEMTHREAD e Depurando-os
• Tornar o paralelismo é acionável (CXPACKET e CXCONSUMER)
• ESPERA THREADPOOL

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

• Examinar o plano de consulta da consulta
• Atualizar Estatísticas
• Identifique e aplique índices ausentes. Para obter mais etapas sobre como identificar índices ausentes, consulte Ajustar índices nãoclusos com sugestões de índice ausentes
• Reprojetar ou reescrever as consultas
• Identificar e resolve planos sensíveis a parâmetros
• Identificar e resolve problemas de capacidade de SARG
• Identifique e resolve Problemas de meta de linha em que loops aninhados de longa execução podem ser causados por TOP, EXISTS, IN, FAST, SET ROWCOUNT, OPTION (FAST N). Para obter mais informações, consulte Aprimoramentos de Linha Goals Gone Rogue e Showplan – Estimativa de Meta de LinhaRowsWithoutRowGoal
• Avaliar e resolve problemas de estimativa de cardinalidade. Para obter mais informações, confira Redução do desempenho da consulta após a atualização de SQL Server 2012 ou anterior a 2014 ou posterior
• Identifique e resolve pedreiras que parecem nunca concluídas, consulte Solucionar problemas de consultas que parecem nunca terminar em SQL Server
• Identificar e resolve consultas lentas afetadas pelo tempo limite do otimizador
• Identifique problemas de alto desempenho da CPU. Para obter mais informações, confira Solucionar problemas de alto uso da CPU no SQL Server
• Solucionar problemas de uma consulta que mostra uma diferença de desempenho significativa entre dois servidores
• Aumentar os recursos de computação no sistema (CPUs)
• Solucionar problemas de desempenho update com planos estreitos e amplos

Recursos recomendados

• Tipos detectáveis de gargalos de desempenho de consulta em SQL Server e Instância Gerenciada de SQL do Azure
• Ferramentas de ajuste e monitoramento de desempenho
• Opções de ajuste automático no SQL Server
• Diretrizes de design e arquitetura de índice
• Solucionar problemas de erros de tempo limite de consulta
• Solucionar problemas de uso alto de CPU no SQL Server
• Desempenho de consulta reduzido após a atualização de SQL Server 2012 ou anterior a 2014 ou posterior