Snapshot Isolation and System Views

Snapshot Isolation is a database-scoped configuration that allows for row versioning within a database to improve concurrency while removing the barriers of blocking and deadlocking that come from such things as low resources, bad code, or even worse code.  The question came up today on Twitter as to whether system views are affected by Snapshot Isolation just as user tables are.  I initially figured so - after all this is a database-wide configuration - but you never know when it comes to what lay 'neath the covers of SQL Server.  The only way I could offer an answer was to test this out myself.  Luckily one of the chapters in my book with Louis Davidson on the subject of Dynamic Management Objects was written by me so I was ready to jump right in and see if - at least in regards to this situation and system view - if there was an impact on row versioning when system views are involved.

In order to answer the question at hand I figured we would need to follow this process:

1. Create a testing database and set it up to use Snapshot Isolation

2. In two separate sessions create tables inside separate transactions without committing or rolling back the transactions.  If we left the transactions open we could observe their impact in the Dynamic Management Views as they pertain to sessions and transactions.

3. Repeat the process with two additional sessions against a database not running under Snapshot Isolation

4. Create a simple query and look for the results.

Code for First Session: (Snapshot Isolation)

CREATE DATABASE [Test_Snapshot_DB];GOALTER DATABASE [Test_Snapshot_DB]SET ALLOW_SNAPSHOT_ISOLATION ON;ALTER DATABASE [Test_Snapshot_DB]SET READ_COMMITTED_SNAPSHOT ON;GOBEGIN TRANSACTION CREATE TABLE Test_Snapshot_DB.dbo.foo(id INT);

Code for Second Session: (Snapshot Isolation)

BEGIN TRANSACTION CREATE TABLE Test_Snapshot_DB.dbo.different_foo(id INT);

Code for Third Session: (Not Under Snapshot Isolation)

CREATE DATABASE [iDBA];GOBEGIN TRANSACTION CREATE TABLE iDBA.dbo.foo(id INT);

 Code for Fourth Session: (Not Under Snapshot Isolation)

BEGIN TRANSACTION CREATE TABLE iDBA.dbo.foo2(id INT);

At this point what we now have are two databases, one in Snapshot Isolation and one that is not, with two uncommitted table creation DDL statements running against them. This is where we'll first confim that the databases are configured as stated above (using a simple query against sys.databases) then examine their row versioning state in order to reach the conclusion and see if my initial assumptions are correct. (Care to place bets?)

Snapshot Isolation Status

The following query can easily tell you if Snapshot Isolation is configured for a database.  In this case we're particularly interested in the two in our test:

SELECT name, is_read_committed_snapshot_onFROM sys.databases WHERE name IN ('iDBA', 'Test_Snapshot_DB');

OK, so as expected iDBA is not running under row versioning and we should expect row versioning behavior from Test_Snapshot_DB.  Let's finally see if the two databases behave differently then as they pertain to system views.  We'll do so by querying their behavior from sys.dm_tran_active_snapshot_database_transactions and then fleshing out a few more details from the sys.dm_exec_sql_text Dynamic Management Function:

SELECT DB_NAME(ST.dbid) AS the_database , ST.text , ASDT.is_snapshot , ASDT.first_snapshot_sequence_num FROM sys.dm_tran_active_snapshot_database_transactions ASDT LEFT JOIN sys.dm_exec_connections eC   ON ASDT.session_id = eC.most_recent_session_id CROSS APPLY sys.dm_exec_sql_text(eC.most_recent_sql_handle) AS STORDER BY 1;

It appears my original assumption was wrong and that (at least in this case) system views don't necessarily follow the same behavior for row versioning as we would expect when Snapshot Isolation is on for a specific database.  is_snapshot is valued at 0 for each of these transactions and the associated value for first_snapshot_sequence_num is NULL.  If I go ahead and commit each of these four transactions then execute the following queries against each we'll see by comparison what we would expect for behavior under Snapshot Isolation for user tables and row versioning: