What is the recommended isolation level for a website's database?
-
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 KarasziOct 25, 2019 at 6:44
1 Answer
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;
For most new applications READ COMMITTED
with the READ_COMMITTED_SNAPSHOT
option is preferred.