View execution plan for a sproc in the wild

Got stored procedure problems?  I feel sorry for you son.

Here's a quick query to pull the cached execution plan out of memory.

I italicized cached because it's not going to stay there forever.

I used this query to find the execution plan of a stored proc that had been running in a SQL job.  Instead of pulling down the sproc and trying to estimate the query plan, I chose to see what the cached plan looked like.




select eqp.query_plan
from sys.objects o
inner join sys.dm_exec_procedure_stats eps on eps.object_id = o.object_id
cross apply sys.dm_exec_query_plan (eps.plan_handle) eqp
where o.name = 'PoorPerformingProcedure'



Run this in SQL Management Studio and click on the "hyperlinked" XML in the results window, and you'll find the execution plan displaying just like it would if you'd hit Ctrl-L.

What else can you do with this?

How about finding the top 10 longest running execution plans of stored procs in cache?

select top 10 eqp.query_plan

from sys.objects o

inner join sys.dm_exec_procedure_stats eps on eps.object_id = o.object_id

cross apply sys.dm_exec_query_plan (eps.plan_handle) eqp

order by eps.total_worker_time desc

How about the top 10 most-frequently executed stored procedure plans in cache, in the current database?

select top 10 eqp.query_plan

from sys.objects o

inner join sys.dm_exec_procedure_stats eps on eps.object_id = o.object_id

cross apply sys.dm_exec_query_plan (eps.plan_handle) eqp

where eqp.DBID = DB_ID()

order by eps.execution_count desc