Solución de problemas de consultas que parecen no terminar nunca en SQL Server

Artículo
07/30/2024

En este artículo se describen los pasos de solución de problemas del problema en el que tiene una consulta que parece que nunca se ha completado o que la realización puede tardar muchas horas o días.

¿Qué es una consulta sin fin?

Este documento se centra en las consultas que continúan ejecutándose o compilando, es decir, su CPU continúa aumentando. No se aplica a las consultas que están bloqueadas o esperando algún recurso que nunca se libera (la CPU permanece constante o cambia muy poco).

Importante

Si se deja una consulta para finalizar su ejecución, finalmente se completará. Puede tardar unos segundos, o puede tardar varios días.

El término nunca finalizado se usa para describir la percepción de una consulta que no se completa cuando, de hecho, la consulta se completará finalmente.

Identificación de una consulta sin fin

Para identificar si una consulta se está ejecutando o se bloquea continuamente en un cuello de botella, siga estos pasos:

Ejecute la siguiente 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

Compruebe la salida de ejemplo.
- Los pasos de solución de problemas de este artículo se aplican específicamente cuando observa una salida similar a la siguiente en la que la CPU aumenta proporcionalmente con el tiempo transcurrido, sin tiempos de espera significativos. Es importante tener en cuenta que los cambios en logical_reads no son relevantes en este caso, ya que algunas solicitudes T-SQL enlazadas a CPU podrían no hacer ninguna lectura lógica en absoluto (por ejemplo, realizar cálculos o un WHILE bucle).
  
  session_id status cpu_time logical_reads wait_time wait_type
  
  56 en ejecución 7038 101000 0 NULL
  
  56 ejecutable 12040 301000 0 NULL
  
  56 en ejecución 17020 523000 0 NULL
- Este artículo no es aplicable si observa un escenario de espera similar al siguiente en el que la CPU no cambia o cambia muy ligeramente, y la sesión está esperando un 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 obtener más información, consulte Diagnóstico de esperas o cuellos de botella.

session_id	status	cpu_time	logical_reads	wait_type
56	en ejecución	7038	101000	NULL
56	ejecutable	12040	301000	NULL
56	en ejecución	17020	523000	NULL

session_id	status	logical_reads	wait_time	wait_type
56	suspended	3	8312	LCK_M_U
56	suspended	3	13318	LCK_M_U
56	suspended	5	18331	LCK_M_U

Tiempo de compilación largo

En raras ocasiones, es posible que observe que la CPU está aumentando continuamente con el tiempo, pero que no está controlada por la ejecución de consultas. En su lugar, podría controlarse mediante una compilación excesivamente larga (el análisis y la compilación de una consulta). En esos casos, compruebe la columna de salida transaction_name y busque un valor de sqlsource_transform. Este nombre de transacción indica una compilación.

Recopilación de datos de diagnóstico

Para recopilar datos de diagnóstico mediante SQL Server Management Studio (SSMS), siga estos pasos:

Capture el XML del plan de ejecución de consultas estimado.
Revise el plan de consulta para ver si hay indicaciones obvias de dónde puede proceder la lentitud. Entre los ejemplos típicos se incluyen:
- Exámenes de tabla o índice (examine las filas estimadas).
- Bucles anidados controlados por un conjunto de datos de tabla externa enorme.
- Bucles anidados con una rama grande en el lado interno del bucle.
- Colas de tabla.
- Funciones de la SELECT lista que tardan mucho tiempo en procesar cada fila.
Si la consulta se ejecuta rápidamente en cualquier momento, puede capturar las ejecuciones "rápidas " del plan de ejecución XML real que se va a comparar.

La infraestructura ligera de generación de perfiles de consultas se introdujo en estas versiones de SQL Server. Permite capturar estadísticas reales durante la ejecución de una consulta lenta. Esta característica de solución de problemas le permite examinar los operadores de consulta en un plan de consulta en tiempo de ejecución y comprender dónde se dedica la mayor parte del tiempo en una consulta.

Para identificar los pasos lentos de la consulta mediante la infraestructura de generación de perfiles de estadísticas de ejecución de consultas ligeras v1, siga estos pasos:

Ejecute los comandos siguientes para habilitar XEvent query_thread_profile :

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 la consulta nunca finalizada afectada desde la aplicación.

