READ COMMITTED SNAPSHOT does optimistic reads and pessimistic writes. In contrast, SNAPSHOT does optimistic reads and optimistic writes. Microsoft recommends READ COMMITTED SNAPSHOT for most apps that need row versioning. Read this excellent Microsoft article: Choosing Row Versioning-based Isolation Levels. It explains the benefits and costs of both isolation levels. And here’s a more thorough … Read more
You can check the status of the READ_COMMITTED_SNAPSHOT setting using the sys.databases view. Check the value of the is_read_committed_snapshot_on column. Already asked and answered. As for the duration, Books Online states that there can’t be any other connections to the database when this takes place, but it doesn’t require single-user mode. So you may be … Read more
Recently I had a deadlock problem because Sql Server locks more then necessary (page). You can’t really do anything against it. Now we are catching deadlock exceptions… and I wish I had Oracle instead. Edit: We are using snapshot isolation meanwhile, which solves many, but not all of the problems. Unfortunately, to be able to … Read more
SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name=”YourDatabase” Return value: 1: READ_COMMITTED_SNAPSHOT option is ON. Read operations under the READ COMMITTED isolation level are based on snapshot scans and do not acquire locks. 0 (default): READ_COMMITTED_SNAPSHOT option is OFF. Read operations under the READ COMMITTED isolation level use Shared (S) locks.