Datafly.pro

Nous allons créer deux bases de données identiques, contenant une table avec quelques données : Sample1 et Sample2

USE master
GO
-- Create 2 identical databases
CREATE DATABASE Sample1
GO
USE Sample1
CREATE TABLE Table1
    (
     ID INT IDENTITY CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED
    ,NOM CHAR(40)
    ,PRENOM CHAR(40)
    )
GO
INSERT  INTO [Table1]
        ( [NOM], [PRENOM] )
VALUES  
 ( 'Manvussa', 'Gérard' )
,( 'Bonneau', 'Jean' )
,( 'Lingot', 'Albert' )
,( 'Versère', 'Annie' )
,( 'Thérieur', 'Alain' )
GO
CREATE DATABASE Sample2
GO
USE Sample2
CREATE TABLE Table1
    (
     ID INT IDENTITY CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED
    ,NOM CHAR(40)
    ,PRENOM CHAR(40)
    )
GO
INSERT INTO [Table1]
([NOM],[PRENOM])
SELECT NOM,PRENOM
FROM Sample1..Table1;

Passons ces deux bases de données en isolation READ COMMITTED SNAPSHOT

-- Now set RCSI isolation
USE master
GO
ALTER DATABASE Sample1 SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
GO
ALTER DATABASE Sample2 SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
GO

Vérifions : les lignes sont strictement de même longueur :

-- Compare lines sizes
SELECT 'Sample1' AS db,
       min_record_size_in_bytes,
       max_record_size_in_bytes,
       avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID('sample1'), OBJECT_ID('sample1.dbo.table1'), NULL, NULL, 'DETAILED')
UNION ALL
SELECT 'Sample2' AS db,
       min_record_size_in_bytes,
       max_record_size_in_bytes,
       avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID('sample2'), OBJECT_ID('sample2.dbo.table1'), NULL, NULL, 'DETAILED');
GO

Faisons une modification des lignes dans la base Sample2 (mais sans même changer les valeurs)

-- Update all rows in Sample2 -> version tag
USE Sample2
GO
UPDATE Table1 SET NOM = NOM
GO

Si nous vérifions maintenant la taille des lignes, nous pouvons voir que le tag de version a été ajouté, passant de 91 à 105 octets (+14 octets) :

On peut le voir plus en détail en allant examiner directement la page de données dans la base Sample2 :

DBCC TRACEON(3604)
GO
-- With version tag (sample2)
DECLARE @sqlcmd NVARCHAR(MAX)
SET @sqlcmd  = 'DBCC PAGE (''Sample2'','
+ (SELECT  TOP (1) CAST(allocated_page_file_id AS VARCHAR(8)) FROM sys.dm_db_database_page_allocations(DB_ID('Sample2'),OBJECT_ID('Sample2..Table1'),1,NULL,'DETAILED')
WHERE page_type = 1)+','+(SELECT  TOP (1) CAST(extent_page_id AS VARCHAR(8)) FROM sys.dm_db_database_page_allocations(DB_ID('Sample2'),OBJECT_ID('Sample2..Table1'),1,NULL,'DETAILED')
WHERE page_type = 1)+',3)'
PRINT @sqlcmd
EXEC (@sqlcmd)
GO

On bien voit ici la présence du tag de version à la fin de la ligne :

Jusqu'ici, sur notre toute petite base de données, c'est sans grave conséquence. Mais imaginons que nous ayons fait cette même opération sur une table beaucoup plus volumineuse : ici avec 50 millions de lignes.

Avant : tout va bien, mon index vient d'être reconstruit et n'est pas fragmenté :

Après avoir modifié toutes les lignes, on voit que l'ajout du tag de version a fait exploser la fragmentation :

Pensez donc au FILLFACTOR au cas où...

A noter que la reconstruction d'un index OFFLINE supprime les tags de version (notre table revient à l'état d'origine). Mais ce n'est pas le cas avec un rebuild ONLINE qui les laisse intacts...

Notez aussi que si vous mettez en place un réplica Always On en lecture seule, le tag de version est ajouté (sur le primaire comme sur les secondaires) pour assurer l'isolation snapshot du réplica readonly !