Overhead of Row Versioning

re: Overhead of Row Versioning

GProssliner — Mon, 31 Mar 2008 10:35:59 GMT

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?

Thank you!

re: Overhead of Row Versioning

Sunil Agarwal — Mon, 31 Mar 2008 19:36:34 GMT

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.

FOR RCSI::

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.

re: Overhead of Row Versioning

Eladio Rincón — Fri, 06 Jun 2008 13:44:30 GMT

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.

Eladio

re: Overhead of Row Versioning

Sunil Agarwal — Fri, 06 Jun 2008 20:16:02 GMT

Can you please send me a simple repro? thanks

re: Overhead of Row Versioning

Eladio Rincón — Sat, 07 Jun 2008 00:42:36 GMT

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 )

-- http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/30/overhead-of-row-versioning.aspx

-- Solid Quality Mentors

set nocount on

use master

alter database Northwind

set allow_snapshot_isolation Off

alter database Northwind

set READ_COMMITTED_SNAPSHOT off

use northwind

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

alter database Northwind

set allow_snapshot_isolation ON

select

index_level, min_record_size_in_bytes

, avg_record_size_in_bytes, max_record_size_in_bytes

from sys.dm_db_index_physical_stats (

db_id('Northwind')

, object_id('t2_snapshot')

, 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

select

index_level, min_record_size_in_bytes

, avg_record_size_in_bytes, max_record_size_in_bytes

from sys.dm_db_index_physical_stats (

db_id('Northwind')

, object_id('t2_snapshot')

, null, null, 'DETAILED')

index_level min_record_size_in_bytes avg_record_size_in_bytes max_record_size_in_bytes

----------- ------------------------ ------------------------ ------------------------

0 1011 1011.014 1025

1 11 11 11

alter index pk_t2_snapshot

on dbo.t2_snapshot

reorganize

select

index_level, min_record_size_in_bytes

, avg_record_size_in_bytes, max_record_size_in_bytes

from sys.dm_db_index_physical_stats (

db_id('Northwind')

, object_id('t2_snapshot')

, null, null, 'DETAILED')

index_level min_record_size_in_bytes avg_record_size_in_bytes max_record_size_in_bytes

----------- ------------------------ ------------------------ ------------------------

0 1011 1011.014 1025

1 11 11 11

alter index pk_t2_snapshot

on dbo.t2_snapshot

rebuild

select

index_level, min_record_size_in_bytes

, avg_record_size_in_bytes, max_record_size_in_bytes

from sys.dm_db_index_physical_stats (

db_id('Northwind')

, object_id('t2_snapshot')

, 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

re: Overhead of Row Versioning

Sunil Agarwal — Sat, 07 Jun 2008 03:51:23 GMT

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.

re: Overhead of Row Versioning

Eladio Rincón — Sun, 08 Jun 2008 13:44:20 GMT

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:

select @@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

create table dbo.t2_snapshot (

c1 int identity

, c2 char(1000) default('a')

, constraint pk_t2_snapshot primary key (c1)

)

this is the complete repro:

use Northwind

truncate table dbo.t2_snapshot

insert dbo.t2_snapshot (c2)

select top (1000) 'a'

from [Order Details]

alter database Northwind

set allow_snapshot_isolation ON

update dbo.t2_snapshot set c2 = 'b'

where c1 = 1

select max(max_record_size_in_bytes) max

from sys.dm_db_index_physical_stats (

db_id('Northwind'),

object_id('dbo.t2_snapshot'),

null, null, 'DETAILED')

max

-------

1025

alter index pk_t2_snapshot

on dbo.t2_snapshot

reorganize

select max(max_record_size_in_bytes) max

from sys.dm_db_index_physical_stats (

db_id('Northwind'),

object_id('dbo.t2_snapshot'),

null, null, 'DETAILED')

max

-------

1025

alter database Northwind

set allow_snapshot_isolation Off

alter index pk_t2_snapshot

on dbo.t2_snapshot

reorganize

select max(max_record_size_in_bytes) max

from sys.dm_db_index_physical_stats (

db_id('Northwind'),

object_id('dbo.t2_snapshot'),

null, null, 'DETAILED')

max

-------

1025

alter index pk_t2_snapshot

on dbo.t2_snapshot

rebuild

select max(max_record_size_in_bytes) max

from sys.dm_db_index_physical_stats (

db_id('Northwind'),

object_id('dbo.t2_snapshot'),

null, null, 'DETAILED')

max

-------

1011

truncate table dbo.t2_snapshot

insert dbo.t2_snapshot (c2)

select top (1000) 'a'

from [Order Details]

alter database Northwind

set allow_snapshot_isolation ON

update dbo.t2_snapshot set c2 = 'b'

where c1 = 1

select max(max_record_size_in_bytes) max

from sys.dm_db_index_physical_stats (

db_id('Northwind'),

object_id('dbo.t2_snapshot'),

null, null, 'DETAILED')

max

-------

1025

alter index pk_t2_snapshot

on dbo.t2_snapshot

rebuild

select max(max_record_size_in_bytes) max

from sys.dm_db_index_physical_stats (

db_id('Northwind'),

object_id('dbo.t2_snapshot'),

null, null, 'DETAILED')

max

-------

1011

Bulk Loading Data into a Table with Concurrent Queries

Technical Notes — Mon, 06 Apr 2009 22:21:29 GMT

Authors: Alexei Khalyako, Stuart Ozer Contributor: Sanjay Mishra Technical Reviewers: Mark Souza, Denny

Why did the size of my indexes expand when I rebuilt my indexes?

Microsoft SQL ISV Program Management Team — Mon, 18 May 2009 21:27:55 GMT

Recently I worked with a partner who was seeing some interesting behavior. Upon rebuilding their indexes

Why did the size of my indexes expand when I rebuilt my indexes?

SQL Programmability & API Development Team Blog — Mon, 18 May 2009 21:27:58 GMT

Recently I worked with a partner who was seeing some interesting behavior. Upon rebuilding their indexes