Un collègue m’a demandé il y a quelques temps, quelle était la différence entre un index non cluster classique et un index non cluster avec colonnes incluses ? Cette question est venue suite à la création de ce type d’index (avec colonnes incluses) pour couvrir certaines colonnes demandées par une requête et éviter ainsi une recherche par pointeur sur l’index cluster qui pouvait être contre performant dans ce cas car elle concernait une table très volumineuse. Dans ce billet, on verra quelle est la différence entre ces deux types d’index et comment SQL Server stocke en interne les index avec colonnes incluses.

On prendra comme exemple une table nommée Person.Address de la base de données bien connue AdventureWorks. Nous voulons satisfaire la requête suivante :

USE AdventureWorks;
GO

SELECT
    AddressLine1,
    AddressLine2,
    City,
    StateProvinceID,
    PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N’98000′ and N’99999′;

Pour cela nous allons créé deux index :

- Un index non cluster classique :

– Index non cluster classique
CREATE INDEX IX_Address_PostalCode    
ON Person.Address (PostalCode,AddressLine1, AddressLine2, City, StateProvinceID);
GO

- Un index non cluster avec colonnes incluses :

– Index non cluster avec colonnes incluses
CREATE INDEX IX_Address_PostalCode_Included      
ON Person.Address (PostalCode)      
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO

Je vous laisse vérifier via le plan d’exécution si ces 2 index permettent de satisfaire à la requête ci-dessus sans passer par l’index cluster de la table.

Regardons maintenant la taille de chaque index créé via la DMV sys.dm_db_index_physical_stats :

SELECT
    i.name,
    SUM(ips.page_count * 8. / 1024) AS size_in_kb
FROM sys.dm_db_index_physical_stats(DB_ID(‘AdventureWorks’), OBJECT_ID(‘Person.Address’), NULL, NULL, ‘DETAILED’) AS ips
INNER JOIN sys.indexes AS i
ON i.index_id = ips.index_id
AND i.object_id = ips.object_id
  AND i.name LIKE ‘IX_Address_PostalCode%’
GROUP BY i.name;

qui donne le résultat suivant :

 

La requête suivante donne plus de détails sur les 2 index créées :

SELECT
    i.index_id,
    i.name,
    i.type_desc,
    ips.index_depth,
    ips.page_count,
    ips.record_count
FROM sys.dm_db_index_physical_stats(DB_ID(‘AdventureWorks’), OBJECT_ID(‘Person.Address’), NULL, NULL, ‘DETAILED’) AS ips
INNER JOIN sys.indexes AS i
ON i.index_id = ips.index_id
AND i.object_id = ips.object_id
  AND i.name LIKE ‘IX_Address_PostalCode%';

qui donne le résultat suivant :

Première constatation :

L’index non cluster (1.67 Ko) est plus volumineux que l’index non cluster avec colonnes incluses (1.65 Ko). Un niveau supplémentaire existe pour l’index non cluster classique. (3 contre 2 pou l’index non cluster avec colonnes incluses).

Pour comprendre cette différence il va falloir regarder en détails chaque type de page de chaque index à l’aide des commandes habituelles DBCC IND et DBCC PAGE.

1- Pour les pages racines de chaque type d’index :

- Index non cluster classique :

DBCC IND (‘AdventureWorks’, ‘Person.Address’, 6);
GO
– Root : 11568
DBCC TRACEON(3604);
GO
– Index IX_Address_PostalCode
DBCC PAGE(‘AdventureWorks’, 1, 11570, 3);
GO

qui donne le résultat suivant :

La page racine comporte toutes les colonnes qui composent l’index. Ceci est le comportement attendu pour un index non cluster classique. Nous retrouvons également les pointeurs vers les pages suivantes ainsi que les signets vers la clés de l’index cluster (AddressID).

- Index non cluster avec colonnes incluses :

DBCC IND (‘AdventureWorks’, ‘Person.Address’, 7);
GO
– Root : 11632
– Index IX_Address_PostalCode_Included
DBCC PAGE(‘AdventureWorks’, 1, 11632, 3);
GO

qui donne le résultat suivant :

Ici nous n’avons que la colonne qui compose directement l’index non cluster. Les colonnes incluses, quant à elles, ne sont pas présentes. Nous retrouvons comme pour un index non cluster classique les pointeurs vers les pages suivantes ainsi que les signets vers la clés de l’index cluster (AddressID).

2- Pour les pages de niveaux feuilles de chaque type d’index : (Il suffit de prendre n’importe quel ID de page au niveau 0).

- Index non cluster classique

On retrouve un schéma classique d’un contenu de page de niveau feuille. Il n’existe évidemment plus de pointeurs de page car nous sommes au niveau feuille.

- Index non cluster avec colonnes incluses

Nous avons exactement la même structure de contenu de page au niveau feuille pour un index non cluster avec colonnes incluses. Pour ce dernier, nous avons cette fois ci toutes les colonnes composants l’index plus les colonnes incluses.

Voilà, nous avons donc vu les différentes notoires qu’il existait entre un index non cluster classique et un index non cluster avec colonnes incluses. Pour ce dernier seul les pages de niveau feuilles hébergent l’ensemble des colonnes de l’index (clé(s) d’index + colonnes incluses), ce qui explique une quantité de stockage plus faible. Dans le cas des tables très volumineuses cela peut être très intéressant car le stockage de l’index sera considérablement réduit et la mise à jour d’une des colonnes incluses n’impactera que le niveau feuille de cet index.

Bien sûr la question qui se pose logiquement par la suite est la suivante : dans ce cas pourquoi ne pas utiliser uniquement des index non cluster avec colonnes incluses ? Je répondrais par ceci : il ne faut pas oublier une règle de base qui stipule qu’un index doit être le plus court possible. En effet, avec un index avec colonnes incluses, même s’il est possible de dépasser la limite des 900 octets, il est cependant déconseiller d’inclure un trop grand nombre de colonnes. La raison en est simple : plus vous ajoutez de colonnes dans votre index plus il faudra de pages pour pouvoir héberger les données de ces mêmes colonnes bien que celles-ci se situent uniquement au niveau feuille. Il y aura donc forcément plus de pages à parcourir pour pouvoir lire les données et selon le cas il risque d’y voir plus de split de pages lors des insertions ou mises à jour de données. Vous l’aurez compris les performances I/O risquent d’être impactées. La création d’un tel index doit, à mon sens, faire l’objet d’une étude approfondie !!!

David BARBARIN (Mikedavem)
Elève ingénieur CNAM
MVP SQL Server