Performance

Remember that for every trigger, since we execute more code than for a simple insertion, keep trigger code minimal. Triggers are also ‘hidden’ from a developer, as it is not immediately visible that a table has a trigger on it, unless the developer knows the system, or there is good documentation around. They may create a new process that refreshes the data every night and may not immediately realize this slows down the process.

For example, if we have a number of records to load from a data repository, a stored procedure or DTS process loads the data into the main table that has an audit trigger on it. This trigger modifies the audit table with the notified changes. The overall process will slow from a straightforward load due to the trigger, the extra insertion, and any other work SQL Server has to index on the audit table. We may try removing the trigger itself and then updating the audit table as a separate batch process later in the overall cycle, if possible.

Figure 2

Deferred Updates and Direct Updates

While modifying rows in a table, SQL Server can employ any of the two different methods – direct update or deferred update.

As the name suggests, a direct update will modify the table directly with the data that is already there. Once this is done, the transaction log is then created. Every modification to a row in the modified table will then create a record of the row in the transaction log, and the altered column values of the underlying table will simply have their information overwritten in the transaction log.

In a deferred update, the row to be modified is placed into the transaction log as a delete, and then an insertion with the modifications is placed in the transaction log. The row is then deleted from the table, and then reinserted into the table with the new information, with the last two actions taking the information from the log. This is obviously slower than the first method as we have to re-read the data from the transaction log, as well as removing and inserting whole rows of data.

As soon as we place a trigger on a table, we force SQL Server to use the deferred update method of table modification. Therefore, although we may find that the code within the trigger itself is very quick, we will degrade performance of the data modification by placing the trigger.