DaveWentzel.com            All Things Data

Snapshot Isolation Level Notes

 

Definition
is essentially a means for read transactions to not block write transactions.  Read transactions no longer acquire shared locks using snapshot isolation level. 
 
Syntax
 
ALTER DATABASE

SET READ_COMMITTED_SNAPSHOT ON;

go

ALTER DATABASE

SET ALLOW_SNAPSHOT_ISOLATION ON;

go

select snapshot_isolation_state_desc, is_read_committed_snapshot_on

from sys.databases;

go
 
 
Problems


check sql logs for "tempdb full" row versioning errors:

3967

3966

3959

3958


 
Monitoring Row Versioning
You can monitor it by using sys.dm_tran_current_transaction, sys.dm_tran_top_version_generators, sys.dm_tran_version_store, sys.dm_tran_active_snapshot_database_transactions, and sys.dm_tran_transactions_snapshot
 
PerfMon Counters
Transactions\Free Space in tempdb (KB)
Transactions\Longest Transaction Running Time
Transactions\NonSnapshot Version Transactions
Transactions\Snapshot Transactions
Transactions\Transactions
Transactions\Update Conflict Ratio
Transactions\FrUpdate Snapshot Transactions
Transactions\Version Cleanup rate (KB/s)
Transactions\Version Generation rate (KB/s)
Transactions\LongeVersion Store Size (KB)
Transactions\Version store unit count
Transactions\Version store unit creation
Transactions\Version Store unit truncation
 
 
How to Size for the Version Store


[size of common version store] = 2 * [version store data generated per minute] * [longest running time (minutes) of the transaction]
See the attachment to this note for a perfmon settings .htm file. 
 
Overhead/Tradeoffs
1)each UPDATE/DELETE generates a row version. 
2)tempdb
3)Queries need to traverse the row chains.  This could lead to tons of random IO since row versions might not be on contiguous pages. 
4)row versioning adds a 14 byte overhead to each row that tracks the transaction id and rowid. This is only added for new rows AFTER row versioning has been enabled, or when an existing row (inserted prior to turning on the feature) gets modified.  The extra 14bytes is removed when the option is later disabled. 
 
When do you enable it?
When your application experiences reader/writer blocking. 
 
Some Other Issues to be Aware Of
SQL Server automatically disables snapshot isolation when checking FKs (and then reenables it).  This ensures the integrity of your data, but at the cost of the concurrency that SI is supposed to bring.  If you use "custom" data integrity tools (UDFs, triggers, computed columns, schema-bound views WITH CHECK OPTION) then you should test this thoroughly with SI.  Unlike with FK constraints you will have to manually reduce the concurrency to ensure integrity is maintained. 

 

Add new comment