Execution Plan Drilldown Issue

• zachary.curtin

  SSC Enthusiast

  Points: 183

  More actions

  September 3, 2013 at 9:00 am

  #395409

  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:

• SQL Severe DBA

  Hall of Fame

  Points: 3579

  More actions

  September 3, 2013 at 9:56 am

  #1647089

  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

  SSC-Insane

  Points: 24038

  More actions

  September 3, 2013 at 3:47 pm

  #1647157

  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]

• zachary.curtin

  SSC Enthusiast

  Points: 183

  More actions

  September 4, 2013 at 7:19 am

  #1647292

  Thank you very much this stupid little error was killing me!