Encontrar consultas con mayor lectura lógica

La carga de datos parece excesiva o determinadas operaciones presionan memoria y CPU. Queremos identificar sentencias terminadas que han solicitado más páginas al búfer durante la vida de sus planes en caché.

Qué necesitamos distinguir

Una página es la unidad de datos de 8 KB que utiliza SQL Server. El búfer es la memoria donde conserva páginas; una lectura física debe traerla desde almacenamiento y una lógica solicita una página al motor aunque ya esté en memoria.

Una lectura lógica es un acceso a una página ya solicitada al motor; no equivale a una lectura física del disco. El total muestra impacto acumulado y el promedio aproxima el trabajo por ejecución, pero ambos dependen de los planes que aún permanecen en caché.

Consulta

/*
    Antes de ejecutar:
        Revisa los permisos y los límites antes de ejecutar la consulta.
        Confirma el alcance, la base activa y el costo posible de la consulta.
        Captura evidencia en varios momentos antes de atribuir una causa.

    Este script es de observación. No aplica correcciones ni modifica datos.
*/

SET NOCOUNT ON;
GO
-- Consulta
DECLARE @Cantidad INT = 25;

SELECT TOP (@Cantidad)
    DB_NAME(st.dbid) AS BaseDatos,
    OBJECT_SCHEMA_NAME(st.objectid, st.dbid) AS Esquema,
    OBJECT_NAME(st.objectid, st.dbid) AS Objeto,
    qs.execution_count AS Ejecuciones,
    qs.total_logical_reads AS LecturasLogicasTotales,
    CONVERT
    (
        DECIMAL(18, 2),
        1.0 * qs.total_logical_reads / NULLIF(qs.execution_count, 0)
    ) AS LecturasLogicasPromedio,
    qs.last_logical_reads AS LecturasUltimaEjecucion,
    qs.max_logical_reads AS LecturasMaximas,
    qs.creation_time AS PlanCreado,
    qs.last_execution_time AS UltimaEjecucion,
    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 Sentencia
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE qs.execution_count > 0
ORDER BY
    qs.total_logical_reads DESC,
    qs.execution_count DESC;
GO

Parámetros o permisos

@Cantidad controla cuántas filas se muestran. Se necesita VIEW SERVER STATE, o VIEW SERVER PERFORMANCE STATE desde SQL Server 2022. SQL Server contabiliza accesos a páginas, incluso cuando una página ya está en memoria. La misma página puede participar varias veces en el trabajo de una consulta; por eso no conviene convertir el contador directamente a espacio leído del almacenamiento.

Cómo leer el resultado

Compara LecturasLogicasTotales con Ejecuciones. Un total alto con promedio moderado suele indicar frecuencia; un promedio y un máximo altos justifican revisar acceso a tablas, cardinalidades e índices. La diferencia entre LecturasUltimaEjecucion y LecturasMaximas también puede advertir variación por parámetros o por cantidad de datos, aunque no explica todavía su origen.

Qué conclusión sí permite obtener

Permite priorizar sentencias terminadas que más páginas han consultado entre los planes conservados en caché y distinguir acumulación por frecuencia de costo por ejecución.

Qué conclusión todavía no permite obtener

No mide directamente bytes leídos del disco, no conserva entradas expulsadas y no demuestra que muchas lecturas sean evitables. Una consulta analítica puede necesitar recorrer un conjunto grande de datos.

Siguiente comprobación razonable

Recupera el plan de ejecución y revisa estimaciones, operadores y predicados. Contrasta los índices existentes antes de interpretar una sugerencia de índice faltante.

Buscar en SQL Server