April 8, 2015 at 1:42 pm
Hello experts !!
The following is from an accidental dba perspective 🙂
To enable RCSI on a database, referencing these two parameters:
is_read_committed_snapshot_on (value 0 or 1)
and
snapshot_isolation_state (value of 0,1,2 or 3) - based on specific needs. At a minimum, it should be a 1
Both parameters are required, Is this correct ?
thank you,
SQLJay
April 8, 2015 at 2:00 pm
RCSI and Snapshot Isolation are different things.
For our databases where we have only enabled RCSI, is_read_committed_snapshot_on = 1 and snapshot_isolation_state = 0.
This is the command to turn on RCSI:
ALTER DATABASE db_name SET READ_COMMITTED_SNAPSHOT ON;
Twitter: @SQL_JGood
Blog: sqljgood.wordpress.com/[/url]
April 8, 2015 at 4:13 pm
If RCSI is on, you wouldn't have any need to use ALLOW_SNAPSHOT_ISOLATION.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
April 8, 2015 at 9:37 pm
Thank you very much for the replies, guys. It was very helpful.
Cheers,
SQLJay
April 9, 2015 at 2:46 pm
Just a follow up question perhaps looking for some further clarification as I might be reading an old thread on the web.
Was there ever a need to run both statements in the past ?
ALTER DATABASE DatabaseName SET READ_COMMITTED_SNAPSHOT ON
ALTER DATABASE DatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON
Reading one the replies above states the latter statement is not necessary if set READ_COMMITTED_SNAPSHOT has been executed
apologies for the noob question, just wanting to ensure 🙂
April 10, 2015 at 9:59 am
SQLJay (4/9/2015)
Reading one the replies above states the latter statement is not necessary if set READ_COMMITTED_SNAPSHOT has been executed
That's not entirely correct. This link details the following
Microsoft TechNet
When to Use Read Committed Isolation Using Row Versioning--------------------------------------------------------------------------------
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:
•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.
When to Use Snapshot Isolation
--------------------------------------------------------------------------------
Snapshot isolation provides transaction-level read consistency. A data snapshot is taken when the snapshot transaction starts, and remains consistent for the duration of the transaction. Use snapshot isolation when:
•Optimistic concurrency control is desired.
•Probability is low that a transaction would have to be rolled back because of an update conflict.
•An application needs to generate reports based on long-running, multi-statement queries that must have point-in-time consistency. Snapshot isolation provides the benefit of repeatable reads (see Concurrency Effects) without using shared locks. Database snapshot can provide similar functionality but must be implemented manually. Snapshot isolation automatically provides the latest information in the database for each snapshot isolation transaction.
So, for multi statement queries where consistency is required you would use snapshot isolation as the row versions are held for the duration of the transaction.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply