When a SQL Server database is found to be producing locks, deadlocks, or any kind of blocking, then we often recommend using a couple of settings on the database to resolve (or at least reduce) these issues.
The following database settings must be changed from "False" to "True":
- Allow Snapshot Isolation
- Is Read Committed Snapshot On
For example, you can do this for a database like [CranSoft] by running this query:
USE [master]
ALTER DATABASE [CranSoft] SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE [CranSoft] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
OR by right-clicking on the database in SQL Server Management Studio (SSMS), selecting 'Properties', and changing the settings to "True" on the Options page:
NOTE: Each database isolation level has its risks and advantages. We don't support enabling READ_COMMITED_SNAPSHOT on any database unless there is a reason (such as reducing locks). For READ_COMMITED_SNAPSHOT, there's a potential performance impact, especially for large tables and rows with several versions (this would increase database file sizes and increase usage of the tempdb database).