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.
I usually add the _WITH ROLLBACK IMMEDIATE;_ syntax to the command. That takes some of the waiting out of the picture and avoids the single-user dance. Is there any reason you don’t use it here? I understand it may tack additional caveats to the post that would get in the way, but figured I’d ask.
Comment by Erik Darling — November 25, 2022 @ 3:23 pm
There’s no particular reason that I avoided it.
Rolling back transactions in flight is a choice itself. I remember when we rolled out RCSI we had to take a maintenance window. It was a very short maintenance window. But we had to do it based on my context. I had to convince folks that the cure was a lot less painful than the disease. It’s only going to hurt one last time. I promise.
I have no problem believing that in most contexts ROLLBACK IMMEDIATE would be the wisest choice.
Comment by Michael J. Swart — November 25, 2022 @ 3:31 pm
[…] Michael J. Swart says don’t worry, be happy: […]
Pingback by RCSI and Blocking – Curated SQL — November 28, 2022 @ 8:00 am
I’m guessing this is for a OLTP environment where the sessions are dealing in small(ish) data sizes?
If I remember correctly a snap shot means it’ll drop a copy off the data in tempdb for the session to work with? Before I go and slam this setting on button into gear I am guessing in a research environment where my users are dealing in millions or even billions of rows at a time, it isn’t wise as it will kill our tempdb. Does that thinking sound right?
Comment by Rlw — December 3, 2022 @ 1:10 pm