Solucionar problemas de consultas que parecem nunca terminar em SQL Server

Artigo
07/30/2024

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:

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

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.

session_id	status	cpu_time	logical_reads	wait_type
56	executando	7038	101000	NULO
56	executável	12040	301000	NULO
56	executando	17020	523000	NULO

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

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:

Capture o XML estimado do plano de execução da consulta.
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.
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.

A infraestrutura de criação de perfil de consulta leve foi introduzida nessas versões do SQL Server. Ele permite que você capture estatísticas reais durante a execução de uma consulta lenta. Esse recurso de solução de problemas permite examinar operadores de consulta em um plano de consulta em tempo de execução e entender onde a maior parte do tempo é gasta em uma consulta.

Para identificar as etapas lentas na consulta usando a infraestrutura de criação de perfil de estatísticas de execução de consulta leve v1, siga estas etapas:

Execute os seguintes comandos para ativar o query_thread_profile XEvent:

CREATE EVENT SESSION [NodePerfStats] ON SERVER
ADD EVENT sqlserver.query_thread_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

ALTER EVENT SESSION [NodePerfStats] ON SERVER STATE = START

Inicie a consulta interminável afetada do aplicativo.

Execute o seguinte comando várias vezes com um minuto ou mais de intervalo para verificar as estatísticas de execução em tempo de execução para os operadores do plano de consulta:

SELECT CONVERT (varchar(30), getdate(), 126) as runtime,
            qp.session_id,
            convert(nvarchar(48), qp.physical_operator_name) as physical_operator_name,
            qp.row_count,
            qp.estimate_row_count,
            qp.node_id,
            req.cpu_time,
            req.total_elapsed_time,
            substring
            (REPLACE
            (REPLACE
                (SUBSTRING
                (SQLText.text
                , (req.statement_start_offset/2) + 1
                , (
                    (CASE statement_END_offset
                        WHEN -1
                        THEN DATALENGTH(SQLText.text)  
                        ELSE req.statement_END_offset
                        END
                        - req.statement_start_offset)/2) + 1)
            , CHAR(10), ' '), CHAR(13), ' '), 1, 512)  AS active_statement_text
FROM sys.dm_exec_query_profiles qp 
RIGHT OUTER JOIN sys.dm_exec_requests req
    ON qp.session_id = req.session_id
LEFT OUTER JOIN sys.dm_exec_sessions sess
    on req.session_id = sess.session_id
LEFT OUTER JOIN sys.dm_exec_connections conn on conn.session_id = req.session_id
OUTER APPLY sys.dm_exec_sql_text (ISNULL (req.sql_handle, conn.most_recent_sql_handle)) as SQLText
WHERE req.session_id <> @@SPID 
    AND sess.is_user_process = 1 
ORDER BY qp.session_id asc, row_count desc 
--this is to prevent massive grants
OPTION (max_grant_percent = 3, MAXDOP 1)

Capture três ou quatro instantâneos espaçados de um minuto para fornecer dados suficientes para análise. Especificamente, você pode comparar os números de cada operador ao longo do row_count tempo e ver qual mostra um aumento significativo na contagem de linhas (milhões ou mais).
Em uma nova janela de consulta no SSMS, capture um plano de consulta estimado para a consulta com problema executando os seguintes comandos:
```
SET SHOWPLAN_XML ON
GO
<problem query here>
GO
SET SHOWPLAN_XML OFF
```
Usando a ID do nó com a contagem de linhas mais alta identificada pela consulta na etapa 3, localize o mesmo nó no plano de consulta estimado. Esta etapa ajudará a entender qual operador do plano é a principal causa do longo tempo de execução.

Pare o XEvent executando o seguinte comando:

ALTER EVENT SESSION [NodePerfStats] ON SERVER STATE = STOP

Você pode usar a infraestrutura de criação de perfil de estatísticas de execução de consulta leve v2 para capturar planos de consulta em tempo real com valores reais para contagem de linhas. Essa infraestrutura de criação de perfil permite examinar operadores de consulta em um plano de consulta em tempo de execução e entender onde a maior parte do tempo é gasta em uma consulta.

Para identificar as etapas lentas na consulta, siga estas etapas:

Para habilitar a infraestrutura leve nessas versões do SQL Server, use um dos seguintes métodos:

