Version Store
A interesting quote " Are you using Read Committed Snapshot isoloation? We are and it is blowing up our TempDB. " (https://thesurfingdba.weebly.com/my-version-store-is-huge.html)
The version store contains the committed rows which is how a SELECT operation does not get blocked when another UPDATE/DELETE is operating on the same row, because the SELECT reads the row from the version store, instead of the actual base table. When you enable this, the row has to be stored somewhere and tempdb happens to be the place. A row is maintained in the version store when there are transactions operating on that row in question. When the transaction is committed, the row is cleaned up from the version store tables. Row versions must be stored for as long as an active transaction needs to access it. Once every minute, a background thread removes row versions that are no longer needed and frees up the version space in tempdb. A long-running transaction prevents space in the version store from being released if it meets any of the following conditions:
• It uses row versioning-based isolation.
• It uses triggers, MARS, or online index build operations.
• It generates row versions.
Here is an article about how to monitor version store
The above problem is not uncommon, see the following articles as examples. When the version store gets really, really large, performance can take a hit.
https://kohera.be/blog/sql-server/tempdb-the-ghost-of-version-store, https://www.sqlservercentral.com/articles/tempdb-growth-due-to-version-store-on-alwayson-secondary-server, https://am2.co/2019/10/what-the-heck-is-the-sql-server-version-store.
Returns a table that displays total space in tempdb used by version store records for each database.
sys.dm_tran_version_store_space_usage is efficient and not expensive to run, as it does not navigate through individual version store records, and returns aggregated version store space consumed in tempdb per database (https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-version-store-space-usage?view=sql-server-ver15).
You can also check the version store using the DMV sys.dm_tran_version_store (https://thesqldude.com/tag/version-store).
At times, when there are long running transactions or orphaned transactions, you might notice tempdb growth due to the version store.
You can use the following query to find the oldest transactions that are active and using row versioning.
SELECT top 5 a.session_id, a.transaction_id, a.transaction_sequence_num,
a.elapsed_time_seconds, b.program_name, b.open_tran, b.status
FROM sys.dm_tran_active_snapshot_database_transactions a
join sys.sysprocesses b
on a.session_id = b.spid
ORDER BY elapsed_time_seconds DESC
Example:
You have a database named dbReporting. Users run a large number of read-only ad hoc queries against the database. The application and all ad hoc queries use default database transaction isolation levels. You set the value of the READ_COMMITTED_SNAPSHOT database option to ON.
You have an application that updates 10 tables sequentially and modifies a large volume of records in a single transaction. The updates are isolated from each other. Users report an error which indicates that the version store us full. You need to reduce the number of occurrences of the error.
Solution1: You increase the maximum database size for the tempdb database.
Solution2: You increase the auto growth rate for the dbReporting database.
Solution3: You increase the maximum database size for the dbReporting database.
Answer explanation: During the transaction, SQL Server copies rows modified by other transactions into a collection of pages in tempdb known as the version store. When a row is updated multiple times, a copy of each change is in the version store. This set of row versions is called a version chain.
READ_COMMITTED_SNAPSHOT uses less tempdb space than snapshot isolation, but it is still important to ensure tempdb has enough space for both normal operations and row versioning. Note that both READ_COMMITTED_SNAPSHOT and SNAPSHOT isolation levels can be enabled at the same time, but there is only one copy of data in the version store.
Ref1:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a374f346-de84-4330-83cc-f5d2c3aa99ee/tempdb-version-store-cleanup?forum=sqldatabaseengine
Ref 2:
https://docs.microsoft.com/en-us/archive/blogs/sqlserverstorageengine/managing-tempdb-in-sql-server-tempdb-basics-version-store-simple-example
Ref 3:
https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-2017
Further read an article by R yan Ries
https://techcommunity.microsoft.com/t5/ask-the-directory-services-team/the-version-store-called-and-they-8217-re-all-out-of-buckets/ba-p/400415