Si vous êtes administrateur de bases de données SQL Server vous connaissez certainement les tables HEAP (Tables ne possédant pas d’index cluster) et à contrario les tables possédant un index cluster. Savez vous seulement comment sont organisées les lignes d’une de ces tables dans les pages de données ? La plupart de la documentation que j’ai pu voir dit qu’une table possédant un index cluster voit ses lignes de données ordonnées physiquement selon la définition de ce même index cluster.. Est ce vraiment le cas ? Je vous propose dans ce billet une tentative de réponse .

Pour commencer, je pense qu’il est bon de rappeler certaines choses concernant l’organisation physique d’une page de données. Celle-ci est composée d’une entête, des données elles mêmes et d’une table d’offset en fin de page. Cette table permet de déterminer la position des données dans la page et indique également le nombre de lignes contenu dans la page.

Table HEAP

Créons une table test_cluster avec le jeu de données suivant :

DECLARE @i INT
SET @i = 0;

WHILE @i < 10
BEGIN
    INSERT INTO dbo.test_cluster VALUES (@i,’TEST’ + CAST(@i AS VARCHAR(50)));
    SET @i = @i + 2;
END;

SET @i = 1;

WHILE @i < 10
BEGIN
    INSERT INTO dbo.test_cluster VALUES (@i,’TEST’ + CAST(@i AS VARCHAR(50)));
    SET @i = @i + 2;
END;

Une instruction SELECT sur la table test_cluster donne le résultat suivant :

Regardons ensuite le détail de la page hébergeant les lignes de données de la table test_cluster. Pour trouver cette page de données on peut utiliser la commande non documentée DBCC IND :

DBCC IND(internal_database, test_cluster, -1);
GO

Le résultat est le suivant :

C’est la page ayant un PID égale à 89 qui nous intéresse ici (PageType = 1 correspond à une page de données). Je n’ai mis ici que les informations intéressantes pour notre explication.

DBCC TRACEON(3604);
GO
DBCC PAGE(internal_database, 1, 89, 1);
GO

 

.

On remarque ici que la table d’offset indique que la page comporte 10 lignes de données (slot 0 à 9) avec des valeurs d’offsets correspondants qui indiquent la position de ces lignes dans la page. Par exemple la 1ère ligne de données correspond au slot 0 avec un offset ayant une valeur décimale de 96. En regardant dans la partie DATA on constate que le slot 0 et l’offset ayant la valeur décimale de 96 correspond bien à 1ère ligne de données (0, ‘TEST0′) de la table test_cluster. De même le slot 1 et l’offset ayant une valeur décimale 118 correspond à la 2ème ligne de données (1, ‘TEST1′) de la table test_cluster.

Attardons nous maintenant sur les slots 8 et 9. Les lignes de données de la table test_cluster correspondantes sont respectivement (8, ‘TEST8′) et (9, ‘TEST9′). L’ordre physique des données dans la page correspond en fait dans ce cas à l’ordre d’insertion des données du script utilisé au début de ce billet. Mais attention ceci ne garantie en rien que cet ordre soit respecté à tout moment !! 

Supprimons maintenant la ligne de données ayant l’id égale à 3 et insérons une nouvelle à l’aide du script suivant :

DELETE FROM dbo.test_cluster
WHERE id = 3;
GO

INSERT INTO dbo.test_cluster VALUES (12,’TEST’ + CAST(12 AS VARCHAR(50)));

Voici le nouveau contenu détaillé de la page 89 :

On constate que la ligne de données de la table test_cluster (12, ‘TEST12′) a pris la place de la ligne (3, ‘TEST3′) qui a été supprimée dans le slot 6. Ceci est caractéristique d’une table HEAP. Une ligne de données supprimée initialisera le numéro d’offset correspond.  Le numéro de ligne / slot aura par conséquent une valeur d’offset à 0. Une des futures insertions de données dans la table test_cluster se fera probablement sur ce numéro de ligne / slot.

–> Pour une table HEAP il n’y a aucun maintien de l’ordre des lignes de données dans une page.

 

Table avec un index cluster

Ajoutons maintenant un index cluster à la table test_cluster sur la colonne id :

CREATE CLUSTERED INDEX IDX_test_cluster
ON dbo.test_cluster
(
id
);
GO

Regardons maintenant les pages allouées à la table test_cluster avec la commande DBCC IND :

DBCC IND(internal_database, test_cluster, -1);
GO

Le résultat est le suivant :

On observe tout d’abord que les numéros de pages IAM et de données ont changé. La page IAM avait un PID égale à 88 et la page de données avait un PID égale à 89 précédemment. Ceci s’explique par le fait que lorsqu’un index cluster est créé, les lignes de données de la table HEAP sont copiées et ordonnées en fonction de l’index cluster. La table d’origine n’existe donc plus par la suite.

Regardons le détail de la nouvelle page de données :

DBCC TRACEON(3604);
GO
DBCC PAGE(internal_database, 1, 93, 1);
GO

Remarquez que la construction de l’index a réordonné physiquement les données dans la page. En effet, la ligne (12, ‘TEST12′) est cette fois ci positionnée dans le slot 9 et à l’offset 294. Les numéros d’offsets ont donc été affecté en suivant l’ordre déterminé par l’index cluster. Cet ordre est également respecté si l’on regarde les numéros de slots.

Supprimons à nouveau une ligne de données (id = 8) et insérons une nouvelle ligne :

DELETE FROM dbo.test_cluster
WHERE id = 8;
GO

INSERT INTO dbo.test_cluster VALUES (11,’TEST’ + CAST(11 AS VARCHAR(50)));
GO

Le détail de la page de donnés est maintenant le suivant :

La nouvelle ligne insérée n’a pas pris la place de la ligne supprimée comme dans le cas d’une table HEAP. De plus celle-ci se trouve dans le slot 8 et à l’offset 367, ce qui montre que la ligne de données (11, ‘TEST11′) de la table test_cluster est physiquement placée après la ligne (12, ‘TEST12′) dans le slot 9 et à l’offset 344.

Quant aux numéros de lignes ceux-ci ont été maintenant à jour suite aux modifications apportées à la table test_cluster. L’ordre imposé par cet index est ici bien respecté (La ligne de données (11, ‘TEST11′) est placée avant la ligne de données (12, ‘TEST12′).

–> A la construction d’un index cluster, SQL Server réorganise physiquement les lignes de données d’une table à l’intérieur d’une page de données. Ceci est également le cas pour une reconstruction d’index (avec l’instruction ALTER INDEX REBUILD) Par la suite le maintien d’un tri physique n’a plus lieu. A la place,  SQL Server réordonne logiquement les données par le biais des numéros de slot / lignes en fonction de l’index cluster.

En y réfléchissant ceci peut paraître évident : un maintien physique des données d’une table possédant un index cluster serait extrêmement coûteux !! CQFD !

 

David BARBARIN (Mikedavem)
Elève ingénieur CNAM Lyon