question

I have used SET ALLOW_SNAPSHOT_ISOLATION ON to mitigate deadlocking, but I tend to use it on a procedure-by-procedure basis, using `SET TRANSACTION ISOLATION LEVEL SNAPSHOT` within the code rather than setting it as the database default. Because row versions are stored in tempdb, I didn't want to just throw everything in there; only specific procedures that were having issues. If you set `READ_COMMITTED_SNAPSHOT` as the database default, you may still have to change stored procedure code. For example, I knew a DBA who coded each and every stored procedure he wrote to use the SQL Server default isolation level: `SET TRANSACTION ISOLATION LEVEL READ COMMITTED;` In this case, setting the database default will not help as the procedure code would override the `READ_COMMITTED_SNAPSHOT` default.

To be able to use the SNAPSHOT isolation level you have to alter the database and `SET ALLOW_SNAPSHOT_ISOLATION ON`. Until this is enabled on database level, you are not able to use the SNAPSHOT isolation and doesn't matter whether you want to set it globally on database level or locally in queries or stored procedures. As @KenJ noted if you enable the SNAPSHOT on database level, then you will not need to modify the stored procedures only if there isn't set some other isolation level locally. If the stored proc as using a locally set isolation level, then you have to alter your stored procedures as @KenJ noted.

It is my belief, and experience, that 99% of deadlocks are avoided with coding and indexing modifications, NOT changing the isolation level. You can minimize deadlocks with some isolation levels, but at the expense of concurrency and performance.