6

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?

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.

  • Ah right, so that means even if there are few or even no snapshot transactions in flight, enabling snapshot transactions might slow things down slightly - since the DB must assume one will be started at any time. I'll see how it works out on my workload; by the looks of it it's not going to cause very significant problems at least. – Eamon Nerbonne Mar 21 '13 at 21:50
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.)

  • I'm not sure where the OP mentioned amazon, but the suggestion to put tempdb on the fastest drive makes sense anyway depending on the workload. Worth a test I think – Tom V Dec 10 '15 at 19:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.