September 3, 2013 at 9:00 am
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:
September 3, 2013 at 9:56 am
Have you tried SQL profiler including the execution plan
and also you can add object id of that specific index in the filter .
September 3, 2013 at 3:47 pm
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 4, 2013 at 7:19 am
Thank you very much this stupid little error was killing me!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply