SQL Server READ COMMITTED SNAPSHOT ISOLATION

Question

when enabling the option of READ COMMITTED SNAPSHOT ISOLATION, how can we estimate the tempdb usage (version store) overhead ?

Answer 1

Hi @sakuraime ,

Please try to use below T-SQL;

-- Show space usage in tempdb  
SELECT DB_NAME(vsu.database_id) AS DatabaseName,  
    vsu.reserved_page_count,   
    vsu.reserved_space_kb,   
    tu.total_page_count as tempdb_pages,   
    vsu.reserved_page_count * 100. / tu.total_page_count AS [Snapshot %],  
    tu.allocated_extent_page_count * 100. / tu.total_page_count AS [tempdb % used]  
FROM sys.dm_tran_version_store_space_usage vsu  
    CROSS JOIN tempdb.sys.dm_db_file_space_usage tu  
WHERE vsu.database_id = DB_ID(DB_NAME());  

Please refer to the blog Snapshot Isolation in SQL Server. to get more information.
Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.

Answer 2

Every environment is different. The version store is used when you have a query that is modifying data in a table and then while the modification is happening a select query comes in and retrieves data. The snapshot isolation part takes a snapshot of the data as it was at the beginning of the write transaction and puts it in the version store and keeps it there until no other query needs that version and the write completes, then the version store is cleaned up eventually.

While this is happening the query above shows you the version store usage and you can trend that over time to see how much overhead the version store is. There is no good answer that says this is how you tell how much version store you will use. It is mostly a case by case basis.