Both READ_COMMITTED_SNAPSHOT ON and ALLOW_SNAPSHOT_ISOLATION ON

Question

I inherited a SQL Server 2012 instance that hosts Hyperion databases. I believe the Hyperion techs configured the system and they have both READ_COMMITTED_SNAPSHOT ON and ALLOW_SNAPSHOT_ISOLATION ON at the same time. This doesn't make sense to me. Does it make sense?

Also I am seeing 60-70% of the waits are Lock_M_X, which is much higher than i have ever seen. Wondering if this might be related to the above.

And I have been running 1 minute sp_whoisactive snapshots into a table and have not been able to capture a single X type lock. A reasonable number of S type locks show up.

Any suggestions?

Answer 1

From a previous answer to this:

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.

Basically, with only ALLOW_SNAPSHOT_ISOLAION, you still have to specify the isolation level in the query/SP to make it use that isolation level.

With READ_COMMITTED_SNAPSHOT set to ON, the DB is automatically in that mode and there's no need to modify your queries.

ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT

In the answer I linked to above, there is a link to Kim Tripp's video on snapshot isolation. It's pure gold.