2024-09-25

Get the Last Known Actual Execution Plan

SQL Server 2019 introduces sys.dm_exec_query_plan_stats new DMV which returns the equivalent of the last known actual execution plan for a previously cached query plan (All plans are estimated plans. "Actual" plan is essentially = Estimated Plan + Runtime Metrics). You can turn this new feature on at the server level using trace flag 2451 or at database level with LAST_QUERY_PLAN_STATS a new database scoped configuration. For example:

USE [<DB_Name>];

GO

ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;

GO

Then you can query the last known actual execution plan likes below:

SELECT cp.objtype, DB_NAME(st.dbid) AS DB, OBJECT_NAME(st.objectid, st.dbid) AS ObjectName, st.[text], qps.query_plan

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 OBJECT_NAME(st.objectid, st.dbid) = '<stored proc/function name>'

;

In the result, you can click on the query_plan XML to show the graphical plan.

As you see, the plan includes actual runtime metrics.



No comments:

Post a Comment