Overhead of Row Versioning

re: Overhead of Row Versioning

Hasham Niaz — Wed, 05 Sep 2012 06:13:22 GMT

Just Okay ...

re: Overhead of Row Versioning

Sunil Agarwal — Tue, 23 Nov 2010 23:02:10 GMT

Please send me the details..By looking at the comment history, I am not fully sure what the specific question is.

thanks

Sunil

re: Overhead of Row Versioning

SQL Wonk — Tue, 23 Nov 2010 22:56:09 GMT

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?

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

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

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

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

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 — 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 — Fri, 06 Jun 2008 20:16:02 GMT

Can you please send me a simple repro? thanks