2

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.

Andrew
  • 789
  • 7
  • 13

2 Answers2

4

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.

Alejandro
  • 7,290
  • 4
  • 34
  • 59
  • 1
    Thank you! This works perfectly. I tried using SHOWPLAN_ALL in the same way you used STATISTICS XML here, but it error'd because it only applies at the batch level. So, I made the assumption that similar methods were all batch level, but clearly I was wrong. – Andrew Jul 26 '18 at 18:16
0

Use something like a recursive cte to generate your vals and then insert into a table. This will result in a plan to generate the values and then a plan to insert into the table. Don't do individual inserts. You could have put all those values into one insert as well. 

DECLARE @ TABLE (val INT) 

;with cte AS (
SELECT 1 AS col_

UNION ALL 

SELECT col_ + 1
FROM cte
WHERE col_ < 1000
)

INSERT INTO @
SELECT * 
FROM cte 
OPTION (MAXRECURSION 0)


SELECT * 
FROM @

If you want to totally remove the insert plan, you need to insert into a persistent table first, like a user table or a temp table, then run the non-insert TSQL by itself.

dfundako
  • 8,022
  • 3
  • 18
  • 34