Grant Fritchey (5/5/2015)
Snapshot isolation level is a great way to reduce contention on the system. It does add load to tempdb, so be sure you're ready for that. However, I'm a strong advocate of using snapshot isolation on most systems (unless there are reasons not to due to existing load, stuff like that).
Yup. It's at the point where I recommend it in every performance review I do.
It's not a guaranteed fix for deadlocks, as writers still block writers in read committed snapshot/snapshot, but it will remove most. Remaining deadlocks will likely be fixable with performance tuning, indexing or minor code changes.
Steve, any chance you can post the deadlock graph? You can get it from the extended events system health session.
This may help overall: https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability