Row-level Versioning in SQL Server 2005

1. Row-level Versioning in SQL Server 2005 Tom Kopcik MCSD, MCDBA, MCITP, MCT Software Engineer/Trainer PTSI

2. Agenda • Row-level versioning overview • Row-level versioning internals • Snapshot Isolation • Tempdb and performance considerations • Triggers under row-level versioning • Other features using row-level versioning • Database Snapshots

3. Agenda • Row-level versioning overview • Row-level versioning internals • Snapshot Isolation • Tempdb and performance considerations • Triggers under row-level versioning • Other features using row-level versioning • Database Snapshots

4. Row-level versioning - Overview • Row-level versioning allows SQL Server 2005 to simultaneously maintain multiple versions of a given row. • The technology’s fundamental purpose is to provide another isolation level that allows a reader to get to a consistent set of data as of a point in time (i.e. snapshot). • Older versions of a row are maintained in a Version Store located in the tempdb database.

5. Features Using Row-level Versioning • Snapshot Isolation • Triggers • Multiple Active Result Sets (MARS) • On-line Index Operations (Enterprise Edition only)

6. Isolation Levels • Concurrency is the ability for multiple processes to access or change data simultaneously. • Consistency ensures that the data system will allow only a logical state of the data. • An IsolationLevel defines a balance between concurrency and consistency.

7. Isolation Levels – SQL Server 2000 • Read Uncommitted • Dirty Read • Read Committed (default) • Non-repeatable Read • Repeatable Read • Phantom Read • Serializable • Transaction Independence

8. New Isolation Levels – SQL Server 2005 • Snapshot Isolation Levels • Read Committed Snapshot Isolation (RCSI) • Snapshot Isolation (SI) • Both provide data consistency at a point in time: • RCSI at the start of a SQL statement • SI at the first data access in a transaction. • Both use row-level versioning information maintained in the Version Store to provide the consistency

9. Agenda • Row-level Versioning Overview • Row-level versioning internals • Snapshot Isolation • Triggers under row-level versioning • Other features using row-level versioning • Tempdb and performance considerations • Database snapshots

10. Row Version Storage • The most current version of a row is maintained in the normal data pages utilized to store table data. • Older versions of a row are maintained in a Version Store in tempdb. • The new row contains a pointer to the previous version; older rows in the version store may have pointers to even older versions; thus all the required versions are maintained in a linked list.

11. Linked List of Versions

12. Row Version Storage Management • The row versioning behavior is automatic for all updates in a database where snapshot isolation is enabled. This overhead is incurred even if there are no readers of the data. • The Version Store is managed automatically by SQL Server; a cleanup thread that runs periodically deletes row versions no longer required by any reader.

13. Versioning Metadata • Each row in a database in which snapshot-based isolation is enabled will have an additional 14 bytes of metadata added. • When a row is updated, the new row is stamped with a 6- byte transaction sequence number (XSN). The XSN is a monotonically increasing number that is unique within a SQL server instance. • An 8-byte pointer to the previous row version completes this metadata. • Additionally one of the bits in the first byte of each data row (TagA byte) is turned on to indicate that this row has versioning.

14. Versioning Metadata • When a snapshot isolation retrieves a record from the version store, it looks for a record with a XSN less than, but closest to the XSN of the statement requesting the data.

15. Agenda • Row-level Versioning Overview • Row-levelversioning internals • Snapshot Isolation • Tempdb and performance considerations • Triggers under row-level versioning • Other features using row-level versioning • Database snapshots

16. Snapshot Isolation • Snapshot Isolation Levels • Read Committed Snapshot Isolation (RCSI) • Snapshot Isolation (SI) • Both are disabled by default; both must be enabled at the database level: ALTER DATABASE VersionTest SET READ_COMMITTED_SNAPSHOT ON ALTER DATABASE VersionTest SET ALLOW_SNAPSHOT_ISOLATION ON

17. Read Committed Snapshot Isolation (RCSI) • The RCSI is invoked automatically on any connection that is utilizing Read Committed isolation; it is not explicitly set at the session level. • Provides a consistent set of data as of the point in time at which the read statement starts executing. • RCSI essentially provides a ‘non-blocking Read Committed isolation’. It does not honor exclusive locks, rather it traverses the version store to find the state of the row at the beginning of the statement. • RCSI does not provide a repeatable read • Write statements are unaffected, they will honor exclusive locks; RCSI can not result in update conflicts.

18. Snapshot Isolation (SI) • SI must be set at the Session level SET TRANSACTION ISOLATION LEVEL SNAPSHOT • Provides a consistent set of data as of the point in time at which a transaction first accessed data; note that it is not the point in time when the transaction was created • SI essentially provides a ‘non-blocking Serializable’ isolation. It does not honor exclusive locks, rather it traverses the version store to find the state of the row at the beginning of the transaction. • Write statements are affected, they will honor exclusive locks, but if a conflicting update occurs they will terminate with SQL error 3960

19. Demonstration • Read Committed Snapshot Isolation (RCSI) • Snapshot Isolation (SI) • Concurrency Errors

20. Agenda • Row-level Versioning Overview • Row-levelversioning internals • Snapshot Isolation • Tempdb and performance considerations • Triggers under row-level versioning • Other features using row-level versioning • Database snapshots

21. Version Store • Remember that maintaining versions in the version store is automatic if either form of Snapshot Isolation is enabled. • Versions are maintained even if no reader requires them. • Additional features such as Triggers, MARS, and Online Index operations will automatically invoke row-versioning as required for the context of the operation.

22. tempdb • The Version Store is maintained in the tempdb database • tempdb in general is used by SQL server for temporary storage. Temporary tables, cursors and large sort operations have always utilized space in tempdb. • Row-level versioning puts additional demands on tempdb and it can easily become a processing bottleneck. • The single most important consideration is locating the tempdb files on a data storage device with appropriate speed and throughput. • Note that the default installation location is C:\Program Files\Microsoft SQL Server\MSSQL.x\MSSQL\Data\msdbdata.mdf"

23. FileA Filegroup FileB FileC Disk Controller FileD FileE Disk Controller FileF FileG Disk Controller FileH Disk Controller Transaction Log Operating System Transaction Log Optimizing the Database Using Filegroups with Hardware-based RAID

24. Tempdb & Version Store Performance Counters • Free Space in tempDB • Version Store Size • Version Generation Rate • Version Cleanup Rate • Update Conflict rate • Longest Transaction Running Time • Snapshot Transactions

25. Snapshot Transaction Metadata • sys.dm_tran_version_store - Returns a virtual table that displays all the records in the version store. • sys.dm_tran_current_transaction - Returns a single row that displays the state information of the active transaction in the current session. • sys.dm_tran_transactions_snapshot When a snapshot transaction starts, the Database Engine records all of the transactions that are active at that time; this DMV reports this information for all currently active snapshot transactions. • sys.dm_tran_active_snapshot_database_transactionsReturns a virtual table for all active transactions in snapshot-enabled databases under the SQL Server instance

26. Demonstration • Snapshot Data Management Views

27. Agenda • Row-level versioning overview • Row-level versioning internals • Snapshot Isolation • Tempdb and performance considerations • Triggers under row-level versioning • Other features using row-level versioning • Database Snapshots

28. Triggers - Overview • A trigger is a T-SQL procedure that executes when an action occurs on a database table: • INSERT • DELETE • UPDATE • Triggers use two pseudo-tables to provide information about the triggering event to the procedure • inserted • deleted

29. Pseudo-table Generation • In SQL Server 2000 and prior, the trigger pseudo-tables are populated by scanning the transaction log for all records affected by the triggering statement • In SQL Server 2005, the pseudo-tables are materialized using row versioning. • Regardless of whether snapshot isolation has been enabled, changes to a table with a relevant trigger are versioned. • Again, this is a potential problem if you use triggers and do not have the tempdb on a high performance data store

30. Demonstration • Triggers

31. Agenda • Row-level versioning overview • Row-level versioning internals • Snapshot Isolation • Tempdb and performance considerations • Triggers under row-level versioning • Other features using row-level versioning • Database Snapshots

32. Multiple Active Result Sets (MARS) • MARS allows you to have multiple active queries in the same connection • Example – A DataReader reading through a table interleaved with Update Commands on select records from the same table. • MARS is defined in terms of interleaving, not in terms of parallel execution • Any data modifications made on a MARS connection are versioned using the Version Store, and are not visible to readers until the transaction is committed. • SQL Server 2005 Demo @ Engine\DataAccess\ADO\MARS

33. Online Index Operation • Normally during a index rebuild (not a reorg), an index is unavailable for use by queries (it is off-line). • SQL Server 2005 Enterprise Edition can optionally perform online rebuilds (WITH ONLINE = ON). • Online index operations work by maintaining two copies of the index simultaneously • The original (Source) – all reading is performed from the Source, modifications are applied to the Source and are versioned • The new one (Target) is used initially only to write changes made while the rebuild is going on. • Online index operations are an availability feature; they typically take longer than offline operation

35. Agenda • Row-level versioning overview • Row-level versioning internals • Snapshot Isolation • Tempdb and performance considerations • Triggers under row-level versioning • Other features using row-level versioning • Database Snapshots

36. Database Snapshots • Database Snapshots are an Enterprise Edition only feature that allows creation of a point-in-time read-only copy of a database. CREATE DATABASE AdventureWorks_Snapshot ON (NAME = N'AdventureWorks_Data‘ ,FILENAME = 'C:\AW_snapshot.mdf' ) AS SNAPSHOT OF AdventureWorks

37. Database Snapshots • Database Snapshots do not use the row-versioning infrastructure. • They use a page-level copy-on-write operation. • A bitmap maintained in cache is used that indicates if a specific page has been copied to the snapshot.

38. Demonstration (Optional) • Database Snapshots

39. Summary • In most situations Read Committed Snapshot Isolation (RCSI) is recommended over Snapshot Isolation (SI). • Consider using SI only for long-running multi-statement queries that must have point-in-time consistency • Be very conscious of performance implications on tempdb with row-level versioning. • Be aware that row versioning may be occurring in your database even if you do not enable snapshot isolation per se, because a number of features (triggers, MARS, online index operations) use it inherently.

40. Questions ?

41. Row-level Versioning in SQL Server 2005 Tom Kopcik MCSD, MCDBA, MCITP, MCT Software Engineer/Trainer PTSI