[SQL Server] Eléments d’audit d’un fichier de données : état de l’organisation des lignes de données d’une page d’une table en « heap » et d’une page d’une table en index en cluster

L’objectif de ce billet est de valider certains concepts théoriques évoqués dans le billet [SQL Server] Un peu de théorie sur l’architecture d’un fichier de données…
et [SQL Server] Un peu de théorie sur les indexes…. Il s’agit d’observer la façon dont le moteur SQL organise les pages de données d’une table en heap et d’un index.

Audit de l’organisation des lignes d’une page d’une table en heap

Créons une base de données de test appelée MaBase, ainsi qu’une table appelée MaTable :

USE master
GO
CREATE DATABASE MaBase
GO
USE MaBase
GO
–- Création d'une table de test MaTable
CREATE TABLE MaTable(MaTable_ID INT,MaTable_Desc NVARCHAR(50))
GO
Insérons 5 enregistrements au hasard :
USE MaBase
GO
–- Insertion de 5 enregistrements au sein de MaTable
DECLARE @i INT
SET @i = 0
WHILE @i < 5
BEGIN
    INSERT INTO dbo.MaTable VALUES (@i,'ENREG' + CAST(@i AS VARCHAR(50)));
    SET @i = @i + 2
END
SET @i = 1
WHILE @i < 5
BEGIN
    INSERT INTO dbo.MaTable VALUES (@i,'ENREG' + CAST(@i AS VARCHAR(50)));
    SET @i = @i + 2;
END
GO

Consultons ensuite le détail de la page stockant les 5 lignes de données de la table MaTable et identifiée (via DBCC IND) comme étant la 73 :

DBCC TRACEON(3604)
GO
DBCC PAGE(MaBase, 1, 73, 1)
GO

Voici un extrait du résultat de la commande qui nous intéresse (partie données) :


Pour rappel, le numéro slot correspond à la position d’une ligne au sein de la table de décalage de lignes (appelée table d’offsets). L’ordre des slots est inversé par rapport à l’ordre des lignes de données.

Au sein du résultat ci-dessus, on note que la table d’offsets comporte 5 lignes de données (slots 0 à 4) avec des valeurs d’offset pour chaque ligne indiquant leur position dans la page. Par exemple, la 1ère ligne de données (ENREG0) d’une longueur de 27 octets correspond au slot 0 avec un offset ayant une valeur hexadécimale égale à 0x60 (soit, en décimal, 96). La 2ème ligne de données (ENREG2) de longueur 27 octets correspond au slot 1 avec un offset ayant une valeur hexadécimale égale à 0x7b (soit, en décimal, 123, ce qui signifie, pour rappel, que le décalage a été calculé comme suit : 96 (valeur d’offset du slot précédent) + 27 octets (taille de la ligne du slot 1) = 123). Etc…, etc…

En fait, au sein de la table heap, l’ordre physique des données correspond à l’ordre d’insertion des données réalisé par le script en début de section. Vous pouvez lire la table des offsets en fin de résultat.

Pour mieux étayer nos explications, voici un schéma de l’état de la page après ajout des 5 enregistrements :


Maintenant, supprimons la ligne de données ENREG2 d’identifiant MaTable_ID égal à 2 :

USE MaBase
GO
DELETE
FROM dbo.MaTable
WHERE MaTable_ID = 2
GO

Effectuons un petit SELECT pour apprécier le résultat :


Maintenant, ajoutons un enregistrement appelé ENREG5, avec pour identifiant 5 :

USE MaBase
GO
INSERT INTO dbo.MaTable VALUES (5,'ENREG' + CAST(5 AS VARCHAR(50)));
GO

Voici le résultat d’un nouveau SELECT sur la table :


On peut noter que le nouvel enregistrement a pris la place de celui précédemment supprimé… Pour mieux confirmer cela, consultons le résultat de la réexécution de la commande DBCC PAGE :

[…]
Slot 1, Offset 0xe7, Length 27, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Memory Dump @0x000000000B81C100
0000000000000000: 30000800 05000000 0200fc01 001b0045 †0…………..E
0000000000000010: 004e0052 00450047 003500†††††††††††††.N.R.E.G.5.
[…]

OFFSET TABLE:

Row – Offset

4 (0x4) – 204 (0xcc)

3 (0x3) – 177 (0xb1)

2 (0x2) – 150 (0x96)

1 (0x1) – 231 (0xe7)

0 (0x0) – 96 (0x60)

Au sein d’une table heap, chaque fois qu’une ligne de données est supprimée, sa valeur d’offset dans la table de décalage des lignes est initialisée à 0. C’est ainsi que le prochain enregistrement ajouté prendra la place de l’ancienne ligne via l’insertion de sa valeur d’offset dans le slot dont la valeur d’offset est égale à 0 dans la table de décalage de lignes.

Voici un schéma de l’état final de la page après nos tests :


Au regard des tests ci-dessus, ce qu’il faut donc retenir c’est que pour une table heap il n’y a aucune maintenance de l’ordre des lignes de données d’une page.

Audit de l’organisation des lignes d’une page d’une table avec un index en cluster

Sans modifier le contenu de la table MaTable, ajoutons maintenant un index en cluster :

USE MaBase
GO
CREATE CLUSTERED INDEX IDX_MaTable_ID ON dbo.MaTable(MaTable_ID)
GO

Regardons maintenant le contenu de la table :


