ALLOW_SNAPSHOT_ISOLATION is similar to READ_COMMITTED_SNAPSHOT, but it is based at the transaction level, not the statement level. When the ALLOW_SNAPSHOT_ISOLATION is turned on at the database level and the TRANSACTION ISOLATION LEVEL SNAPSHOT isolation level is turned on for the transaction (using the SET command), all statements see a snapshot of the data as it existed at the start of the transaction. The biggest benefit of this isolation level is that it gives you a transactionally consistent view of your data. Any data read will be the most recent committed version of the data.
To take advantage of ALLOW_SNAPSHOT_ISOLATION, you not only have to turn it on at the database level, but also must modify your current code so that the TRANSACTION ISOLATION LEVEL SNAPSHOT isolation level is set for it. Since this is a new feature of SQL Server 2005, you have to either modify your old code, or wait until you write new code to take advantage of this feature.
Unlike the four older isolation levels, the two new isolation levels are implemented differently, which can be a little confusing at first. Keep the following in mind:
- To use either of these two new isolation levels, they have to be turned on at the database level. This means that all transactions in this database will implement row versioning.
- You can turn on either READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION at the database level, or you can turn on both READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION at the database level.
- If READ_COMMITTED_SNAPSHOT is turned on, then any statement or transaction that is SET to (or defaults to) READ_COMMITTED will use row versioning. If you SET a different isolation level, then that setting overrides the default behavior.
- If you want to take advantage of ALLOW_SNAPSHOT_ISOLATION, then this setting must be turned on at the database level, AND you must use the SET command to specify TRANSACTION ISOLATION LEVEL SNAPSHOT for the connection.
To turn these options on and off at the database level, you use the ALTER DATABASE command. Below are some examples:
ALTER DATABASE AdventureWorks
SET READ_COMMITTED_SNAPSHOT ON
ALTER DATABASE AdventureWorks
SET READ_COMMITTED_SNAPSHOT OFF
ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION OFF
While both of these new isolation levels have pros and cons, READ_COMMITTED_SNAPSHOT is generally recommended over ALLOW_SNAPSHOT_ISOLATION for the following reasons:
- It uses less tempdb space.
- It works with distributed transactions.
- It works with most pre-existing applications.
- It is not vulnerable to update conflicts.
While the main advantage of these new isolation levels is better overall performance, there can be some downsides to using them. They include:
- Increased use of the tempdb. You may want to watch its size more closely, and also place it on an isolated disk or array for better performance.
- Update transactions will be slowed down because of the need to version data rows as updates are made. If your database is very data modification intensive, then these isolation levels may not be appropriate for you.
- They should not be used if you expect update conflicts in you data (more than one connection will update the same data at the same time).
If you decide to use one or both of these two new isolation levels, you can quickly determine the status of the database-wide setting by running this statement:
SELECT name, snapshot_isolation_state_desc, is_read_committed_snapshot_on FROM sys.databases
This produces results like the following:
In addition, there are a series of DMVs (dynamic management views) that you can watch to monitor the effect of using these isolation levels. They include:
- sys.dm_db_file_space_usage
- sys.dm_db_session_space_usage
- sys.dm_db_task_space_usage
- sys.dm_tran_top_version_generators
- sys.dm_tran_version_store
- sys.dm_tran_active_snapshot_database_transactions
- sys.dm_tran_transactions_snapshot
- sys.dm_tran_current_transaction
- sys.dm_tran_current_snapshot
In addition to the above DMVs, there are a number of performance counters you can also use to monitor performance related to these new isolation levels. They include:
- Free Space in tempdb (kb)
- Version Store Size (kb)
- Version Generation Rate (kb/s)
- Version Cleanup Rate (KB/s)
- Version Store Unit Creation
- Version Store Unit Truncation
- Update Conflict Ratio
- Longest Transaction Running time
- Transactions
- Snapshot Transactions
- Update Snapshot Transactions
- NonSnapshot Version Transactions
As you can see, there is a lot to learn about these new isolation levels. You can find even more information in Books Online. If you feel you might be able to take advantage of one or both of these options, be sure you thoroughly test them in a test database before rolling them out to production. Like most performance enhancement tips, this one may help performance, or it may hurt performance. It is your job as the DBA to sort this out for your particular database applications.
]]>