SQL Server 2005 – Index include

Parmi les nouveautés de SQL Server 2005 (et donc SQL 2008) figurent les Index avec colonnes incluses.

Si l’on remonte quelques jours en arrière, je me demandais si l’index include était vraiment plus performant qu’un index couvrant. Car je faisais mes tests sur de petites tables, 50.000 enregistrements tout au plus. Et les informations renvoyées par la vue système sys.dm_db_index_physical_stats ne me montraient aucune différence ! Même niveau de profondeur des index, mêmes IO disque lors des requêtes. J’ai donc creusé cette histoire, d’où l’article de ce jour.

Pour tester cette fonctionnalité, je me suis basé sur la base exemple AdventureWorks2008 que vous pouvez télécharger sur CodePlex.
La table [Production].[TransactionHistory] est peu volumineuse (113.443 enregistrements pour un peu plus de 6Mb de stockage), mais finalement assez représentative pour la requête cliente à analyser.
clip_image002
J’ai donc créé une base Test sur mon serveur. Pas de spécification particulière pour la gestion des fichiers.
Ensuite, afin de mesurer les différences, j’ai recopié en trois exemplaires [Production].[TransactionHistory] dans cette base.

SELECT *
INTO Test.dbo.TransactionHistory_IDX_Include
FROM AdventureWorks2008.Production.TransactionHistory
GO

SELECT *
INTO Test.dbo.TransactionHistory_IDX_Couvrant
FROM AdventureWorks2008.Production.TransactionHistory
GO

SELECT *
INTO Test.dbo.TransactionHistory
FROM AdventureWorks2008.Production.TransactionHistory
GO

Sachant que je n’ai recopié que des données, on va repositionner les clés primaires.

ALTER TABLE TransactionHistory_IDX_Include ADD  CONSTRAINT [PK_TransactionHistory_IDX_Include] PRIMARY KEY CLUSTERED
(    [TransactionID] ASC )
ON [PRIMARY];

ALTER TABLE TransactionHistory_IDX_Couvrant ADD  CONSTRAINT [PK_TransactionHistory_IDX_Couvrant] PRIMARY KEY CLUSTERED
(    [TransactionID] ASC )
ON [PRIMARY];

ALTER TABLE TransactionHistory ADD  CONSTRAINT [PK_TransactionHistory] PRIMARY KEY CLUSTERED
(    [TransactionID] ASC )
ON [PRIMARY];

La requête somme toute assez simple consiste à rechercher dans la table TransactionHistory les valeurs des champs TransactionDate et ModifiedDate pour un ReferenceOrderID donné:

SELECT TransactionDate, ModifiedDate FROM dbo.TransactionHistory WHERE ReferenceOrderID = 71214;

Sachant que je n’ai ajouté aucun autre index à cette table, le plan d’exécution annonce un clustered index scan, ce qui est logique.
clip_image004
Si l’on regarde les statistiques d’entrée/sortie disque:
Table ‘TransactionHistory’. Scan count 1, logical reads 792, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Afin d’optimiser cette requête, la première idée consiste à ajouter un index sur la colonne ReferenceOrderID :

CREATE NONCLUSTERED INDEX [IX_TransactionHistory]
       ON TransactionHistory (ReferenceOrderID)
ON [PRIMARY]

On remarque tout de suite les changements au niveau du plan d’exécution et des IO disque :

clip_image006

Table ‘TransactionHistory’. Scan count 1, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

L’ajout de l’index est donc primordial pour accélérer les recherches.

Quiconque s’est un jour penché sur l’optimisation de requête a rencontré la notion d’index couvrant. Un index est dit couvrant si tous les champs d’une requête SQL, que ce soit dans la condition WHERE ou bien dans la clause SELECT, sont contenus dans l’index.

Pourquoi utiliser un tel type d’index ?
Cela permet de réduire le nombre d’IO disque car toutes les informations sont directement accessibles au moteur pour satisfaire à la requête, sans avoir à parcourir l’arbre d’index Cluster (s’il y a un index cluster au travers de la clé cluster) ni a utiliser un RID pour accéder à la donnée si la table est un segment de mémoire (pas d’index cluster). Cela permet aussi de soulager les pages de données de la table, car on ne travaille plus que sur l’index, cela permet du coup de diminuer le verrouillage, et donc, d’augmenter les performances. C’est d’autant plus vrai si vous pouvez disposer d’un sous-système disque de type SAN. Un groupe de fichier pour les Data, un groupe de fichier pour les Index. Chaque groupe de fichier sur une LUN séparée sur le SAN. Les accès disques sont donc parallélisés. La base de données en sera que plus performante. Et c’est encore plus vrai si on multiplie les fichiers à l’intérieur d’un même groupe de fichier sur un système à plusieurs processeurs, l’OS gérant ainsi de multiples Thread d’entée/sortie. Bref, cet aspect des choses peut à lui seul faire l’objet d’une série de post.

