sakuraime 2,321 Reputation points

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,852 questions
{count} votes

2 answers

Sort by: Most helpful
  1. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor

    Hi @sakuraime ,

    Please try to use below T-SQL;

    -- Show space usage in tempdb  
    SELECT DB_NAME(vsu.database_id) AS DatabaseName,  
        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,

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

    0 comments No comments

  2. Ben Miller (DBAduck) 951 Reputation points

    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.

    0 comments No comments