sqlsql-serverdatabasessmssql-optimization

How do you show the actual query plan for a single query in a batch in SSMS?

I have a stored procedure I'm trying to optimize which accepts a table variable as parameter. The table variable has a single column and is used to pass in a list of primary keys. When testing, I create a dummy table variable, run a bunch of INSERT statements to add dummy values to it, and pass it into the stored procedure. This works fine, but when I want to look at the actual query execution plan, SSMS compiles a query plan for all of the INSERT statements and the stored procedure. So, if I have 1000 dummy INSERT statements, SSMS will crash. Unfortunately, the table variable has to be executed in the same batch as the stored procedure, so I can't enable/disable query plans by batch.

Is there a way to compile the actual query plan for the stored procedure only, ignoring all INSERT statements in the batch?

Here's what it looks like:

DECLARE @dummyIds AS PrimaryKeyTable
INSERT INTO @dummyIds VALUES(1)
INSERT INTO @dummyIds  VALUES(2)
...
INSERT INTO @dummyIds VALUES(1000)

EXEC MyStoredProc @dummyIds

If I executed that batch including the actual query plan, it would generate 1001 query plans, when I really only want the 1.

The SQL server instance is running SQL SERVER 2014.

Solution

  • One way to achieve this is not to rely on SSMS to generate the request query plans, but do in your test batch at the required points only. For this, just manually insert the SET STATISTICS XML instruction before the to-be-analyzed query and disable it afterwards, and keep the option in SSMS disabled.

    Something like that will do:

    DECLARE @dummyIds AS PrimaryKeyTable
INSERT INTO @dummyIds VALUES(1)
INSERT INTO @dummyIds  VALUES(2)
...
INSERT INTO @dummyIds VALUES(1000)

SET STATISTICS XML ON          --Enable plan generation
EXEC MyStoredProc @dummyIds
SET STATISTICS XML OFF         --Disable plan generation

    That way the server will return query plans just for the code in-between. Internally, SSMS does exactly this for the whole batch. Note that with this change SSMS won't show an "execution plan" tab in the result panel, but instead an extra result set with a lone XML document will be returned (or more precisely, one for each plan). You must click those manually and SSMS will then show the graphical plan for it.