What’s the best way to avoid most blocking issues in SQL Server? Turn on Read Committed Snapshot Isolation (RCSI). That’s it.
Configuring RCS isolation level
To see if it’s enabled on your database, use the is_read_committed_snapshot_on column in sys.databases like this:
select is_read_committed_snapshot_on from sys.databases where database_id = db_id(); |
To enable the setting alter the database like this:
ALTER DATABASE CURRENT SET READ_COMMITTED_SNAPSHOT ON |
Is it that easy?
Kind of. For the longest time at work, we ran our databases with this setting off. Mostly because that’s the default setting for SQL Server. As a result, we encountered a lot of blocking and deadlocks. I got really really good at interpreting deadlocks and blocking graphs. I’ve written many blog posts on blocking and I even wrote a handy tool (the blocked process report viewer) to help understand who the lead blocker was in a blocking traffic jam.
Eventually after a lot of analysis we turned on RCSI. Just that setting change probably gave us the biggest benefit for the least effort. We rarely have to deal with blocking issues. I haven’t made use of the blocked process report viewer in years.
Be like Severus Snape
I’m reminded of a note that Snape (from the Harry Potter books) wrote in his textbook on poison antidotes “Just shove a bezoar down their throats.” The idea was that you didn’t have to be good at diagnosing and creating antidotes because a bezoar was simply an “antidote to most poisons”.
In the same way, I’ve found that RCSI is an antidote to most blocking.
