Temp Tables and Tuning Advisor

Question

Temp Tables and Tuning Advisor

rickK_

Mr or Mrs. 500

Points: 500
More actions
September 17, 2010 at 12:53 pm

#240510

Does anyone know why various #temp tables fail the analyzer and \or Display Estimated Execution Plan with an Invalid object name error. The proc works fine, and the #temp is normally a select into. Any input will be greatly appreciated.
ex:
select a, b
into #temp
where something
Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

can you provide the actual execution plan for one of these cases where you see this happening?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

rickK_ Mr or Mrs. 500 Points: 500 More actions · Answer 2

can't generate one.

get :

Msg 208, Level 16, State 0, Procedure d_scroll_assignments_sp, Line 1129

Invalid object name '#tmp4'.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

what version and service pack level?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

rickK_ Mr or Mrs. 500 Points: 500 More actions · Answer 4

2005 standard sp2 on server

xp sp3 client on workstation using advisor

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

can you generate the execution plan from the server?

Just the "actual execution plan"

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

rickK_ Mr or Mrs. 500 Points: 500 More actions · Answer 6

if i execute it and set the include actual execution plan, it works from client. I just cant post it because of confidentiality agreement with large vendor.

winash SSChampion Points: 11446 More actions · Answer 7

If you're using the "Display Estimated Execution Plan" then the following will fail:

SELECT * INTO #T1 FROM sys.objects WHERE 1=2

-- uncomment the following line and use display estimated execution plan and it will fail

-- provided you didn't create the temp table already

--SELECT * FROM #T1

What's at line 1129 of your SP? Are you using the temp table created by a SELECT...INTO in that line? It looks like a temp table created using SELECT..INTO cannot be resolved while creating the estimated execution plan

-- this will fail the display estimated exec plan

SELECT * INTO #T1 FROM sys.objects WHERE 1=2

-- uncomment this and use display estimated execution plan and it will fail

-- provided you didn't create the temp table already of course

CREATE TABLE #T2(objectid int);

SELECT T1.object_id,T2.objectid FROM #T1 T1 INNER JOIN #T2 T2 ON T1.object_id = T2.objectid

GO

-- this will work

CREATE TABLE #T3(object_id int);

CREATE TABLE #T4(objectid int);

SELECT T1.object_id,T2.objectid FROM #T3 T1 INNER JOIN #T4 T2 ON T1.object_id = T2.objectid

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 8

rickK_ (9/17/2010)
Does anyone know why various #temp tables fail the analyzer and \or Display Estimated Execution Plan with an Invalid object name error. The proc works fine, and the #temp is normally a select into. Any input will be greatly appreciated.

The reason why Temp Tables cause the Analyzer to fail is because the Analyzer runs in a different scope than the current SSMS session and the temp tables don't actually exist in the Analyzer session. You'll need to temporarily change Temp tables to 3 part naming in TempDB to be able to use the Analyzer against "temp tables" by temporarily turning them into "real" tables.

So far as the estimated execution plan goes... it's going to lie to you anyway... why bother? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rickK_ Mr or Mrs. 500 Points: 500 More actions · Answer 9

if (something)

begin

select a

into #temp1

from realtable

select b

from realtable2 r2, #temp1 t

where r2.b = t.a --fails here

this is also inside an if else statement.

end

else

begin

......

end

rickK_ Mr or Mrs. 500 Points: 500 More actions · Answer 10

rickK_

Mr or Mrs. 500

Points: 500

September 17, 2010 at 2:29 pm

#1223320

so I would change #tmp to tembDB.dbo.#tmp ?

winash SSChampion Points: 11446 More actions · Answer 11

nope - changing #tmp to tempdb.dbo.#tmp will not work...like I said - the issue is that the estimated execution plan (and I'm guessing the tuning advisor) cannot resolve temp tables created with SELECT...INTO (or created using dynamic SQL for that matter)

Even using SHOWPLAN_TEXT or SHOWPLAN_XML doesn't seem to work...I think the sure way out is to create the temp table using the CREATE TABLE #tmp(...) and not using SELECT...INTO

Or use the actual execution plan instead of the estimated execution plan

rickK_ Mr or Mrs. 500 Points: 500 More actions · Answer 12

rickK_

Mr or Mrs. 500

Points: 500

September 17, 2010 at 2:51 pm

#1223333

OK, thanks alot.