0

My website is mostly informative,
Users cannot Update or Delete, they can perform Insert but that almost doesn't happen.

Most of the site uses SELECT queries and the information doesn't change often so I don't mind of if user reads information that was published even a day ago.

Now the isolation level is the default which is Read Committed which seems a bit overkill for my website I think.

After a bit research I think about changing it to Snapshot or read_committed_snapshot.

What do you suggest?

  • 2
    For most new applications READ COMMITTED with the READ_COMMITTED_SNAPSHOT option is preferred. – David Browne - Microsoft Oct 24 '19 at 14:53
  • @DavidBrowne-Microsoft I try to change it like so: ALTER DATABASE mydb SET READ_COMMITTED_SNAPSHOT ON but it stucks on Excuting. I tried to close all connections but it says: Cannot use KILL to kill your own process. – Offir Pe'er Oct 24 '19 at 15:07
  • 1
    See new answer. – David Browne - Microsoft Oct 24 '19 at 15:12
  • Just a note that the snapshot alternatives has a higher overhead compared to the "traditional" ones. That is to store the prior version in tempdb and also the row will be 24 byte bigger to accommodate that pointer. Possibly you wont notice, but I just wanted to point it out... – Tibor Karaszi Oct 25 '19 at 6:44
3

To switch a database to RCSI:

USE mydb;
ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE mydb SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE mydb SET MULTI_USER;
| improve this answer | |

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.