Understand Version Store better

Context

This article discusses when version store starts generating rows, when cleanup takes place, and how TempDB space gets occupied due to this activity.

I’ve hardly used PerfMon much in the past, nor was I a fan of it, just like many of you. But, when I used it a few times to determine how many data files needed to be added to TempDB while resolving contention, it helped me make a clear decision on the number of files needed. Since then, I’ve become a fan of PerfMon.
Although I don’t use it daily, I don’t hesitate to turn to it when I think it might help me understand things better.

Coming back to our topic of version store in this article, I resorted to PerfMon counters because visually watching the lines go up and down when an update is performed on a table helped me better understand how the version generation and cleanup works.

Demo

Update will be run on table dbo.t1 on database DBATest, which has Read Committed Snapshot Isolation(RCSI) enabled.

1. Let’s get an idea of TempDB file size and version store size, before kicking off the update operation.
   Result: Space used in TempDB = 3.68 MB,
   Space used by version store = 0 MB

   /*Space consumed in TempDB, by version store of each database*/
   USE DBATest
   SELECT DB_NAME(database_id) AS 'Database Name'
   	,reserved_space_kb / 1024.0 AS 'Space(MB) used in tempdb for version store'
   FROM sys.dm_tran_version_store_space_usage
   WHERE reserved_space_kb > 0
   ORDER BY 1;
   
   /*TempDB Total Files Size*/
   USE tempdb;
   SELECT 
       DB_NAME() AS DatabaseName,
       SUM(CASE WHEN type_desc = 'ROWS' THEN size ELSE 0 END) / 128.0 AS TotalDataFilesSizeInMB,
   	SUM(CASE WHEN type_desc = 'ROWS' THEN FILEPROPERTY(name, 'SpaceUsed') ELSE 0 END)/128.0 AS TotalDataSpaceUsedInMB,
   	SUM(CASE WHEN type_desc = 'ROWS' THEN size  - FILEPROPERTY(name, 'SpaceUsed') ELSE 0 END)/128.0 AS TotalFreeSpaceInMB,
       SUM(CASE WHEN type_desc = 'LOG' THEN size ELSE 0 END) / 128.0 AS TotalLogFileSizeInMB,
   	SUM(CASE WHEN type_desc = 'LOG' THEN FILEPROPERTY(name, 'SpaceUsed') ELSE 0 END)/128.0 AS TotalLogSpaceUsedInMB
   FROM sys.database_files;

   

Version Store Generation - PerfMon

1. Open up PerfMon and add following counters and then hit Run -

   1. Version Cleanup Rate

   2. Version Generation Rate

   3. Version Store size

   4. Free space in TempDB

2. Run an update on dbo.t1

update dbo.t1 set b='b_1';

Let’s take a look at what we observe in PerfMon, as the update started to run at point A and finished at point B.

• Yellow line - Versioned rows started generating at point A, when the update started to run and then ended, as the update finished at point B.

• Green line - Version store size started increasing at point A, as it contains the versioned rows in it. Then, when update finished at point B, version store size doesn’t grow anymore and continues to remain the same size, until clean up happens.

• Brown line - TempDB data files size increase, since versioned rows or version store take up space in TempDB.

• Red line - Version cleanup does not happen yet, so it is a zero flat line.

In between point A and B while the update is running, if there is a concurrent read operation on this table, then it will retrieve the rows from version store in TempDB, without being blocked waiting for the update to finish on the table.

Version Store Generation - SSMS

Simultaneously, lets also check above counters from SSMS:

/*Space consumed in TempDB, by version store of each database*/
USE DBATest
SELECT DB_NAME(database_id) AS 'Database Name'
	,reserved_space_kb / 1024.0 AS 'Space(MB) used in tempdb for version store'
FROM sys.dm_tran_version_store_space_usage
WHERE reserved_space_kb > 0
ORDER BY 1;

/*TempDB Total Files Size*/
USE tempdb;
SELECT 
    DB_NAME() AS DatabaseName,
    SUM(CASE WHEN type_desc = 'ROWS' THEN size ELSE 0 END) / 128.0 AS TotalDataFilesSizeInMB,
	SUM(CASE WHEN type_desc = 'ROWS' THEN FILEPROPERTY(name, 'SpaceUsed') ELSE 0 END)/128.0 AS TotalDataSpaceUsedInMB,
	SUM(CASE WHEN type_desc = 'ROWS' THEN size  - FILEPROPERTY(name, 'SpaceUsed') ELSE 0 END)/128.0 AS TotalFreeSpaceInMB,
    SUM(CASE WHEN type_desc = 'LOG' THEN size ELSE 0 END) / 128.0 AS TotalLogFileSizeInMB,
	SUM(CASE WHEN type_desc = 'LOG' THEN FILEPROPERTY(name, 'SpaceUsed') ELSE 0 END)/128.0 AS TotalLogSpaceUsedInMB
FROM sys.database_files;

• Change in version store size is from 0 to 105.5 MB(matching with green line)

• Change in TempDB datafile size is from around 64MB to 576 MB(matching with brown line). But the used space has increased from 4 MB to 110 MB, which means TempDB datafile size increased by 106 MB, which explains the version store size 105.5 MB filled up in tempdb datafiles.

• Change in TempDB logfile size is 0.03 MB for this operation.

It is important to note that versioned rows take up space in TempDB datafiles and not logfile.
Logfile remains almost the same before and after the update.

Version Store Cleanup - PerfMon

Version store cleanup task runs -

• once a minute and

• during an autogrowth event in TempDB, when TempDB is full.

At point C in the graph,

• Red line - Version store cleanup kicked off. It spikes up as it cleans up versioned rows and then drops as soon as it is done.

• Green line - Version store size goes down, since versioned rows are cleaned up.

• Brown line - TempDB datafiles size remain the same, though all of it is now free space.

Version store cleanup - SSMS

Let’s check these counters in SSMS:

• Version store size is reduced from 105.5MB to 0. (matching with green line)

• TempDB datafile size remains the same(matching with brown line). But the used space is reduced from 110.31MB to 4.75 MB equals 105.5 MB.
  Note the reduction of 105.5MB, which is nothing but the version store size. Again, this also shows that version store is saved in TempDB datafiles and not logfile.

• Logfile size remains the same.

What did we see now

When there is an update or delete operation on a table in an RCSI enabled database,

• Version generation takes place, which means the last committed(versioned) rows get placed in version store in TempDB.

• Version store size increases in TempDB and can be figured for which database the versioned rows have been generated.

• Version store is saved in TempDB datafiles and not logfile.

• If there is a concurrent read operation on this table while the update is running, then it will retrieve the rows from version store in TempDB, without being blocked waiting for the update to finish on the table.

• Version store clean up happens every minute and it is a background process in TempDB.

• Version store clean up also happens when there is an autogrowth operation in TempDB, when TempDB is full.

Version store does come with implications especially when there are long running idle transactions. I’ll discuss this in the future article.

I hope this helped you understand when version store starts generating rows, when it cleans up, and how TempDB space gets occupied due to this activity.

Resources

• Issues with Version Store - https://gohigh.substack.com/p/version-store-implications

• To read about Read Committed Isolation level(via which version store gets enabled) and how it works, here is the link - https://gohigh.substack.com/p/avoid-read-write-blocking-with-read