read committed snapshot on

Surendra Adhikari 211 Reputation points
2022-11-23T05:25:02.657+00:00

Recently I turned read committed snapshot to ON in one of the databases.
I got the feedback from client that a delete query became slow. I just want to know could this be due the read committed snapshot turned ON?
What impacts does this have on performance?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,320 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. PandaPan-MSFT 1,911 Reputation points
    2022-11-23T06:32:16.98+00:00

    Hi @Surendra Adhikari ,

    due the read committed snapshot turned ON

    Yes

    What impacts does this have on performance?

    The database option that changes the behavior of the READ COMMITTED transaction isolation level will increase concurrency, which will add locks and dirty read and so on.

    You can check the three following links:
    https://www.mssqltips.com/sqlservertip/6368/sql-server-readcommittedsnapshot-database-option-and-read-commited-transaction-isolation-level/
    https://www.mssqltips.com/sqlservertip/4120/comparison-of-sql-server-serializable-and-snapshot-isolation-levels/
    https://social.technet.microsoft.com/wiki/contents/articles/51484.sql-server-concurrency-control-models-acid-properties-and-transaction-isolation-levels.aspx


    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

    0 comments No comments

  2. Dan Guzman 9,226 Reputation points
    2022-11-23T10:24:38.72+00:00

    The main performance implications with READ_COMMITTED_SNAPSHOT ON or ALLOW_SNAPSHOT ON are:

    • Storage increases by 14 bytes per row
    • Increased tempdb storage and usage for the row version store

    In your case (slow deletes), review tempdb configuration to ensure space is pre-allocated (to avoid auto-grow during modifications, multiple tempdb data files (to avoid allocation page contention), and on high-performance storage.

    0 comments No comments