SQL Server Performance Forum – Threads Archive
Database engine tuning advisor and triggersHi, 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
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.
Hi Thomas, Thanks for your answer. You’ve confirmed my suspicions. Cheers Jose