April 14, 2012 at 3:14 am
for SQL 2005,
What do i need to check for changing isolation from read committed to RCSI.
1. Is an restart of SQL needed for the changes to apply
2. while doing the change, should the DB be brought in to single user mode.
3. General pitfalls of moving to RCSI
April 14, 2012 at 3:45 am
1) no
2) no
3) increased load on TempDB, possible change in behaviour (update conflicts instead of lost updates). Do test the app on read committed snapshot before just switching production over.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 14, 2012 at 4:46 am
Thanks again Gail.
About point2, I was wondering what would happen to the live/running transactions when I change from RC to RCSI. Since, only committed data is read, should I not have to worry about this?
About point3, yes, comprehensive testing would be done. While reading, i gathered that Update conflicts would not occur in RCSI. Am I wrong in my understanding?
April 14, 2012 at 5:29 am
balasach82 (4/14/2012)
About point2, I was wondering what would happen to the live/running transactions when I change from RC to RCSI. Since, only committed data is read, should I not have to worry about this?
I'm pretty sure that's discussed in Books Online.
About point3, yes, comprehensive testing would be done. While reading, i gathered that Update conflicts would not occur in RCSI. Am I wrong in my understanding?
No, you're right, that's snapshot isolation that has those. Still do test thoroughly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 14, 2012 at 6:01 am
I will look in BOL for the point2 (what would happen to the queries running at the time of point of change).
Can you please clarify on,
a. whether would it take long time for the ALTER DATABASE to complete (for enabling RCSI)?
b. After enabling RCSI at the DB level, do we need to specify the isolation level before each queries (we have to give explicitly in snapshot isolation. I am not planning on enabling both SI and RCSI. RCSI only for me)
April 14, 2012 at 6:07 am
a) No, it won't because there must be no other users. You must quiesce the database before enabling the option, single user mode though is not required.
From Books Online:
When the READ_COMMITTED_SNAPSHOT database option is set ON, the mechanisms used to support the option are activated immediately. When setting the READ_COMMITTED_SNAPSHOT option, only the connection executing the ALTER DATABASE command is allowed in the database. There must be no other open connection in the database until ALTER DATABASE is complete. The database does not have to be in single-user mode.
b) no. You cannot explicitly request read committed snapshot isolation as it is not a separate isolation level. It's read committed isolation level implemented with row versions not locks.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 14, 2012 at 6:45 am
Gail, your answers are very crisp and to the point. Thanks.
Now, I am clear that,
a. all connections to the DB must be closed (killed as the case maybe) and then the ALTER DATABASE is run. The BOL reference also made it much clear.
b. Enabling RCSI at DB is enough.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply