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 que ela 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 é liberado (a CPU permanece constante ou muda muito pouco).

Importante

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

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

Identificar uma consulta interminável

Para identificar se uma consulta está em execução contínua 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 da amostra.

    • As etapas de solução de problemas neste artigo são especificamente aplicáveis quando você percebe uma saída semelhante à seguinte, em que a CPU está aumentando proporcionalmente ao tempo decorrido, sem tempos de espera significativos. É importante observar que as alterações não logical_reads são relevantes nesse caso, pois algumas solicitações T-SQL associadas à 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 NULO
      56 executável 12040 301000 0 NULO
      56 executando 17020 523000 0 NULO
    • Este artigo não será aplicável se você observar um cenário de espera semelhante ao seguinte, em que a CPU não é alterada ou é alterada muito ligeiramente e a sessão está aguardando um recurso.

      session_id status cpu_time logical_reads wait_time wait_type
      56 suspenso 0 3 8312 LCK_M_U
      56 suspenso 0 3 13.318 LCK_M_U
      56 suspenso 0 5 18331 LCK_M_U

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

Longo tempo de compilação

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 controlado por uma compilação excessivamente longa (a análise e compilação de uma consulta). Nesses casos, verifique 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 o SSMS (SQL Server Management Studio ), siga estas etapas:

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

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

    • Varreduras de tabela ou índice (veja as linhas estimadas).
    • Loops aninhados acionados por um enorme conjunto de dados de tabela externa.
    • Loops aninhados com uma ramificação grande no lado interno do loop.
    • Carretéis de mesa.
    • 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" do Plano de Execução XML Real para comparar.

Método para revisar os planos coletados

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

Siga estas etapas para comparar os 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 Plano de Execução.

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

  4. Procure setas grossas que indiquem um grande número de linhas fluindo entre os 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 ocorre nos mesmos operadores.

    Veja um exemplo:

    Compare planos de consulta no SSMS.

Resolução

  1. Certifique-se de que as estatísticas sejam 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 simplificá-la:

    • Use predicados mais seletivos WHERE para reduzir os dados processados antecipadamente.
    • Quebre-o.
    • Selecione algumas peças em tabelas temporárias e junte-as mais tarde.
    • 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_ROWGOAL dica. Para obter mais informações, consulte Metas de linha desonestas.
    • Evite usar CTEs (Common Table Expressions) nesses casos, pois elas combinam instruções em uma única grande consulta.
  4. Tente usar dicas de consulta para produzir um plano melhor:

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

Diagnosticar esperas ou gargalos

Esta seção está incluída aqui como uma referência caso seu problema não seja 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). Assim 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 de 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) Tempo de CPU (ms) Tempo de espera (ms)
3200 3000 200
7080 1000 6080

Identifique o gargalo ou aguarde

  • Para identificar consultas históricas de longa espera (por exemplo, >20% do tempo total decorrido é 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 consultas em execução no momento 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, verifique os WaitStats nas propriedades do plano de execução no SSMS:

    1. Execute a consulta com Incluir Plano de Execução Real ativado.
    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, a propriedade WaitStats .
    4. Verifique os WaitTimeMs e WaitType.
  • Se você estiver familiarizado com os cenários PSSDiag/SQLdiag ou SQL LogScout LightPerf/GeneralPerf, considere usar qualquer um deles para coletar estatísticas de desempenho e identificar consultas em espera em sua instância do 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 resolver todos os tipos de espera. Aqui estão os artigos para solucionar 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 esperas.