Snapshot isolation is an optimistic SQL Server isolation level that lets transactions read versioned rows without the locking and blocking associated with pessimistic isolation. Older row versions remain in TempDB so a transaction can reference the appropriate data version. section 10.12
Mistake: treating optimistic isolation as a magic bullet
Snapshot isolation can reduce reader-writer blocking, but it moves work into TempDB. The Version Store may create substantial additional I/O and consume extra disk space, so suitability depends on the workload and TempDB capacity. A local NVMe-backed TempDB with ample free space is a more favorable situation than an I/O-bound or space-constrained one. section 10.12
Use these checks and configuration choices deliberately:
- Enable Read Committed Snapshot when the goal is optimistic behavior equivalent to Read Committed. section 10.12
- Enable Snapshot isolation when the workload requires optimistic behavior equivalent to Serializable. section 10.12
- Prefer optimistic isolation when lock contention causes performance problems, while ensuring TempDB has sufficient I/O capacity and free space. section 10.14
Listing 10.31 Enabling Snapshot isolation
ALTER DATABASE MarketingArchive SET ALLOW_SNAPSHOT_ISOLATION ON ;
Enable Snapshot isolation for MarketingArchive with ALTER DATABASE. section 10.12
Listing 10.33 Examining the Version Store
SELECT * FROM sys.dm_tran_version_store ; COMMIT
Inspect versioned rows through the TempDB Version Store using sys.dm_tran_version_store. section 10.12
