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 DepartmentSET 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.

Let's look at what happens in a slightly different scenario. Run the code in Listing 6 to create a copy of the Production.Product table called NewProducts.The code will also create an UPDATE trigger, but this trigger won't update the table the trigger is attached to; instead, it will update the Production.Product table.

The UPDATE in Listing 7 then generates six rows in the version store. Four of the rows in the version store will be rows from the original Production.Product table because the trigger is updating a row in that table. They're basically the same four rows that show up in the version store when a simple UPDATE is done on the Production.Product table.

The additional two rows are rows from the NewProducts table. Although you're using both snapshot isolation and a trigger on NewProducts, you again see the optimization I mentioned earlier. That is, SQL Server can use the same row in the version store for both the snapshot of the data and the row in the deleted table.

Although my examples are simple, even for a simple modification that updates one row in a table, you've seen as many as six rows generated in the version store. Actually, you could have even more than six rows, if the table has indexes on any of columns being updated. If you imagine updating hundreds or thousands of rows, imagine that the version store might grow proportionally.

Mind the Store

Triggers combined with snapshot isolation could strain the version store even more than using either feature alone. Since the version store resides in tempdb, you'll need to keep a close eye on tempdb's growth. Last month I discussed which counters might be the best to use for monitoring tempdb's size and suggested that you could programmatically monitor the dynamic management view sys.dm_os_performance_counters. Many counters in the SQL Server: Transactions object provide information about tempdb in general or the version store in particular. If you want to keep even closer tabs on tempdb's growth, you could also consider proactively setting a SQL Agent alert on one or more of the counters in the SQL Server:Transactions performance object. You'd then be notified immediately if any of the performance-counter values exceeded a specified threshold.