Find degraded queries using Query Store

An operation that previously responded normally now takes longer, but the previous plan is no longer cached. We need to compare a recent window with a base period maintained by Query Store.

Query Store is the internal repository that maintains texts, plans and interval execution statistics within a database. Unlike the plan cache, it is designed to maintain history based on the configured retention.

What we need to distinguish

The recent window and baseline period are comparable intervals. A regression is a measured worsening between them, not simply one isolated slow execution.

A downgrade is a change measured relative to a comparable reference. The recent average may increase due to a different plan, parameters, volume, crashes or concurrent load; The comparison locates candidates, but does not decide the cause.

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 @Ahora DATETIMEOFFSET = SYSDATETIMEOFFSET();
DECLARE @InicioReciente DATETIMEOFFSET = DATEADD(DAY, -1, @Ahora);
DECLARE @InicioBase DATETIMEOFFSET = DATEADD(DAY, -8, @Ahora);
DECLARE @MinimoEjecuciones BIGINT = 5;

WITH Acumulado AS
(
    SELECT
        p.query_id,
        SUM(CASE WHEN rsi.end_time > @InicioReciente
            THEN rs.count_executions ELSE 0 END) AS EjecucionesRecientes,
        SUM(CASE WHEN rsi.end_time <= @InicioReciente
            THEN rs.count_executions ELSE 0 END) AS EjecucionesBase,
        SUM(CASE WHEN rsi.end_time > @InicioReciente
            THEN rs.avg_duration * rs.count_executions ELSE 0 END)
            AS DuracionRecientePonderada,
        SUM(CASE WHEN rsi.end_time <= @InicioReciente
            THEN rs.avg_duration * rs.count_executions ELSE 0 END)
            AS DuracionBasePonderada,
        MAX(rs.last_execution_time) AS UltimaEjecucion
    FROM sys.query_store_runtime_stats AS rs
    INNER JOIN sys.query_store_runtime_stats_interval AS rsi
        ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
    INNER JOIN sys.query_store_plan AS p
        ON p.plan_id = rs.plan_id
    WHERE rs.execution_type = 0
      AND rsi.end_time > @InicioBase
      AND rsi.start_time < @Ahora
    GROUP BY p.query_id
),
Comparacion AS
(
    SELECT
        a.query_id,
        a.EjecucionesRecientes,
        a.EjecucionesBase,
        a.DuracionRecientePonderada
            / NULLIF(a.EjecucionesRecientes, 0) / 1000.0 AS RecienteMs,
        a.DuracionBasePonderada
            / NULLIF(a.EjecucionesBase, 0) / 1000.0 AS BaseMs,
        a.UltimaEjecucion
    FROM Acumulado AS a
    WHERE a.EjecucionesRecientes >= @MinimoEjecuciones
      AND a.EjecucionesBase >= @MinimoEjecuciones
)
SELECT TOP (25)
    c.query_id AS IdConsulta,
    c.EjecucionesRecientes,
    c.EjecucionesBase,
    CONVERT(DECIMAL(18, 2), c.RecienteMs) AS DuracionRecienteMs,
    CONVERT(DECIMAL(18, 2), c.BaseMs) AS DuracionBaseMs,
    CONVERT(DECIMAL(10, 2), c.RecienteMs / NULLIF(c.BaseMs, 0))
        AS FactorDegradacion,
    c.UltimaEjecucion,
    LEFT(qt.query_sql_text, 2000) AS Consulta
FROM Comparacion AS c
INNER JOIN sys.query_store_query AS q
    ON q.query_id = c.query_id
INNER JOIN sys.query_store_query_text AS qt
    ON qt.query_text_id = q.query_text_id
WHERE c.RecienteMs > c.BaseMs
ORDER BY
    FactorDegradacion DESC,
    c.EjecucionesRecientes DESC;
GO

Parameters or permissions

Execute the query in a database with Query Store enabled and with sufficient history. Adjust the windows and minimum runs to the actual frequency. VIEW DATABASE STATE, or VIEW DATABASE PERFORMANCE STATE, is required since SQL Server 2022. The split uses the end of each Query Store range. An interval that crosses the boundary is assigned to a single window; For very precise analyzes it is advisable to align the dates with the configured duration of the intervals.

How to read the result

FactorDegradacion compares weighted averages by number of executions. A value of 2.00 means that the recent average duration was about twice the base, not that all runs were doubled. Prioritize queries with sufficient executions and recognizable impact. Also check absolute duration: multiplying a millisecond operation by ten may be less important than doubling a multi-second operation.

What this does establish

It allows you to find queries whose recent average worsened compared to your own preserved history and sort them for further investigation.

What this still does not establish

It does not automatically identify the responsible plan or resource, it does not preserve periods outside of the Query Store retention, and it does not demonstrate that the change is permanent.

Next reasonable check

Compare the plans maintained by Query Store and contrast CPU, readings, parameters and waits between both windows. Confirm the pattern before forcing a plan or modifying code.

Search SQL Server