Version Store Implications

Jan 27, 2025

With every feature comes both advantages and disadvantages and it is important to be aware of both. In regards to version store, we had discussed how to understand version store better and how version store is enabled.

Today, we will talk about the trade-offs and implications that come along when version store is enabled.

Cons of version store

Open transactions: If there is a long open, sleeping transaction, no other transactions occurring after this can clear the versions, even though the latest transactions are committed. The earliest uncommitted transaction will hold up the version cleanup of all subsequent committed transactions.
Storage: A 14-byte storage pointer is added to each row in the original table, and this remains even when versioned rows are cleaned up.

Demo

Let’s do a demo of how an open transaction can hold the cleanup of version store and cause TempDB to blow up.

Before we go ahead, verify there are no transactions using version store.
Result: None as expected.
Here are the 4 things I’m checking below:

Number of rows in version store using dmv - sys.dm_tran_version_store.
Space consumed in TempDB by version store of each user database.
File sizes of TempDB

Check transactions currently using version store

/*DISPLAY VERSIONED ROWS IN VERSION STORE
DO NOT RUN ON PROD DUE TO HUGE NUMBER OF RECORDS*/
SELECT COUNT(*) AS NumberOfVersionedRows 
FROM sys.dm_tran_version_store

/*SPACE CONSUMED IN TEMPDB, BY VERSION STORE OF EACH DATABASE*/
SELECT DB_NAME(database_id) AS 'Database Name'
	,reserved_space_kb  AS 'Space(KB) 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;

/*FIND TRANSACTIONS USING VERSION STORE

https://thesqldude.com/2012/05/15/monitoring-tempdb-space-usage-and-scripts-for-finding-queries-which-are-using-excessive-tempdb-space/

*/
SELECT a.session_id, d.name, a.elapsed_time_seconds/60.00 AS elapsed_time_mins,
b.open_tran, b.status,b.program_name,  a.transaction_id, a.transaction_sequence_num
FROM sys.dm_tran_active_snapshot_database_transactions a
join sys.sysprocesses b on a.session_id = b.spid
join sys.databases d on b.dbid=d.database_id
ORDER BY elapsed_time_seconds DESC

Tables DBAtest.dbo.t1 and FinApp.dbo.t1 are identical tables just on different databases for demo purpose.
```
use DBATEST;
select * from dbo.t1;

use FinAppDB;
select * from dbo.t1
```
Transaction1 : On database DBATest, run an update on dbo.t1 but do not commit yet.
```
use DBATest;

begin tran 
update dbo.t1 set a='a_10';
```
Check number of records and space used in version store.
Result: Since the table has 10 records, there are 10 versioned rows in version store(sys.dm_tran_version_store), which are the last committed rows.
Version store space is 64 KB.
Check version store after a few minutes and you will see that the versioned records are not cleaned up, since the transaction is not committed.
Result: I checked again after 6 minutes and though version store clean up task runs in the background every minute, clean up doesn’t happen due to uncommitted transaction. It will clean up only after the earliest transaction gets committed.
Transaction2: Now, on a different database FinAppDB, run an update on dbo.t1 and commit it.
```
use FinAppDB;

begin tran
update dbo.t1 set a='a_10';
commit
```
Check number of records and space used in version store.
Result: You will see that the version store now contains 10 records from Transaction 1 (in DBATest.dbo.t1) and 10 records from Transaction 2 (in FinAppDB.dbo.t1). In total, there are 20 records.
Therefore, 2 databases are now using version store and each occupy 64 KB.
Even though transaction 2 is committed and is from a different database, you will notice that it hasn’t been cleaned up from version store, even after 10 minutes of committing Transaction 2.
This is because the earliest open transaction (Transaction 1) has not been committed yet.

Any transactions using version store that begin to run now will not get cleaned up from version store until transaction 1 is committed. TempDB datafiles and logfile have increased by a few kilobytes in this demo as I have used tiny tables.
Imagine in production, there will be transactions continuously coming in, which will just remain in version store due to one open, long sleeping transaction. This will cause tempdb datafiles and logfile to grow continuously and eventually blow up. (Versioned rows are saved in TempDB datafiles and a log record of it is saved in logfile. )

Key Takeaway

Whenever you notice an increase in the size of TempDB data files, log files, or version store space, check whether there are any open long sleeping (uncommitted) transactions. Terminating these transactions will help resolve issues related to version store cleanup and prevent TempDB from growing uncontrollably.

Suggested Feature: Automatic Cleanup for Sleeping Transactions

It would be helpful to have a setting in SQL Server, that automatically terminates long sleeping transactions after a specified time limit, with an option to enable/disable based on the environment and workload needs.

Conclusion

I discussed open long transactions that hold up the cleanup in the version store, which can have a greater impact on production.
Note that there is also an additional storage cost of 14 bytes for each row due to version store. But, since storage has become cheaper nowadays, the impact on overall costs may be less significant, but it's still important to consider this overhead, especially in large-scale environments.

Resources

https://gohigh.substack.com/p/avoid-read-write-blocking-with-read

https://gohigh.substack.com/p/understand-version-store-better

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-version-store-transact-sql?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-version-store-space-usage?view=sql-server-ver16

Thanks for reading,
Haripriya.
My LinkedIn

Go High

Discussion about this post