Very Computer

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

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

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

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

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.

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.

Very Computer

Showplan_text and #temp tables

Showplan_text and #temp tables

Showplan_text and #temp tables

Showplan_text and #temp tables

Showplan_text and #temp tables

Showplan_text and #temp tables

Showplan_text and #temp tables