How to Find Queries with Multiple Execution Plans in SQL Server

by

When enabled on a database, SQL Server’s Query Store tracks all execution plans that the optimizer generates for each query. When a query has multiple plans, it often indicates parameter sniffing issues, statistics changes, or index modifications that caused the optimizer to choose different execution strategies over time.

You can query the Query Store system views to find queries that have multiple execution plans:

SELECT 
    q.query_id,
    qt.query_sql_text,
    COUNT(DISTINCT p.plan_id) AS plan_count,
    MAX(rs.last_execution_time) AS last_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
GROUP BY q.query_id, qt.query_sql_text
HAVING COUNT(DISTINCT p.plan_id) > 1
ORDER BY plan_count DESC;

This query uses the HAVING clause with the COUNT() function to narrow the results to just those queries whose plan count is greater than 1. It also uses the ORDER BY clause to sort them by plan_count in descending order.

So in other words, the query returns queries that have more than one execution plan, sorted by the number of plans each query has accumulated.

Filtering by Recent Activity

To focus on queries with multiple plans that have executed recently:

SELECT 
    q.query_id,
    qt.query_sql_text,
    COUNT(DISTINCT p.plan_id) AS plan_count,
    MAX(rs.last_execution_time) AS last_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 rs.last_execution_time >= DATEADD(day, -7, GETUTCDATE())
GROUP BY q.query_id, qt.query_sql_text
HAVING COUNT(DISTINCT p.plan_id) > 1
ORDER BY plan_count DESC;

This shows only queries with multiple plans that executed in the last 7 days.

Finding Out When the Plan Changed

To identify when plans changed for a specific query:

DECLARE @query_id INT = 123;  -- Replace with your query_id

SELECT 
    p.plan_id,
    p.query_plan_hash,
    MIN(rs.first_execution_time) AS first_used,
    MAX(rs.last_execution_time) AS last_used,
    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.query_id = @query_id
GROUP BY p.plan_id, p.query_plan_hash
ORDER BY first_used;

This shows when each plan was first and last used, helping you understand the timeline of plan changes.

Why a Query Might Have Multiple Plans

Multiple plans for the same query can indicate several issues:

  • Parameter sniffing – The optimizer creates different plans based on the first parameter values it sees, which may not be optimal for all subsequent executions.
  • Statistics updates – When statistics are updated, the optimizer may choose a different plan based on the new data distribution information.
  • Index changes – Adding or dropping indexes causes the optimizer to re-evaluate available execution strategies.
  • Plan cache eviction – When plans are removed from cache due to memory pressure, the optimizer regenerates them and may choose differently.

Having multiple plans isn’t always a problem, but queries with many different plans (5+) or with significantly different performance characteristics may warrant investigation.

Excluding Query Store’s Own Queries

If you’re running Query Store diagnostic queries like this frequently, they may appear in the results. To exclude them:

SELECT 
    q.query_id,
    qt.query_sql_text,
    COUNT(DISTINCT p.plan_id) AS plan_count,
    MAX(rs.last_execution_time) AS last_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 qt.query_sql_text NOT LIKE '%sys.query_store%'
GROUP BY q.query_id, qt.query_sql_text
HAVING COUNT(DISTINCT p.plan_id) > 1
ORDER BY plan_count DESC;

This filters out any queries that reference Query Store system views.