SQL Server – How to use READ_COMMITED_SNAPSHOT isolation level

I received a SQL Server database specification which included instructions to enable READ_COMMITTED_SNAPSHOT  .   The main point of READ_COMMITED_SNAPSHOT  is to manage access to versioned rows with the default READ COMMITED isolation level.

 READ COMMITED is the SQL Server Default Isolation Level. It’s managed via the lock mechanism , preventing dirty reads. When designing an application , consideration is given to locks held and for how long these locks are held. Decisions on concurrency can lead to a decision on whether READ_COMMITED_SNAPSHOT is used.

The main difference between READ COMMITED and the READ_COMMITED_SNAPSHOT is no locks are placed on  data being read. READ_COMMITED_SNAPSHOT   transactions do not block other transactions from writing data.

How to check if READ_COMMITED_SNAPSHOT  is enabled and how to enable

--is the db in READ_COMMITED_SNAPSHOT = ON   state?
SELECT is_read_committed_snapshot_on FROM
sys.databases WHERE name= 'database_name'

--to enable READ_COMMITED_SNAPSOT 
ALTER DATABASE  [database_name] SET READ_COMMITTED_SNAPSHOT ON

In the installation specification (mentioned earlier)  the justification for READ_COMMITED_SNAPSHOT  is to improve query performance . In my experience it does not always improve performance and must be weighed up against other potential issues

1) One reason to use  READ_COMMITED_SNAPSHOT   is to solve concurrency issues. No locks are placed on the data when it is read,  so SNAPSHOT transactions do not block other transactions from writing data .   If a SQL Server database experiences locking issues my first step is to analyse the code as a priority.  

2) Row versions are maintained in the Tempdb.  Thorough analysis is required for Tempdb size impact and increased load. How will this impact on other Tempdb queries? SQL Server – List current tempdb statements - SQL Server DBA

3) The most effective usages of READ_COMMITED_SNAPSHOT is in environments where the application is designed with optimistic locking. This minimises the possibility of  business logic issues or incorrect results returned.  This makes it more effective in OLTP environments

4) There are exceptions . Datawarehouse implementations can attempt READ_COMMITED_SNAPSHOT  , but once you consider the characteristics of a Datawarehouse  , the advantages aren’t as obvious. For example, typically a Datawarehouse will have a load window , with minimal , if any reads during the window. This makes TABLOCK more appropriate , and once the load window completes revert to row locks

 My experience of READ_COMMITED_SNAPSHOT  leads me to believe : Don’t assume anything and only implement once testing is completed.

 Read More

READUNCOMMITTED, NOLOCK and dirty reads - SQL Server DBA

Concurrency Control – Optimistic and Pessimistic - SQL Server DBA

SQL Server – Find sql server Locks - SQL Server DBA