1

We have crm-2015 on-prem, inplace and running and causing lot of deadlocks when someone tries to update anything in batch and same time if multiple users are reading those activities.

We are planning to turn on RCSI. I already did on DEV. server and tested and didn't get any locks, but I know that's not the right and only test.

My questions to all experts here is,

  1. Current size of DB is 40 GB, how hard is to turn that option now to ON.
  2. Once we turn on, can we turn back off using the same procedure stopping all the services making DB with one active transaction only and then execute ALTER DB command with set option to OFF.
  3. I also read that some code changes requires but in existing application what to inform our developers about changing the code, or it is best to do all operations which we are performing and see if anything breaks.
  4. last but I guess this most important once we turn on does tempdb database version store cleanup happens by itself or do we have to turn on, if we have to from where?

Please help.

Thanks.

1

It's critical that you do not enable RCSI on a database without the application vendor and your own developers certifying that they support it. The reason is that the behaviour of all your read queries will change, but not actually break in such a way that replaying your workload will detect. Read section 3 at https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/.

To answer your specific questions:

  1. Switching on will be near-instantaneous (assuming only one DB connection).
  2. Yes, switching off is just as painless.
  3. See above.
  4. Cleanup happens by itself. Section 2 of the post above has some things to consider.
  • @user2040021 No problem. By the way, I would suspect RCSI can only partly solve your problem, as although reads will no longer be blocked, user updates during a batch update will still block/deadlock as they do now. And more generally, it sounds like it might not be safe to do mass updates in the way they're currently being done, since depending how the app is written, some records might lose the mass update if they manage to get updated towards the end of the batch update but with stale data--which could also happen a lot more under RCSI. Perhaps the mass update process needs reconsideration. – T.H. Feb 7 '17 at 20:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.