I recently had to do a very quick ‘rip and tear’ (high fives for the ‘Doom’ reference!) of stored execution plans for a bit of exploratory work I was involved with. It seems like a simple, shareable piece so here it is for you to grab, use and modify as and when you need it.

The backbone of this query relies on the following System Dynamic Management Views:

1. sys.dm_exec_cached_plans – returns a row for each cached query plan. This is used to infer the object type linked to the cached plan along with details such as the amount of times this plan has been reused.
2. sys.dm_exec_query_plan – the primary function of this view is to obtain the XML query plan content.
3. sys.dm_exec_sql_text – used here to get the physical SQL tied to the cached execution plan.
4. sys.dm_exec_query_stats – acts as a gateway to performance metrics for a given query plan.

You’ll notice that some of the views are configured as table-valued functions that consume a plan handle, the common examples use a CROSS APPLY to link entities and I’ve stuck with this approach. Other than that, the physical SQL is fairly straightforward; some inline comments have been added for clarity:

SELECT
COALESCE(OBJECT_NAME(st.objectid, st.[dbid]), '[No Object]') AS [ObjectName]
, cp.objtype AS [ObjectType]
, cp.usecounts AS [TimesExecuted]
, DB_NAME(qp.[dbid]) AS [DatabaseName]
, cp.size_in_bytes AS [SizeInBytes]
, cp.cacheobjtype AS [CacheObjectType]
, st.[text] AS [PhysicalQuery]
, qp.query_plan AS [QueryPlanXml]
, qs.last_execution_time AS [LastRan]
, qs.last_elapsed_time AS [LastRunElapsedTime]
FROM sys.dm_exec_cached_plans cp
	CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
	CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
	-- Extra join added for optional execution stats (like the last 'run' time and execution times)
	LEFT OUTER JOIN sys.dm_exec_query_stats qs ON cp.plan_handle = qs.plan_handle
-- Omit this is you want results for all databases contained on an instance
WHERE DB_NAME(qp.[dbid]) = 'JsonForTestDatabase'
-- Place the most 'used' first within the results set
ORDER BY cp.usecounts DESC;

I’ve gathered a small subset of the returnable columns to show the object name, type, query text (along with the XML query plan) as well as some simple usage information. I’m filtering the returned data by database name and placing the most used query plans first in the returned results; feel free to modify as needed.

You should retrieve output in the following vein:

Simply clicking content in the QueryPlanXml column will display the execution plan in the usual, expected format:

All pretty easy to get to grips with. Happy coding!