How are clustered indexes updated when Snapshot Isolation is ON in SQL Server?

Question

I have a SQL Server 2008 database with SET ALLOW_SNAPSHOT_ISOLATION ON and a Person table with columns ID (primary key), and SSN (unique non-clustered index).

One of the rows in the database is ID = 1, SSN = 776-56-4453.

One one connection, this happens:

set transaction isolation level snapshot
begin transaction snapshot
while (1 = 1) select * from person where SSN = '777-77-7777'

Then on another connection:

update person set SSN = '555-55-5555' where ID = 1

As expected, the first connection continues to show the SSN as '777-77-7777' even after the second connection finishes execution. The execution plan for the first connection shows a 'clustered index seek' on SSN, but how can the first connection continue to use the index, if the index key has been updated on the other connection?

Does SQL server do anything special to keep multiple versions of the indexes to accommodate for this?

I am trying to understand the performance characteristics of Snapshot Isolation level, and so want to confirm that SQL Server is smart enough to use existing indexes even when retrieving stale data from the row's previous versions.

Answer 1

As far as I can tell (using DBCC IND and DBCC PAGE as described here and looking at sys.dm_tran_version_store) when updating the index key in a database with snapshot isolation enabled the following happens.

1. The original row is copied into the version store.
2. The original row is marked as a ghost and gets the Version Pointer updated to point to the correct location.
3. A new row is inserted for the new key value.
4. At some later point the ghost clean up process runs and removes the row.

The only difference in your scenario seems to be that the ghost cleanup process does not clean up the row until it is no longer required by an outstanding snapshot isolation transaction. i.e. the BTree contains rows for both the old and new key values until they are no longer required which allows an index seek on the old value to still work as before.

Answer 2

With snapshot isolation, SQL Server will put "snapshots" of the data being modified into tempDB and other connections will read from there. So your first connection here is reading its values and all the relevant indices involved from a snapshot copy in tempDB