Explorar guías rápidas · SQL Server

Tecnología: SQL ServerCategoría: RendimientoNivel: IntermedioActualizado

Encontrar consultas con muchas lecturas

Identifica las consultas de SQL Server que realizan más lecturas lógicas y físicas, distingue la carga de trabajo dominada por lecturas de la dominada por CPU y elige el siguiente paso de optimización adecuado.

En esta página
  1. Problema
  2. Respuesta rápida
  3. Código / comandos
  4. 1. Encontrar las solicitudes activas que más lecturas lógicas realizan
  5. 2. Mostrar la instrucción que se está ejecutando dentro del lote
  6. 3. Encontrar consultas en caché por lecturas lógicas totales
  7. 4. Encontrar consultas con el promedio de lecturas lógicas más alto
  8. 5. Encontrar consumidores de lecturas de alta frecuencia
  9. 6. Agrupar consultas lógicamente equivalentes por query hash
  10. 7. Mostrar el plan de ejecución de las consultas en caché con muchas lecturas
  11. 8. Comparar las lecturas lógicas con las lecturas físicas
  12. 9. Usar Query Store para el historial de consultas con muchas lecturas
  13. 10. Comprobar si las lecturas altas se concentran en una sola tabla
  14. Qué significa el resultado
  15. Qué revisar
  16. Lecturas lógicas totales altas + número de ejecuciones alto
  17. Lecturas lógicas promedio altas + número de ejecuciones bajo
  18. Lecturas lógicas altas + CPU alto
  19. Lecturas lógicas altas + CPU bajo
  20. Lecturas físicas altas
  21. Gran diferencia entre las lecturas lógicas mínimas y máximas
  22. Notas de producción
  23. Las lecturas lógicas no son lecturas de disco
  24. Muchas lecturas lógicas no son automáticamente algo malo
  25. sys.dm_exec_query_stats se basa en la caché
  26. Las estadísticas de consultas se actualizan cuando termina la ejecución
  27. Los efectos del buffer pool importan
  28. Los permisos varían según la versión
  29. Errores comunes
  30. 1. Tratar las lecturas lógicas como E/S de disco
  31. 2. Asumir que muchas lecturas significan «índice faltante»
  32. 3. Mirar solo las lecturas totales
  33. 4. Mirar solo las lecturas promedio
  34. 5. Ignorar la variabilidad de los parámetros
  35. 6. Optimizar antes de comprobar los requisitos del conjunto de resultados
  36. 7. Ignorar la frecuencia de llamadas de la aplicación
  37. Siguiente paso
  38. Para profundizar

Problema

SQL Server está haciendo demasiada E/S, la presión sobre el buffer pool aumenta, la latencia del almacenamiento es visible o algunas consultas acceden a muchas más páginas de lo esperado.

El objetivo es responder:

  • ¿Qué consultas realizan más lecturas lógicas?
  • ¿Qué solicitudes activas están dominadas por lecturas ahora mismo?
  • ¿Qué consultas en caché son responsables del mayor volumen total de lecturas?
  • ¿Qué consultas son costosas por ejecución?
  • ¿Las lecturas altas se deben a scans, predicados deficientes, la estrategia de join o simplemente a cargas de trabajo grandes y legítimas?
  • ¿El problema es de E/S lógica, de E/S física o de ambas?

Respuesta rápida

Para las solicitudes activas, empieza por sys.dm_exec_requests.

SELECT TOP (20)
    r.session_id,
    DB_NAME(r.database_id) AS database_name,
    r.status,
    r.command,
    r.logical_reads,
    r.reads AS physical_reads,
    r.cpu_time AS cpu_ms,
    CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
    r.wait_type,
    r.blocking_session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    txt.text AS batch_text
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    r.session_id <> @@SPID
    AND s.is_user_process = 1
ORDER BY
    r.logical_reads DESC;

Para la carga de trabajo completada recientemente, usa sys.dm_exec_query_stats.

