Data versioning is one of the most important features of SQL 2005. Different data readers can obtain different values from the same record, like in Oracle. It can be achieved using SNAPSHOT ISOLATION LEVEL or DATABASE SNAPSHOTS.

In this study, we measure how these two new SQL Server 2005 features affect performance of data readers and data writers.

Preparation

At first, we prepare a test table:

create table NUM
   (n int primary key, m money)
GO

And populate it with 100K records.

set nocount on
declare @n int
set @n=100000
while @n>0 begin 
  insert into NUM select @n,@n*100
  set @n=@n-1
  end
GO

To measure the performance of the data writer, we use the following code:

begin transaction
declare @t1 datetime, @n int
set @t1=getdate()
set @n=10
while @n>0 
  begin
  update NUM set m=m+$0.01
  set @n=@n-1 
  end
select datediff(ms,@t1,getdate())/10
commit transaction

We update the column'm' (all records) 10 times, and measure the execution time. Don't forget to exclude the first measurement (because of caching, extension of the data files etc).

Data reader works faster than data writer, so I repeat reading 100 times:

declare @t1 datetime, @n int, @m money
set @t1=getdate()
set @n=100
while @n>0 
  begin
  select @m=sum(m) from NUM
  set @n=@n-1 
  end
select datediff(ms,@t1,getdate())/10

In my case, the writer takes 319 ms to execute, while the reader - only 49.4ms

Snapshot Isolation Level

To enable data versioning, use one of the following statements, or a combination of both (check what is appropriate in your case):

ALTER DATABASE Snap SET READ_COMMITTED_SNAPSHOT ON
ALTER DATABASE Snap SET ALLOW_SNAPSHOT_ISOLATION ON

(test database is called 'Snap'). For this study we used

READ_COMMITTED_SNAPSHOT ON

The result is the following:

For obvious reasons, with the SNAPSHOT isolation level it takes longer, as SQL server must keep track of all changes.

But was the reader also affected? If you re-run the batch for the reader, you get the same result. To measure the effect precisely, we have to force the reader to 'go backwards in time', reconstructing data rows as they existed before. To do that, we have to modify our scripts.

We open 2 connections. One of these I call connection A, another one - B. At first, we create an application lock in the connection A:

Connection A:

exec sp_getapplock 'MyLock', 'Exclusive', 'Session', -1

Data reader will read data only when this lock is released:

Connection B:

declare @t1 datetime, @n int, @m money
exec sp_getapplock 'MyLock', 'Exclusive', 'Session', -1
exec sp_releaseapplock 'MyLock', 'Session'
set @t1=getdate()
set @n=100  
while @n>0 
  begin
  select @m=sum(m) from NUM
  set @n=@n-1 
  end
select datediff(ms,@t1,getdate())/100

And now the most important part. Data writer releases a lock after N loops (I vary N). Immediately after releasing a lock, it stops for 10 seconds to give time for the reader to complete without competing with it.

Again, connection A

begin transaction
declare @t1 datetime, @n int
set @t1=getdate()
set @n=10
while @n>0 
  begin
  if @n=10-8 /* vary from 0 to 9 */
    begin
    exec sp_releaseapplock 'MyLock', 'Session'
    waitfor delay '00:00:10'
    end
  update NUM set m=m+$0.01
  set @n=@n-1 
  end
select datediff(ms,@t1,getdate())/10
commit transaction

Results:

While it is obvious that reading the modified data takes much more time (it is more then 3 times slower), I have absolutely no explanation, why the more you modify the data, the faster you read it. Below is a graphic representation of the same result:

Database snapshots

Database snapshots are created by a command like the following one, database name, file name and location may vary:

CREATE DATABASE Snap_1 
  ON ( NAME = Snap, 
     FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\snap_1.ss' ) 
  AS SNAPSHOT OF Snap;

Don't forget to disable snapshot isolation level after the first experiment - just to measure both effect separately:

ALTER DATABASE Snap SET READ_COMMITTED_SNAPSHOT Off;
ALTER DATABASE Snap SET ALLOW_SNAPSHOT_ISOLATION Off;

I had created 5 database snapshots, running the data writer every time:

Result:

It is almost linear:

This is quite logical. Apparently, database snapshot takes 40% of performance each.

For the data reader, the measurement does not show any significant difference when it reads a database directly or when it reads a database snapshot.