Ejecute el siguiente comando varias veces un minuto o más para comprobar las estadísticas de ejecución en tiempo de ejecución para los operadores del plan 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 tres o cuatro instantáneas separadas por un minuto para proporcionar datos suficientes para el análisis. En concreto, puede comparar los row_count números de cada operador a lo largo del tiempo y ver qué muestra un aumento significativo en el número de filas (millones o más).
En una nueva ventana de consulta de SSMS, capture un plan de consulta estimado para la consulta de problema mediante la ejecución de los siguientes comandos:
```
SET SHOWPLAN_XML ON
GO
<problem query here>
GO
SET SHOWPLAN_XML OFF
```
Con el identificador de nodo con el recuento de filas más alto identificado por la consulta en el paso 3, busque el mismo nodo en el plan de consulta estimado. Este paso ayudará a comprender qué operador del plan es la causa principal del tiempo de ejecución prolongado.

Detenga el XEvent ejecutando el comando siguiente:

ALTER EVENT SESSION [NodePerfStats] ON SERVER STATE = STOP

Puede usar la infraestructura de generación de perfiles de estadísticas de ejecución de consultas ligeras v2 para capturar planes de consulta dinámicos con valores reales para el recuento de filas. Esta infraestructura de generación de perfiles le permite examinar los operadores de consulta en un plan de consulta en tiempo de ejecución y comprender dónde se dedica la mayor parte del tiempo en una consulta.

Para identificar los pasos lentos de la consulta, siga estos pasos:

Para habilitar la infraestructura ligera en estas versiones de SQL Server, use uno de los métodos siguientes:

Habilite la marca de seguimiento 7412 mediante la ejecución del comando siguiente:
```
DBCC TRACEON (7412, -1)
```

Como alternativa, habilite el query_thread_profile XEvent mediante la ejecución de los siguientes 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 la consulta nunca finalizada afectada desde la aplicación.
Use un comando similar al siguiente para identificar el de la Session_id consulta sin finalizar que se está ejecutando:
```
SELECT t.text, session_id 
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as t
```
Ejecute el siguiente comando tres o cuatro veces separados por un minuto para examinar el plan de consulta y las estadísticas reales del plan. Asegúrese de guardar el plan de consulta cada vez, por lo que puede compararlos y establecer qué operador de consulta consume la mayor parte del tiempo de CPU. En concreto, puede comparar el recuento de filas (número real de filas) para cada operador a lo largo del tiempo y ver cuál de los operadores muestra un aumento significativo en el recuento de filas (millones o más). Reemplace por <session_id> el valor entero que encontró en el paso 3 anterior.
```
SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
```

Detenga el XEvent si inició uno o deshabilite la marca de seguimiento:

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

Puede usar la infraestructura de generación de perfiles de estadísticas de ejecución de consultas ligeras v3 para capturar planes de consulta dinámicos con valores reales para el recuento de filas. Esta infraestructura de generación de perfiles le permite examinar los operadores de consulta en un plan de consulta en tiempo de ejecución y comprender dónde se dedica la mayor parte del tiempo en una consulta. La generación de perfiles ligera está habilitada de forma predeterminada en SQL Server 2019.

Para identificar los pasos lentos de la consulta, siga estos pasos:

Inicie la consulta nunca finalizada afectada desde la aplicación.
Use un comando similar al siguiente para identificar el de la Session_id consulta sin finalizar que se está ejecutando:
```
SELECT t.text, session_id 
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as t
```
Ejecute el siguiente comando tres o cuatro veces para examinar el plan de consulta y las estadísticas reales del plan. Asegúrese de guardar el plan de consulta cada vez, por lo que puede compararlos y establecer qué operador de consulta consume la mayor parte del tiempo de CPU. Reemplace por <session_id> el valor entero que encontró en el paso 3 anterior.
```
SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
```
En concreto, seleccione el vínculo XML en la columna query_plan . Una vez que se abra el plan de consulta gráfico en una nueva ventana, haga clic con el botón derecho en él y seleccione Guardar plan de ejecución como.... Repita los pasos para capturar tres o cuatro instantáneas separadas por un minuto para proporcionar datos suficientes para el análisis. En concreto, puede comparar el recuento de filas (número real de filas) para cada operador a lo largo del tiempo y ver cuál de los operadores muestra un aumento significativo en el recuento de filas (millones o más).
Nota:

Si no obtiene ninguna salida de sys.dm_exec_query_statistics_xml, puede comprobar si la opción LAST_QUERY_PLAN_STATS de base de datos se ha deshabilitado ejecutando el siguiente comando:
```
SELECT name, value, value_for_secondary, is_value_default 
FROM sys.database_scoped_configurations
WHERE name = 'LAST_QUERY_PLAN_STATS'
```
Puede habilitar las últimas estadísticas del plan de consulta en el nivel de base de datos ejecutando ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON.

Método para revisar los planes recopilados

