How to View Historical Execution Plans for a Query in SQL Server

by

SQL Server has a feature called Query Store that maintains a history of all execution plans generated for each query over time. Unlike examining the current plan in cache with SHOWPLAN or execution plan tools, Query Store lets you see every plan the optimizer has chosen historically, allowing you to compare performance across plan changes and investigate regressions.

The following examples assume that Query Store is enabled on your database.

Finding the Query ID

Before you can view historical execution plans, you need to find the query_id for your query. If you know part of the SQL text, you can use a query like this to find it:

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;

Replace your_table_name with a table or keyword from your query. If it returns too many rows, you can adjust the filter in order to narrow the results down.

Once you identify the right query, note its query_id to use in the examples below.

Alternatively, if you know the stored procedure name:

SELECT 
    q.query_id,
    OBJECT_NAME(q.object_id) AS procedure_name,
    qt.query_sql_text
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE OBJECT_NAME(q.object_id) = 'YourProcedureName';

Viewing the Execution Plans

To view all execution plans for a specific query, check the sys.query_store_plan view:

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

SELECT 
    p.plan_id,
    p.query_plan_hash,
    p.query_plan,
    p.last_compile_start_time,
    p.last_execution_time,
    p.count_compiles,
    p.is_forced_plan
FROM sys.query_store_plan p
WHERE p.query_id = @query_id
ORDER BY p.last_execution_time DESC;

Replace 123 with the query_id you want to investigate. This returns all plans for that query along with compilation and execution timing information.

Viewing the Execution Plan Graphically

The query_plan column contains the complete execution plan in XML format. To view it graphically (rather than as raw XML), you’ll need to use tools that understand SQL Server’s execution plan format.

Try this:

DECLARE @query_id INT = 123;

SELECT 
    p.plan_id,
    CAST(p.query_plan AS XML) AS execution_plan
FROM sys.query_store_plan p
WHERE p.query_id = @query_id;

The CAST(p.query_plan AS XML) converts the stored text into an XML data type.

  • In SQL Server Management Studio (SSMS): Click on the XML value in the results to open it in the graphical execution plan viewer. This is the same interface you see when viewing live execution plans, complete with operators, costs, index usage, and warnings.
  • In other tools (VS Code, Azure Data Studio): You may need to save the plan as a .sqlplan file first. To do this, click on the XML to open it in a new tab, then save the file with a .sqlplan extension. The tool should automatically display the graphical representation.

The .sqlplan format is also useful for sharing execution plans with colleagues or including them in documentation, as anyone with SSMS or compatible tools can open and view them.

Viewing Plans with Execution Statistics

To see how each plan performed:

DECLARE @query_id INT = 123;

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,
    AVG(rs.avg_cpu_time) / 1000.0 AS avg_cpu_ms,
    AVG(rs.avg_logical_io_reads) AS avg_logical_reads,
    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 performance metrics for each plan, helping you identify which plans perform better or worse.

Identifying Forced Plans

To see if any plans are currently forced for a query:

DECLARE @query_id INT = 123;

SELECT 
    p.plan_id,
    p.is_forced_plan,
    p.force_failure_count,
    p.last_force_failure_reason_desc
FROM sys.query_store_plan p
WHERE p.query_id = @query_id
  AND p.is_forced_plan = 1;

When is_forced_plan is 1, someone has manually forced SQL Server to use that specific plan regardless of what the optimizer would normally choose. DBAs and developers can force plans as a quick fix when the optimizer starts choosing a poor execution plan. Perhaps this is due to parameter sniffing, outdated statistics, or other issues. By forcing a known-good plan, you can immediately restore performance while investigating the root cause.

Plans are forced using the sp_query_store_force_plan stored procedure. However, forced plans should be temporary solutions, as they prevent the optimizer from adapting to changing data patterns or taking advantage of improvements in newer SQL Server versions.

Finding All Queries and Their Plans

To see queries along with their plan counts:

SELECT 
    q.query_id,
    LEFT(qt.query_sql_text, 50) + '...' AS query_preview,
    COUNT(DISTINCT p.plan_id) AS plan_count
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
LEFT JOIN sys.query_store_plan p ON q.query_id = p.query_id
GROUP BY q.query_id, qt.query_sql_text
ORDER BY plan_count DESC;

This helps you identify which queries have accumulated the most execution plans, which may warrant investigation.

Understanding Plan Hash Values

The query_plan_hash provides a hash of the execution plan structure. Plans with the same hash are structurally identical even if they have different plan_ids. This can happen when plans are recompiled but the optimizer makes the same choices.

To group plans by their hash:

DECLARE @query_id INT = 123;

SELECT 
    p.query_plan_hash,
    COUNT(p.plan_id) AS instances,
    MIN(p.last_compile_start_time) AS first_compiled,
    MAX(p.last_execution_time) AS last_executed
FROM sys.query_store_plan p
WHERE p.query_id = @query_id
GROUP BY p.query_plan_hash
ORDER BY instances DESC;

This shows how many times each unique plan structure has been compiled for the query.