Execution Plans and #TempTables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Execution Plans and #TempTables

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2004-09-01 : 16:22:24

Whenever a temporary table is used in a stored procedure, QA won't generate an execution plan because #Mytable is not a known object.

I could code it as a fixed table just to get the execution plan. Anyone know a better way to go?

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-01 : 16:27:27

Huh?

Too many Trigger Tonics?


CREATE PROC mySproc99
AS
SELECT * INTO #myTemp99 FROM Orders

SELECT * FROM #myTemp99

DROP TABLE #myTemp99
GO

[CTRL+K]

EXEC mySproc99
GO

DROP PROC mySproc99
GO

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-01 : 16:31:51

Sam is hitting the Display Estimated Execution Plan rather than CTRL+K way. So Brett's way is the workaround.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-01 : 16:38:34

Sam...pass the damn jug, you've had enough...

What's the difference I reckon?

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-01 : 16:41:09

One is estimated, the other is actual?

Tara

SamC
White Water Yakist

3467 Posts

Posted - 2004-09-01 : 16:43:47

Tara's right. Didn't realize there was a dif. ICONs are the same.

I see QA's drop-down menu specifies ctrl-k. Neither CTRL-K or the menu item do anything (but I don't get an error either).

What am I doing wrong?

Sam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-01 : 16:44:54

You hit CTRL + K, then run the stored procedure. You'll see a second tab in the results window. That's where you'll find the execution plan.

Tara

SamC
White Water Yakist

3467 Posts

Posted - 2004-09-01 : 17:04:19

Handy to know.

And this is the "Actual", not "Estimated" execution plan...

I wonder how many hours I've spent chasing "Estimated" Index Scans when the "Actual" was a Index Seek?

Sam

Edit: Ignore Brett's quote. I never wrote that.

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-01 : 17:11:47

quote:
Originally posted by SamC

Handy to know.

And this is the "Actual", not "Estimated" execution plan...

I wonder how many hours I've spent chasing "Estimated" Table Scans when the "Actual" was a Table Seek?

Sam

[smacks forehead]

Table Seeks...give me the damn bottle

[/smacks forehead]

I gotta get out here....it's 5:00 somewhere

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2004-09-01 : 17:17:49

OK OK... INDEX ! I meant index.

When good threads go bad.

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-02 : 09:19:12

quote:
Originally posted by SamC

Edit: Ignore Brett's quote. I never wrote that.

Sorry Sam...I was just wondering if you were drunk?

Thanks for the chuckle.

Brett

8-)

mr_mist
Grunnio

1870 Posts

Posted - 2004-09-02 : 10:30:18

quote:
Originally posted by tduggan

Sam is hitting the Display Estimated Execution Plan rather than CTRL+K way. So Brett's way is the workaround.

It would be nice to be able to do an estimated plan for a query with temp tables, though.

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources