次の方法で共有

Managing TempDB in SQL Server: TempDB Basics (Version Store: Why do we need it?)

  • [アーティクル]

Version store is a new entity in SQL Server 2005. It is used to store versions of data and index rows. A row version typically is an older copy of the data or an index row and is created to support existing (triggers) and new features (snapshot based isolation levels, MARS and ONLINE index build) in SQL Server 2005. Let me give you an example of row version in each of these contexts as follows

· Triggers: These operate on DELETED and INSERTED rows as part of DML operations on a table. Before SQL Server 2005, the trigger logic constructed these rows by traversing the UNDO/REDO logs. This can and does cause the disk head to move back and forth as the SQL Server will need to traverse the older records thereby compromising the IO throughput of log disk. Remember the log disk is typically expected to have sequential writes given that transactional rollbacks are not that common. Trigger implementation disrupts that an application with heavy usage of triggers can potentially cause IO bottleneck on the log disk. Starting with SQL Server 2005, the trigger implementation was changed to use row versions. So now, the DELETED and INSERTED rows are created as a row versions and a handle to these rows is attached to the transaction. You can understand that row versions take the pressure off log disk but at the expense of the TempDB.

· ONLINE Index: An index can be built ONLINE but while it is being built, there can be concurrent changes in the rows. SQL Server uses row versioning to get the consistent view of the data in the table

· MARS: It stands for multiple active result sets and SQL Server uses row versioning to implement it. It is a complex topic and I will suggest you to review the BOL but at high level SQL Server uses row versioning to provide statement level consistency to the transaction as MARS allows multiple statements within the same transaction to be active at the same time.

· Snapshot Isolation and RCSI: Row versioning is the foundation of implementing these new isolations levels. SQL Server creates versions of the modified rows so that a transaction running under these isolation levels can see a consistent view.

All features except for triggers are new in SQL Server 2005 and because triggers now use row versioning, you cannot really get around using version store once you upgrade to SQL Server 2005. I don’t mean to imply that version store is something to avoid. In fact it is a better implementation, I am sure you will agree too, of triggers and SQL Server provides good tools to monitor and troubleshoot version store. Besides, it is the foundation of many new features as described above starting with SQL Server 2005.

In my next blog, I will dig deeper into the storage, traversal and garbage collection of data in the version store.

Thanks

Sunil Agarwal

Comments