Encontrar consultas con varios planes y rendimientos diferentes

Una misma consulta alterna entre respuestas rápidas y lentas, o la comparación de ventanas en Query Store mostró una degradación. Queremos comparar los planes que Query Store conservó para esa consulta dentro de una ventana común.

Query Store conserva textos, planes y estadísticas de ejecución por intervalos dentro de la base. Un plan es la estrategia compilada que SQL Server eligió para ejecutar una consulta; comparar varios planes permite relacionar esa elección con las métricas observadas, pero no atribuye automáticamente la causa del cambio.

Qué necesitamos distinguir

Una recompilación vuelve a crear el plan. Un cambio de esquema modifica la estructura de los objetos y una estimación es la cantidad de filas que SQL Server espera procesar; cualquiera puede influir en la estrategia elegida.

Tener varios planes no es necesariamente un error. Recompilaciones, cambios de esquema, distintas estimaciones o características del motor pueden producir alternativas válidas. La comparación debe considerar ejecuciones suficientes y no solo el mejor caso de cada plan.

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 @Desde DATETIMEOFFSET = DATEADD(DAY, -7, SYSDATETIMEOFFSET());
DECLARE @MinimoEjecuciones BIGINT = 5;

WITH RendimientoPlan AS
(
    SELECT
        p.query_id,
        p.plan_id,
        p.is_forced_plan,
        p.is_parallel_plan,
        SUM(rs.count_executions) AS Ejecuciones,
        SUM(rs.avg_duration * rs.count_executions)
            / NULLIF(SUM(rs.count_executions), 0) / 1000.0
            AS DuracionPromedioMs,
        SUM(rs.avg_cpu_time * rs.count_executions)
            / NULLIF(SUM(rs.count_executions), 0) / 1000.0
            AS CpuPromedioMs,
        SUM(rs.avg_logical_io_reads * rs.count_executions)
            / NULLIF(SUM(rs.count_executions), 0)
            AS LecturasLogicasPromedio,
        MAX(rs.max_duration) / 1000.0 AS DuracionMaximaMs,
        MAX(rs.last_execution_time) AS UltimaEjecucion
    FROM sys.query_store_plan AS p
    INNER JOIN sys.query_store_runtime_stats AS rs
        ON rs.plan_id = p.plan_id
    INNER JOIN sys.query_store_runtime_stats_interval AS rsi
        ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
    WHERE rs.execution_type = 0
      AND rsi.end_time > @Desde
    GROUP BY
        p.query_id,
        p.plan_id,
        p.is_forced_plan,
        p.is_parallel_plan
),
ConsultasConVarios AS
(
    SELECT rp.query_id
    FROM RendimientoPlan AS rp
    GROUP BY rp.query_id
    HAVING COUNT(*) > 1
       AND SUM(rp.Ejecuciones) >= @MinimoEjecuciones
)
SELECT
    rp.query_id AS IdConsulta,
    rp.plan_id AS IdPlan,
    rp.is_forced_plan AS EsPlanForzado,
    rp.is_parallel_plan AS EsPlanParalelo,
    rp.Ejecuciones,
    CONVERT(DECIMAL(18, 2), rp.DuracionPromedioMs)
        AS DuracionPromedioMs,
    CONVERT(DECIMAL(18, 2), rp.CpuPromedioMs) AS CpuPromedioMs,
    CONVERT(DECIMAL(18, 2), rp.LecturasLogicasPromedio)
        AS LecturasLogicasPromedio,
    CONVERT(DECIMAL(18, 2), rp.DuracionMaximaMs) AS DuracionMaximaMs,
    rp.UltimaEjecucion,
    LEFT(qt.query_sql_text, 2000) AS Consulta
FROM RendimientoPlan AS rp
INNER JOIN ConsultasConVarios AS cv
    ON cv.query_id = rp.query_id
INNER JOIN sys.query_store_query AS q
    ON q.query_id = rp.query_id
INNER JOIN sys.query_store_query_text AS qt
    ON qt.query_text_id = q.query_text_id
ORDER BY
    rp.query_id,
    rp.DuracionPromedioMs DESC,
    rp.Ejecuciones DESC;
GO

Parámetros o permisos

Ejecuta la consulta en una base con Query Store habilitado. Ajusta @Desde y el mínimo de ejecuciones a la retención y frecuencia de la carga. Se requiere VIEW DATABASE STATE, o VIEW DATABASE PERFORMANCE STATE desde SQL Server 2022. Las métricas se ponderan por ejecuciones y reúnen las filas del mismo plan en varios intervalos. El resultado solo incluye planes ejecutados durante la ventana elegida.

Cómo leer el resultado

Compara duración, CPU, lecturas y cantidad de ejecuciones dentro del mismo IdConsulta. Un plan mucho más lento con pocas ejecuciones puede reflejar parámetros o condiciones específicas; uno frecuente y consistentemente peor merece prioridad. EsPlanForzado describe el estado registrado, pero no demuestra que el forzado haya resuelto la causa. Recupera el XML del plan desde Query Store o usa su identificador para comparar operadores y estimaciones.

Qué conclusión sí permite obtener

Permite reconocer consultas que utilizaron más de un plan y comparar el rendimiento agregado de cada alternativa durante una ventana común.

Qué conclusión todavía no permite obtener

No determina qué plan debe conservarse, no explica por qué se eligió cada uno y no garantiza que el plan históricamente más rápido sea correcto para todos los parámetros.

Siguiente comprobación razonable

Compara los XML, parámetros, contexto de ejecución y distribución de datos. Prueba cualquier corrección en un entorno controlado antes de considerar forzado de plan, recompilación o cambios de índices.

Buscar en SQL Server