SQL Server Performance

Database engine tuning advisor and triggers

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by jmf, Nov 24, 2006.

  1. jmf New Member

    Hi,

    I captured a trace using profiler with the tuning template.

    Amongst the events it captures, there is the code for the triggers which contain things like 'INSERTED' etc.

    I provided this trace to the tuning advisor to get an idea of how to optimise indexes.

    The problem seems to be that the tuning advisor does not recognise "INSERTED". The error below appears on the tuning log:

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

    So I am wondering whether the triggers have been considered for the recomendations.

    Cheers

    Jose

  2. thomas New Member

    The DTA doesn't actually execute code to tune it. So when it runs, things like temp tables created in SPs and the inserted and deleted tables in triggers won't exist and it errors when trying to analyse them. The only way around this is manually to change the code to use permanent tables, etc. which is a hassle. Otherwise you can edit troublesome calls out of your workload file.

    So no, it won't have considered your trigger code.

  3. jmf New Member

    Hi Thomas,

    Thanks for your answer. You've confirmed my suspicions.

    Cheers

    Jose

Share This Page