Explorar guías rápidas · SQL Server
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
- Problema
- Respuesta rápida
- Código / comandos
- 1. Encontrar las solicitudes activas que más lecturas lógicas realizan
- 2. Mostrar la instrucción que se está ejecutando dentro del lote
- 3. Encontrar consultas en caché por lecturas lógicas totales
- 4. Encontrar consultas con el promedio de lecturas lógicas más alto
- 5. Encontrar consumidores de lecturas de alta frecuencia
- 6. Agrupar consultas lógicamente equivalentes por query hash
- 7. Mostrar el plan de ejecución de las consultas en caché con muchas lecturas
- 8. Comparar las lecturas lógicas con las lecturas físicas
- 9. Usar Query Store para el historial de consultas con muchas lecturas
- 10. Comprobar si las lecturas altas se concentran en una sola tabla
- Qué significa el resultado
- Qué revisar
- Lecturas lógicas totales altas + número de ejecuciones alto
- Lecturas lógicas promedio altas + número de ejecuciones bajo
- Lecturas lógicas altas + CPU alto
- Lecturas lógicas altas + CPU bajo
- Lecturas físicas altas
- Gran diferencia entre las lecturas lógicas mínimas y máximas
- Notas de producción
- Las lecturas lógicas no son lecturas de disco
- Muchas lecturas lógicas no son automáticamente algo malo
- sys.dm_exec_query_stats se basa en la caché
- Las estadísticas de consultas se actualizan cuando termina la ejecución
- Los efectos del buffer pool importan
- Los permisos varían según la versión
- Errores comunes
- 1. Tratar las lecturas lógicas como E/S de disco
- 2. Asumir que muchas lecturas significan «índice faltante»
- 3. Mirar solo las lecturas totales
- 4. Mirar solo las lecturas promedio
- 5. Ignorar la variabilidad de los parámetros
- 6. Optimizar antes de comprobar los requisitos del conjunto de resultados
- 7. Ignorar la frecuencia de llamadas de la aplicación
- Siguiente paso
- 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.
¿Te resultó útil este contenido?
¿Qué podríamos mejorar?
Gracias — esto me ayuda a mejorar Javi Builds.
Mantente al día
Recibe solo las novedades de Javi Builds que realmente te interesan.
Revisa tu bandeja de entrada
Te he enviado un enlace de confirmación. Tu suscripción se activará cuando la confirmes.