Solución de problemas de consultas de ejecución lenta en SQL Server

Versión del producto original: SQL Server
Número de KB original: 243589

Resumen

En este artículo se describe cómo solucionar problemas de consultas de ejecución lenta en SQL Server, uno de los problemas de rendimiento más comunes que experimentan las aplicaciones de base de datos. Proporciona una metodología que determina si una consulta es lenta porque está esperando un cuello de botella o porque se está ejecutando (en ejecución) en la CPU durante mucho tiempo. Después de identificar en qué categoría entra una consulta, puede aplicar la resolución de coincidencias, como reducir esperas, optimizar índices, actualizar estadísticas, examinar el plan de consulta o resolver planes que distinguen parámetros.

Esta metodología se aplica a SQL Server. El enfoque de alto nivel de separar las consultas en espera de las consultas en ejecución también puede ayudar al solucionar problemas de rendimiento de consultas en Azure SQL Database y Azure SQL Managed Instance, aunque las herramientas, permisos y opciones de recursos disponibles difieren en esos productos.

Cómo identificar consultas de ejecución lenta en SQL Server

Para establecer que tiene problemas de rendimiento de consultas en la instancia de SQL Server, empiece por examinar las consultas por su tiempo de ejecución (tiempo transcurrido). Compruebe si el tiempo supera un umbral (en milisegundos) que estableció en función de una línea base de rendimiento establecida. Por ejemplo, en un entorno de pruebas de esfuerzo, puede establecer un umbral de no más de 300 ms para la carga de trabajo y, a continuación, usar ese umbral.

A continuación, identifique todas las consultas que superen ese umbral, centrándose en cada consulta individual y su duración de línea base preestablecida. En última instancia, los usuarios empresariales se preocupan por la duración general de las consultas de base de datos, por lo que el enfoque principal es la duración de la ejecución. Otras métricas, como el tiempo de CPU y las lecturas lógicas, se recopilan para ayudar a reducir la investigación.

Si Almacén de consultas está habilitado en la base de datos, también puede usar los informes integrados para buscar las consultas de mayor duración y comparar su rendimiento con el tiempo.

  • Para ejecutar instrucciones actualmente, compruebe las columnas total_elapsed_time y cpu_time en sys.dm_exec_requests. Ejecute la consulta siguiente para obtener los datos:

    SELECT 
    req.session_id
    , req.total_elapsed_time AS duration_ms
    , req.cpu_time AS cpu_time_ms
    , req.total_elapsed_time - req.cpu_time AS wait_time
    , req.logical_reads
    , SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1, 
       ((CASE statement_end_offset
           WHEN -1
           THEN DATALENGTH(ST.text)  
           ELSE req.statement_end_offset
         END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 
      1, 512)  AS statement_text  
FROM sys.dm_exec_requests AS req
    CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
ORDER BY total_elapsed_time DESC;

  • Para las ejecuciones anteriores de la consulta, compruebe las columnas last_elapsed_time y last_worker_time en sys.dm_exec_query_stats. Ejecute la consulta siguiente para obtener los datos:

    SELECT t.text,
     (qs.total_elapsed_time/1000) / qs.execution_count AS avg_elapsed_time,
     (qs.total_worker_time/1000) / qs.execution_count AS avg_cpu_time,
     ((qs.total_elapsed_time/1000) / qs.execution_count ) - ((qs.total_worker_time/1000) / 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/1000) AS cumulative_elapsed_time_all_executions
FROM sys.dm_exec_query_stats qs
     CROSS apply sys.Dm_exec_sql_text (sql_handle) t
WHERE t.text like '<Your Query>%'
-- Replace <Your Query> with your query or the beginning part of your query. The special chars like '[','_','%','^' in the query should be escaped.
ORDER BY (qs.total_elapsed_time / qs.execution_count) DESC

    Nota:

    Si avg_wait_time muestra un valor negativo, se trata de una consulta paralela.

  • Si puede ejecutar la consulta a petición en SQL Server Management Studio (SSMS), sqlcmd o la extensión MSSQL para Visual Studio Code, ejecútelo con SET STATISTICS TIMEON y SET STATISTICS IOON.

    SET STATISTICS TIME ON
SET STATISTICS IO ON
<YourQuery>
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

    A continuación, en Mensajes, verá el tiempo de CPU, el tiempo transcurrido y las lecturas lógicas como esta:

      Table 'tblTest'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

  SQL Server Execution Times:
    CPU time = 460 ms,  elapsed time = 470 ms.

  • Si puede recopilar un plan de consulta, compruebe los datos de las propiedades del plan de ejecución.

    1. Ejecute la consulta con Incluir plan de ejecución actual activado.

    2. Seleccione el operador más a la izquierda en el plan de ejecución.

    3. En Propiedades, expanda la propiedad QueryTimeStats .

    4. Compruebe ElapsedTime y CpuTime.

      Captura de pantalla de la ventana de propiedades del plan de ejecución de SQL Server con la propiedad QueryTimeStats expandida.

Ejecución frente a espera: ¿por qué las consultas se ralentizan en SQL Server?

Si encuentra consultas que superan el umbral predefinido, examine por qué podrían ser lentas. La causa de los problemas de rendimiento de las consultas en SQL Server se divide en dos categorías: en ejecución o en espera.

  • WAITING: las consultas pueden ser lentas porque esperan un cuello de botella durante mucho tiempo. Consulte una lista detallada de cuellos de botella en tipos de esperas.

  • RUNNING: Las consultas pueden ser lentas porque se ejecutan durante mucho tiempo. En otras palabras, estas consultas usan activamente recursos de CPU.

Una consulta puede estar ejecutándose durante algún tiempo y esperar algún tiempo en su duración. Sin embargo, el enfoque es determinar cuál es la categoría dominante que contribuye a su largo tiempo transcurrido. Por lo tanto, la primera tarea consiste en establecer en qué categoría se encuentran las consultas. Es sencillo: si una consulta no se está ejecutando, está esperando. Idealmente, una consulta pasa la mayor parte de su tiempo transcurrido en un estado en ejecución y muy poco tiempo esperando recursos. Además, en el mejor de los casos, una consulta se ejecuta dentro o por debajo de una línea base predeterminada. Compare el tiempo transcurrido y el tiempo de CPU de la consulta para determinar el tipo de problema.

Tipo 1: enlazado a CPU (ejecutor)

Si el tiempo de CPU está cerca, es igual a o superior al tiempo transcurrido, puede tratarlo como una consulta enlazada a la CPU. Por ejemplo, si el tiempo transcurrido es de 3000 milisegundos (ms) y el tiempo de CPU es de 2900 ms, significa que la mayor parte del tiempo transcurrido se dedica a la CPU. A continuación, podemos decir que es una consulta enlazada a la CPU.

Ejemplos de consultas en ejecución (enlazadas a CPU):

Tiempo transcurrido (ms) Tiempo de CPU (ms) Lecturas (lógicas)
3200 3000 300000
1080 1 000 20

Lecturas lógicas: leer páginas de datos o índices en la memoria caché, son los controladores más frecuentes del uso de CPU en SQL Server. Podría haber escenarios en los que el uso de CPU procede de otros orígenes: un bucle while (en T-SQL u otro código como XProcs o objetos CRL de SQL). En el segundo ejemplo de la tabla se muestra este escenario, donde la mayor parte de la utilización de la CPU no se debe a las lecturas.

Nota:

Si el tiempo de CPU es mayor que la duración, indica que se ejecuta una consulta paralela; varios subprocesos usan la CPU al mismo tiempo. Para obtener más información, consulte Consultas paralelas: ejecutor o proceso en espera.

Tipo 2: Esperando un cuello de botella (waiter)

Una consulta está esperando en un punto de cuello de botella si el tiempo transcurrido es significativamente mayor que el tiempo de procesamiento de la CPU. El tiempo transcurrido incluye el tiempo que ejecuta la consulta en la CPU (tiempo de CPU) y el tiempo en espera de que se libere un recurso (tiempo de espera). Por ejemplo, si el tiempo transcurrido es de 2000 ms y el tiempo de CPU es de 300 ms, el tiempo de espera es de 1700 ms (2000 - 300 = 1700). Para obtener más información, vea Tipos de esperas.

Ejemplos de consultas en espera:

Tiempo transcurrido (ms) Tiempo de CPU (ms) Lecturas (lógicas)
2000 300 28000
10080 700 80000

Consultas paralelas: ejecutor o esperador

Las consultas paralelas pueden usar más tiempo de CPU que la duración total. El objetivo del paralelismo es permitir que varios subprocesos ejecuten partes de una consulta simultáneamente. En un segundo de la hora del reloj, una consulta puede usar ocho segundos de tiempo de CPU ejecutando ocho subprocesos paralelos. Por lo tanto, resulta difícil determinar un límite de CPU o una consulta en espera en función del tiempo transcurrido y la diferencia de tiempo de CPU. Sin embargo, como regla general, siga los principios enumerados en las dos secciones anteriores. El resumen es:

  • Si el tiempo transcurrido es mucho mayor que el tiempo de CPU, considere que es un waiter.
  • Si el tiempo de CPU es mucho mayor que el tiempo transcurrido, tenga en cuenta que es un ejecutor.

Ejemplos de consultas paralelas:

Tiempo transcurrido (ms) Tiempo de CPU (ms) Lecturas (lógicas)
1200 8100 850000
3080 12300 1 500 000

Representación visual de alto nivel de la metodología de solución de problemas

Captura de pantalla de un diagrama de flujo que muestra cómo solucionar problemas de consultas de ejecución lenta en SQL Server separando las consultas en espera de las consultas en ejecución.

Diagnóstico y resolución de consultas en espera en SQL Server

Si establece que las consultas de interés son waiters, céntrese en resolver problemas de cuello de botella. De lo contrario, vaya a Diagnosticar y resolver consultas en ejecución.

Para optimizar una consulta que está experimentando cuellos de botella, identifique cuánto dura 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 consiste en esperar.

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 1.0 * (qs.total_elapsed_time - qs.total_worker_time) / NULLIF(qs.total_elapsed_time, 0)
         > 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 las WaitStats de las propiedades del plan de ejecución en SSMS.

    1. Ejecute la consulta con Incluir el plan de ejecución real activado.
    2. Haga clic derecho en el operador más a 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.

Diagnóstico y resolución de consultas en ejecución en SQL Server

Si el tiempo de CPU (trabajador) está muy cerca de la duración total transcurrida, la consulta pasa la mayor parte de su tiempo en ejecución. Normalmente, cuando el motor de SQL Server impulsa un uso elevado de la CPU, el uso elevado de cpu procede de las consultas que impulsan un gran número de lecturas lógicas (la razón más común).

Para identificar las consultas responsables de la alta actividad de CPU actual, ejecute las siguientes instrucciones:

SELECT TOP 10 s.session_id,
           r.status,
           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
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
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC

Si las consultas no impulsan la CPU en este momento, puede ejecutar la siguiente instrucción para buscar consultas históricas enlazadas a la CPU:

SELECT TOP 10  qs.last_execution_time, st.text AS batch_text,
    SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, ((CASE qs.statement_end_offset WHEN - 1 THEN DATALENGTH(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text,
    (qs.total_worker_time / 1000) / qs.execution_count AS avg_cpu_time_ms,
    (qs.total_elapsed_time / 1000) / qs.execution_count AS avg_elapsed_time_ms,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    (qs.total_worker_time / 1000) AS cumulative_cpu_time_all_executions_ms,
    (qs.total_elapsed_time / 1000) AS cumulative_elapsed_time_all_executions_ms
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
ORDER BY(qs.total_worker_time / qs.execution_count) DESC

Métodos comunes para resolver las consultas de larga duración, enlazadas a la CPU

Contenido relacionado

  • Last updated on