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.
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.