SQL Server: Enabling Read Committed Snapshot Isolation

When using Microsoft SQL Server, enabling Read Committed Snapshot Isolation (RCSI) is one way to prevent reads (SELECT statements) from escalating into full table locks. Depending on your application this can either be a good or a bad thing. I’m not going to get into the why’s and why-nots of each strategy - this is a good article to read if you’re having a hard time deciding which strategy to choose and why.

So let’s say you want to enable RCSI on a fictional database MyDB. This can be achieved by simply issuing the following T-SQL:

ALTER DATABASE MyDB SET READ_COMMITTED_SNAPSHOT ON
GO

To check that it was successfully enabled, you can check the System View sys.databases:

SELECT is_read_committed_snapshot_on
FROM sys.databases
WHERE [name] = 'MyDB'

If it returns 1 then RCSI was successfully applied, you’re done! Unless…