Row-level versioning (RLV) is a powerful new technology in SQL Server 2005 that supports concurrent access to data being modified. But using RLV increases the demand on your tempdb database. I'll show you how you can mitigate RLV's costs by monitoring the use of tempdb using some new tools included in SQL Server 2005. But before I do that, let's look at some hidden uses of RLV that can require increased monitoring of tempdb and costs that you might be incurring even if you've just upgraded a database from SQL Server 2000 and don't use any of the new SQL Server 2005 features. (For more information about RLV, see "Keeping Concurrent,"October 2005,InstantDoc ID 47389 and "RLV with a View," February 2006, InstantDoc ID 48721.)
Triggers and RLV
Triggers have long been a part of SQL Server and were the only feature prior to SQL Server 2005 that provided any type of historical (or versioned) data. Triggers can access two pseudo-tables called deleted and inserted. Inside the trigger, you can access these two tables as if they were real tables, but accessing them while not in a trigger results in an unknown object error. If the trigger is a DELETE trigger, the deleted table contains copies of all the rows deleted by the operation that caused the trigger to fire. If the trigger is an INSERT trigger, the inserted table contains copies of all the rows inserted by the operation that caused the trigger to fire. And if the trigger is an UPDATE trigger, the deleted table contains copies of the old versions of the rows, and the inserted table contains all the new versions. Before SQL Server 2005, SQL Server would determine which rows were included in these pseudo-tables by scanning the transaction log for all the log records belonging to the current transaction. Any log records containing data inserted in or deleted from the table to which the trigger was tied were included in the inserted or deleted tables.
In SQL Server 2005, these pseudo-tables are created by using RLV technology. When data-modification operations are performed on a table that has a relevant trigger defined, SQL Server creates versions of the old and new data in the version store in tempdb.This occurs whether or not either of the snapshot-based isolation levels has been enabled.When a SQL Server 2005 trigger accesses the deleted table, it retrieves the data from the version store.When a trigger needs to determine which rows in the table are new rows and accesses the inserted table, SQL Server again gets the inserted table rows from the version store.
The organization of the inserted and deleted rows in the version store is a bit different than it is for rows versioned by one of the snapshot-isolation levels. As I mentioned in "Keeping Concurrent," when SQL Server determines that row versioning is needed, it adds 14 bytes to each row involved in the versioning. These 14 bytes are used as a row pointer.The row in the actual data page can point to a row in the version store, and a row in the version store can point to another row. With snapshot isolation, each row points to the next older version of the same row, and the row with a NULL for the pointer is the earliest one. With triggers, the pointer works a bit differently. Each modified row in the base table points to the deleted version of that row, but each deleted row is linked to another deleted row so that all the rows in the deleted table are linked together. Similarly, all the rows in the inserted table are linked together.
We can verify that the version store is used, even if the database isn't otherwise enabled for row versioning.The code in Listing 1 creates a copy of the HumanResources.Department table in the AdventureWorks database, then creates two triggers on the new table.(Some of the code wraps to multiple lines because of space constraints in print.)The trigger simply returns a single row containing the number of rows and the size of all the row versions in the version store. The script includes the statements to turn off row versioning in the AdventureWorks database to confirm that the triggers' use of version store doesn't depend on any database option.
Now update a single row in the Department table and notice the count of rows in the version store. There should be one row for the inserted table and one row for the deleted table.
UPDATE dbo.Department
SET ModifiedDate = getdate()
WHERE DepartmentID = 11;
Next, delete a single row in the Department table and notice the count of rows in the version store.
DELETE dbo.Department
WHERE DepartmentID = 12
There should be only one row for the deleted table. If your version cleanup thread didn't come through before executing this DELETE, you should see a total of three rows in the version store, with two still remaining for the previous UPDATE and one for the new DELETE.
Prev. page
[1]
2
next page 
