When are the unnecessary row versions being removed from the version store

Question

Sorry for my bad English :)

I need a help with understanding version store`s cleaning. I have the following situation.

1. 12:00 Start a select statement under the snapshot isolation level and not commit.
2. 12:01 Start an update statement (update a single row) under the read committed isolation level and commit.
3. 12:02 Start a select statement under the snapshot isolation level and not commit.
4. 12:03 Start an update statement (update a single row) under the read committed isolation level and commit.

Now if we look at the version store such as

SELECT * FROM sys.dm_tran_version_store

we will see there 2 rows (one row for each update statement).

Now if we commit the first transactions that starts at 12:00, no row is being removed from the version store (even pass a minute).

The question is WHY???.

In my opinion on of the rows have to be removed from the version store, because there are no any active transactions, that have to access to that version.

I tried also to update no 1 row, but 100 rows (Total more then 100 KB) but I have the same situation, the version store is not being removed.

Please help me to understand when does the version store is being clean. Thanks a lot for help.

Answer 1

Now if we commit the first transactions that starts at 12:00, no row is being removed from the version store (even pass a minute).

The question is WHY ?

Minimal condition for a version row to be garbage collected is when SQL Server determines, based on the transactional states, that this version is no longer needed.

Also, referring to my SQL Server internals notes, for management of version store, SQL Server will perform a regular cleanup every minute with a background process to reclaim all the reusable space from the version store.

You can monitor Version generation rate and version cleanup rate in SQLServer:Transactions perfmon counter to find out rate at which space is acquired and released from version store in KB/Sec.

Check : Managing TempDB in SQL Server: TempDB Basics (Version Store: Simple Example)