6

I have a stored procedure that among other things builds and executes a large number of Dynamic SQL statements. (Don't hate me, I didn't write it.)

I would like to retrieve the actual execution plan for the overall procedure so that I can analyze it in SQL Sentry plan explorer.

Turning on Actual execution plan when I execute the PROC in a query window just crashes Management studio when I try to look at it and save it off.

If I turn on SHOW STATISTICS XML that just results in each individual statements plan xml as a separate result. That doesn't help me because I need the entire plan as a whole.

My problem is I would like to get a .sqlplan file saved off of the server and save it to my local machine where I can analyze it in Plan Explorer. The reason is that I cannot connect directly to the server running Plan Explorer. RDP through a proxy is my only connection method, so I can only analyze .sqlplan files that have been saved and that I have retrieved to my local machine for analysis.

When I retrieve the estimated plan for the Procedure I can save off the returned graphical plan as a .sqlplan file that has a single root node and a single Batch Sequence node. I can then bring that file to my local computer and open it in Plan Explorer and the software can see all of the different statements that make up the batch.

If I turn on XML Statistics, each statements plan is returned as it's own standalone plan with it's own root node. I can't put that in a single file because that's not valid xml.

Any ideas?

Thanks, Bill

Improve this question
5
  • If you trace this procedure in Profiler, how many distinct statements does it have?
    – Marian
    Jan 17, 2014 at 22:15
  • Please update your question to reflect that you can't install any software on the server where the procedure is running, and you can't connect to it remotely from a local copy of Plan Explorer.
    – Aaron Bertrand
    Jan 17, 2014 at 23:11
  • Bill your requirements suck buddy I don't think we can help you.
    – Zane
    Jan 17, 2014 at 23:12
  • 2
    If you have all the individual XML plans isn't it quite trivial to consolidate them into one XML document?
    – Martin Smith
    Jan 18, 2014 at 1:14
  • Hey everyone, I know this question is old, but I have worked around this issue. I was able to convince the client that their "Security Concerns" with installing 3rd party software were ridiculous and that my time figuring out how to work around them was costing them more money than it was worth. I was then able to deal with the generated statements on the server in groups of about 50 at a time which made it more manageable. Thanks everyone for your help.
    – Bill Hurt
    Feb 12, 2014 at 15:44

1 Answer 1

Reset to default
4

I can't say for certain because I've never dealt with that large a stored procedure but you could try this:

SELECT query_plan
FROM sys.dm_exec_procedure_stats
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE object_id = object_id('procedure_name')

Then save the output to a text file.

Improve this answer
6
  • Just a couple of comments - this is the cached plan, not the actual plan, so will be missing some information and may not be the same plan as you would get executing the stored procedure at a different point in time or with certain parameters. Also no guarantee that every statement has a plan currently in the cache. Also this suffers the same problem described toward the end of the question: these are a bunch of individual plans, which the OP already seems capable of getting. They want everything in one plan, like you would get if you saved a set of execution plans from SSMS output.
    – Aaron Bertrand
    Jan 18, 2014 at 2:14
  • Why that is a requirement, I'm not sure. I'd be analyzing the plans individually anyway, and as Martin said, you could assemble if needed.
    – Aaron Bertrand
    Jan 18, 2014 at 2:18
  • @AaronBertrand, based on BOL and admittedly minimal experience with it I thought this particular DMO returned only 1 row per stored procedure not per query in it. Is that not always the case? I agree with the problem that the sp will change based on the parameters but I don't see a way around that.
    – Kenneth Fisher
    Jan 18, 2014 at 2:27
  • 1
    I want to make it clear that I still think this is better than nothing, especially given the mountains of roadblocks the OP is facing, just want to make sure the limitations are known.
    – Aaron Bertrand
    Jan 18, 2014 at 2:54
  • 1
    @AaronBertrand Absolutely. Although the OP could clear the cache for this particular SP using DBCC FREEPROCCACHE(plan_handle), run it with the parameters he is interested in, then check the DMO. Then clear the cache for the SP again, run it with a different set of parameters, check the DMO etc.
    – Kenneth Fisher
    Jan 18, 2014 at 3:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.