Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 5 down vote accepted

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.

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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