6

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.

Improve this question
0

3 Answers 3

Reset to default
8

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.

Improve this answer
1
  • This is the only way I can run my stored procedure and generate an XML sql plan right now. I get about 1 gb memory usage and a hang up or crash, or an outright failure to grab the plan, when I use SQL Sentry Plan Explorer.
    – Warren P
    Nov 29, 2012 at 18:57
9

Have you looked at SentryOne Plan Explorer? It can handle humongous plans that Management Studio chokes on. You can also generate both actual and estimated plans from within the tool, so you don't even have to bother with SSMS.

Improve this answer
13
  • 4
    I don't work for SQL Sentry and I Plus One this suggestion. The free version is excellent, the Pro version is better but they are both fine.
    – Mike Walsh
    Nov 15, 2012 at 15:32
  • SQL Plan explorer dies the same way SSMS does in my case. Changing answers.
    – Warren P
    Nov 29, 2012 at 18:56
  • @WarrenP Can you send the XML you get from Management Studio to abertrand AT sqlsentry DOT net? If there's a plan that we can't render, we'd like the opportunity to fix it.
    – Aaron Bertrand
    Nov 29, 2012 at 19:03
  • I believe the issue is that I'm invoking a stored procedure which generates >10K plans. Stored proc -> Cursor -> Loop over cursor -> Plan per subquery per cursor row. Does that sound like a case you would care to fix? I'm looking for ways to get "enough" information without getting "too much" so I don't flood the tools. It may not be your fault at all. :-) Once I've got the very large plan collected, I can open it in your tool, just not collect it.
    – Warren P
    Nov 29, 2012 at 19:23
  • 1
    Ah yes, I see, I thought you were talking about one plan, not thousands of plans. I think you may just be exceeding the capacity of any graphical tool to try and render that many plans. Would be interested to see what your stored procedure does that it can execute in 3-5 seconds but be using/interacting with that many plans - something not right in the math there I think.
    – Aaron Bertrand
    Nov 29, 2012 at 19:33
5

I've had this happen from time to time. The option to SET STATISTICS XML ON as referenced above is probably the best answer (outside of using a thrid party). However, I've found that running SSMS on the server itself can help. That said, if you are crashing SSMS on your local box, probably not a great idea to try it on the server.

Another thing you can do is query the plan cache to see which execution plan(s) are in cache for that proc with a query similar to this one:

select
querytext.text,executionplan.query_plan , plan_handle
from
sys.dm_exec_cached_plans 
outer apply sys.dm_exec_sql_text(plan_handle) as querytext
outer apply sys.dm_exec_query_plan(plan_handle) as executionplan 
Where text like 'Create procedure dbo.procname%'

Another thing that I always do - even before looking at an execution plan, is SET STATISTICS IO ON and then run the proc / code. If I'm happy with the output of SET STATISTICS IO ON, chances are I have a plan that I'll like. http://msdn.microsoft.com/en-us/library/ms184361.aspx

Improve this answer
4

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.