Habilite o sinalizador de rastreamento 7412 executando o seguinte comando:
```
DBCC TRACEON (7412, -1)
```

Como alternativa, habilite o query_thread_profile XEvent executando os seguintes comandos:

CREATE EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER
ADD EVENT sqlserver.query_plan_profile(
 ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
   sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
   sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
   sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
 EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
 MAX_DISPATCH_LATENCY=30 SECONDS,
 MAX_EVENT_SIZE=0 KB,
 MEMORY_PARTITION_MODE=NONE,
 TRACK_CAUSALITY=OFF,
 STARTUP_STATE=OFF);

ALTER EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER STATE = START

Inicie a consulta interminável afetada no aplicativo.
Use um comando semelhante ao seguinte para identificar a Session_id consulta interminável que está sendo executada:
```
SELECT t.text, session_id 
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as t
```
Execute o comando a seguir três ou quatro vezes espaçadas em um minuto para examinar o plano de consulta e as estatísticas reais no plano. Certifique-se de salvar o plano de consulta todas as vezes, para que você possa compará-los e estabelecer qual operador de consulta está consumindo a maior parte do tempo da CPU. Especificamente, você pode comparar a contagem de linhas (Número Real de Linhas) para cada operador ao longo do tempo e ver qual dos operadores está mostrando um aumento significativo na contagem de linhas (milhões ou mais). Substitua <session_id> pelo valor inteiro encontrado na etapa 3 anterior.
```
SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
```

Pare o XEvent se você iniciou um ou desative o sinalizador de rastreamento:

ALTER EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER STATE = STOP
-- or
DBCC TRACEOFF (7412, -1)

Você pode usar a infraestrutura de criação de perfil de estatísticas de execução de consulta leve v3 para capturar planos de consulta ao vivo com valores reais para contagem de linhas. Essa infraestrutura de criação de perfil permite examinar operadores de consulta em um plano de consulta em tempo de execução e entender onde a maior parte do tempo é gasta em uma consulta. A criação de perfil leve é habilitada por padrão no SQL Server 2019.

Para identificar as etapas lentas na consulta, siga estas etapas:

Inicie a consulta interminável afetada do seu aplicativo.
Use um comando semelhante ao seguinte para identificar a Session_id consulta interminável que está sendo executada:
```
SELECT t.text, session_id 
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as t
```
Execute o comando a seguir três ou quatro vezes para examinar o plano de consulta e as estatísticas reais no plano. Certifique-se de salvar o plano de consulta todas as vezes, para que você possa compará-los e estabelecer qual operador de consulta está consumindo a maior parte do tempo da CPU. Substitua <session_id> pelo valor inteiro encontrado na etapa 3 anterior.
```
SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
```
Especificamente, selecione o link XML na coluna query_plan . Quando o plano de consulta gráfica for aberto em uma nova janela, clique com o botão direito do mouse nele e selecione Salvar plano de execução como.... Repita as etapas para capturar três ou quatro instantâneos espaçados por um minuto para fornecer dados suficientes para análise. Especificamente, você pode comparar a contagem de linhas (número real de linhas) para cada operador ao longo do tempo e ver qual dos operadores está mostrando um aumento significativo na contagem de linhas (milhões ou mais).
Observação

Se você não estiver obtendo nenhuma saída do , poderá verificar se a opção LAST_QUERY_PLAN_STATS de banco de dados foi desativada sys.dm_exec_query_statistics_xmlexecutando o seguinte comando:
```
SELECT name, value, value_for_secondary, is_value_default 
FROM sys.database_scoped_configurations
WHERE name = 'LAST_QUERY_PLAN_STATS'
```
Você pode habilitar as estatísticas do último plano de consulta no nível do banco de dados executando ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON.

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:

Abra um arquivo de plano de execução de consulta salvo anteriormente (.sqlplan).
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.
Escolha o segundo arquivo de plano de consulta que você gostaria de comparar.
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.
Compare o segundo e o terceiro planos para ver se o maior fluxo de linhas ocorre nos mesmos operadores.

Veja um exemplo:

Resolução

Certifique-se de que as estatísticas sejam atualizadas para as tabelas usadas na consulta.
Procure uma recomendação de índice ausente no plano de consulta e aplique qualquer.
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.
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
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.

Partilhar via