leftinsurance.blogg.se

I'm still not actually using the snapshot isolation, or am I? I would think that when ALLOW_SNAPSHOT_ISOLATION is OFF, setting READ_COMMITTED_SNAPSHOT to ON wouldn't have any effect. This may or may not be giving you the desired results in your queries. The "speed up" you're seeing is most likely due to the reduced waits on blocking for those queries as it is now using the optimistic concurrency's version store instead of blocking and waiting. RCSI is turned on, thus all RC isolation queries are being promoted to RCSI where readers don't block writers and writers don't block readers. SI provides a point-in-time view for an entire transaction.RCSI provides a point-in-time (snapshot) view of the database for a single statement.RCSI will be used immediately by all RC isolation queries.RCSI and Snapshot (SI) differ in three main ways: In addition, READ_COMMITTED_SNAPSHOT (henceforth called RCSI) will only give you some properties of optimistic concurrency. READ_COMMITTED_SNAPSHOT ON causes all READ_COMMITTED sessions to use row-versioning instead of locking so that will avoid blocking and may explain your perceived performance improvement.

SET READCOMMITTEDSNAPSHOT ON WITH ROLLBACK IMMEDIATE To verify the changes, use this query which should result in 1: SELECT sd.isreadcommittedsnapshoton FROM sys.databases AS sd WHERE sd.

The options are independent and both do not need to be turned on, although many mistakenly do so. I'm still using the default READ COMMITTED isolation level to connect to the database. I noticed that when enabling this, many queries got a lot faster.