L’autre jour en regardant les index non cluster existants d’une table de l’ERP Microsoft bien connu NAVISION, je me suis aperçu que tous étaient composés de leurs colonnes de clé respectives plus la colonne de table qui composait la clé primaire. Je me suis donc posé la question de savoir si les données de cette colonne étaient dupliquées dans ces index, ce qui pourrait avoir en plus un impact sur la quantité de stockage supplémentaire et nécessaire pour héberger cette duplication d’informations . Nous verrons dans ce billet à travers un exemple très simple qu’il n’en est rien…

Nous utiliserons dans ce billet une table nommée test_doublon_key ayant la définition suivante :

CREATE TABLE test_doublon_key
(
col1 INT IDENTITY(1,1) PRIMARY KEY,
col2 INT NOT NULL,
col3 INT NOT NULL
);

Cette table possède 3 colonnes de type INTEGER avec une clé primaire séquentielle monotone.

Nous remplirons cette table avec un jeu de données à l’aide du script suivant :

DECLARE @i INT
SET @i = 0;

WHILE @i < 10000
BEGIN
    INSERT INTO test_doublon_key (col2, col3) VALUES (@i,@i);
    SET @i +=1;
END;

Enfin nous créerons l’index suivant :

CREATE INDEX idx_non_cluster
ON test_doublon_key
(
col3, col1
);

L’index est de type non cluster et il possède dans sa définition la colonne composant la clé primaire (col1) de la table et par conséquent l’index de type cluster qui en résulte. On pourrait penser, à priori, que cette colonne sera présente deux fois dans l’index non cluster. Cela signifie qu’il existerait une duplication d’informations et par conséquent un doublement de la quantité de stockage !!??? Vérifions le .

Commençons par récupérer l’ID de notre index non cluster :

SELECT
    index_id,
    name,
    type_desc
FROM sys.indexes
WHERE object_id = OBJECT_ID(‘test_doublon_key’);

 

Récupérons maintenant les pages allouées à la table test_doublon_key à l’aide de la commande DBCC IND :

DBCC IND(‘test’, ‘test_doublon_key’, 2);
GO

 

Nous regarderons en premier lieu le détail de la page racine de l’index non cluster (Celle-ci est au plus haut niveau de l’index et ne possède aucun lien de page) à l’aide de la commande DBCC PAGE :

DBCC TRACEON(3604);
GO
– Root page
DBCC PAGE(‘test’, 1, 4319, 3);
GO

qui donne le résultat suivant :

Le détail de cette page permet de constater immédiatement que l’information de la colonne col1 n’est présente qu’une seule fois. La clé de l’index non cluster comportant déjà la colonne composant la clé de l’index cluster, la colonne col1 n’est donc présente qu’une seule fois. Nous pouvons également vérifier que l’information n’est pas dupliquée en utilisant la DMF sys.dm_db_index_physical_stats qui permet de visualiser les différentes tailles d’une ligne d’index :

SELECT
    index_type_desc,
    index_level,
    record_count,
    page_count,
    min_record_size_in_bytes AS min_size,
    max_record_size_in_bytes AS max_size,
    avg_record_size_in_bytes AS avg_size
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(‘test_doublon_key’), 2, NULL, ‘DETAILED’);

qui donne le résultat suivant :

Notre index comporte 2 niveaux :

- Le niveau 1 correspond à la racine de l’index. Une seule page existe dans ce niveau (page_count = 1). La taille d’une ligne d’index est égale à 15 octets. Au niveau racine ou intermédiaire celle-ci est composée de la clé d’index non cluster, de la clé d’index cluster (seulement les colonnes ne faisaient pas parti de l’index non cluster), d’un pointeur de 6 octets de référencement de page (2 octets pour le numéro de fichier et 4 octets pour le numéro de page dans ce fichier) et une longueur d’octets variable de gestion supplémentaire dans l’entête de page (Les paramètres de format fixe ou variable des colonnes d’une ligne de données et de valeurs NULL requièrent des octets supplémentaires).

Le détail du calcul de taille est le suivant :

[Clé d’index (col1 + col3) = 4 octets + 4 octets] + [gestion du pointeur de référencement de page = 6 octets] + [octets supplémentaires entête (colonnes fixes et non NULL = 1 octet ] = 15 octets.

 

- Le niveau 0 correspond au niveau feuille de l’index. La taille d’une ligne d’index de ce niveau est égale à 9 octets. Il n’existe pas de gestion de référence de page à ce niveau. Ceci est normal puisque nous sommes au niveau feuille de l’index !!! De plus, rappelez vous que les pages de ce niveau comportent le ROW ID de l’index cluster (puisque nous avons un index cluster ). (Voir le billet sur le parcours des index)

Le détail d’une page au niveau feuille permet de constater tout cela :

Nous n’avons pas de colonnes ChildFileID et PageChildID dans ce cas mais le plus important est qu’il n’existe pas de colonne en doublon. SQL Server se sert de la colonne de la clé d’index comme ROW ID de la clé cluster … ce qui revient au même dans ce cas !!

Le détail du calcul est le suivant :

[Clé d’index (col3) = 4 octets] + [Clé index cluster (col1) = colonne Clé index non cluster (col1) = 4 octets] + [Gestion de l’entête de page = 1 octet] = 9 octets.

Nous faisons donc l’économie de 4 octets par ligne d’index. (soit 10000 lignes de données * 4 octets = 39 Ko). Sur une table comportant un nombre important de lignes de données et dont les index sont beaucoup plus volumineux cela représente une économie certaine !!

David BARBARIN
Ingénieur CNAM Lyon