Temporary table query execution plan

Question

I have 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 works slowly. When I try to view the execution plan, I cannot, because SQL Server Management Studio says: "Msg 208, level 16, state 0, line 31 The object name is" #temp "."

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

Answer 1

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

So how does this create #temp

 SELECT Id INTO #temp FROM table WHERE ... 

it will not work

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

So the solution is to add this to the beginning (or make an equivalent via SSMS)

 SET SHOWPLAN_TEXT OFF GO SET STATISTICS PROFILE ON GO 

Answer 2

It should allow you to see the evaluation plan for the first description.

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

(Or you can, of course, enable the "Include actual execution plan" option in SSMS and run all of this if you are not trying to view the proposed plan)

Answer 3

The optimizer that is used to create plans for prospective execution plans does not execute T-SQL. It runs instructions through an algebraist, a process described earlier that is responsible for checking the names of database objects.

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

---

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

Answer 4

You can use this operator before running a query

  SET STATISTICS PROFILE ON SELECT Id INTO #temp FROM table WHERE ... DELETE FROM #temp INNER JOIN table2 ON a=b WHERE ...