Many SQL folks believe that the execution plan they see in the plan cache is an Actual Execution Plan. Unfortunately, it is an Estimated Execution Plan. An Estimated Execution Plan does not have actual runtime statistics that may be helpful in query tuning. Actual Execution Plan has the runtime stats that you really need. Well, you can use the Query Store feature that can automatically persist the Actual Execution Plans. But remember, Query Store is a full-fledged feature that needs careful planning before you implement it. Are there other ways of getting Actual Execution Plans from the Plan Cache? Extended Events? Well, again, needs to be implemented, and capturing plans may turn out to be an expensive affair if you do not filter enough. What I mean is, try to capture only what you want out and cut down the noise. So what else? In SQL Server 2019, you can use the DMF sys.dm_exec_query_plan_stats to retrieve the equivalent of the last known actual execution plan for a previously cached query plan. Note that, this is an opt-in feature. If you want to enable this at the server level for all the databases, use trace flag 2451. To enable at the database level, turn ON the LAST_QUERY_PLAN_STATS database scoped config option. Sounds interesting? Watch the video below.

Subscribe to SQLMaestros YouTube channel. If you want more learning content in your inbox, subscribe to SQLMaestros Bulletin.

SQLMaestros YouTube | SQLMaestros Bulletin | SQLMaestros Twitter

Thanks for reading & watching. If you liked the content, do share it. I am on Twitter if you wish to connect.

You can also subscribe to my exclusive newsletter ConnectWithAB.