Find queries with several different plans and performances

The same query alternates between fast and slow responses, or Query Store window comparison showed degradation. We want to compare the plans that Query Store kept for that query within a common window.

Query Store preserves texts, plans, and interval execution statistics within the database. A plan is the compiled strategy that SQL Server chose to execute a query; Comparing multiple plans allows you to relate that choice to the observed metrics, but does not automatically attribute the cause of the change.

What we need to distinguish

Recompilation creates the plan again. A schema change modifies object structure, and an estimate is the number of rows SQL Server expects to process; any of these can influence the selected strategy.

Having multiple plans is not necessarily a mistake. Recompilations, schema changes, different estimates or engine characteristics can produce valid alternatives. The comparison should consider sufficient executions and not just the best case of each plan.

Query

/*
    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

Parameters or permissions

Runs the query against a database with Query Store enabled. Adjusts @From and the minimum number of executions to the retention and frequency of the load. VIEW DATABASE STATE, or VIEW DATABASE PERFORMANCE STATE, is required from SQL Server 2022. Metrics are weighted by runs and aggregate rows from the same plan over multiple intervals. The result only includes plans executed during the chosen window.

How to read the result

Compare duration, CPU, readings and number of executions within the same QueryId. A much slower plan with few executions may reflect specific parameters or conditions; a frequent and consistently worse one deserves priority. EsPlanForzado describes the recorded state, but does not demonstrate that the forced has resolved the cause. Retrieves the plan XML from the Query Store or uses its identifier to compare operators and estimates.

What this does establish

It allows you to recognize queries that used more than one plan and compare the aggregate performance of each alternative during a common window.

What this still does not establish

It does not determine which plan should be retained, it does not explain why each was chosen, and it does not guarantee that the historically fastest plan is correct for all parameters.

Next reasonable check

Compare the XML, parameters, execution context and data distribution. Test any fixes in a controlled environment before considering plan forcing, rebuilding, or index changes.

Search SQL Server