On peut constater, ci-dessus, que les lignes de données ont été ordonnées par l’index en cluster. Pour mieux noter cela :

  • Identifions le numéro de page d’index en cluster :
DBCC IND(MaBase, MaTable, -1)
GO


  • Utilisons la commande DBCC PAGE (avec trace des résultats) pour analyser le détail de la page :
DBCC TRACEON(3604)
GO
DBCC PAGE(MaBase, 1, 89, 1)
GO

Dont voici le résultat (partie données) :

[…]DATA:
Slot 0, Offset 0x60, Length 29, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Memory Dump @0x000000000AC3C060

0000000000000000: 30000800 00000000 0300f902 0011001d †0……………

0000000000000010: 0045004e 00520045 00470030 00††††††††.E.N.R.E.G.0.

Slot 1, Offset 0x7d, Length 29, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x000000000AC3C07D

0000000000000000: 30000800 01000000 0300f902 0011001d †0……………

0000000000000010: 0045004e 00520045 00470031 00††††††††.E.N.R.E.G.1.

Slot 2, Offset 0x9a, Length 29, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x000000000AC3C09A

0000000000000000: 30000800 03000000 0300f902 0011001d †0……………

0000000000000010: 0045004e 00520045 00470033 00††††††††.E.N.R.E.G.3.

Slot 3, Offset 0xb7, Length 29, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x000000000AC3C0B7

0000000000000000: 30000800 04000000 0300f902 0011001d †0……………

0000000000000010: 0045004e 00520045 00470034 00††††††††.E.N.R.E.G.4.

Slot 4, Offset 0xd4, Length 29, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x000000000AC3C0D4

0000000000000000: 30000800 05000000 0300f902 0011001d †0……………

0000000000000010: 0045004e 00520045 00470035 00††††††††.E.N.R.E.G.5.

OFFSET TABLE:

Row – Offset

4 (0x4) – 212 (0xd4)

3 (0x3) – 183 (0xb7)

2 (0x2) – 154 (0x9a)

1 (0x1) – 125 (0x7d)

0 (0x0) – 96 (0x60)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Ici, les lignes de données valent 29 octets. On peut noter qu’ENREG5 a été placé dans le 5ème slot (slot 4), ENREG1 dans le 2ème slot (slot 1), confirmant ainsi le classement physique par ordre numérique des lignes de données par la construction de l’index en cluster comme on peut le constater avec la table des offsets.

Voici le schéma de l’état de la page de données après construction de l’index sur la table heap :


Supprimons maintenant la ligne de données ENREG4 d’identifiant 4, puis enchaînons avec l’insertion d’un nouvel enregistrement appelé ENREG2 :

USE MaBase
GO
DELETE
FROM dbo.MaTable
WHERE MaTable_ID = 4
GO
INSERT INTO dbo.MaTable VALUES (2,'ENREG' + CAST(2 AS VARCHAR(50)));
GO

Lançons un petit SELECT des familles pour vérifier le résultat :


On peut noter que l’index en cluster a gardé les lignes de données ordonnées.

Maintenant, jetons un coup d’œil dans le détail de la page pour avoir des informations détaillées sur ENREG2 et sur la table des offsets:

[…]
Slot 2, Offset 0x10e, Length 29, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Memory Dump @0x000000000AE3C10E
0000000000000000: 30000800 02000000 0300f802 0011001d †0……………
0000000000000010: 0045004e 00520045 00470032 00††††††††.E.N.R.E.G.2.
[…]

OFFSET TABLE:

Row – Offset

4 (0x4) – 212 (0xd4)

3 (0x3) – 154 (0x9a)

2 (0x2) – 241 (0xf1)

1 (0x1) – 125 (0x7d)

0 (0x0) – 96 (0x60)

Au regard des informations ci-dessus, on peut souligner que la nouvelle ligne de données (ENREG2) n’a pas remplacé celle précédemment supprimée (ENREG4), ENREG2 (slot 2) ayant été placé après ENREG1 (slot 1) suivant l’ordre numérique de la clé d’index en cluster (on parle de tri logique). Par contre, si l’on se réfère à la valeur d’offset d’ENREG2, on peut noter qu’elle est supérieure à celle de la ligne suivante (ENREG3) ce qui signifie qu’il n’y a pas eu de tri physique.

Voici le schéma de l’état final de la page de données de la table indexée en cluster après modification :


La moralité dans l’histoire est la suivante : à chaque (re)construction d’un index en cluster, SQL Server réorganise physiquement les lignes de données. Par la suite, le maintien du tri physique est remplacé par une réorganisation logique des données par le biais des numéros de slot. Pour vous en convaincre, reconstruisez l’index (ALTER INDEX IDX_MaTable_ID On dbo.MaTable REBUILD;) puis, après avoir identifié le nouvel PID de l’index (qui change à chaque reconstruction), jetez un coup d’œil au détail des informations de la page, ainsi qu’à sa table des offsets.
Le fait que SQL Server fasse un tri logique plutôt qu’un tri physique tant qu’il n’y a pas de reconstruction (ou réorganisation) d’index est un choix compréhensible. En effet, un maintien régulier de l’ordre physique des lignes de données pourrait devenir très vite coûteux.
Par ailleurs, concernant les clés naturelles (c’est-à-dire, non-numériques), l’index en cluster effectue ses tris par ordre alphabétique.


Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s