We use a set of three triggers to Audit all Insert, Update and Delete activity on every table in our database. We have a piece of SQL that auto generates trigger create scripts for each table automatically. This means that every time we make a structural alteration to a table we must re-run the script for that table and update the triggers as part of the release.

In the tables with lots of columns we noticed poor performance during updates, looking in Query Analyser at the Execution Plan revealed that for each update there were multiple scans of the Inserted and the Deleted pseudo tables.

This was happening because the Update Audit trigger had an Insert statement in it for each column in the table. The Insert statement caused the scan of the Inserted and Deleted pseudo tables. I remembered reading in the books online that you can use IF UPDATE (column) to test if a column has been updated, by placing this statement around each Insert statement we were able to greatly increase the speed with which the trigger operates, because the Inserted/Deleted scans were only happening for the columns that had been updated.

I dedicate this blog post to Kimberly Tripp's interview on .Net Rocks, it was a great show full of invaluable SQL Server performance tuning advice.