Sql-server – Capture Complex Actual Execution Plans

execution-plansql-server-2008-r2ssms

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

Best Answer

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.

Related Question