Tuning Advisor and Temp tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MarcG - 04 Mar 2008 14:34 GMT

I'm trying to use the tuning advisor to fix some VERY slow stored procs.
Unfortunately they contain code that creates temp tables and when I run the
analysis, it fails with the error
E000 exec cmt_common_measurements_org @quest_id=561 1 [Microsoft][SQL
Native Client][SQL Server]Invalid object name '#tt'.

I've seen references to this problem with the comment that you have to "SET
STATISTICS PROFILE ON" but I'm obviously not getting it because I can't get
past the problem.

My workload file contains the following...

exec cmt_common_measurements_org @quest_id=561
GO
exec cmt_common_measurements_org @quest_id=562
GO

Any help appreciated
Thx
Marc

Reply to this Message

Charles Wang[MSFT] - 05 Mar 2008 03:02 GMT

Hi Marc,
Are there any code snippet of using "SELECT ... INTO " in your stored
procedure?
For example:
-------------------------------------
SELECT * INTO #t1 FROM t1 WHERE 1=0
INSERT INTO #t1
-------------------------------------

If so, it is a by design product issue since currently DTA tunes statements
referencing transient tables by using the following logic:
Create Table table_name(...)
Go
SELECT * FROM table_name

I read of the comments from product team and know that currently SQL team
has not added it to the near plan for fixing it, so now I recommend that
you follow the logic of "CREATE TABLE" to replace "SELECT... INTO" if you
used it.

Please feel free to let me know if you have any other questions or
concerns. Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Reply to this Message