How to check actual execution plan of a query after it has been executed

In SQL Server, how can we see actual execution plan of a query, after it has been executed?

As per my knowledge, only estimated execution plan (not actual execution plan) are stored in plan cache. is that correct?

Note: We do not have any third party monitoring tools.

If you’re running SQL Server 2012 or above, you could consider creating an Extended Events session and configure it to capture the query_post_execution_showplan event to capture the actual execution plan after the query has been executed. But obviously the session would need to be setup and enabled prior to query execution.

However, be aware that enabling this event will impact performance so this is probably something you’d want to switch on for investigative purposes, then switch off again once you’ve captured the plan you’re after.

Prior to SQL Server 2019, there was NO possibility to get the actual execution plan after the query has been executed.

Started with SQL Server 2019+, that option exists.

First you should ALTER DATABASE SCOPE CONFIGURATION.

This option enables the equivalent of the last actual execution plan in sys.dm_exec_query_plan_stats ( the same could be accomplished by enabling TF 2451 ).

ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;

See more on ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)

And than you can execute query like this one in order to get the actual execution plan.

SELECT qps.query_plan, 
       st.text, 
       DB_NAME(st.dbid) DBName, 
       OBJECT_NAME(st.objectid) ObjectName, 
       cp.usecounts, 
       cp.size_in_bytes /1024 [Size in kB],
       cp.objtype,
   cp.cacheobjtype
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 st.encrypted = 0
      AND DB_NAME(st.dbid) = 'AdventureWorks2016'
ORDER BY [Size in kB] DESC;

And the result is shown in the image below.