En esta sección se muestra cómo revisar los datos recopilados. Usará los varios planes de consulta XML (mediante la extensión *.sqlplan) recopilados en SQL Server 2016 SP1 y versiones posteriores.

Siga estos pasos para comparar los planes de ejecución:

Abra un archivo de plan de ejecución de consultas guardado anteriormente (.sqlplan).
Haga clic con el botón derecho en un área en blanco del plan de ejecución y seleccione Comparar plan de presentación.
Elija el segundo archivo de plan de consulta que desea comparar.
Busque flechas gruesas que indican un gran número de filas que fluyen entre operadores. A continuación, seleccione el operador antes o después de la flecha y compare el número de filas reales entre dos planes.
Compare los planes segundo y tercero para ver si el mayor flujo de filas se produce en los mismos operadores.

Este es un ejemplo:

Solución

Asegúrese de que las estadísticas se actualicen para las tablas usadas en la consulta.
Busque una recomendación de índice que falta en el plan de consulta y aplique cualquiera.
Vuelva a escribir la consulta con el objetivo de simplificarla:
- Use predicados más selectivos WHERE para reducir los datos procesados por adelantado.
- Divida.
- Seleccione algunas partes en tablas temporales y acompáñelas más adelante.
- Quite TOP, EXISTSy FAST (T-SQL) en las consultas que se ejecutan durante mucho tiempo debido al objetivo de fila del optimizador. Como alternativa, puede usar la DISABLE_OPTIMIZER_ROWGOAL sugerencia . Para obtener más información, consulte Objetivos de fila no autorizados.
- Evite usar expresiones de tabla comunes (CTE) en tales casos, ya que combinan instrucciones en una sola consulta grande.
Pruebe a usar sugerencias de consulta para generar un plan mejor:
- HASH JOIN o MERGE JOIN sugerencia
- Sugerencia FORCE ORDER
- Sugerencia FORCESEEK
- RECOMPILE
- USE PLAN N'<xml_plan>' si tiene un plan de consulta rápido que puede forzar
Use Almacén de consultas (QDS) para forzar un buen plan conocido si existe dicho plan y si la versión de SQL Server admite Almacén de consultas.

Diagnóstico de esperas o cuellos de botella

Esta sección se incluye aquí como referencia en caso de que el problema no sea una consulta de conducción de CPU de larga duración. Puede usarlo para solucionar problemas de consultas que son largas debido a esperas.

Para optimizar una consulta que está esperando cuellos de botella, identifique cuánto tiempo es la espera y dónde está el cuello de botella (el tipo de espera). Una vez confirmado el tipo de espera, reduzca el tiempo de espera o elimine la espera por completo.

Para calcular el tiempo de espera aproximado, resta el tiempo de CPU (tiempo de trabajo) del tiempo transcurrido de una consulta. Normalmente, el tiempo de CPU es el tiempo de ejecución real y la parte restante de la duración de la consulta está esperando.

Ejemplos de cómo calcular la duración aproximada de la espera:

Tiempo transcurrido (ms)	Tiempo de CPU (ms)	Tiempo de espera (ms)
3200	3000	200
7080	1 000	6080

Identificación del cuello de botella o espera

Para identificar consultas históricas de larga espera (por ejemplo, >el 20 % del tiempo de espera total transcurrido es el tiempo de espera), ejecute la consulta siguiente. Esta consulta usa estadísticas de rendimiento para los planes de consulta almacenados en caché desde el inicio de 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 las consultas que se ejecutan actualmente con esperas de más de 500 ms, ejecute la consulta siguiente:

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

Si puede recopilar un plan de consulta, compruebe WaitStats de las propiedades del plan de ejecución en SSMS:
1. Ejecute la consulta con Incluir plan de ejecución real activado.
2. Haga clic con el botón derecho en el operador de la izquierda en la pestaña Plan de ejecución.
3. Seleccione Propiedades y, a continuación, propiedad WaitStats .
4. Compruebe waitTimeMs y WaitType.
Si está familiarizado con los escenarios PSSDiag/SQLdiag o SQL LogScout LightPerf/GeneralPerf, considere la posibilidad de usar cualquiera de ellos para recopilar estadísticas de rendimiento e identificar consultas en espera en la instancia de SQL Server. Puede importar los archivos de datos recopilados y analizar los datos de rendimiento con SQL Nexus.

Referencias para ayudar a eliminar o reducir las esperas

Las causas y resoluciones de cada tipo de espera varían. No hay ningún método general para resolver todos los tipos de espera. Estos son los artículos para solucionar y resolver problemas comunes de tipo de espera:

Para obtener descripciones de muchos tipos de espera y lo que indican, consulte la tabla en Tipos de esperas.

Compartir a través de