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
COUNT(*) AS Count
Currently in SQL Server versions prior to 2019 you can get the cached execution plans. Use this query and see what queries are cached:
, q.text AS query_text
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:
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)