Sql-server – Both READ_COMMITTED_SNAPSHOT ON and ALLOW_SNAPSHOT_ISOLATION ON

Your understanding is correct. It does get a little confusing.

Kim Tripp (one of the programmers of SQL Server and a integral part of SQLSkills) goes through exactly what you stated in the MCM videos on Snapshot Isolation. Fast fwd to 41:45 in the video to get to the part where she answers your question.

If you use ALLOW_SNAPSHOT_ISOLATION make sure you use SET TRANSACTION ISOLATION LEVEL SNAPSHOT in your code, otherwise you will not get any of the benefits.

If you set SET READ_COMMITTED_SNAPSHOT ON, then there is no need to modify any code. MS SQL Server automatically applies snapshot isolation for that table.

I haven't tested to see what happens if you ask for a different isolation level in your code, I suspect it will overwrite this option but test it first.

A quick look at performance overhead using Snapshot Isolation.

Good article on how snapshot isolation can change the expected behavior of your app. It shows examples of how a update statement and a select statement might return totally different and unexpected results.

...will it block for a period of time proportional to the record count, or will the data be updated asynchronously.

Neither.

The additional 14 bytes are used to store two elements:

• The transaction sequence number that modified the row.
• A pointer to the previous version of the row in the version store (in tempdb) if one exists.

These are only added to the row when it is modified, subsequent to your enabling snapshot isolation. There is no blocking or additional load generated to add the 14 bytes to each row at the point you switch snapshot on.

The only blocking action you may encounter at the point of enabling is due to the need to wait for all current transactions to commit, which is worth keeping in mind. Ideally make the change during a quiet period or preferably a moments downtime where you shut all activity out.

If downtime isn't an option, avoid any period where long running transactions might occur (ETL for example). If you don't get a response within a few seconds, you can query sys.dm_tran_active_snapshot_database_transactions to identify what's getting in the way.