First published on MSDN on Mar 29, 2008

Last week I was teaching a class on snapshot isolation and we discussed the overheads of snapshot isolation. There are three main overheads that you need to be aware of when you enable snapshot isolation read-committed-snapshot on a database. First, each UPDATE/DELETE operation generates a row version. Note, that the row version is not generated when you INSERT a new row but a 14 byte overhead (described below) is added to the inserted row . There is an exceptions to this, but I will skip that discussion here. Second, row versions are created and kept in tempdb that leads to increase space usage and IOs there. Third, the queries run under snapshot isolation or read-committed-snapshot need to traverse the row version chain which may lead to as many logical IOs and potentially as many 'random' physical IOs because row versions for a given row are not necessarily on the same or even contiguous physical page(s). It is interesting to note that if we did not traverse the row versions, the query would have blocked otherwise. So basically, at some expense of IO/CPU, the SQL Server can return the transactional consistent results without incurring blocking. We recommend enabling row versioning based isolation levels only when your application is incurring reader/writer blocking.

One question that came up was on the overhead of row versioning on the data or index row. When you enable snapshot isolation or read-committed-snapshot option on the database, the SQL Server starts adding a 14 byte overhead to each row to keep the following information:

• XTS (transaction sequence number). It takes 6 bytes. This is used for marking the XSN that did the DML operation on the row


• RID (row identifier) that points to the versioned row. It takes 8 bytes.

But this overhead is only added when an existing row is actually modified or if the database was already enabled for either of these options. Let me give a very simple example.

create table foo..t2_snapshot (c1 int)

go

insert into foo..t2_snapshot values (1)

go

-- show the max rowsize in bytes

select max_record_size_in_bytes

from sys.dm_db_index_physical_stats (db_id('foo'),

object_id('foo.t2_snapshot'),

null, null, 'DETAILED')

This will show a max row size of 11 bytes (note, the 7 bytes are the metadata overhead within the row and other 4 bytes store the integer value). Now, let me enable snapshot isolation on the database foo. Note, this is a metadata operation and it will NOT change the existing rows in the tables to add extra 14 bytes.

alter database foo set allow_snapshot_isolation ON

go

-- you can verify the datbaase state using the following

select is_read_committed_snapshot_on, snapshot_isolation_state_desc,

snapshot_isolation_state from sys.databases where name='foo'

go

Now I run the query

select max_record_size_in_bytes

from sys.dm_db_index_physical_stats (db_id('foo'),

object_id('foo.t2_snapshot'),

null, null, 'DETAILED')

You will notice that the row length still remains 11 bytes. No extra 14 bytes overhead even though the database foo has been enabled for snapshot isolation.  Let us now update the data row as follows

update foo..t2_snapshot set c1 = 2

After the update, you will notice the length of the row has become 25 bytes. In other words, there is a 14 byte overhead. Also, the new rows that you insert will have a length of 25 bytes (including row versioning overhead of 14 bytes).There are two interesting observations to be made. First, an update of a fixed length column may lead to page splits because the length of the row has increased. This only happens the first time the row is updated. Second, the increased row length will cause the table size to grow. A typical row size in production is around 300 bytes. So this means that snapshot isolation or read-committed-snapshot will cause 5% growth in the size of the table (in the worst case when all the rows were updated). One point to keep in mind is that new rows inserted after SI/RCSI, will have this 14 byte overhead even though there is no row version to point to.,

Now, if I disable the snapshot isolation and update the same row again, the extra 14 bytes will go away. This is because row versioning has been disabled. I can do the same by rebuilding the index

Thanks

Sunil