Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

get cached excution plan of single query out of procedure plan Expand / Collapse
Author
Message
Posted Friday, February 8, 2013 6:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 19, 2014 4:36 AM
Points: 199, Visits: 740
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
Post #1417655
Posted Friday, February 8, 2013 8:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:26 AM
Points: 14,205, Visits: 28,536
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
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1417724
Posted Friday, February 8, 2013 8:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 19, 2014 4:36 AM
Points: 199, Visits: 740
Thank you, exactly what I was looking for
Post #1417737
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse