Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Execution Plan Drilldown Issue


Execution Plan Drilldown Issue

Author
Message
zachary.curtin
zachary.curtin
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 80
I am trying to list the execution plan in xml (SET SHOWPLAN ON) of a given sp in a query that returns other things as well.

To do that I have been trying to use sp_helptext to return the contents of the given sp in a query but I then want the execution plan of that query itself.

Basically I am trying to view all SPs that hit a given index and then list the execution plan for the sp that caused the most load on the server and I am trying to do all of this in one query.

I have had some luck pulling the indexes of all the tables out and wrapping that up into an sp itself but I am having trouble pulling out the sp that hits an index the most and showing the execution plan for that sp. Can anyone help me out?

Here is what I have so far but I cannot get the second select statement inside of the SP to return anything no matter what I do. I have very little experience with XML and I know that the issue is in the last CROSS APPLY but I cannot for the life of me figure out what it is:
RatanDeep Saha
RatanDeep Saha
SSChasing Mays
SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)

Group: General Forum Members
Points: 648 Visits: 687
Have you tried SQL profiler including the execution plan

and also you can add object id of that specific index in the filter .
Erland Sommarskog
Erland Sommarskog
SSC Eights!
SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)

Group: General Forum Members
Points: 935 Visits: 866
Your error was actually fairly trivial: you hade overlooked that the tag looks like this:

<Object Database="[bos_sommar]" Schema="[dbo]" Table="[currencies]" Index="[pk_cur]" IndexKind="Clustered" />



That is, the index name is quoted in brackets.

I changed the procedure, to have this line in the beginning:

SELECT @IndexName = quotename(@IndexName)



And then I removed two invocations of quotename that you had, and now I got output from the second query.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
zachary.curtin
zachary.curtin
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 80
Thank you very much this stupid little error was killing me!
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