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?

1 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.


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.