Execution plan over query with temp tables

sql-server sql-server-2008-r2 sql-execution-plan
Diego Jancic picture Diego Jancic · Jan 11, 2012 · Viewed 21.7k times · Source

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

Conrad Frix picture Conrad Frix · Jan 11, 2012

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

Related questions

Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

I have a development database that re-deploy frequently from a Visual Studio Database project (via a TFS Auto Build). Sometimes when I run my build I get this error: ALTER DATABASE failed because a lock could not be placed on …

Read More
Select SQL Server database size

how can i query my sql server to only get the size of database? I used this : use "MY_DB" exec sp_spaceused I got this : database_name database_size unallocated space My_DB 17899.13 MB 5309.39 MB It returns me several …

Read More
CREATE TABLE IF NOT EXISTS equivalent in SQL Server

CREATE TABLE IF NOT EXISTS works on mysql but fails with SQL Server 2008 R2. What is the equivalent syntax?

Read More