RCSI and LOB data – how tempdb version store is used?

Row Version Store

We know RCSI (Read Committed Snapshot Isolation), as well as SI (Snapshot Isolation) use row versioning to store previous verions of rows in part of tempdb called “row version store”. If you are using default isolation level RC (Read Committed) and think that you are not using row versioning and version store at all – you are wrong! From SQL 2005 version store is used to store previous row versions for tables with triggers (“deleted” special pseudo-table rows are there) and online index (re)builds. Row versions which are not needed anymore are cleaned up periodically from tempdb.

Query the space version store uses within the tempdb:

 LOB (BLOB, CLOB) and row versioning

The question arised from my colleague SQL Master Denny Cherry (@mrdenny), are also BLOBs previous versions stored in tempdb? By LOB we think of large data types stored in separate allocation unit called LOB_DATA. For example “MAX” types (VARCHAR(MAX), VARBINARY(MAX) NVARCHAR(MAX)), XML, plus old deprecated types like TEXT and IMAGE. The short answer is: yes, old versions of LOBs are also stored in row version store! Let us prove it:

 Summary

SQL Server uses version store in tempdb to store old version of the row, not just IN_ROW_DATA, but also LOB_DATA and ROW_OVERFLOW_DATA allocation units. It is used for DELETE and UPDATE statements, but NOT for INSERT (since there is no “old” row to store when you insert a new one). Strangely, version store allocates twice the size of old (deleted/updated) version of the row. Therefore, beware of UPDATE and DELETEs of the LOBs in RCSI isolation, they might take a huge amount of version store space!

2 comments on “RCSI and LOB data – how tempdb version store is used?
  1. ManishKumar1980 says:

    In my case it is always showing NULL for space usage dm_db_file_space_usage sql server 2012.

    • Vedran Vedran says:

      Have you turned on READ_COMMITTED_SNAPSHOT (RCSI) for the current database?

      select d.name, d.compatibility_level,
      d.snapshot_isolation_state_desc, d.is_read_committed_snapshot_on
      from sys.databases d
      where database_id=DB_ID()

      Try running the complete script in one go, without any changes.

Leave a Reply

Your email address will not be published. Required fields are marked *

*