Viewing execution plans is useful for troubleshooting queries and stored procedures. But sometimes the query or stored procedure takes a long time to run, or you cannot run it yourself because it modifies data.

You can view these plans by pulling them from the plan cache. SQL Server provides a way to do this:

UPDATE 7/18/2016 – When I first wrote this, I thought that this would show the actual execution plans. I have since learned that this shows the estimated execution plans. Hopefully some readers may still find it useful.

select	cp.usecounts, 
	cp.cacheobjtype, 
	cp.objtype, 
	cp.size_in_bytes,
	st.[text], 
	qp.query_plan
from	sys.dm_exec_cached_plans cp
	cross apply sys.dm_exec_sql_text(plan_handle) st
	cross apply sys.dm_exec_query_plan(plan_handle) qp;

Be aware than the plan cache can be large, so you will usually want to filter on the objtype or text column to narrow down on the plan you want to see.

You’ll get a result set like this:

If you click the query_plan xml value it will render the execution plan in SSMS:

Here’s the specific query I used for this example:

select	cp.usecounts, 
	cp.cacheobjtype, 
	cp.objtype, 
	cp.size_in_bytes,
	st.[text], 
	qp.query_plan
from	sys.dm_exec_cached_plans cp
	cross apply sys.dm_exec_sql_text(plan_handle) st
	cross apply sys.dm_exec_query_plan(plan_handle) qp
where	cp.objtype = 'Adhoc'
and	st.[text] like '%dbo.Staff%'
and	st.[text] not like '%dm_exec_cached_plans%';