Sql-server – Large set of execution plans hangs up SSMS

execution-plansql-server-2008-r2ssms

I have a stored procedure that takes about 3-5 seconds that I'm trying to understand so I want an execution plan for it. When I run it in SQL Server Management Studio with Execution Plan enabled, it takes over 15 minutes and then, I get a tab that says "Execution Plan" but the tab is empty. Any further attempts to run any SQL at all (even select * from foo) no longer works. I have a broken SQL server management studio app, I must shut it down and try again. I've done it three times and wasted 45 minutes, and I am now ready to learn about some alternatives.

First is there a reliable command line way to generate an SQLPLAN file for a particular sql script, maybe from the command line, and then I could investigate using some other tool than SSMS to generate my plans.

Secondly, if there isn't a built-in reliable command-line way to generate and store an SQL execution plan as text or XML, then I'd like to know if there exists some other third party tools that would do a good job on very large SQLPlans, in particular not choking and dying when the GUI-drawing parts of it get overwhelmed.

What do you do when SSMS won't generate and show you an execution plan? I'm using SQL 2008 R2 Standard and its included SSMS version, and I don't have any extra plugins.

Update I'm invoking a stored procedure which creates a cursor, which does subqueries, and does great evil, in a loop, generating in excess of 10K individual subquery statements. It looks like I really need to refactor this down to generate less of a storm of output.

Update2 It really looks like server-side-tracing (to zero in on problem areas) and then a return to profiling then a return to query plans might be required. I am zoomed in far too deep in a big picture, and I need to zoom the heck out a bit.

Best Answer

Try SET STATISTICS XML ON before you run your query. That will return the plan in XML format without trying to render it in SSMS. You could then copy and paste the text into a file. From there, you'll get the plan in XML format, and you can open it up in a tool like SQL Sentry Plan Explorer.

Related Question