February 8, 2013 at 6:13 am
Hello there,
when analyzing a procedure I often query the cached execution plans of procedures. I use this (simplified) query:
select
querytext.objectid,
querystat.plan_handle,
QueryText = substring( querytext.text, querystat.statement_start_offset/2+1, ( querystat.statement_end_offset - querystat.statement_start_offset )/2 + 1 ),
queryplan.query_plan
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
February 8, 2013 at 8:05 am
Sure, you can pull from sys.dm_exec_text_query_plan. That requires a statement start offset and a statement end offset, both of which are available from sys.dm_exec_query_stats or sys.dm_exec_requests. It does return the query plan as text, so you have to throw a CAST AS XML on it if you want to be able to click on it in query results. Other than that, it's a great way to access plans as text and to get the isolated plan for a given statement.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 8, 2013 at 8:22 am
Thank you, exactly what I was looking for 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply