Rowlevel 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 technologys 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 Isolation Level 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-level versioning 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_SNAPSH
  OT 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-level versioning 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\MSSQ
  L\Data\msdbdata.mdf"

23
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_transactions
  Returns 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 _at_ 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

34
(No Transcript)
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