Learn more about SQL Server tools

   
   




































Latest from MSSQLTips














Snapshot Isolation in SQL Server 2005

MSSQLTips author Edgewood Solutions By:   |   Read Comments   |   Related Tips: More > Database Administration

Problem
Row versioning, although not specifically documented under that phrase, was around in previous versions of SQL Server but was reserved for maintaining data integrity (i.e., during UPDATE statements) and replication. In SQL Server 2005 Microsoft implemented row versioning as a method of gaining access to data, known as Snapshot Isolation Level is a means for read transactions to not block write transactions.

Solution
Snapshot isolation works by maintaining versions of modified data in a store (version store) in TEMPDB. Read transactions that run under the snapshot isolation level do not acquire shared locks, although schema locks are acquired for a brief period. Accessing data using snapshot isolation decreases the possibility of blocking transactions attempting to modify data. In addition, this isolation level facilitates index maintenance using the ONLINE option because reads are performed on the snapshot, not the table itself.

Enabling snapshot isolation is a two-step process:
1. Enable the READ_COMMITTED_SNAPSHOT option
2. Enable the ALLOW_SNAPSHOT_ISOLATION option

Statements used to enable snapshot isolation options

Both of these options must be ON in order to use the snapshot isolation level, otherwise you'll get the following error:

Error when attempting to use snapshot isolation when options not enabled

When coding to use the SNAPSHOT ISOLATION LEVEL, use the SET TRANSACTION ISOLATION LEVEL statement:

Statement used to set the transaction isolation level to SNAPSHOT

Dynamic management views (DMV's) are available that allow administrators to view information about row versioning:

  • sys.dm_tran_top_version_generators (may affect system performance): Returns information on objects creating the most versions
  • sys.dm_tran_active_snapshot_database_transactions: Returns information about processes that generate or could be accessing version rows
  • sys.dm_tran_transactions_snapshot: Returns the sequence number of transactions active when each snapshot transaction starts
  • sys.dm_tran_version_store: Returns all version records currently in the version store
  • sys.dm_tran_current_transaction: Returns state information of the transaction in the current session
  • sys.dm_tran_current_snapshot: Returns all active transactions at the time when the current snapshot transaction occurs
  • sys.dm_db_file_space_usage: Returns information on the amount of space used by TEMPDB
  • sys.dm_db_session_space_usage: Returns the number of pages allocated and deallocated by TEMPDB by session
  • sys.dm_db_task_space_usage: Returns the number of pages allocated and deallocated by TEMPDB by task

Next Steps



Last Update: 10/9/2006





About the author
MSSQLTips author Edgewood Solutions
Edgewood Solutions is a technology company focused on Microsoft SQL Server and founder of MSSQLTips.com.

View all my tips
Related Resources


print tip Print  
Become a paid author


Learn more about SQL Server tools








Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     





 
More SQL Server Solutions



 

Follow

Get Free SQL Tips

Twitter

LinkedIn

Google+

Facebook

Pinterest

RSS

Learning

DBAs

Developers

BI Professionals

Careers

Q and A

Today's Tip

Resources

Tutorials

Webcasts

Whitepapers

Tools

Search

Tip Categories

Search By TipID

Authors

Community

First Timer?

Pictures

Free T-shirt

Contribute

Events

User Groups

Author of the Year

More Info

Join

About

Copyright

Privacy

Disclaimer

Feedback

Advertise

Copyright (c) 2006-2015 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.