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