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?

Improve this question

2 Answers 2

Reset to default
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.

Improve this answer
1
  • 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, 2013 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.)

Improve this answer
1
  • 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, 2015 at 19:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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