RCSI (Read committed
Snapshot isolation) was the greatest feature that was introduce in SQL Server
2005 and available in later version. Oracle Database is having this feature for
long time.
“Read committed isolation using row versioning provides
statement-level read consistency. As each statement within the transaction
executes, a new data snapshot is taken and remains consistent for each
statement until the statement finishes execution. Enable read committed
isolation using row versioning when:
SELECT name, is_read_committed_snapshot_on FROM sys.databases
DBA who support
SQL SERVER Database, really need this feature that deal with Deadlock/ blocking
in multi-user Database. Now a days PeopleSoft deliver script to enable it when you
install new tools with SQL Server Database but DBA can enable it if they are supporting their custom built database
My Recommendations:
1. When DBA enable this option in SQL SERVER database,
then he/ she must consider temp database size as it grow much fast because all
version are stored in Temp database. It always better to have multiple temp data
file correspondence with CPU count and place on RAID 1 on SAN.
2. Use Single user mode to enable this feature.
3. This feature is very useful in multi user environment.
·
Reader/writer blocking occurs to
the point that concurrency benefits outweigh increased overhead of creating and
managing row versions.
·
An application requires absolute
accuracy for long-running aggregations or queries where data values must be
consistent to the point in time that a query starts.”
To enable (RCSI) on
a database, one needs to execute the following command:
ALTER
DATABASE <db_name> SET READ_COMMITTED_SNAPSHOT ON
If you query the is_read_committed_snapshot_on column of the sys.databases catalog view
on the server, you will see the value 1 (which means “ON”).
SELECT name, is_read_committed_snapshot_on FROM sys.databases
