Compartilhar via


Solucionar problemas de consultas que parecem nunca terminar em SQL Server

Este artigo descreve as etapas de solução de problemas para o problema em que você tem uma consulta que parece nunca ser concluída ou fazer com que ela seja concluída pode levar muitas horas ou dias.

O que é uma consulta interminável?

Este documento se concentra em consultas que continuam a ser executadas ou compiladas, ou seja, sua CPU continua a aumentar. Ele não se aplica a consultas bloqueadas ou aguardando algum recurso que nunca é lançado (a CPU permanece constante ou muda muito pouco).

Importante

Se uma consulta for deixada para concluir sua execução, ela será concluída eventualmente. Pode levar apenas alguns segundos, ou pode levar vários dias.

O termo interminável é usado para descrever a percepção de uma consulta que não está sendo concluída quando, na verdade, a consulta será concluída.

Identificar uma consulta sem fim

Para identificar se uma consulta está sendo executada continuamente ou presa em um gargalo, siga estas etapas:

  1. Execute a seguinte consulta:

    DECLARE @cntr int = 0
    
    WHILE (@cntr < 3)
    BEGIN
        SELECT TOP 10 s.session_id,
                        r.status,
                        r.wait_time,
                        r.wait_type,
                        r.wait_resource,
                        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,
                        atrn.name as transaction_name,
                        atrn.transaction_id,
                        atrn.transaction_state
            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
            LEFT JOIN (sys.dm_tran_session_transactions AS stran 
                 JOIN sys.dm_tran_active_transactions AS atrn
                    ON stran.transaction_id = atrn.transaction_id)
            ON stran.session_id =s.session_id
            WHERE r.session_id != @@SPID
            ORDER BY r.cpu_time DESC
    
        SET @cntr = @cntr + 1
    WAITFOR DELAY '00:00:05'
    END
    
  2. Verifique a saída de exemplo.

    • As etapas de solução de problemas neste artigo são especificamente aplicáveis quando você observa uma saída semelhante à seguinte em que a CPU está aumentando proporcionalmente com o tempo decorrido, sem tempos de espera significativos. É importante observar que as alterações no logical_reads não são relevantes nesse caso, pois algumas solicitações T-SQL vinculadas à CPU podem não fazer nenhuma leitura lógica (por exemplo, executar cálculos ou um WHILE loop).

      Session_id status cpu_time logical_reads wait_time wait_type
      56 Executando 7038 101000 0 NULL
      56 executável 12040 301000 0 NULL
      56 Executando 17020 523000 0 NULL
    • Este artigo não será aplicável se você observar um cenário de espera semelhante ao seguinte, em que a CPU não muda nem muda ligeiramente, e a sessão aguarda um recurso.

      Session_id status cpu_time logical_reads wait_time wait_type
      56 suspended 0 3 8312 LCK_M_U
      56 suspended 0 3 13318 LCK_M_U
      56 suspended 0 5 18331 LCK_M_U

    Para obter mais informações, consulte Diagnosticar esperas ou gargalos.

Tempo de compilação longo

Em raras ocasiões, você pode observar que a CPU está aumentando continuamente ao longo do tempo, mas isso não é impulsionado pela execução da consulta. Em vez disso, ele pode ser impulsionado por uma compilação excessivamente longa (a análise e a compilação de uma consulta). Nesses casos, marcar a coluna de saída transaction_name e procure um valor de sqlsource_transform. Esse nome de transação indica uma compilação.

Coletar dados de diagnóstico

Para coletar dados de diagnóstico usando SQL Server Management Studio (SSMS), siga estas etapas:

  1. Capture o plano de execução de consulta estimado XML.

  2. Examine o plano de consulta para ver se há alguma indicação óbvia de onde a lentidão pode vir. Exemplos típicos incluem:

    • Verificações de tabela ou índice (veja linhas estimadas).
    • Loops aninhados controlados por um enorme conjunto de dados de tabela externa.
    • Loops aninhados com um branch grande no lado interno do loop.
    • Carretels de tabela.
    • Funções na SELECT lista que levam muito tempo para processar cada linha.
  3. Se a consulta for executada rapidamente a qualquer momento, você poderá capturar as execuções "rápidas" Plano de Execução real XML a serem comparadas.

Método para revisar os planos coletados

Esta seção ilustrará como examinar os dados coletados. Ele usará os vários planos de consulta XML (usando a extensão *.sqlplan) coletados em SQL Server SP1 de 2016 e versões e builds posteriores.

Siga estas etapas para comparar planos de execução:

  1. Abra um arquivo de plano de execução de consulta salvo anteriormente (.sqlplan).

  2. Clique com o botão direito do mouse em uma área em branco do plano de execução e selecione Comparar Showplan.

  3. Escolha o segundo arquivo de plano de consulta que você gostaria de comparar.

  4. Procure setas grossas que indicam um grande número de linhas que fluem entre operadores. Em seguida, selecione o operador antes ou depois da seta e compare o número de linhas reais em dois planos.

  5. Compare o segundo e o terceiro planos para ver se o maior fluxo de linhas acontece nos mesmos operadores.

    Veja um exemplo:

    Compare os planos de consulta no SSMS.

Resolução

  1. Verifique se as estatísticas são atualizadas para as tabelas usadas na consulta.

  2. Procure uma recomendação de índice ausente no plano de consulta e aplique qualquer.

  3. Reescreva a consulta com o objetivo de simplificar:

    • Use predicados mais seletivos WHERE para reduzir os dados processados antecipadamente.
    • Quebre-o.
    • Selecione algumas partes em tabelas temporárias e junte-as posteriormente.
    • Remova TOP, EXISTSe FAST (T-SQL) nas consultas que são executadas por muito tempo devido à meta de linha do otimizador. Como alternativa, você pode usar a DISABLE_OPTIMIZER_ROWGOALdica. Para obter mais informações, consulte Linha Goals Gone Rogue.
    • Evite usar CTEs (Expressões comuns de tabela) nesses casos, pois combinam instruções em uma única consulta grande.
  4. Tente usar dicas de consulta para produzir um plano melhor:

    • HASH JOIN ou MERGE JOIN dica
    • FORCE ORDER Dica
    • FORCESEEK Dica
    • RECOMPILE
    • USE PLAN N'<xml_plan>' se você tiver um plano de consulta rápido que possa forçar
  5. Use Repositório de Consultas (QDS) para forçar um bom plano conhecido se esse plano existir e se sua versão SQL Server for compatível com Repositório de Consultas.

Diagnosticar esperas ou gargalos

Esta seção é incluída aqui como uma referência no caso de seu problema não ser uma consulta de condução de CPU de longa duração. Você pode usá-lo para solucionar problemas de consultas que são longas devido a esperas.

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.