How triggers are implemented in SQL 2005/2008?

Triggers operate on DELETED and INSERTED rows as part of DML operations on a table.

Before SQL Server 2005, the trigger logic constructed these rows by traversing the UNDO/REDO logs. This may cause the disk head to move back and forth as the SQL Server will need to traverse the older records thereby compromising the IO throughput of log disk. Log disk is typically expected to have sequential writes given that transactional rollbacks are not that common. Trigger implementation disrupts that an application with heavy usage of triggers can potentially cause IO bottleneck on the log disk.

Starting with SQL Server 2005, the trigger implementation was changed to use row versions. So now, the DELETED and INSERTED rows are created as a row versions and a handle to these rows is attached to the transaction.
Row versions take the pressure off log disk but at the expense of the TempDB.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s