SELECT TOP (50)
    qs.execution_count,
    qs.total_logical_reads,
    CAST(
        qs.total_logical_reads * 1.0
        / NULLIF(qs.execution_count, 0)
        AS decimal(18,2)
    ) AS avg_logical_reads,
    qs.total_physical_reads,
    qs.last_execution_time,
    txt.text AS batch_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
ORDER BY
    qs.total_logical_reads DESC;

Usa las lecturas lógicas como evidencia del trabajo realizado sobre páginas en memoria. No trates un número alto de lecturas lógicas como prueba automática de un índice faltante.

Código / comandos

1. Encontrar las solicitudes activas que más lecturas lógicas realizan

SELECT TOP (20)
    r.session_id,
    DB_NAME(r.database_id) AS database_name,
    r.status,
    r.command,
    r.logical_reads,
    r.reads AS physical_reads,
    r.cpu_time AS cpu_ms,
    r.total_elapsed_time AS elapsed_ms,
    r.wait_type,
    r.wait_resource,
    r.blocking_session_id,
    txt.text AS batch_text
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    r.session_id <> @@SPID
ORDER BY
    r.logical_reads DESC;

Usa esta consulta durante un incidente en curso.

Un número alto de lecturas lógicas suele apuntar a scans grandes, accesos repetidos o rutas de acceso ineficientes.


2. Mostrar la instrucción que se está ejecutando dentro del lote

SELECT TOP (20)
    r.session_id,
    r.logical_reads,
    r.reads AS physical_reads,
    r.cpu_time AS cpu_ms,
    CASE
        WHEN r.statement_start_offset IS NULL THEN txt.text
        ELSE SUBSTRING(
            txt.text,
            (r.statement_start_offset / 2) + 1,
            (
                (
                    CASE r.statement_end_offset
                        WHEN -1 THEN DATALENGTH(txt.text)
                        ELSE r.statement_end_offset
                    END
                    - r.statement_start_offset
                ) / 2
            ) + 1
        )
    END AS statement_text,
    txt.text AS batch_text
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    r.session_id <> @@SPID
ORDER BY
    r.logical_reads DESC;

Esto es más útil que revisar el procedimiento almacenado completo cuando una sola instrucción es la que provoca la presión de lectura.


3. Encontrar consultas en caché por lecturas lógicas totales

SELECT TOP (50)
    qs.execution_count,
    qs.creation_time,
    qs.last_execution_time,

    qs.total_logical_reads,

    CAST(
        qs.total_logical_reads * 1.0
        / NULLIF(qs.execution_count, 0)
        AS decimal(18,2)
    ) AS avg_logical_reads,

    qs.total_physical_reads,

    CAST(
        qs.total_physical_reads * 1.0
        / NULLIF(qs.execution_count, 0)
        AS decimal(18,2)
    ) AS avg_physical_reads,

    txt.text AS batch_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
ORDER BY
    qs.total_logical_reads DESC;

Usa esta consulta cuando la pregunta es:

¿Qué carga de trabajo en caché ha accedido a más páginas en total?


4. Encontrar consultas con el promedio de lecturas lógicas más alto

SELECT TOP (50)
    qs.execution_count,

    CAST(
        qs.total_logical_reads * 1.0
        / NULLIF(qs.execution_count, 0)
        AS decimal(18,2)
    ) AS avg_logical_reads,

    qs.total_logical_reads,
    qs.max_logical_reads,
    qs.min_logical_reads,
    qs.last_execution_time,
    txt.text AS batch_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
WHERE
    qs.execution_count > 0
ORDER BY
    avg_logical_reads DESC;

Usa esta consulta para encontrar ejecuciones individuales costosas.

Ten siempre a la vista execution_count.


5. Encontrar consumidores de lecturas de alta frecuencia

