When SQL Server's read committed snapshot is on (and thus some amount of row versioning is necessary), is there any additional overhead caused merely by enabling snapshot isolation?
Snapshot isolation causes some overhead by making rows a little larger and requiring some tempdb to store previous versions until commit, even for non-snapshot transactions. As I understand it, this infrastructure is also necessary for read-committed-snapshot. Can I assume therefore that enabling snapshot isolation has no additional overhead for non-snapshot transactions whenever read committed snapshot is already on?
Best Answer
The underlying mechanics is pretty much the same. However, in snapshot isolation the versions are potentially kept significantly longer causing a higher strain on the resources overall.