Inside Microsoft SQL Server 2005: The Storage Engine (Solid Quality Learning)

Although the motivation behind adding row versioning to SQL Server 2005 was to maintain a version store for optimistic concurrency and to support snapshot-based isolation levels to solve the problem of data writers blocking all readers, other features can also take advantage of this data management technology. Two of these features, Multiple Active Result Sets (MARS) and online index rebuilds, are new in SQL Server 2005; the third is a new way of managing triggers, an existing feature. I discussed online index rebuilding in Chapter 7, so I won't discuss that feature again here.

Triggers and Row Versioning

Triggers have been a part of SQL Server since the earliest version, and they were the only feature in those earlier versions that offered any type of historical (or versioned) data. One special feature of triggers is the ability to access pseudo-tables called deleted and inserted. If the trigger is a DELETE trigger, the deleted table contains all the rows that were deleted by the operation that caused the trigger to fire. If the trigger is an INSERT trigger, the inserted table contains all the rows that were inserted by the operation that caused the trigger to fire. If the trigger is an UPDATE trigger, the deleted table contains the old version of all the data rows changed by the UPDATE statement that caused the trigger to fire and the inserted table contains all the new versions. Previous versions of SQL Server populated these pseudo-tables by scanning the transaction log looking for all the log records in the current transaction that changed the table to which the trigger was tied.

In SQL Server 2005, the deleted and inserted tables are materialized using row versioning. When data modification operations are performed on a table that has a relevant trigger defined, the changes to the table are versioned, regardless of whether a snapshot-based isolation level has been enabled. When the trigger needs to access the deleted table, it retrieves the data from the version store. New data, whether from an UPDATE or an INSERT, is accessible through the inserted table. When a trigger scans inserted, it looks for the most recent versions of the rows.

This short example will show you that the version store is used even if the database is not otherwise enabled for row versioning. The following code creates a copy of the HumanResources.Department table and then creates two triggers on the new Department table. All the trigger does is return a single row containing the number of rows in the version store and the size of all the row versions in the version store. I have included the statement to turn off row versioning in the AdventureWorks database to confirm that the use of the version store by triggers doesn't depend on any database option.

-- Turn off the snapshot options ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF; ALTER DATABASE AdventureWorks SET READ_COMMITTED_SNAPSHOT OFF; GO -- Make a copy of the Department table (15 rows) USE AdventureWorks SELECT * INTO Department FROM HumanResources.Department; GO -- Create two triggers, one for UPDATE and one for DELETE CREATE TRIGGER upd_Department ON Department FOR UPDATE AS SELECT count(*) AS NumRows, (sum(record_length_first_part_in_bytes) + sum(record_length_second_part_in_bytes))/8060. AS Version_store_Pages FROM sys.dm_tran_version_store; GO CREATE TRIGGER del_Department ON Department FOR DELETE AS SELECT count(*) AS NumRows, (sum(record_length_first_part_in_bytes) + sum(record_length_second_part_in_bytes))/8060. AS Version_store_Pages FROM sys.dm_tran_version_store; GO

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 Department SET ModifiedDate = getdate() WHERE DepartmentID = 11;

Now delete a single row in the Department table and notice the count of rows in the version store. There should be only one row for the deleted table.

DELETE Department WHERE DepartmentID = 12

Because tempdb is used for the version store, applications that make heavy use of triggers in SQL Server 2000 must be aware of potentially increased demands on tempdb after an upgrade to SQL Server 2005.

MARS and Row Versioning

Although MARS is a client-side feature of SQL Server 2005, its implementation relies on the version store, which is very much a server-side feature. I discussed MARS briefly earlier in this chapter as an alternative to bound connections, and I'll tell you a bit more about how this feature uses row versioning.

In earlier versions of SQL Server, database applications could not maintain multiple active statements on a connection when using default result sets. The application had to process or cancel all result sets from one batch before it could execute any other batch on that connection. With SQL Server 2005, an application can specify an attribute in the connection string to allow the application to have more than one pending request per connectionin particular, to have more than one active default result set per connection.

If two batches are submitted under a MARS connection, one of them containing a SELECT statement and the other containing an UPDATE statement, the UPDATE can begin execution before the SELECT has processed its entire result set. However, the UPDATE statement must run to completion before the SELECT statement can make progress, and all changes made by the UPDATE will be versioned. If both statements are running under the same transaction, any changes made by the UPDATE statement after the SELECT statement has started execution are not visible to the SELECT because the SELECT will access the older version of the required data rows. Let's look at an example. Table 8-17 shows two batches sent from the same application, using the same MARS-enabled connection. Most of the statements are just pseudocode because the requests are not really sent in Transact-SQL but are sent to SQL Server using the client API.

The example shows that row versioning is used for data modification operations submitted on the same connection as a currently active SELECT. The SELECT will read the versioned rows so it will have a consistent snapshot of the data as it was when it started reading. Any other connections submitted by the same application or by a completely different user will see the data as modified by Batch 2.