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?

http://www.sql-server-performance.com/2007/temp-tables-vs-variables/ — Bassam Mehanni, Jan 11 '12 at 16:34
@BassamMehanni - That link contains errors. Table variables are logged same as `#temp` tables in order to allow statement rollback. — Martin Smith, Jan 11 '12 at 16:45

Conrad Frix · Accepted Answer · 2012-01-11T17:05:29.667

7

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

edited Jan 11 '12 at 17:05

answered Jan 11 '12 at 16:40

Conrad Frix

51,984
12
96
155

score 2 · Answer 2 · answered Jan 11 '12 at 16:40

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)

score 1 · Answer 3 · answered Jan 11 '12 at 16:37

The optimizer, which is what is used to generate Estimated Execution plans, doesn't execute T-SQL. It does run the statements 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.

score 1 · Answer 4 · answered Jan 11 '12 at 16:42

1

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 ...

answered Jan 11 '12 at 16:42

praveen

12,083
1
41
49

score 0 · Answer 5 · answered Aug 05 '20 at 11:14

0

I simply selected the "select into" clause first and executed it by pressing F5. This created the tmp table.

Then I selected rest of the queries and clicked "Display Plan" (or press Ctrl+L).

answered Aug 05 '20 at 11:14

Cagin Uludamar

372
1
3
16

Execution plan over query with temp tables

5 Answers5