Jusqu’à la version 2005 de SQL Server, l’utilisation d’index couvrant était une réponse aux problématiques d’optimisation et de montée en charge.
Voyons ce que peuvent nous apporter les index avec colonne incluse dans ce cadre.

Afin de ne pas fausser les statistiques, on va travailler indépendamment sur les tables crées précédemment:

CREATE NONCLUSTERED INDEX [IX_TransactionHistory_IDX_Couvrant]
       ON TransactionHistory_IDX_Couvrant (ReferenceOrderID , TransactionDate, ModifiedDate)
ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_TransactionHistory_IDX_Include]
       ON TransactionHistory_IDX_Include (ReferenceOrderID ) INCLUDE (TransactionDate, ModifiedDate)
ON [PRIMARY]

Que constate-t-on maintenant ?

clip_image008
Table ‘TransactionHistory_IDX_Couvrant’. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘TransactionHistory_IDX_Include’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Les plans d’exécution des 2 requêtes sont similaires. Les index sont donc bien mis en œuvre pour améliorer les recherches. Cependant on note une différence au niveau des lectures logiques.

Cette différence au niveau des IO disque est du à la définition même de l’index :

  • L’index couvrant stocke toutes les colonnes dans les pages intermédiaires et les pages de niveau feuille de l’arbre d’index. Un index de ce type a donc un impact assez fort sur la volumétrie. De plus, il convient de penser que si les données de ce type de colonne sont modifiées, cela va impacter le système car il faut aussi modifier l’index en question. L’ajout/modification/suppression d’enregistrements seront, finalement, pénalisés par un index couvrant. Les personnes les ayant utilisés jusqu’à présent, car c’était la seule alternative, étaient conscientes de cet état de fait et les index couvrant étaient souvent cantonnés à des systèmes plutôt orientés lecture intensive.
  • L’index include stocke toutes les colonnes dans les pages de niveau feuille. Dans les niveaux intermédiaires de l’arbre, seule la colonne de clé est stockée. Cela permet de stocker davantage d’information dans une page, et apporte donc davantage de performance lors du parcours de l’arbre. De plus, lors des ajouts ou des modifications de données sur une colonne faisant partie de l’index, le système est bien moins pénalisé par rapport à un index couvrant, car seul le niveau feuille sera impacté.

Et effectivement, lorsque l’on regarde de près ces index, au travers des vues système, on constate que le nombre de niveaux est différent:

SELECT object_name([object_id]),alloc_unit_type_desc,index_depth,index_level,
       page_count,record_count,avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID(N’Test’), OBJECT_ID(N’dbo.TransactionHistory_IDX_Couvrant’), NULL, NULL , ‘DETAILED’) WHERE index_id > 1;

SELECT  object_name([object_id]),alloc_unit_type_desc,index_depth,index_level,
       
page_count,record_count,avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID(N’Test’), OBJECT_ID(N’dbo.TransactionHistory_IDX_Include’), NULL, NULL , ‘DETAILED’) WHERE index_id > 1;

clip_image010

L’index couvrant montre 3 niveaux alors que l’index include se satisfait de 2. Le niveau 0 représentant le niveau feuille est identique, ce qui est logique car les deux type d’index possèdent toutes les informations au niveau feuille. C’est bien dans les niveaux intermédiaires que tout se joue : alors que l’index include se satisfait d’une seule page pour stocker la clé de l’index en niveau intermédiaire, il en faut 4 pour l’index couvrant qui contient même en niveau intermédiaire les données de toutes les colonnes. Tout cela est corroboré par la taille moyenne des enregistrements qui est identique au niveau feuille et qui diffère en niveaux intermédiaires.

Est-ce que le nombre d’enregistrements fait varier les performances ?

Pour alourdir un peu la taille de l’enregistrement, j’ai ajouté la colonne ReferenceOrderLineID à l’index .

Si on passe de 113.443 enregistrements par table à 453.772 (5 exécutions d’ajout des mêmes données), on revient sur un nombre de niveaux identiques, donc un nombre de reads identiques aussi. Les performances seront redevenues similaires.
clip_image012

A 1.000.000 d’enregistrements on est toujours sur 3 niveaux.
A 4.000.000 aussi …

clip_image014
Et donc toujours un nombre de reads identiques pour les requêtes que ce soit en index include ou en index couvrant:

