SQL Server READ COMMITTED SNAPSHOT ISOLATION

sakuraime 2,321 Reputation points
2021-01-19T08:08:51.133+00:00

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
    2021-01-20T10:27:43.487+00:00

    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.

    0 comments No comments

  2. Ben Miller (DBAduck) 951 Reputation points
    2021-01-21T05:44:38.88+00:00

    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