Compartilhar via

Solucionar problemas de consultas que parecem ser executadas sem parar no SQL Server

Este artigo fornece diretrizes de solução de problemas para problemas em que uma consulta do Microsoft SQL Server leva um tempo excessivo para terminar (horas ou dias).

Sintomas

Este artigo se concentra em consultas que parecem ser executadas ou compiladas sem fim. Ou seja, o uso da CPU continua aumentando. Este artigo não se aplica a consultas bloqueadas ou aguardando um recurso que nunca foi liberado. Nesses casos, o uso da CPU permanece constante ou muda apenas ligeiramente.

Importante

Se uma consulta for deixada para continuar em execução, ela poderá eventualmente ser concluída. Esse processo pode levar apenas alguns segundos ou vários dias. Em algumas situações, a consulta pode realmente ser infinita, por exemplo, quando um loop WHILE não é encerrado. O termo "sem fim" é usado aqui para descrever a percepção de uma consulta que não termina.

Motivo

As causas comuns de consultas de execução longa (sem fim) incluem:

  • O NL (Loop Aninhado) une-se a tabelas muito grandes: Devido à natureza das junções nl, uma consulta que une tabelas que têm muitas linhas pode ser executada por um longo tempo. Para obter mais informações, consulte Junções.
    • Um exemplo de uma junção nl é o uso de TOP, FASTou EXISTS. Mesmo que uma junção hash ou mesclagem possa ser mais rápida, o otimizador não poderá usar nenhum dos operadores devido à meta de linha.
    • Outro exemplo de uma junção nl é o uso de um predicado de junção de desigualdade em uma consulta. Por exemplo, SELECT .. FROM tab1 AS a JOIN tab 2 AS b ON a.id > b.id. O otimizador também não pode usar junções de mesclagem ou hash aqui.
  • Estatísticas desatualizadas: As consultas que escolhem um plano com base em estatísticas desatualizadas podem ser abaixo do ideal e levar muito tempo para serem executadas.
  • Loops sem fim: Consultas T-SQL que usam loops WHILE podem ser gravadas incorretamente. O código resultante nunca sai do loop e é executado sem parar. Essas consultas são verdadeiramente intermináveis. Eles correm até serem mortos manualmente.
  • Consultas complexas que têm muitas junções e tabelas grandes: Consultas que envolvem muitas tabelas unidas normalmente teriam planos de consulta complexos que podem levar muito tempo para serem executadas. Esse cenário é comum em consultas analíticas que não filtram linhas e que envolvem um grande número de tabelas.
  • Índices ausentes: As consultas poderão ser executadas significativamente mais rapidamente se os índices apropriados forem usados em tabelas. Os índices permitem que a seleção de um subconjunto dos dados forneça acesso mais rápido.

Solução

Etapa 1: Descobrir consultas intermináveis

Procure uma consulta interminável em execução no sistema. Você precisa determinar se uma consulta tem um longo tempo de execução, um longo tempo de espera (preso em um gargalo) ou um longo tempo de compilação.

1.1 Executar um diagnóstico

Execute a seguinte consulta de diagnóstico em sua instância do SQL Server em que a consulta sem fim está ativa:

DECLARE @cntr INT = 0

WHILE (@cntr < 3)
BEGIN
    SELECT TOP 10 s.session_id,
                    r.status,
                    CAST(r.cpu_time / (1000 * 60.0) AS DECIMAL(10,2)) AS cpu_time_minutes,
                    CAST(r.total_elapsed_time / (1000 * 60.0) AS DECIMAL(10,2)) AS elapsed_minutes,
                    r.logical_reads,
                    r.wait_time,
                    r.wait_type,
                    r.wait_resource,
                    r.reads,
                    r.writes,
                    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

1.2 Examinar a saída

Há vários cenários que podem fazer com que uma consulta seja executada por muito tempo: execução longa, longa espera e compilação longa. Para obter mais informações sobre por que uma consulta pode ser executada lentamente, consulte Running vs. Waiting: por que as consultas estão lentas?

Tempo de execução longo

As etapas de solução de problemas neste artigo são aplicáveis quando você recebe uma saída semelhante à seguinte, em que o tempo da CPU aumenta proporcionalmente ao tempo decorrido sem tempos de espera significativos.

session_id status cpu_time_minutes elapsed_time_minutes logical_reads wait_time_minutes wait_type
56 executando 64.40 23.50 0 0.00 NULO

