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

Execution Plan Drilldown Issue Expand / Collapse
Author
Message
Posted Tuesday, September 3, 2013 9:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 27, 2014 12:11 PM
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:

Post #1490996
Posted Tuesday, September 3, 2013 9:56 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, November 23, 2014 9:00 PM
Points: 611, Visits: 448
Have you tried SQL profiler including the execution plan

and also you can add object id of that specific index in the filter .
Post #1491021
Posted Tuesday, September 3, 2013 3:47 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 2:39 PM
Points: 816, Visits: 742
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
Post #1491105
Posted Wednesday, September 4, 2013 7:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 27, 2014 12:11 PM
Points: 19, Visits: 80
Thank you very much this stupid little error was killing me!
Post #1491285
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse