As far as cutting down the number of rows processed, that is effectively what I am doing with...

WHERE PoliID IN (SELECT PoliID FROM Inserted)

...(PoliID is the Primary Key field) I have tried using an INNER JOIN on the ‘Inserted’ table instead but it causes the trigger processing to take even longer for some reason. The following statement is also present at the top of the trigger…

IF @@ROWCOUNT = 0 RETURN

…which I assume will abort running of the trigger if no rows at all were updated.

The tblPoliPolicy table has a Primary Key on the PoliID field (non-clustered – the clustered index is on another field). There are also another 24 non-clustered indexes on this table. I have done a DBCC REINDEX on the table but it hasn’t improved anything. The table has over 200 columns and I am beginning to wonder if a table structure this huge is somehow affecting the processing of trigger code. My personal opinion/preference would be to split this table down into smaller one-to-one tables but the table has existed in this state for a long time (before I even joined the company) and is referenced from a whole load of places so splitting it up would be a mammoth task, probably a project in it’s own right. There doesn't appear to be any other performance issues with a table this size - it was only after trigger code was added that the performance seriously declined.

As I am now constrained by timescales and pressure from the powers above to get the performance issue sorted quickly, I have started stripping out the trigger code and putting it in all the stored procedures that perform inserts/updates on the tblPoliPolicy table. I know this probably isn’t the best solution but by doing so I have already noticed a dramatic improvement. A single update to the tblPoliPolicy code with ALL the original trigger code implemented used to take over 3 seconds. With two thirds of the trigger code now stripped out and put into stored procedures the update time is already down to under 1 second, and there is still trigger code left to remove. I only intend this to be a short-term fix so when I have got the performance of the live system back to a usable state, I will then try and do some more investigation.

In the meantime I appreciate the help that everyone has tried to give me with this problem.