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:
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)
insert into foo..t2_snapshot values (1)
-- show the max rowsize in bytes
from sys.dm_db_index_physical_stats (db_id('foo'),
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
-- 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'
Now I run the query
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
When RowVersioning is enabled on a Database (even with ROLLBACK IMMEDIATE), it takes about a second (with a small or empty database) or several minutes to complete. You say that the extra Data needed for each row is added in a lazy way only when needed (my assumption was that it does it when executing the ALTER DATABASE command), so
1. what happens when executing the ALTER DATABASE Command?
2. is there a kind of "formula" how long will it takes approximately for an existing Database? Does it depend solely on the Database-Size?
No formula. It depends on the existing transactional activity in the database.
Here is from BOL:
For SNAPSHOT ISOLATION::
When you set ALLOW_SNAPSHOT_ISOLATION to a new state (from ON to OFF, or from OFF to ON), ALTER DATABASE does not return control to the caller until all existing transactions in the database are committed. If the database is already in the state specified in the ALTER DATABASE statement, control is returned to the caller immediately. If the ALTER DATABASE statement does not return quickly, use sys.dm_tran_active_snapshot_database_transactions to determine whether there are long-running transactions. If the ALTER DATABASE statement is canceled, the database remains in the state it was in when ALTER DATABASE was started. The sys.databases catalog view indicates the state of snapshot-isolation transactions in the database. If snapshot_isolation_state_desc = IN_TRANSITION_TO_ON, ALTER DATABASE ALLOW_SNAPSHOT_ISOLATION OFF will pause six seconds and retry the operation.
To set READ_COMMITTED_SNAPSHOT ON or OFF, there must be no active connections to the database except for the connection executing the ALTER DATABASE command. However, the database does not have to be in single-user mode. You cannot change the state of this option when the database is OFFLINE.
I've done a few tests and reorganizing the index does not free the extra bytes. Am I correct? I understand that the difference is not big, because only affects to the modified rows, but there are scenarios where customers cannot afford rebuilding the index -- specially non-EE. Would be expensive including that code in the reorganize algorithm?
Thank you for this post Sunil.
Can you please send me a simple repro? thanks
The script is based on you example; in the original script before querying the DMV I inserted a waitfor because takes a bit refreshing the DMV; some of the comments are in spanish, but the TSQL is easy.
Thank you for checking it Sunit.
-- Implicaciones en filas de niveles de aislamiento row-versioning
-- Eladio Rincón (script basado en Sunil Agarwal )
-- Solid Quality Mentors
set nocount on
alter database Northwind
set allow_snapshot_isolation Off
set READ_COMMITTED_SNAPSHOT off
if exists (select * from sys.tables where name = 't2_snapshot')
drop table dbo.t2_snapshot
-- tabla de 1000 y pico bytes
create table dbo.t2_snapshot (
c1 int identity
, c2 char(1000) default('a')
, constraint pk_t2_snapshot primary key (c1)
-- inserción de 1000 filas
insert dbo.t2_snapshot (c2)
select top (1000) 'a'
from [Order Details]
-- habilitar snapshot_isolation
-- no requiere libre de conexiones
-- si para read-commited-snapshot
set allow_snapshot_isolation ON
, avg_record_size_in_bytes, max_record_size_in_bytes
from sys.dm_db_index_physical_stats (
, null, null, 'DETAILED')
index_level min_record_size_in_bytes avg_record_size_in_bytes max_record_size_in_bytes
----------- ------------------------ ------------------------ ------------------------
0 1011 1011 1011
1 11 11 11
update dbo.t2_snapshot set c2 = 'b'
where c1 = 1
0 1011 1011.014 1025
alter index pk_t2_snapshot
The reorganize (i.e. defrag) does not remove the versioning information but offline rebuild of index can remove it. But you will need to disable snapshot isolation. Please try the script that I sent in my blog to start off.
The versioning informations is only removed when you rebuild the index. Reorganize does not remove the extra bytes in any case (with or without snapshot enabled).
Maybe si my version:
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
this is the script I've used. the only difference with your:
1) table with clustered index
2) table in Northwind Database
this is the complete repro:
truncate table dbo.t2_snapshot
select max(max_record_size_in_bytes) max
null, null, 'DETAILED')
Authors: Alexei Khalyako, Stuart Ozer Contributor: Sanjay Mishra Technical Reviewers: Mark Souza, Denny
Recently I worked with a partner who was seeing some interesting behavior. Upon rebuilding their indexes
I'm sorry, but your snap response to turn SI on without fully understanding the implications of what you are saying strikes me as way too broad-based Sunil. I had a client who spoke to some 'expert' who told them to go back and do exactly that in the database - and this so-called expert had NO IDEA what that may or may not have affected. If your app hasn't been designed for this behavior, then can you explain why you feel turning this on just solves the world's problems?
Please send me the details..By looking at the comment history, I am not fully sure what the specific question is.
Just Okay ...