Table ‘TransactionHistory_IDX_Couvrant’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘TransactionHistory_IDX_Include’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Le nombre d’enregistrements parait influent car le nombre de lectures logiques augmente petit à petit, mais ce n’est pas très explicite, et vu le serveur utilisé (une machine virtuelle Hyper-V Windows 2008 x64 avec 4 processeurs et SQL Server 2008 sur un host physique quadri 4 core), le temps de parcours de l’index est systématiquement négligeable. En production, on devrait quand même noter un ralentissement avec l’augmentation du nombre d’enregistrements, mais ce ne sont pas les IO disque qui limiteront les performances.

Est-ce que la taille des enregistrements fait varier les performances ?

Pour ce test, j’ai ajouté une colonne de longueur fixe (500) à ma table. J’ai ajouté plus de 900.000 enregistrements. Et j’ai modifié les index pour ajouter cette nouvelle colonne:

clip_image016
Cette fois ci, sachant que le poids des informations à stocker dans l’index est vraiment différent entre un index include et un index couvrant, le nombre de niveaux et le nombre de pages diffèrent fortement lorsque l’on fait le SELECT :
SELECT TransactionDate, ModifiedDate,ColonneLg500 FROM dbo.TransactionHistory_IDX_Couvrant WHERE ReferenceOrderID = 71214;
T
able ‘TransactionHistory_IDX_Couvrant’. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SELECT TransactionDate, ModifiedDate,ColonneLg500 FROM dbo.TransactionHistory_IDX_Include WHERE ReferenceOrderID = 71214;
Table ‘TransactionHistory_IDX_Include’. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Cette fois ci on note une réelle différence au niveau des IO disque, l’index couvrant consommant plus que l’index include.
Davantage d’IO disque, davantage d’espace requis au niveau du stockage, traitements d’insertion/mise à jour ralentis, dans le cas d’index sur des colonnes de taille élevée, l’index include est un vrai plus. Il serait intéressant de croiser cette analyse avec la compression au niveau Page/Table de SQL Server, l’index include devrait toujours être plus performant, mais la différence devrait être moins importante ?

Vraiment plus performant ?
La réponse est quand même oui. L’index include est plus performant. Il y a un gain d’espace non négligeable à prendre en considération, d’où des IO plus rapides, des backups plus rapides, des réindexations plus faciles, etc …

Et si …
Et si on poussait le raisonnement un peu plus loin. Comment réagit SQL Server, si au lieu de mettre la condition sur le champ ReferenceOrderID qui est le champ clé de mon index include, on faisait la requête sur une condition WHERE TransactionDate = « une valeur ». Cette colonne n’apparaissant que dans l’élément Include de l’index.
De prime abord, j’aurais répondu de la sorte : l’index couvrant devrait être plus performant car à tous les niveaux de l’arbre, cette colonne est présente, alors que pour l’index include, c’est seulement au niveau feuille que l’information est présente.

Et bien, le verdict est un peu différent :
clip_image018
Table ‘TransactionHistory_IDX_Couvrant’. Scan count 5, logical reads 79923, physical reads 2121, read-ahead reads 59557, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘TransactionHistory_IDX_Include’. Scan count 5, logical reads 66441, physical reads 2847, read-ahead reads 59020, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Le nombre de reads est élevé car j’ai laissé la colonne de type char(500).

Si je supprime cette colonne (et donc modification des index include et couvrant), l’index include est encore plus performant en IO, mais dans une moindre mesure :clip_image020

Table ‘TransactionHistory_IDX_Couvrant’. Scan count 1, logical reads 3054, physical reads 14, read-ahead reads 3050, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘TransactionHistory_IDX_Include’. Scan count 1, logical reads 3047, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

On peut en déduire que même si la condition WHERE porte sur une colonne qui n’apparait pas en tant que référence dans l’index Include, l’optimiseur utilise quand même l’index. Et ce dernier est plus performant que l’index couvrant.

On constate aussi que des « conseils » apparaissent en vert sur le premier plan d’exécution : on nous demande de créer, pour les 2 tables, un index include avec seulement les bonnes colonnes. Microsoft a l’air de privilégier la solution consistant à créer de multiples index Include, plus légers et plus rapides, plutôt que de volumineux index composites.

Il ne faut pas pour autant tomber dans l’excès inverse. Cela ne sert à rien d’indexer à outrance en espérant avoir une base de données ultra performante. Car tous ces index ralentissent les mises à jour, les insertions et les suppressions.

Pour conclure …

Comme je le conseillais lors des cours MOC Microsoft sur SQL Server, prévoyez autant de temps pour définir les index de votre base de données que ce que vous avez mis à en définir la structure ….

A propos Christophe

Consultant SQL Server Formateur certifié Microsoft MVP SQL Server MCM SQL Server 2008
Cet article, publié dans SQL Server, est tagué , . Ajoutez ce permalien à vos favoris.

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