Showplan_text and #temp tables

Showplan_text and #temp tables

Post by Microsoft New » Wed, 16 Jun 1999 04:00:00



I'm sure this question has been asked before, so at the risk of being flamed
mercilessly I'm going to ask it again.

SQL Server 6.5's "showplan" accommodated temporary tables nicely, even when
used in conjuntion with "noexec."  This capability proved invaluable in
debugging and tuning ill-behaved SQL, including scripts which took advantage
of Transact-SQL's ability to create and manage temporary tables.

With 7.0, this appears to have gone away.  "Showplan" and "noexec" have been
effectively combined into "showplan_text" (or "showplan_all") which on their
surface appear to be a functional equivalent.  They're not.  Unlike in 6.5,
temporary tables which are created as part of the SQL being analyzed are not
actually created.  Subsequent referenced to said tables then fail with a
"table does not exist" message.

Is there a work-around to this?  Is there something I'm missing?  Has MS
addressed this at all in this forum or on their Web site?

Any guidance would be greatly appreciated.

Bill Mallary

 
 
 

Showplan_text and #temp tables

Post by Tibor Karasz » Wed, 16 Jun 1999 04:00:00


Bill,

I guess that there might be a better way, but if you don't mind executing
the query:

SET STATISTICS PROFILE ON
(Which is used by QA's: Query | Show Execution Plan.)
--
Tibor Karaszi
MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB
Please reply to the newsgroup only, not by email.


> I'm sure this question has been asked before, so at the risk of being
flamed
> mercilessly I'm going to ask it again.

> SQL Server 6.5's "showplan" accommodated temporary tables nicely, even
when
> used in conjuntion with "noexec."  This capability proved invaluable in
> debugging and tuning ill-behaved SQL, including scripts which took
advantage
> of Transact-SQL's ability to create and manage temporary tables.

> With 7.0, this appears to have gone away.  "Showplan" and "noexec" have
been
> effectively combined into "showplan_text" (or "showplan_all") which on
their
> surface appear to be a functional equivalent.  They're not.  Unlike in
6.5,
> temporary tables which are created as part of the SQL being analyzed are
not
> actually created.  Subsequent referenced to said tables then fail with a
> "table does not exist" message.

> Is there a work-around to this?  Is there something I'm missing?  Has MS
> addressed this at all in this forum or on their Web site?

> Any guidance would be greatly appreciated.

> Bill Mallary



 
 
 

Showplan_text and #temp tables

Post by Microsoft New » Wed, 16 Jun 1999 04:00:00


This helps in that it gives me the showplan that I need.  I'd like not
actually execute, however, especially when displaying the query plans for
STORED PROCEDURES which perform data manipulation.

This was easily accomplished in 6.5 using the following syntax:

set showplan, fmtonly on
go

exec procedure_name
go

set fmtonly, showplan off
go

Any other ideas?

Bill Mallary


>Bill,

>I guess that there might be a better way, but if you don't mind executing
>the query:

>SET STATISTICS PROFILE ON
>(Which is used by QA's: Query | Show Execution Plan.)
>--
>Tibor Karaszi
>MCSE, MCSD, MCT, SQL Server MVP
>Cornerstone Sweden AB
>Please reply to the newsgroup only, not by email.



>> I'm sure this question has been asked before, so at the risk of being
>flamed
>> mercilessly I'm going to ask it again.

>> SQL Server 6.5's "showplan" accommodated temporary tables nicely, even
>when
>> used in conjuntion with "noexec."  This capability proved invaluable in
>> debugging and tuning ill-behaved SQL, including scripts which took
>advantage
>> of Transact-SQL's ability to create and manage temporary tables.

>> With 7.0, this appears to have gone away.  "Showplan" and "noexec" have
>been
>> effectively combined into "showplan_text" (or "showplan_all") which on
>their
>> surface appear to be a functional equivalent.  They're not.  Unlike in
>6.5,
>> temporary tables which are created as part of the SQL being analyzed are
>not
>> actually created.  Subsequent referenced to said tables then fail with a
>> "table does not exist" message.

>> Is there a work-around to this?  Is there something I'm missing?  Has MS
>> addressed this at all in this forum or on their Web site?

>> Any guidance would be greatly appreciated.

>> Bill Mallary


 
 
 

Showplan_text and #temp tables

Post by Tibor Karasz » Fri, 18 Jun 1999 04:00:00


Bill,

I peeked around a bit, and there doesn't seem to be another way. Sorry.

--
Tibor Karaszi
MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB


> This helps in that it gives me the showplan that I need.  I'd like not
> actually execute, however, especially when displaying the query plans for
> STORED PROCEDURES which perform data manipulation.

> This was easily accomplished in 6.5 using the following syntax:

> set showplan, fmtonly on
> go

> exec procedure_name
> go

> set fmtonly, showplan off
> go

> Any other ideas?

> Bill Mallary


> >Bill,

> >I guess that there might be a better way, but if you don't mind executing
> >the query:

> >SET STATISTICS PROFILE ON
> >(Which is used by QA's: Query | Show Execution Plan.)
> >--
> >Tibor Karaszi
> >MCSE, MCSD, MCT, SQL Server MVP
> >Cornerstone Sweden AB
> >Please reply to the newsgroup only, not by email.



> >> I'm sure this question has been asked before, so at the risk of being
> >flamed
> >> mercilessly I'm going to ask it again.

> >> SQL Server 6.5's "showplan" accommodated temporary tables nicely, even
> >when
> >> used in conjuntion with "noexec."  This capability proved invaluable in
> >> debugging and tuning ill-behaved SQL, including scripts which took
> >advantage
> >> of Transact-SQL's ability to create and manage temporary tables.

> >> With 7.0, this appears to have gone away.  "Showplan" and "noexec" have
> >been
> >> effectively combined into "showplan_text" (or "showplan_all") which on
> >their
> >> surface appear to be a functional equivalent.  They're not.  Unlike in
> >6.5,
> >> temporary tables which are created as part of the SQL being analyzed
are
> >not
> >> actually created.  Subsequent referenced to said tables then fail with
a
> >> "table does not exist" message.

> >> Is there a work-around to this?  Is there something I'm missing?  Has
MS
> >> addressed this at all in this forum or on their Web site?

> >> Any guidance would be greatly appreciated.

> >> Bill Mallary


 
 
 

Showplan_text and #temp tables

Post by Gert Draper » Sat, 19 Jun 1999 04:00:00


That still works in 7.0 like

set showplan_text on
go
set fmtonly on
go


go

set fmtonly off
go
set showplan_text off
go

-GertD

No live without an edge.

 
 
 

Showplan_text and #temp tables

Post by Microsoft New » Sat, 19 Jun 1999 04:00:00


Not really.

Once the "set showplan_text on" is executed, nothing which follows will be
actually executed, including the "set fmtonly on" statement which
immediately follows.

In your example, you executed a stored procedure.  My bet is that this
stored procedure does not create and reference a temp table.  If it did,
you'd get a message similar to the following:

Msg 208, Level 16, State 0, Server SQL7_TEST, Procedure sp_who2, Line 122
Invalid object name '#tb1_sysprocesses'.

The message in the above example comes when trying to execute "sp_who2" with
"showplan_text" turned on.  What I want to do is analyze the query plans for
procedures like sp_who2 which create and reference temporary tables.  MS
documentation for showplan_text explicitly states that analysis of any SQL
which does this is not supported.  What I am looking for is a work-around or
an undocumented feature which will allow me the same functionality I had in
MS SQL-Server 6.5.

Thank you for your response, however.

Bill


>That still works in 7.0 like

>set showplan_text on
>go
>set fmtonly on
>go


>go

>set fmtonly off
>go
>set showplan_text off
>go

>-GertD

>No live without an edge.

 
 
 

1. Temp table vs Global Temp table

I think this is a very simple question, however, I don't know the
answer.  What is the
difference between a regular Temp table and a Global Temp table?  I need
to create
a temp table within an sp that all users will use.  I want the table
recreated each
time someone accesses the sp, though, because some of the same info may
need
to be inserted and I don't want any PK errors.

thanks!!
Toni Eibner

2. Finding the root of a word

3. returning temp result of temp table to ADO/ASP

4. Oracle books for Solaris

5. Difference between create table #temp and ##temp ?

6. AFTER INSERT triggers in v7.0

7. temp table problem with global temp option

8. UPDATE with multiple subqueries

9. Create table temp or select into temp.

10. Massive Updates: Temp Or Not Temp Tables?

11. temp table in temp dbspace

12. SHOWPLAN is not available. Use SHOWPLAN_TEXT or SHOWPLAN_ALL

13. ALTER TABLE on temp tables, table variables