Triggers and the Version Store

Triggers are a long-standing feature of SQL Server, but in SQL Server 2005 Microsoft has redesigned triggers so that their internal details are much different than before. As you learned last month in "The Hidden Costs of Row-Level Versioning," InstantDoc ID 93465, in SQL Server 2005 triggers use a new technology called row-level versioning (RLV). Inside the trigger code, you can still access two pseudo-tables, called inserted and deleted. In SQL Server 2005, the old and new versions of changed data, which are viewable in the pseudo-tables, are actually managed by using RLV. Last month I showed you some examples of triggers in a database in which snapshot isolation wasn't enabled, to illustrate the fact that the version store contained rows processed by my triggers. I stressed the point that DBAs need to manage the version store in tempdb even if they don't use snapshot isolation.This month, I continue to examine the impact of triggers on the version store, but this time we'll look at a database that also has snapshot isolation enabled. (I discuss only AFTER triggers here. SQL Server 2005 doesn't use the version store to handle the inserted and deleted tables for INSTEAD OF triggers.)

Triggers and the Version Store
To examine the number of rows in the version store, run this simple query:

SELECT count(*) AS NumRows FROM sys.dm_tran_version_store 

Run the code in Listing 1 to re-create the Department table that I used last month.(Some of the lines in this and other code in this article wrap because of space constraints.) This time,the script doesn't build any triggers on the table, but it does enable snapshot isolation.

Now run the following UPDATE, and examine the number of rows in the version store:

UPDATE Department
SET ModifiedDate =
  getdate()
WHERE DepartmentID = 11; 

Last month, when you ran this UPDATE statement in a database with an UPDATE trigger and no snapshot isolation, you saw two rows in the version store. This time, you should see only one row. Only the previous version of the row for department 11 needs to be stored, so that other connections querying this table via snapshot isolation can use that row version.

If you run the DELETE statement in last month's article, and more than one minute has passed since you ran any other statement that added rows to the version store, you'll see one row in the version store.

Triggers and Snapshot Isolation
Now let's create triggers and see what happens when you use both triggers and snapshot isolation. Run the code in Listing 2 to build the same triggers as we had last month, which only report on the version-store size. After creating the triggers, execute the UPDATE and DELETE statements in Listing 3.

Notice that the number of rows isn't cumulative. There were two rows for the UPDATE in the version store when you used triggers and no snapshot isolation and one row after running the UPDATE with snapshot isolation and no triggers. But when you use both triggers and snapshot isolation, there are only two rows. For the DELETE, only one additional row is needed in the version store. Although SQL Server uses the rows in the version store for triggers and those for snapshot isolation for different purposes, SQL Server doesn't duplicate the information.The UPDATE trigger and snapshot transactions can both use one version-store row for the previous version of the department 7 row. Similarly, when we perform the DELETE, only one versioned row is needed, which both snapshot transactions and the DELETE trigger can use.

UPDATE Triggers and the Version Store
Now let's get a little fancier and look at a trigger that actually makes some changes. The Production.Product table in the AdventureWorks database has an UPDATE trigger on it called uProduct. (You can determine the trigger's name by running

EXEC sp_helptrigger
  'Production.Product' 

You'll see the name uProduct.Then run

sp_helptext 'Production.uProduct' 

to see the text of the trigger.) The trigger executes the UPDATE in Listing 4 to update the ModifiedDate column to reflect when the change was made.The UPDATE statement in Listing 5, which changes just one row in the Production. Product table, generates four rows in the version store: one versioned row for the initial update; one versioned row for the update inside the trigger; and two rows for the inserted and deleted table, for the update inside the trigger.

You might think that SQL Server could further reduce the rows in the version store because the versioned row for the initial update would have the same information as the row for the deleted table inside the trigger. However, at this time, if a trigger on a table updates the same table, SQL Server maintains separate rows for versioning and for the trigger's rows.

Also note that although the row in the Production.Product table was updated twice, there are only one inserted and one deleted row since this database isn't enabled for recursive triggers. The UPDATE trigger on Production.Product won't fire a second time.