Database engine tuning advisor and triggers | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Database engine tuning advisor and triggers

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
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
]]>