read committed snapshot on

Question

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?

Answer 1

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"

Answer 2

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.