Reducing Reader-Writer Blocking in SQL Server with READ COMMITTED SNAPSHOT

The blocking complaint that starts the conversation

During peak hours, a report running under the default READ COMMITTED isolation level holds shared locks on rows it reads. At the same time, an application process tries to update those same rows. The update waits. The report keeps running. Users see timeouts, and the support ticket lands on your desk with two symptoms: readers blocked by writers, and writers blocked by readers.

One common fix is to enable READ COMMITTED SNAPSHOT (RCSI) on the user database. When it is on, SQL Server uses row versioning in tempdb so a statement running under READ COMMITTED reads the last committed version of a row instead of taking shared locks. Readers do not block writers, and writers do not block readers. It is not the same as enabling snapshot isolation for the whole database; ALLOW_SNAPSHOT_ISOLATION is a separate database option, and RCSI only changes behavior under the default READ COMMITTED level.

Check the setting before you change it

Run this query to see the current state of the user database:

SELECT name, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = 'SalesDB';

A value of 0 means RCSI is off. A value of 1 means it is already on. If it is off and you want to enable it, plan for a brief maintenance window. The ALTER DATABASE statement typically requires exclusive access to the database, so active connections can block the change or be terminated depending on how you issue it.

Enable it and verify the change

Run the following in SQL Server Management Studio, Azure Data Studio, or sqlcmd. You need ALTER permission on the database, typically through db_owner or a server-level role such as sysadmin.

ALTER DATABASE [SalesDB]
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;

WITH ROLLBACK IMMEDIATE rolls back uncommitted transactions and disconnects active sessions so the statement can obtain exclusive access. That is fast, but it can interrupt users. If you cannot drop connections, omit the rollback clause and schedule the change when no one is using the database, or identify and end specific sessions first.

Existing connections do not pick up the new behavior until they reconnect. After the statement succeeds, confirm the flag:

SELECT name, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = 'SalesDB';

Expected result: is_read_committed_snapshot_on = 1. To observe the behavior, open two sessions. In session one, begin a transaction and update a row without committing. In session two, run a SELECT against the same row. With RCSI enabled, the SELECT should return the last committed version and not wait for the uncommitted update. Without RCSI, the SELECT would typically block until the writer commits or rolls back.

Trade-offs and what to watch after enabling

RCSI removes reader-writer blocking under READ COMMITTED, but it does not make all blocking disappear.

• Two concurrent writers still block each other. RCSI does not prevent update conflicts or lost updates by itself.
• Every updated or deleted row carries versioning information, which adds overhead to write operations.
• Row versions live in tempdb. Long-running transactions can prevent the version store from being cleaned up, causing tempdb growth.
• Applications that depend on readers blocking to observe uncommitted data or to serialize work may behave differently after the change.

Monitor tempdb after enabling. This query shows version store space usage:

SELECT * FROM sys.dm_tran_version_store_space_usage;

If version store usage climbs steadily, look for long-running transactions using sys.dm_tran_database_transactions or sys.dm_exec_sessions, and consider whether tempdb needs more files or faster storage.

Roll back the change if needed

Because ALTER DATABASE changes database state, you can reverse it:

ALTER DATABASE [SalesDB]
SET READ_COMMITTED_SNAPSHOT OFF
WITH ROLLBACK IMMEDIATE;

The same exclusive-access and reconnection rules apply. Test the reversal in a non-production environment before scheduling it in production.

Decide based on your workload

Enable RCSI when reader-writer blocking under READ COMMITTED is a proven problem and your tempdb can absorb version store growth. Do not enable it simply because blocking exists somewhere; confirm the contention pattern first, measure tempdb usage afterward, and remember that writer-writer blocking remains.