SELECT TOP (50)
    qs.execution_count,
    qs.total_logical_reads,

    CAST(
        qs.total_logical_reads * 1.0
        / NULLIF(qs.execution_count, 0)
        AS decimal(18,2)
    ) AS avg_logical_reads,

    qs.last_execution_time,
    txt.text AS batch_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
WHERE
    qs.execution_count >= 100
ORDER BY
    qs.total_logical_reads DESC;

El filtro 100 es solo un ejemplo.

Ajústalo a la carga de trabajo.

Una consulta con un volumen moderado de lecturas puede convertirse en un gran consumidor de recursos si se llama con frecuencia.


6. Agrupar consultas lógicamente equivalentes por query hash

Los literales de las consultas ad hoc pueden fragmentar la caché de planes.

SELECT TOP (50)
    qs.query_hash,
    SUM(qs.execution_count) AS executions,
    SUM(qs.total_logical_reads) AS total_logical_reads,

    CAST(
        SUM(qs.total_logical_reads) * 1.0
        / NULLIF(SUM(qs.execution_count), 0)
        AS decimal(18,2)
    ) AS avg_logical_reads,

    MAX(qs.last_execution_time) AS last_execution_time
FROM sys.dm_exec_query_stats AS qs
GROUP BY
    qs.query_hash
ORDER BY
    total_logical_reads DESC;

Usa esto cuando muchas instrucciones ad hoc similares hagan que la carga de trabajo parezca menor de lo que realmente es.


7. Mostrar el plan de ejecución de las consultas en caché con muchas lecturas

SELECT TOP (20)
    qs.execution_count,
    qs.total_logical_reads,
    CAST(
        qs.total_logical_reads * 1.0
        / NULLIF(qs.execution_count, 0)
        AS decimal(18,2)
    ) AS avg_logical_reads,
    txt.text AS batch_text,
    qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY
    qs.total_logical_reads DESC;

Busca:

  • scans grandes de tablas o índices
  • lookups repetidos en bucles anidados (nested loops)
  • joins demasiado amplios
  • predicados poco selectivos
  • problemas de estimación de filas
  • movimiento de filas anchas
  • índices faltantes o ineficaces

No decidas basándote en un solo operador.


8. Comparar las lecturas lógicas con las lecturas físicas

Las lecturas lógicas cuentan los accesos a páginas atendidos desde el buffer pool.

Las lecturas físicas cuentan las páginas que tuvieron que leerse desde el almacenamiento.

SELECT TOP (50)
    qs.execution_count,
    qs.total_logical_reads,
    qs.total_physical_reads,

    CAST(
        qs.total_logical_reads * 1.0
        / NULLIF(qs.execution_count, 0)
        AS decimal(18,2)
    ) AS avg_logical_reads,

    CAST(
        qs.total_physical_reads * 1.0
        / NULLIF(qs.execution_count, 0)
        AS decimal(18,2)
    ) AS avg_physical_reads,

    txt.text AS batch_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
ORDER BY
    qs.total_logical_reads DESC;

Interpreta con cuidado:

  • lecturas lógicas altas + lecturas físicas bajas → mucho trabajo con páginas en memoria
  • lecturas físicas altas → el almacenamiento tuvo que atender más lecturas
  • ambas altas → la consulta puede ser a la vez ineficiente e intensiva en almacenamiento

9. Usar Query Store para el historial de consultas con muchas lecturas

Si la carga de trabajo ya no está en la caché, Query Store es la mejor fuente histórica.

SELECT TOP (20)
    q.query_id,
    p.plan_id,
    qt.query_sql_text,
    SUM(rs.count_executions) AS executions,

    CAST(
        SUM(rs.avg_logical_io_reads * rs.count_executions)
        AS decimal(18,2)
    ) AS total_logical_reads,

    CAST(
        SUM(rs.avg_logical_io_reads * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0)
        AS decimal(18,2)
    ) AS weighted_avg_logical_reads
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
GROUP BY
    q.query_id,
    p.plan_id,
    qt.query_sql_text
