when analyzing a procedure I often query the cached execution plans of procedures. I use this (simplified) query:
QueryText = substring( querytext.text, querystat.statement_start_offset/2+1, ( querystat.statement_end_offset - querystat.statement_start_offset )/2 + 1 ),
from sys.dm_exec_query_stats querystat
cross apply sys.dm_exec_sql_text( querystat.sql_handle ) as querytext
cross apply sys.dm_exec_query_plan( querystat.plan_handle ) as queryplan
where querytext.dbid = db_id()
and querytext.objectid = Object_id( 'dbo.SomeProcedure')
Unfortunately I get the execution plan only for the whole procedure. As I can query the text of each single statement I'd like to have the execution plan of each single statement too. This would be much more conventient as for procedures containing many statements it's often difficult to find the query within the large execution plan.
Is there any possibility?
I know I could use the SQL Profiler to get each single query whith it's single execution plan. As the profiler is not always running I'd like to query it from the procedure cache.
Thank you, Wolf