Snapshot Isolation

Gerald Britton takes us through snapshot isolation in SQL Server:

Snapshot isolation avoids most locking and blocking by using row versioning. When data is modified, the committed versions of affected rows are copied to tempdb and given version numbers. This operation is called copy on write and is used for all inserts, updates and deletes using this technique. When another session reads the same data, the committed version of the data as of the time the reading transaction began is returned.

By avoiding most locking, this approach can greatly increase concurrency at a lower cost than transactional isolation. Of course, “There ain’t no such thing as a free lunch!” and snapshot isolation has a hidden cost: increased usage of tempdb.

Gerald covers both varieties, Read Committed Snapshot Isolation and proper Snapshot Isolation. RCSI is definitely worth understanding in almost any environment, and even Snapshot Isolation has its uses.

Related Posts

Accelerated Database Recovery and Filegroups

Randolph West shows a change to Accelerated Database Recovery in SQL Server 2019 CTP 3.2 and later: ADR makes use of a per-database version store, instead of putting everything in the transaction log and TempDB. In most cases, the payoff more than makes up for valuable transaction log and TempDB storage. In my testing, enabling […]

Read More

Calculated Columns and Memory Usage in Analysis Services

Teo Lachev troubleshoots a customer issue: Scenario: A client reports a memory spike during processing. They have a Tabular semantic model deployed to Azure Analysis Services. They fully process the model daily. The model normally takes less than 50 GB RAM but during processing, it spikes five times and Azure Analysis Services terminates the processing task […]

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Categories

August 2019
MTWTFSS
« Jul  
 1234
567891011
12131415161718
19202122232425
262728293031