How can I see the plan for ONE statement in a stored procedure?

Question

I've got some SQL which I've run several times a day in development, generally getting execution time of 3-8 minutes.

Now I've wrapped it in a stored procedure (with no parameters), the performance is impossible. THe first "heavy" statement, which involves only c.27k rows, never finishes.

Actual execution plan is not an option, because I can't even get this statement to finish. So I have to look at estimated execution plan.

Trouble is I get a separate "sub-pane" in the Execution Plan pane for every statement. I can't comment out preceding statements as they're necessary for the problem statement to run (though I've commented out all following statements).

How can I view just the plan for the problem statement on-screen? It's obviously a huge plan. All the other statements' plans (which I'm not interested in) take up an enormous amount of screen space, so it's impossible to scroll through the important one and find the chokepoints. But there seems to be now way to "Show just the plan for this statement, and take up the full screen".

Am I missing something?

Answer 1

I don't think there's a way to do that in SSMS.

You should use Sentry One Plan Explorer for this. It's a free tool that provides an alternative way of viewing SQL Server execution plans.

I've run this code on my local copy of the Stack Overflow 2010 sample database:

CREATE PROCEDURE #sp_TestQuery
AS
SELECT COUNT_BIG(*) FROM dbo.Users u WHERE u.Id > 10;
SELECT MAX(p.CommentCount) FROM dbo.Posts p WHERE p.Id > 1000;
SELECT MIN(v.BountyAmount) FROM dbo.Votes v WHERE v.Id > 100000;
GO

EXEC #sp_TestQuery;

Running the EXEC... line produces the problem you mentioned in SSMS:

One option I have here is to at least resize the different panes, by dragging the border between them. If I wanted to mostly see the middle query:

But that's still not ideal. The default view in Plan Explorer shows me a list of statements at the top, and I can click on each statement to view the graphical plan below it. Here's the middle query in Plan Explorer:

Answer 2

There IS a way.

• In SSMS set Include Actual Exec Plan (Ctrl-M)
• SET STATISTICS XML OFF; in first statement of batch
• Wrap the statement in question like:
  SET STATISTICS XML ON;
  statement(s)
  SET STATISTICS XML OFF;

Even works within stored procs.