Isolation snapshot et version tag

Une question que posent souvent les développeurs : comment éviter les verrous bloquants à la lecture des données ?

Une des (mauvaises) solutions est d'utiliser la directive NOLOCK (ou isolation READ UNCOMMITTED) : c'est un faux ami car cela ne garantit pas la cohérence transactionnelle des données que l'on lit.

L'autre solution est d'utiliser un niveau d'isolation "optimiste", par versionning : SNAPSHOT ou READ COMMITTED SNAPSHOT (RCSI).

Le principe de l'isolation par versionning est le suivant : plutôt que de poser un verrou partagé pour lire les données (ce qui empêchera de lire des données en cours de modification), SQL Server va renvoyer la dernière version validée des données : il utilise pour cela le "Version Store", qui stocke de manière transparente cette version des données dans Tempdb.

Un effet de bord mal connu de l'isolation SNAPSHOT (ou RCSI) est qu'il ajoute un pointeur de 14 octets à chaque ligne de données dès qu'on la modifie, ce qui peut poser certains problèmes. Voyons cela de plus près.

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 !

Commentaires

1. Le jeudi 16 juillet 2020, 12:22 par Jean-Yves

Bonjour,

Exactement le cas qui vient de m'arriver. Deux bases, deux environnements de test, structures identiques, données potentiellement différentes, mais index (composites) remplis de façon identique. Suivant les colonnes indexées, les index prenaient entre 50 et 100% d'espace disque en plus, à cardinalité identique, avec une fragmentation nulle (0 à 0.01%).

La réponse: RCSI sur les deux bases, et les index de l'une reconstruits ONLINE quand ceux de l'autre se faisaient OFFLINE. CQFD.

Merci pour la lumière (au bout du tunnel) :)

Ajouter un commentaire

Le code HTML est affiché comme du texte et les adresses web sont automatiquement transformées.

Fil des commentaires de ce billet