When a query has multiple execution plans in Query Store, comparing their performance can help you identify which plan performs best and understand why performance may have regressed. This comparison can be very useful for troubleshooting queries that suddenly became slower after a plan change.
Finding the Query ID
Before comparing plans, you need to find the query_id for your query. If you know part of the SQL text, you can do something like this:
SELECT
q.query_id,
qt.query_sql_text,
q.last_execution_time
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE qt.query_sql_text LIKE '%your_table_name%'
ORDER BY q.last_execution_time DESC;You might need to adjust the filter if it returns too many results. Once you identify the right query, you can use its query_id in the comparison queries.
Comparing the Plans
To compare performance metrics across all plans for a specific query:
DECLARE @query_id INT = 123; -- Replace with your query_id
SELECT
p.plan_id,
p.query_plan_hash,
SUM(rs.count_executions) AS total_executions,
AVG(rs.avg_duration) / 1000.0 AS avg_duration_ms,
MIN(rs.min_duration) / 1000.0 AS min_duration_ms,
MAX(rs.max_duration) / 1000.0 AS max_duration_ms,
AVG(rs.avg_cpu_time) / 1000.0 AS avg_cpu_ms,
AVG(rs.avg_logical_io_reads) AS avg_logical_reads,
AVG(rs.avg_physical_io_reads) AS avg_physical_reads,
MIN(rs.first_execution_time) AS first_used,
MAX(rs.last_execution_time) AS last_used
FROM sys.query_store_plan p
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE p.query_id = @query_id
GROUP BY p.plan_id, p.query_plan_hash
ORDER BY avg_duration_ms DESC;This shows key performance metrics for each plan, sorted by average duration to quickly identify the slowest plans.
Understanding the Performance Metrics
The comparison query returns the following metrics:
total_executions– How many times each plan was usedavg_duration_ms– Average total execution time in millisecondsmin_duration_ms/max_duration_ms– Fastest and slowest executionsavg_cpu_ms– Average CPU time consumedavg_logical_reads– Average number of pages read from buffer cacheavg_physical_reads– Average number of pages read from diskfirst_used/last_used– When the plan was first and last executed
Plans with high average duration, high logical reads, or significant physical reads are typically the problem plans you want to investigate.
Comparing Plans Over Time
To see how plan performance changed over specific time periods:
DECLARE @query_id INT = 123;
SELECT
p.plan_id,
rs.runtime_stats_interval_id,
rsi.start_time,
rsi.end_time,
rs.count_executions,
rs.avg_duration / 1000.0 AS avg_duration_ms,
rs.avg_cpu_time / 1000.0 AS avg_cpu_ms,
rs.avg_logical_io_reads AS avg_logical_reads
FROM sys.query_store_plan p
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 p.query_id = @query_id
ORDER BY rsi.start_time, p.plan_id;This breaks down performance by time interval, showing you exactly when plan performance changed and which plan was in use during each period.
Identifying Performance Regressions
To quickly spot when performance got worse:
DECLARE @query_id INT = 123;
WITH PlanStats AS (
SELECT
p.plan_id,
AVG(rs.avg_duration) / 1000.0 AS avg_duration_ms,
MIN(rs.first_execution_time) AS first_used
FROM sys.query_store_plan p
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE p.query_id = @query_id
GROUP BY p.plan_id
)
SELECT
plan_id,
avg_duration_ms,
first_used,
CASE
WHEN avg_duration_ms > LAG(avg_duration_ms) OVER (ORDER BY first_used) * 1.5
THEN 'REGRESSION'
WHEN avg_duration_ms < LAG(avg_duration_ms) OVER (ORDER BY first_used) * 0.75
THEN 'IMPROVEMENT'
ELSE 'STABLE'
END AS performance_change
FROM PlanStats
ORDER BY first_used;This flags plans that show significant performance changes (50% slower or 25% faster) compared to the previous plan, helping you quickly identify regressions.
Here’s an example of what the output might look like:
plan_id avg_duration_ms first_used performance_change
------- --------------- ------------------------ ------------------
29 28.039 2025-12-08T00:43:15.783Z STABLE
30 41.56 2025-12-08T00:43:15.830Z STABLE
31 111.796 2025-12-08T00:43:15.947Z REGRESSION
32 45.708 2025-12-08T00:43:15.997Z IMPROVEMENT
33 51.616 2025-12-08T00:43:16.053Z STABLE
34 55.594 2025-12-08T00:43:16.117Z STABLE
35 65.383 2025-12-08T00:43:16.187Z STABLE
36 38.947 2025-12-08T00:43:16.230Z IMPROVEMENT
Comparing Specific Plans Side-by-Side
If you’ve identified two specific plans to compare:
DECLARE @good_plan_id INT = 456;
DECLARE @bad_plan_id INT = 789;
SELECT
'Good Plan' AS plan_type,
p.plan_id,
AVG(rs.avg_duration) / 1000.0 AS avg_duration_ms,
AVG(rs.avg_cpu_time) / 1000.0 AS avg_cpu_ms,
AVG(rs.avg_logical_io_reads) AS avg_logical_reads,
SUM(rs.count_executions) AS total_executions
FROM sys.query_store_plan p
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE p.plan_id = @good_plan_id
GROUP BY p.plan_id
UNION ALL
SELECT
'Bad Plan' AS plan_type,
p.plan_id,
AVG(rs.avg_duration) / 1000.0 AS avg_duration_ms,
AVG(rs.avg_cpu_time) / 1000.0 AS avg_cpu_ms,
AVG(rs.avg_logical_io_reads) AS avg_logical_reads,
SUM(rs.count_executions) AS total_executions
FROM sys.query_store_plan p
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE p.plan_id = @bad_plan_id
GROUP BY p.plan_id;This provides a direct side-by-side comparison of the two plans’ performance characteristics.
Example output from this query:
plan_type plan_id avg_duration_ms avg_cpu_ms avg_logical_reads total_executions
--------- ------- --------------- ---------- ----------------- ----------------
Good Plan 29 7.177 6.933 30 1
Bad Plan 31 6.17 5.864 10 1
Next Steps After Comparison
Once you’ve identified a poorly performing plan, you can:
- Force a better-performing plan using
sp_query_store_force_plan - Examine the actual execution plans to understand structural differences
- Investigate why the optimizer chose the poor plan (outdated statistics, parameter sniffing, missing indexes)
- Address the root cause so you can eventually unforce the plan
Plan comparison is typically the starting point for deeper performance troubleshooting. It can help you quickly determine whether a plan change is responsible for a performance issue.