ORDER BY
    total_logical_reads DESC;

Úsalo para:

  • análisis de regresiones
  • comparaciones antes/después
  • cambios de plan
  • consultas que ya no están en la caché de planes

10. Comprobar si las lecturas altas se concentran en una sola tabla

Una vez identificada la consulta, revisa primero el plan de ejecución.

Después, revisa el tamaño de las tablas e índices y la ruta de acceso a los objetos afectados.

Para comprobar rápidamente el tamaño de las tablas:

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    SUM(ps.row_count) AS row_count,
    CAST(SUM(ps.used_page_count) * 8.0 / 1024 AS decimal(18,2)) AS used_mb
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.tables AS t
    ON t.object_id = ps.object_id
INNER JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
WHERE
    t.is_ms_shipped = 0
GROUP BY
    s.name,
    t.name
ORDER BY
    used_mb DESC;

Los scans grandes sobre tablas grandes pueden ser completamente normales.

La decisión depende de la selectividad y de la intención de negocio.

Qué significa el resultado

Campo Por qué importa
logical_reads Número de páginas de 8 KB a las que una solicitud activa ha accedido desde el buffer pool.
reads Lecturas físicas realizadas por una solicitud activa.
total_logical_reads Lecturas lógicas acumuladas representadas por una fila de estadísticas de consulta en caché.
total_physical_reads Lecturas físicas acumuladas representadas por una fila de estadísticas de consulta en caché.
avg_logical_reads Promedio calculado de lecturas por ejecución. Útil junto con el número de ejecuciones.
max_logical_reads Número de lecturas lógicas más alto observado en una ejecución durante el ciclo de vida actual de la caché.
min_logical_reads Número de lecturas lógicas más bajo observado. Útil para detectar sesgos.
query_hash Ayuda a agrupar instrucciones lógicamente equivalentes.
execution_count Número de ejecuciones completadas representadas por la fila de estadísticas.

Qué revisar

Lecturas lógicas totales altas + número de ejecuciones alto

Esto suele indicar una ruta crítica que se ejecuta con frecuencia.

Posibles siguientes pasos:

  • reducir la frecuencia de llamadas
  • mejorar la ruta de acceso
  • almacenar en caché los resultados repetidos
  • agrupar las solicitudes en lotes
  • optimizar los joins repetidos

Lecturas lógicas promedio altas + número de ejecuciones bajo

Probablemente se trata de una consulta costosa por sí misma.

Inspecciona:

  • el tamaño de los scans
  • los predicados
  • el orden de los joins
  • las estimaciones de cardinalidad
  • la cobertura de los índices
  • el volumen de datos

Lecturas lógicas altas + CPU alto

Esto suele apuntar a un procesamiento costoso de filas y páginas.

Es posible que el plan esté leyendo y procesando muchos más datos de los necesarios.

Lecturas lógicas altas + CPU bajo

Posibles causas:

  • scans grandes pero eficientes
  • esperas que ocultan el CPU
  • datos que ya están en caché
  • un patrón de acceso sencillo pero amplio

No asumas que es inofensivo ni que es problemático sin el contexto de la carga de trabajo.

Lecturas físicas altas

Esto sugiere un uso más intensivo del almacenamiento.

Correlaciona con:

  • PAGEIOLATCH_*
  • la latencia a nivel de archivo
  • la presión de memoria
  • el comportamiento de los scans
  • los efectos de una caché en frío

Gran diferencia entre las lecturas lógicas mínimas y máximas

Esto puede indicar variabilidad en la ejecución.

Posibles causas:

  • sensibilidad a parámetros
  • una distribución de datos diferente
  • reutilización del plan con entradas desiguales
  • cardinalidad cambiante

Query Store es útil en este caso.

Notas de producción

Las lecturas lógicas no son lecturas de disco

Esta distinción es importante.

Una lectura lógica significa que SQL Server accedió a una página en el buffer pool.

Es posible que la página ya estuviera en memoria.

