Database engine tunning advisor and triggers

Hi,

I captured a trace using the profiler and provided the trace to the tunning advisor for analysis of the indexes.

I am not convinced that it processes the triggers properly. I used the tunning template on the profiler and confirmed that the code from the triggers is captured.

However, in the tunning advisor I am getting an error message like this:

E000

INSERT INTO SP ( FkSID, FkRPID, FkSPStID, NPR )
SELECT SID, RPID, 0, NPR
FROM INSERTED INNER JOIN RP ON
INSERTED.FkCID = RP.FkCID
WHERE 0 = INSERTED.IA

2 [Microsoft][SQL Native Client][SQL Server]Invalid object name 'INSERTED'.

From the above it appears that the advisor does not recognise the "inserted" temp table used in an insert trigger. Is there anyway to have the advisor consider this code as well or am I doing something wrong?

Thanks

Jose Fortuna

DTA should suppose to identify them as a trigger based tables, anyway as a test have you tried another trace capture and try the DTA again. If this persists again then use Connect page at microsoft to report the bug.|||

Hi Satya,

Are you saying that DTA should handle such cases?

Jose

|||

Yes, it should.

Edit:

After I replied here I have checked the same again and it seems this is 'not' working me either and I think due to the fact INSERTED or DELETED tables are temp. tables when a trigger is initiated. When DTA is checking for such values using the above reference it is unable to find the referred tables. So I would expect the behaviour is by default, to get a resolution I believe you need to assign to a permanent tables on trigger.

HTH

|||

Hi,

Further to the above, if you include in the code the creation of the trigger than all is correct, that is, the inserted temp table is recognised.

Since the triggers are an important aspect of database performance, it is a real shame that these cannot be analised through a profiler capture.

Thanks for the help provided here.

Jose