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

Artículo
01/19/2024

En este artículo se describen los pasos para solucionar el problema en el que tiene una consulta que parece no completarse nunca, o para que se complete puede tardar muchas horas o días.

¿Qué es una consulta interminable?

Este documento se centra en las consultas que continúan ejecutándose o compilando, es decir, su CPU sigue aumentando. No se aplica a las consultas que están bloqueadas o esperando en algún recurso que nunca se libera (la CPU sigue siendo 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 never-ending se usa para describir la percepción de que una consulta no se completa cuando, de hecho, la consulta se completará finalmente.

Identificación de una consulta sin fin

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

Ejecute la consulta siguiente:

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 se 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 pertinentes en este caso, ya que es posible que algunas solicitudes T-SQL enlazadas a la CPU no realicen ninguna lectura lógica (por ejemplo, realizar cálculos o un WHILE bucle).
  
  session_id status cpu_time logical_reads wait_time wait_type
  
  56 Corriendo 7038 101000 0 NULL
  
  56 ejecutable 12040 301000 0 NULL
  
  56 Corriendo 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 en 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	Corriendo	7038	101000	NULL
56	ejecutable	12040	301000	NULL
56	Corriendo	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 aumenta continuamente con el tiempo, pero eso no está controlado por la ejecución de consultas. En su lugar, podría estar controlada por una compilación excesivamente larga (análisis y 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 alguna indicación obvia de de dónde puede provenir la lentitud. Entre los ejemplos típicos se incluyen:
- Exámenes de tabla o índice (examine las filas estimadas).
- Bucles anidados controlados por un enorme conjunto de datos de tabla externa.
- Bucles anidados con una rama grande en el lado interno del bucle.
- Grupos de tablas.
- 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 de un plan de consulta en tiempo de ejecución y comprender dónde se dedica la mayor parte del tiempo a 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 ligera v1, siga estos pasos:

Ejecute los siguientes comandos 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 sin fin afectada desde la aplicación.

Ejecute el siguiente comando varias veces al minuto o más para comprobar las estadísticas de ejecución de 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 proporcionarle 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, ejecute los comandos siguientes para capturar un plan de consulta estimado para la consulta de problemas:
```
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 evento XEvent mediante la ejecución del siguiente comando:
```
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 en directo con valores reales para el recuento de filas. Esta infraestructura de generación de perfiles permite examinar los operadores de consulta de un plan de consulta en tiempo de ejecución y comprender dónde se dedica la mayor parte del tiempo a 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 ejecutando el siguiente comando:
```
DBCC TRACEON (7412, -1)
```

Como alternativa, habilite XEvent query_thread_profile 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 sin fin afectada desde la aplicación.
Use un comando similar al siguiente para identificar el de la Session_id consulta interminable 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, para que pueda 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) de cada operador a lo largo del tiempo y ver cuál de los operadores muestra un aumento significativo en el número 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 evento 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 en vivo con valores reales para el recuento de filas. Esta infraestructura de generación de perfiles permite examinar los operadores de consulta de un plan de consulta en tiempo de ejecución y comprender dónde se dedica la mayor parte del tiempo a 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 sin fin afectada desde la aplicación.
Use un comando similar al siguiente para identificar el de la Session_id consulta interminable 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, para que pueda 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 gráfico de consulta 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 espaciados con un minuto de diferencia para proporcionarle datos suficientes para el análisis. En concreto, puede comparar el número de filas (número real de filas) de cada operador a lo largo del tiempo y ver cuál de los operadores muestra un aumento significativo en el número 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 indiquen 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 en dos planes.
Compare los planes segundo y tercero para ver si el flujo más grande de filas se produce en los mismos operadores.

Aquí le mostramos un ejemplo:

Solución

Asegúrese de que las estadísticas se actualizan para las tablas usadas en la consulta.
Busque una recomendación de índice que falte 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.
- Desmoronarse.
- Seleccione algunas partes en tablas temporales y únase a ellas 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_ROWGOALsugerencia. Para obtener más información, vea Row Goals Gone Rogue.
- Evite usar expresiones de tabla comunes (CTE) en casos como cuando combinen instrucciones en una sola consulta grande.
Pruebe a usar sugerencias de consulta para generar un mejor plan:
- HASH JOIN o MERGE JOIN sugerencia
- FORCE ORDER Pista
- FORCESEEK Pista
- 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 plan conocido si existe este tipo de 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 espera 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, reste 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	1000	6080

Identificar el cuello de botella o la espera

Para identificar consultas históricas de larga espera (por ejemplo, >el 20 % del tiempo total transcurrido es 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 superiores a 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, la 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 las 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 problemas comunes de tipo de espera:

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

Share via