How to Identify Regressed Queries in SQL Server (T-SQL)

by

Query regression occurs when a query that previously performed well suddenly becomes slower, often due to a plan change. SQL Server’s Query Store makes it easy to identify these regressions by comparing recent performance against historical baselines.

To find queries that have regressed, you can compare recent performance metrics against earlier time periods:

WITH RecentStats AS (
    SELECT 
        q.query_id,
        p.plan_id,
        AVG(rs.avg_duration) AS recent_avg_duration,
        SUM(rs.count_executions) AS recent_executions
    FROM sys.query_store_query q
    JOIN sys.query_store_plan p ON q.query_id = p.query_id
    JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
    JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
    WHERE rsi.start_time >= DATEADD(day, -1, GETUTCDATE())
    GROUP BY q.query_id, p.plan_id
),
HistoricalStats AS (
    SELECT 
        q.query_id,
        AVG(rs.avg_duration) AS historical_avg_duration
    FROM sys.query_store_query q
    JOIN sys.query_store_plan p ON q.query_id = p.query_id
    JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
    JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
    WHERE rsi.start_time >= DATEADD(day, -7, GETUTCDATE())
      AND rsi.start_time < DATEADD(day, -1, GETUTCDATE())
    GROUP BY q.query_id
)
SELECT 
    r.query_id,
    qt.query_sql_text,
    h.historical_avg_duration / 1000.0 AS historical_avg_ms,
    r.recent_avg_duration / 1000.0 AS recent_avg_ms,
    ((r.recent_avg_duration - h.historical_avg_duration) / h.historical_avg_duration * 100) AS percent_regression,
    r.recent_executions
FROM RecentStats r
JOIN HistoricalStats h ON r.query_id = h.query_id
JOIN sys.query_store_query q ON r.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE r.recent_avg_duration > h.historical_avg_duration * 1.5  -- 50% slower
  AND r.recent_executions >= 10  -- Minimum executions for statistical relevance
ORDER BY percent_regression DESC;

This query compares the last day’s performance against the previous week, flagging queries that are at least 50% slower. Feel free to tweak the filter as required.

Adjusting the Regression Threshold

As alluded to, you’ll probably want to tweak the filter. For example, you can adjust the sensitivity by changing the regression threshold. The query above uses 1.5 (50% slower), but you might want different thresholds.

For example, to flag 25% regressions:

WHERE r.recent_avg_duration > h.historical_avg_duration * 1.25

Or you could flag only severe 100% regressions:

WHERE r.recent_avg_duration > h.historical_avg_duration * 2.0

Finding Queries That Changed Plans

Regressions are often caused by plan changes. To see which regressed queries have new plans:

WITH RecentStats AS (
    SELECT 
        q.query_id,
        p.plan_id,
        AVG(rs.avg_duration) AS recent_avg_duration,
        SUM(rs.count_executions) AS recent_executions
    FROM sys.query_store_query q
    JOIN sys.query_store_plan p ON q.query_id = p.query_id
    JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
    JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
    WHERE rsi.start_time >= DATEADD(day, -1, GETUTCDATE())
    GROUP BY q.query_id, p.plan_id
),
HistoricalStats AS (
    SELECT 
        q.query_id,
        AVG(rs.avg_duration) AS historical_avg_duration
    FROM sys.query_store_query q
    JOIN sys.query_store_plan p ON q.query_id = p.query_id
    JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
    JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
    WHERE rsi.start_time >= DATEADD(day, -7, GETUTCDATE())
      AND rsi.start_time < DATEADD(day, -1, GETUTCDATE())
    GROUP BY q.query_id
),
RegressedQueries AS (
    SELECT 
        r.query_id,
        r.recent_avg_duration,
        h.historical_avg_duration
    FROM RecentStats r
    JOIN HistoricalStats h ON r.query_id = h.query_id
    WHERE r.recent_avg_duration > h.historical_avg_duration * 1.5
      AND r.recent_executions >= 10
)
SELECT 
    q.query_id,
    qt.query_sql_text,
    COUNT(DISTINCT p.plan_id) AS total_plans,
    MAX(p.last_compile_start_time) AS most_recent_compile,
    MAX(rs.last_execution_time) AS most_recent_execution
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE q.query_id IN (SELECT query_id FROM RegressedQueries)
GROUP BY q.query_id, qt.query_sql_text
HAVING COUNT(DISTINCT p.plan_id) > 1
  AND MAX(p.last_compile_start_time) >= DATEADD(day, -2, GETUTCDATE())
ORDER BY most_recent_compile DESC;

This identifies regressed queries that recently compiled a new plan, which is often the root cause of the regression.

Investigating Regression Causes

Once you’ve identified regressed queries, investigate why:

  • Compare execution plans between the old and new plans to understand what changed (different join orders, index usage, etc.).
  • Check statistics to see if they’re outdated or missing for key columns.
  • Review recent schema changes like dropped indexes, altered tables, or new constraints that might affect plan choices.
  • Look for parameter sniffing by examining whether different parameter values produce dramatically different performance.

After identifying the cause, you can either fix the root issue or temporarily force a better plan while you investigate further.