Read Committed Snapshot

The Read Committed Snapshot Isolation (RCSI) option removes blocking between reads and writes in a database, by switching to an optimistic concurrency model based on row versioning.

Enable it with T-SQL rather than through the SSMS interface. The activation needs exclusive access to the database, and the ROLLBACK IMMEDIATE option below disconnects every user from the database. Plan it accordingly with the application owners.

USE [master]
GO
ALTER DATABASE [<database>]
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE
GO

Replace <database> with the name of your database.

What RCSI changes

The default isolation level in SQL Server is READ COMMITTED. A SELECT has to wait for an in-flight data modification to finish before it can read, and a modification has to wait for readers: uncommitted changes cannot be read. When the modification is expensive, this produces blocking.

Read Committed Snapshot changes that behaviour by keeping a version of each row before it is modified. Reads then proceed without waiting and without blocking, against a transactionally consistent version of the data as it stood before the modification.

Reads no longer block writes, and writes no longer block reads.

The one drawback is heavier activity in tempdb, which holds the version store. It also increases the size of the data slightly, since each row gets a versioning pointer.