We had a support-ticket system that deadlocked twice a week like clockwork. Agents updated tickets while reports read the same tables, readers blocked writers, writers blocked readers, and every few days the deadlock monitor picked a victim, usually during the Monday morning rush. One database setting ended it. READ_COMMITTED_SNAPSHOT turned reader-versus-writer blocking into a non-event overnight, and the deadlock graph flatlined. It also introduced two bugs, real ones, that we only caught because we ran a concurrency soak before enabling it in production. Both halves of that story matter.
RCSI is close to a free lunch, which is exactly why people enable it carelessly and get surprised. This is the mechanics, the migration checklist, and the monitoring, in the order I wish someone had handed them to me.
What does READ_COMMITTED_SNAPSHOT actually change?
It changes what READ COMMITTED means. Under the default locking implementation, a reader takes shared locks on the rows it reads, and those shared locks collide with the exclusive locks writers hold, so a big report can freeze the writers behind it. With READ_COMMITTED_SNAPSHOT on, readers take no shared locks at all. Instead, when a statement needs a row that a writer has locked, it reads the last committed version of that row from the version store in tempdb. Writers still block writers exactly as before; only the reader-writer collision disappears.
The mechanics have a price tag you should know. Every update or delete on a versioned database tags the row with a 14-byte pointer into the version chain, and the pre-update image of the row gets pushed into a version store, in tempdb by default, or in the database's own persistent version store when Accelerated Database Recovery is enabled, which it always is in Azure SQL Database and Managed Instance. Updates get slightly more expensive, whichever store applies gains a new tenant, and rows grow by 14 bytes, which on narrow tables can itself cause page splits after you enable the feature. None of that is a reason to stay away. All of it is a reason to measure rather than flip and pray.
How is RCSI different from full SNAPSHOT isolation?
RCSI gives you statement-level consistency automatically, with zero code changes, while SNAPSHOT isolation gives you transaction-level consistency but only where the code asks for it. Under RCSI, each statement sees the database as of the moment that statement started, so two statements in one transaction can see different committed states. Under full SNAPSHOT, enabled with ALLOW_SNAPSHOT_ISOLATION and then requested in code with SET TRANSACTION ISOLATION LEVEL SNAPSHOT, the whole transaction sees one consistent point in time, from first statement to last, which is what report writers and long business transactions actually want when they say "consistent."
The sharper difference is error 3960. Under full SNAPSHOT, if two concurrent snapshot transactions try to update the same row, the second one fails with an update conflict: snapshot isolation transaction aborted due to update conflict, first committer wins. Your retry logic has to expect it. Under plain RCSI, updates do not conflict that way; a writer that finds its row locked by another writer simply waits, as it always did. So RCSI is the safe, silent, app-transparent half of the feature, and full SNAPSHOT is the powerful half that demands code participation. Azure SQL Database ships with RCSI enabled by default, which I take as the strongest available hint about which one Microsoft considers the sane baseline for OLTP.
What actually changes when I flip it on?
The semantics of every "read, decide, write" pattern in your codebase, that is what. This is the migration checklist I run, and both of our caught bugs live on it. First: readers can now observe pre-update values, so any code that read a row and relied on the shared lock to hold reality still while it thought about it has silently changed meaning. The read no longer holds anything. Second: check-then-act across two statements, an IF EXISTS followed by an UPDATE, can now evaluate the EXISTS against an older version than the UPDATE later modifies. Single-statement upserts are unaffected; multi-statement logic needs review, and the fix is usually an explicit UPDLOCK hint or a real transaction design, not panic.
Third, and this one bit us: triggers that enforce invariants by reading other rows now read versions. We had a trigger that rejected an overlapping booking by counting conflicts; under RCSI, two concurrent bookings each read a version where the other did not yet exist, both passed the check, and we double-booked a room in the soak test. The trigger needed an explicit locking hint to see current, unversioned reality. Fourth: hot-row contention does not vanish, it relocates into version chains, so a row updated fifty times a second grows a long chain that every reader walks. Fifth, the version store needs space and monitoring wherever ADR has put it, which gets its own section below. Run a concurrency soak, not a unit test suite. Every one of these bugs is invisible to tests that run one session at a time.
How do I turn it on safely, and monitor the version store?
The setting itself is one statement, and the operational trap is that it needs the database quiet enough to flip. The WITH ROLLBACK clause decides whether existing connections get a grace period or get killed:
ALTER DATABASE [AppDb]
SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK AFTER 60;
SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = 'AppDb';Do it in a change window, verify is_read_committed_snapshot_on comes back 1, and then start watching the version store, because the feature you just enabled rents space by the hour. Where it rents depends on Accelerated Database Recovery: with ADR off the store lives in tempdb, and with ADR on, which is always the case in Azure SQL Database and Managed Instance, versions live in the database's own persistent version store and sys.dm_tran_persistent_version_store_stats is the view that matters instead. On SQL Server 2016 SP2 and later, the tempdb side of the consumption is directly visible:
USE tempdb;
SELECT DB_NAME(database_id) AS database_name,
SUM(reserved_space_kb) / 1024.0 AS version_store_mb
FROM sys.dm_tran_version_store_space_usage
GROUP BY database_id;
SELECT TOP 5 ast.session_id, ast.elapsed_time_seconds,
s.host_name, s.program_name
FROM sys.dm_tran_active_snapshot_database_transactions AS ast
LEFT JOIN sys.dm_exec_sessions AS s
ON s.session_id = ast.session_id
ORDER BY ast.elapsed_time_seconds DESC;The second query is the one that saves you. Version cleanup cannot reclaim anything newer than the oldest open snapshot transaction, so one forgotten SSMS window or one orphaned transaction pins hours of versions and the store grows until tempdb fills. The classic symptom is "tempdb full at 2 a.m." with a version store nobody was watching; the classic culprit is a session that opened a transaction and went home. The tempdb sizing and contention side of this story is in my notes on tempdb contention fixes, and the performance counters Version Store Size (KB) and Longest Transaction Running Time deserve a permanent spot on whatever dashboard you run.
Which systems should flip it, and which should leave it off?
Most OLTP systems should have turned it on years ago, and I do not say that lightly. If your pain is readers blocking writers, if your deadlock graphs show selects dying to updates, if your reporting workload shares tables with your transactional one, RCSI is the highest-value single setting change available to you, and Azure SQL Database defaulting to it on tells you how the wind blows. The systems that should pause are the ones with code that deliberately relies on readers blocking, which is rarer than people claim but real: homegrown queue tables that use locking as a mutex, or multi-statement check-then-act logic nobody is willing to review. Also pause if tempdb is already saturated and unmanaged, because you are about to give it a new tenant, or if nobody will own the version-store monitoring. Those are not arguments against RCSI; they are the work items on its adoption ticket.
The two bugs from my own migration were both on the checklist, both caught in a soak, and both fixed in a day. The blocking it eliminated was costing us two deadlocks a week and a standing argument between the support team and the reporting team. Do the checklist, run the soak, watch tempdb, and flip it.
Where MonPG's SQL Server support stands
MonPG monitors PostgreSQL in production today, and SQL Server support is on the roadmap and in active development, not shipped. The version-store story is a clean example of what we want to graph when it lands: version store size per database trended against tempdb free space, the age of the oldest snapshot transaction with the session that owns it, and 14-byte row overhead surfacing as unexpected table growth after you enable the feature. That is the difference between a scheduled cleanup conversation and a 2 a.m. tempdb-full page. Progress lives on the SQL Server monitoring (coming soon) page. Until it ships, the two monitoring queries above in an agent job, with an alert on elapsed_time_seconds measured in hours, will keep your version store honest.