When you think about execution plans in MS SQL Server you could think that the Estimated query plan are useless. So, here I must disappoint you. All execution plans are estimated. The only difference between the “Actual” and the estimated execution plan is that the so called “Actual” have some added runtime metrics.

Let’s see in the following images examples of estimated and actual execution plans.

Statistics for the estimated plan:

Statistics for the “actual” plan:

Estimated execution plan:

“Actual" execution plan:

Prior to SQL 2019 to be able to get those metrics it is required that you execute the query.

This is no longer needed in SQL Server 2019 thanks to the newly introduced function “sys.dm_exec_query_plan_stats”. Using this function, you can get the actual plan, the last one run on the system.

To be able to get this plan you must first turn on this feature on the database level (or by using the trace flag 2451) by running this query (example how to turn it on or off):

ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;

ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = OFF;

Once you enabled the feature, you'll begin to see the “actual” execution plan data collected in the Dynamic management view (DMV).

Let’s create a simple test stored procedure and execute it on the system.

CREATE OR ALTER PROCEDURE dbo.LightweightStats
AS
BEGIN
	SELECT 
		COUNT(*) AS Count
	FROM 
		(
			SELECT DISTINCT 
				UnitCost
				, UnitsIn
				, MovementDate 
			FROM 
				dbo.FactProductInventory
		) x;
END
GO

Currently in SQL Server versions prior to 2019 you can get the cached execution plans. Use this query and see what queries are cached:

SELECT 
	ep.usecounts
	, cacheobjtype
	, ep.objtype
	, q.text AS query_text
	, pl.query_plan
	, ep.size_in_bytes
FROM 
	sys.dm_exec_cached_plans ep
	CROSS APPLY sys.dm_exec_sql_text(ep.plan_handle) q
	CROSS APPLY sys.dm_exec_query_plan(ep.plan_handle) pl

If you click on the XML value in the column “query_plan” you will get the estimated execution plan (see earlier image).

So, as I told you, you can get the actual plan (the last run on the system) by using the following query:

SELECT 
	deqps.query_plan
FROM 
	sys.dm_exec_procedure_stats AS deps
	CROSS APPLY sys.dm_exec_query_plan_stats(deps.plan_handle) AS deqps

If you click on the XML value in the column “query_plan”, you will get the actual execution plan (see earlier image).

In case of troubleshooting problems or performance tuning here are some examples where this feature could be very useful:

• can't execute the real query to be able to generate the actual plans,
• can't simulate the same environment or
• can't turn on diagnostics to capture actual plans for every query.

*You can download the complete SQL Script with all examples from the post here: SQL Script.sql (1.20 kb)