To fetch an execution plan from SQL Server's buffer cache (i.e., the in-memory cache of execution plans), you can query the dynamic management views (DMVs) that SQL Server exposes. Here's how to do it:

✅ Fetch Cached Execution Plans from SQL Server

You can use the following T-SQL query to view cached execution plans stored in memory:

🔍 Key Notes:

• sys.dm_exec_query_stats: Contains aggregate performance statistics.

• sys.dm_exec_sql_text: Returns the SQL text for a batch.

• sys.dm_exec_query_plan: Returns the actual query plan XML.

• This returns cached plans, not "actual runtime plans" unless the plan includes runtime info (like in Query Store or with SET STATISTICS XML).