En regardant la DMV sys.dm_db_index_usage_stats qui permet de visualiser la façon dont est utilisée les index, j’ai constaté qu’un de ces index (idx_heap_v_b1rvalo) utilisait de manière intensive la recherche (ou lookup) en regardant le compteur de la colonne user_lookups.

La requête qui utilise cet index est la suivante :

DECLARE @BU VARCHAR(15)
DECLARE @SITE VARCHAR(15)
DECLARE @ARTICLE VARCHAR(35)

SET @BU = ‘BU2′
SET @SITE = ‘BU2′
SET @ARTICLE = ‘042’

SELECT    v1.BU,
        v1.ARTICLE,
        v1.COUT_GLOBAL,
        v1.COUT_VAR_GLOBAL,
        v1.COUT_ACHAT,
        v1.COUT_MO,COUT_ST,
        v1.COUT_MA
FROM    dbo.Z_B1RVALO v1
WHERE    EXISTS (
        SELECT    *
        FROM    dbo.Z_B1RVALO v2
        WHERE     v1.ARTICLE = v2.ARTICLE
                AND v2.TYPE_VALO = ‘SE’
                AND v1.BU = v2.BU
        GROUP BY  v2.ARTICLE,v2.BU
        HAVING v1.DATE_FIN_APPLI = MAX(v2.DATE_FIN_APPLI)
)
AND BU = @BU
  AND SITE = @SITE
   AND ARTICLE LIKE @ARTICLE + ‘%’

Cette requête récupère pour un site d’une unité de production et pour un type de valorisation (SE) les derniers types de coût associés pour un ensemble d’article déterminé. Cette requête est utilisée par la plupart des responsables de production et certains managers.

Il n’existe sur la table Z_B1RVALO que l’index non cluster idx_heap_v_b1rvalo qui suit le schéma suivant :
idx_heap_v_b1rvalo |  nonclustered located on PRIMARY |   BU, SITE, ARTICLE, TYPE_VALO

Le plan d’exécution de la requête est le suivant :

 

> Sur ce plan d’exécution, on voit apparaître 2 opérations « RID Lookup« . 2 questions peuvent alors se poser ? Pourquoi apparaissent-elles sur ce plan (on pourrait penser ne voir que les opérateurs « Index Seek« ) et pourquoi sont-elles des opérations coûteuses ? (Le coût global de ces 2 opérations représente 76% du coût global de la requête).

- La réponse à la 1ère question nécessite quelques explications.
La double recherche au travers de l’index non cluster (idx_heap_v_b1rvalo) est déclenché par les clauses WHERE de la requête. L’optimiseur a choisi de parcourir l’index car le nombre de lignes retourné par celui-ci est moins coûteux que de faire un scan de la table V_B1RVALO. (Optimisation basé sur le coût). Cependant l’index idx_heap_v_b1rvalo d’après son schéma ne possède que les informations concernant la BU, le SITE, l’ARTICLE et le TYPE de VALORISATION. Hors on voit que d’après la requête il faut également les informations de coût et de date d’application associées aux articles concernés (colonnes DATE_FIN_APPLI, COUT_GLOBAL, COUT_VAR_GLOBAL, COUT_ACHAT,  COUT_MO,COUT_ST et COUT_MA). Il est donc nécessaire d’aller les chercher. C’est la raison pour laquelle les opérations « RID Lookup » et « Boucles imbriquées » apparaissent dans le plan d’exécution. Ces opérations correspondent à la nécessité lorsque la recherche a atteint le niveau feuille de l’index non cluster idx_heap_v_b1rvalo de trouver chaque ligne correspondante de la table V_B1RVALO en se basant sur son RID (identifiant de ligne).

- La réponse à la 2ème question est que l’opération « RID Lookup » engendre des lectures aléatoires. Ce type de lecture est par définition consommatrice de ressources d’où le coût important de cette opération.

Comment peux t’on y remédier ? Il existe bien entendu plusieurs solutions.
Un des solutions dans notre cas est d’inclure les informations de coût et de date d’application dans notre index non cluster. Les informations nécessaires seront disponibles directement dans notre index et l’opération « RID Lookup » deviendra alors inutile. Avec SQL Server 2005, il est possible d’inclure ces informations qu’au niveau feuille de notre index non cluster afin de rendre celui-ci plus compact et plus performant. Le plan dexécution de la requête est maintenant celui-ci :

> Les opérations « RID Lookup » et « Boucles imbriquées » ont disparu du plan d’exécution.

Pour se rendre du coût engendré par une opération « RID Lookup« , procédons à un dernier test : Relevons le nombre de lectures engendrées par la requête avant et après inclusion des colonnes de coût et de date d’application dans l’index non cluster (avec l’option SET STATISTICS IO)

> On observe un gain de performance de 83% dans notre cas en supprimant le « RID Lookup », ce qui n’est pas négligeable.

Bon indexage !!

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