Execution plan over query with temp tables

Question

I've a stored procedure that does something like this:

SELECT Id
INTO #temp
FROM table
WHERE ...

DELETE FROM #temp
INNER JOIN table2 ON a=b
WHERE ...

But it's running slowly. When I try to view the Execution Plan I can't since the SQL Server Management Studio says "Msg 208, Level 16, State 0, Line 31 Invalid object name '#temp'."

Is there any way to view the execution plan (or the execution details (not the plan)) for such script?

Answer 1

SET SHOWPLAN_TEXT ON (or clicking on Display Estimated Execution Plan is SSMS) creates a plan instead of executing the SQL

Since this creates the #temp

SELECT Id
INTO #temp
FROM table
WHERE ...

this will fail

DELETE FROM #temp
INNER JOIN table2 ON a=b
WHERE ...

So solution is to add this to the top (or do the equivalent via SSMS)

SET SHOWPLAN_TEXT OFF
GO

SET STATISTICS PROFILE ON
GO

Answer 2

It should let you see the estimated execution plan for the first statement fine.

For the second statement you will need to create and populate the #temp table first (population is important so that it shows you the plan that will be used for the correct number of rows).

(Or you can of course just turn on the "Include Actual Execution Plan" option in SSMS and run the whole thing if you aren't specifically trying to see the estimated plan)

Answer 3

The optimizer, which is what is used to generate Estimated Execution plans, doesn't execute T-SQL. It does run the state­ments through the algebrizer , the process outlined earlier that is responsible for verifying the names of database objects.

Since the query has not yet been executed, the temporary table does not yet exist. This is the cause of the error.

---

One way to solve this (and test the execution plan) is to create the #temp table before and make a insert into instead of select ... into in the stored procedure.

Answer 4

You can use this statement before running the query

   SET STATISTICS PROFILE ON
   SELECT Id
   INTO #temp
   FROM table
   WHERE ...

   DELETE FROM #temp
  INNER JOIN table2 ON a=b
  WHERE ...