SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


get cached excution plan of single query out of procedure plan


get cached excution plan of single query out of procedure plan

Author
Message
WolfgangE
WolfgangE
SSC-Addicted
SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)

Group: General Forum Members
Points: 495 Visits: 793
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
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41945 Visits: 32666
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
WolfgangE
WolfgangE
SSC-Addicted
SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)

Group: General Forum Members
Points: 495 Visits: 793
Thank you, exactly what I was looking for :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search