Row Versioning and Snapshot Isolation
SQL Server 2000 could maintain only one version of a row—the most recent one. SQL Server 2005 introduces a new technology that lets it maintain versions of a row at different points in time in linked lists stored in tempdb. This new technology serves several purposes: It supports the new Snapshot and Read Committed with Snapshot isolation levels (known together as Snapshot-based Isolation Levels), building the inserted and deleted tables in triggers and online index operations and supporting Multiple Active Result Sets (MARS).
The new isolation levels let SQL Server work in a mode in which shared locks aren't acquired when you read data; therefore, readers aren't blocked by writers. SQL Server returns to the readers a consistent (committed) version of the row by using row versioning. In snapshot isolation, SQL Server returns to the reader the most recent consistent version of a row according to the time the reader's transaction started (technically, this is the time at which the first statement of the transaction was submitted). Multiple reads within the same transaction are guaranteed to return the same version of the row. Snapshot isolation also detects update conflicts. If another transaction changed the data between the time you accessed a row and the time you modified it in the same transaction, upon modification your transaction will fail. The failure signals that someone else changed the row and your calculations may not be valid anymore. At this point, you can retry the transaction.
SQL Server 2005 introduces another isolation level based on row versioning that's actually an enhancement to an existing isolation level. This new isolation level is called Read Committed with Snapshot. In this isolation level, your transaction doesn't acquire shared locks when reading and gets the most recent committed version of a row according to when the current SELECT statement started (rather than when the transaction started). In this mode, multiple different reads within the same transaction can get different versions of the row (all committed). Your reads don't need to wait for modifying transactions to commit.
These new isolation levels benefit applications that principally read data because the applications won't need to wait for modifying transactions to commit. Also, you now have a simple way to enforce optimistic locking with conflict detection. But remember that the new isolation levels aren't adequate for all environments because of the cost to maintaining and traversing the linked lists with the row versions in tempdb. For example, the isolation levels won't be adequate in cases where modifications are frequent and many update conflicts exist.
Row versioning is also used in SQL Server 2005 to build the inserted and deleted tables in triggers. In SQL Server 2000, inserted and deleted are actually views on top of the section in the transaction log that contains the log records of the change that fired the trigger. By querying inserted and deleted, you gain access to the old and new images of the affected rows. Whenever you access inserted or deleted you're in effect scanning a portion of the transaction log. Even when not working with triggers, the transaction log can potentially be a bottleneck, mainly in online transaction processing (OLTP) systems. SQL Server must first write a change to the transaction log before it can apply the change to the data portion of the database. The architecture of the transaction log is such that SQL Server can only write to it sequentially. Therefore, any interference with transaction log activity (e.g., querying deleted and inserted in triggers, transaction log replication) ultimately postpones flushing changes to the data. You need to put a lot of attention and focus on the transaction log when implementing and tuning your database.
SQL Server 2005 uses row versioning to keep track of versions of rows that are affected both by the change that fired the trigger and by changes submitted from the trigger itself. SQL Server builds inserted and deleted from the linked lists with the row versions it maintains in tempdb. Unlike the transaction log, tempdb can be written to and read from in parallel if tempdb is striped on multiple disk drives. It's comforting to know that the performance tension on the transaction log is reduced, but you should also realize that you need to put more focus on tuning tempdb now.
Another very important new feature that uses row versioning technology is online index operations. All index operations in SQL Server 2000 (including create, rebuild, and drop) occur offline. It's important to rebuild indexes periodically to combat index fragmentation, which can dramatically slow ordered index range scans (i.e., range queries that use an index). When you rebuild a clustered index, SQL Server acquires an exclusive lock on the table so that the table isn't available for reading or writing. When you rebuild a non-clustered index, SQL Server acquires a shared lock on the table so that the table isn't available for writing, and the index itself can't be used during the operation. SQL Server 2000 introduced an online index defragmentation utility (DBCC INDEXDEFRAG) to address some of the problems with index rebuilds. However, this utility sometimes takes longer than an index rebuild, consumes more log resources, and the end result is not as optimal as that of a full index rebuild.
SQL Server 2005 introduces online index operations (i.e., create, rebuild, drop). I find the ability to rebuild an index online important because doing so is necessary for regular maintenance. SQL Server uses row versioning to maintain changes that take place during the rebuild activity. Logically, imagine that another copy of the index is constructed, allowing you to access the source data, then when the operation finishes, the copy substitutes the source. Keep in mind that you'll need enough database space for the operation. This enhancement is especially crucial for 24/7 shops that can't allow downtime.
Prev. page
1
[2]
3
next page 
