SQL Server – View Execution Plan for Single Statement in Stored Procedure

execution-planperformancesql-server-2016

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?

Best Answer

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:

screenshot of default results 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:

screenshot of resizing execution plan panes in SSMS

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:

screenshot of per-statement plan view in Plan Explorer

Related Question