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:

SELECT VersionStoreSize_KB = fs.version_store_reserved_page_count*8
FROM sys.dm_db_file_space_usage fs

1 2	SELECT VersionStoreSize_KB = fs.version_store_reserved_page_count*8 FROM sys.dm_db_file_space_usage fs

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:

-- RCSI and BLOB data - how tempdb version store is used
-- Vedran Kesegic 2015

create database BlobTestDb;
GO
alter database BlobTestDb set READ_COMMITTED_SNAPSHOT ON;
use BlobTestDb;
GO

-- Create table with BLOB data
create table dbo.TestBlob
(    Id int identity primary key,
    BlobValue nvarchar(MAX) DEFAULT (REPLICATE(CONVERT(NVARCHAR(MAX), 'Huge!'), 200000)) -- about 2MB
)
GO

-- version store allocation: 0
SELECT VersionStoreSize_KB = fs.version_store_reserved_page_count*8
FROM sys.dm_db_file_space_usage fs

insert into TestBlob(BlobValue) values(REPLICATE(CONVERT(NVARCHAR(MAX), 'Huge!'), 50000)) -- about 500KB

-- version store allocation: 0, because INSERT DOES NOT get copied into tempdb!
SELECT VersionStoreSize_KB = fs.version_store_reserved_page_count*8
FROM sys.dm_db_file_space_usage fs
GO

update TestBlob set BlobValue = 'nothing'
output DATALENGTH(deleted.BlobValue)/1000 as OldBlob_KB, -- 500 KB
    DATALENGTH(inserted.BlobValue)/1000 as NewBlob_KB -- 0 KB

-- version store allocation: 1024 KB (double of old value!)
SELECT VersionStoreSize_KB = fs.version_store_reserved_page_count*8
FROM sys.dm_db_file_space_usage fs
GO

update TestBlob set BlobValue = DEFAULT
output DATALENGTH(deleted.BlobValue)/1000 as OldBlob_KB, -- 0 KB
    DATALENGTH(inserted.BlobValue)/1000 as NewBlob_KB -- 2000 KB

-- version store allocation: 1088 KB (not yet cleaned up!)
SELECT VersionStoreSize_KB = fs.version_store_reserved_page_count*8
FROM sys.dm_db_file_space_usage fs
GO

update TestBlob set BlobValue = 'nothing'
output DATALENGTH(deleted.BlobValue)/1000 as OldBlob_KB, -- 2000 KB
    DATALENGTH(inserted.BlobValue)/1000 as NewBlob_KB -- 0 KB

-- version store allocation: 4992 KB (double of old value  + 1000 from previus not cleaned versions)
SELECT VersionStoreSize_KB = fs.version_store_reserved_page_count*8
FROM sys.dm_db_file_space_usage fs

-- Wait some time...

-- Zaro again! (Version store cleanup occured)
SELECT VersionStoreSize_KB = fs.version_store_reserved_page_count*8
FROM sys.dm_db_file_space_usage fs

-- Cleanup
use master;
drop database BlobTestDb
--drop table dbo.TestBlob

-- RCSI and BLOB data - how tempdb version store is used

-- Vedran Kesegic 2015

create database BlobTestDb;

alter database BlobTestDb set READ_COMMITTED_SNAPSHOT ON;

use BlobTestDb;

-- Create table with BLOB data

create table dbo.TestBlob

( Id int identity primary key,

BlobValue nvarchar(MAX) DEFAULT (REPLICATE(CONVERT(NVARCHAR(MAX), 'Huge!'), 200000)) -- about 2MB

)

-- version store allocation: 0