7

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?

Eamon Nerbonne
  • 203
  • 1
  • 7

2 Answers2

7

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.

Sebastian Meine
  • 9,163
  • 1
  • 28
  • 32
0

Enabling snapshot isolation mode in SQL Server increases the i/o load on the tempDB. If you are on Amazon EC2 you might consider putting your tempDB file on your fastest drive, either the local volatile SSD or EBS with high iops.

(btw, snapshot isolation mode makes SQL Server behave more like Oracle's default - which reduces row-level contention.)

Robert-Ryan.
  • 1
  • 1