Las lecturas físicas significan que SQL Server tuvo que traer páginas desde el almacenamiento.

Muchas lecturas lógicas no son automáticamente algo malo

Una consulta de data warehouse que recorre una tabla de hechos grande puede leer legítimamente millones de páginas.

Las preguntas importantes son:

  • ¿Es un trabajo esperado?
  • ¿Es eficiente?
  • ¿Está dentro del SLA?
  • ¿Está desplazando a otras cargas de trabajo?

sys.dm_exec_query_stats se basa en la caché

Las filas desaparecen cuando los planes relacionados salen de la caché.

Usa Query Store cuando necesites evidencia histórica duradera.

Las estadísticas de consultas se actualizan cuando termina la ejecución

Usa sys.dm_exec_requests para las solicitudes activas.

Usa sys.dm_exec_query_stats para las ejecuciones completadas que están en caché.

Los efectos del buffer pool importan

Una consulta puede tener:

  • muchas lecturas lógicas
  • pocas lecturas físicas

porque los datos están en caché.

Aun así, puede consumir CPU y ancho de banda de memoria.

Los permisos varían según la versión

Para un acceso amplio a las DMV de rendimiento:

  • SQL Server 2019 y versiones anteriores suelen requerir VIEW SERVER STATE
  • SQL Server 2022 y versiones posteriores suelen requerir VIEW SERVER PERFORMANCE STATE

Azure SQL Database tiene reglas de ámbito y permisos diferentes.

Errores comunes

1. Tratar las lecturas lógicas como E/S de disco

No son lo mismo.

Las lecturas lógicas pueden atenderse por completo desde la memoria.

2. Asumir que muchas lecturas significan «índice faltante»

Las lecturas altas son un síntoma.

Es posible que el plan ya use la mejor ruta de acceso disponible para un conjunto de resultados legítimamente grande.

3. Mirar solo las lecturas totales

Una consulta de alta frecuencia puede dominar los totales y aun así ser barata por llamada.

Compara:

  • las lecturas totales
  • las lecturas promedio
  • el número de ejecuciones

4. Mirar solo las lecturas promedio

Un informe poco frecuente puede tener un promedio de lecturas enorme y aun así ser menos importante que una consulta moderada llamada miles de veces.

5. Ignorar la variabilidad de los parámetros

Una consulta puede leer 100 páginas con un valor de parámetro y 1.000.000 con otro.

Usa los valores mín./máx., Query Store y el análisis de planes.

6. Optimizar antes de comprobar los requisitos del conjunto de resultados

Si la petición de negocio necesita legítimamente la mayoría de las filas, un scan puede ser lo correcto.

No elimines con la optimización un trabajo que es necesario.

7. Ignorar la frecuencia de llamadas de la aplicación

A veces el SQL es aceptable, pero la aplicación lo llama con demasiada frecuencia.

El volumen de la carga de trabajo importa.

Siguiente paso

Usa el patrón de lecturas para elegir la siguiente investigación.

  • Muchas lecturas + CPU alto → inspecciona el plan de ejecución, los scans, los joins, los predicados y las estimaciones.
  • Lecturas físicas altas + PAGEIOLATCH_* → inspecciona la latencia del almacenamiento, la presión de memoria y las rutas de acceso.
  • Lecturas totales altas + número de ejecuciones alto → inspecciona la frecuencia de llamadas de la aplicación y el diseño de la ruta crítica.
  • Lecturas promedio altas + número de ejecuciones bajo → inspecciona esa consulta y su plan.
  • Gran diferencia entre mín./máx. → investiga la sensibilidad a parámetros y la estabilidad del plan.
  • Consultas ad hoc similares → agrupa por query_hash e inspecciona la parametrización.
  • El problema actual ya pasó → usa Query Store.
  • Las lecturas son altas pero esperadas → confirma que la carga de trabajo está dentro del SLA antes de optimizar.

← Todas las guías rápidas