Master Row Versioning in SQL Server for RCSI

Deep Dive into Row Versioning in SQL Server with RCSI

Row versioning is the core mechanism that powers Read Committed Snapshot Isolation (RCSI) and Snapshot Isolation in SQL Server. It provides an optimistic concurrency model, allowing readers to access consistent data without blocking writers (or vice versa) by maintaining older versions of modified rows.

How Row Versioning Works

When a row is updated or deleted in a database where row versioning is active (via RCSI or Snapshot Isolation):

SQL Server copies the pre-modification (previously committed) version of the row into the version store.
The current row in the base table is updated with the new values.
A 14-byte pointer is added to the row in the data page, linking it to the previous version in the version store.
Older versions are chained together in a linked list, allowing SQL Server to traverse the chain to find the appropriate version for a query.

Read operations then retrieve the version of the row that matches the required consistency point (statement start for RCSI, transaction start for Snapshot) from this chain.

![](grok_render_searched_image_card_json={“cards”:[{“cardId”:”a9332a”,”imageId”:”0″,”size”:”LARGE”},{“cardId”:”89c83c”,”imageId”:”2″,”size”:”LARGE”}]})

These diagrams illustrate the row versioning process and version chaining under snapshot-based isolation.

The Version Store

All versioned rows are stored in a unified version store located in tempdb (prior to SQL Server 2019; with Accelerated Database Recovery in 2019+, versions can be in the user database, but tempdb is still used in many cases).

Every update generates a new version record in the version store.
Inserts typically do not generate versions (no “old” row), unless triggers are involved.
The version store is shared across all databases on the instance that use row versioning.

![](grok_render_searched_image_card_json={“cards”:[{“cardId”:”b7b546″,”imageId”:”5″,”size”:”LARGE”},{“cardId”:”459c8a”,”imageId”:”6″,”size”:”LARGE”}]})

These visuals show how the version store operates within tempdb and tracks space usage.

Differences in Version Retention: RCSI vs. Snapshot Isolation

Aspect	RCSI (READ COMMITTED with Snapshot)	Snapshot Isolation
Consistency Level	Statement-level	Transaction-level
Version Cleanup	Versions removed after each statement completes	Versions retained until the entire transaction completes
Tempdb Pressure	Generally lower (shorter retention)	Higher, especially with long-running transactions
Blocking Reduction	Eliminates reader-writer blocking	Eliminates reader-writer blocking; detects update conflicts

Long-running transactions or queries can cause version store bloat in tempdb, leading to growth and potential performance issues. SQL Server runs automatic cleanup tasks, but versions are only removed when no longer needed by any active query.

Overhead and Monitoring

Benefits: Dramatically reduces blocking and deadlocks in read-heavy or contended workloads.
Costs: Increased tempdb I/O and space usage; extra CPU for version management; potential for long version chains slowing reads.
Monitor with DMVs like sys.dm_tran_version_store_space_usage and sys.dm_db_file_space_usage in tempdb.

Row versioning transforms pessimistic locking into an optimistic model, making RCSI one of the most popular concurrency enhancements in SQL Server for OLTP systems.

Have questions about implementing or troubleshooting row versioning? Feel free to ask!