A consulta será executada continuamente se tiver:

  • Um tempo de CPU crescente
  • Um status de running ou runnable
  • Tempo de espera mínimo ou zero
  • Sem wait_type

Nessa situação, a consulta está lendo linhas, ingressando, processando resultados, calculando ou formatação. Essas atividades são todas ações associadas à CPU.

Observação

As alterações nesse logical_reads caso não são relevantes porque algumas solicitações T-SQL associadas à CPU, como a execução de cálculos ou um WHILE loop, podem não fazer nenhuma leitura lógica.

Se a consulta lenta atender a esses critérios, concentre-se em reduzir seu runtime. Normalmente, a redução do runtime envolve a redução do número de linhas que a consulta precisa processar ao longo de sua vida aplicando índices, reescrevendo a consulta ou atualizando estatísticas. Para obter mais informações, consulte a seção Resolução .

Longo tempo de espera

Este artigo não é aplicável a cenários de espera prolongada. Em um cenário de espera, você pode receber uma saída semelhante ao exemplo a seguir no qual o uso da CPU não muda nem muda ligeiramente porque a sessão está aguardando um recurso:

session_id status cpu_time_minutes elapsed_time_minutes logical_reads wait_time_minutes wait_type
56 suspenso 0.03 4.20 50 4.10 LCK_M_U

O tipo de espera indica que a sessão está aguardando um recurso. Um longo tempo decorrido e um longo tempo de espera indicam que a sessão está aguardando a maior parte de sua vida útil para esse recurso. O curto tempo de CPU indica que pouco tempo foi gasto, na verdade, processando a consulta.

Para solucionar problemas de consultas que são longas devido a esperas, consulte Solucionar problemas de consultas de execução lenta no SQL Server.

Longo tempo de compilação

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

Etapa 2: Coletar logs de diagnóstico manualmente

Depois de determinar que existe uma consulta sem fim no sistema, você pode coletar os dados de plano da consulta para solucionar mais problemas. Para coletar os dados, use um dos métodos a seguir, dependendo da sua versão do SQL Server.

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 saber se os dados mostram indicações óbvias do que está causando a lentidão. Exemplos de indicações típicas incluem:

    • Verificações de tabela ou índice (examine as linhas estimadas)
    • Loops aninhados impulsionados por um enorme conjunto de dados de tabela externa
    • Loops aninhados que têm um branch grande no lado interno do loop
    • Spools de tabela
    • Funções na SELECT lista que levam muito tempo para processar cada linha

  3. Se a consulta for executada mais rapidamente a qualquer momento, você poderá capturar as execuções "rápidas" (plano de execução XML real) para comparar os resultados.

Usar o LogScout do SQL para capturar consultas intermináveis

Você pode usar o LogScout do SQL para capturar logs enquanto uma consulta sem fim está em execução. Use o cenário de consulta sem fim com o seguinte comando:

.\SQL_LogScout.ps1 -Scenario "NeverEndingQuery" -ServerName "SQLInstance"

Observação

Esse processo de captura de log requer que a consulta longa consuma pelo menos 60 segundos de tempo de CPU.

O SQL LogScout captura pelo menos três planos de consulta para cada consulta de alto consumo de CPU. Você pode encontrar nomes de arquivo semelhantes servername_datetime_NeverEnding_statistics_QueryPlansXml_Startup_sessionId_#.sqlplana . Você pode usar esses arquivos na próxima etapa quando revisar os planos para identificar o motivo da execução de consulta longa.

Etapa 3: Examinar os planos coletados

Esta seção discute como examinar os dados coletados. Ele usa os vários planos de consulta XML (usando extensão .sqlplan) coletados no Microsoft SQL Server 2016 SP1 e versões e builds posteriores.

Compare os planos de execução seguindo estas etapas:

  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 entre os dois planos.

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

    Por exemplo:

    Captura de tela que mostra a comparação de planos de consulta no SSMS.

Etapa 4: Resolução

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

  2. Procure recomendações de índice ausentes no plano de consulta e aplique qualquer um que você encontrar.

  3. Simplifique a consulta:

    • 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 um longo tempo devido a uma meta de linha do otimizador.
    • Evite usar CTEs (Expressões de Tabela Comuns) nesses casos porque 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
    • Dica de FORCE ORDER
    • Dica de FORCESEEK
    • RECOMPILE
    • USE PLAN N'<xml_plan>' (se você tiver um plano de consulta rápido que possa forçar)

  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.

Conteúdo relacionado

  • Last updated on