SQL SERVER – Get Last Known Actual Execution Plan for a Previously Cached Query Plan

This feature works with SQL Server 2019 and onwards. If you are using an earlier version of SQL Server this feature will not work and will give you an error. One of the most common questions I receive during Comprehensive Database Performance Health Check is how to get the last known actual execution plan for a previously cached query plan.

Here is the script which works in SQL Server 2019 and the later version where it not only gives us execution plan but it also provides the statistics for the last known actual execution plan.

Now there are two steps to get the last known actual execution plan. First, we have to enable the trace flag 2451 and right after that, we have to run the following query.

Step 1: Enable Trace Flag 2451

Here is the script which will enable trace flag 2451 for the entire instance.

DBCC TRACEON(2451, -1);
GO

Step 2: dm_exec_query_plan_stats for Cached Query Plan

Now run the following query which is based on the DMV dm_exec_query_plan_stats.

SELECT 
qps.query_plan, st.text,
DB_NAME(st.dbid) DBName, 
OBJECT_NAME(st.objectid) ObjectName,
cp.usecounts, cp.objtype
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps
WHERE st.encrypted = 0

The very first column of the resultset is what displays the execution plan. You can click on the execution column to see the details of the execution plan. Additionally, in the query, you can see how many time any particular query was executed. If there is a stored procedure executed, you can also see the name of the database along with the name of the stored procedure as well.

If any query does not have plan or plan is too big to fit in the cache, it will display the value NULL.

I hope you find this query helpful. The actual execution plan retrieved from this query is not complete but it still displays quite a lots of interesting details. Let me know your thoughts about the query.

Reference: Pinal Dave (https://blog.sqlauthority.com)