COMPORTEMENT DES COMPOSANTS SSIS 2008

Lors de la conception d'un package, il existe quelques principes qu'il est très utile de connaître afin d'optimiser le temps d'exécution de nos lots SSIS. Nous sommes alors en perpétuelle recherche des meilleures performances afin de coller au plus près des exigences du client (car il ne faut pas perdre de vue que ce dernier possède des contraintes de temps très fortes).

Dans ce tutoriel, nous verrons justement l'un de ces principes à savoir la gestion du comportement des composants du Data Flow. Nous montrerons à quel point l'utilisation d'un composant à la place d'un autre peut influencer grandement la consommation des ressources matérielles disponibles et donc, contribuer à la réduction du temps d'exécution de nos packages.

Avant de poursuivre plus en avant dans cet article, voici quelques notions qu'il nous faut maîtriser avant d'entrer dans le vif du sujet, car ce document s'adresse surtout aux développeurs ayant déjà produit des packages SSIS :

• connaître les principaux objets du DataFlow ;
• être familiarisé avec l'éditeur avancé de ces objets ;
• connaître le comportement global d'un lot (notion de Pipeline, d'Input, d'Output…).

Ce sont les composants les plus gourmands en termes de ressources, il faut donc veiller à les utiliser avec parcimonie. Car les transformations bloquantes nécessitent de passer en revue toutes les lignes arrivant sur leur entrée avant de les envoyer sur leur flux de sortie.

Prenons l'exemple le plus simple : l'objet Sort. Nous nous rendons bien compte que lorsque l'on effectue une opération de tri, il n'y a que lorsque l'on a passé en revue tous les éléments à ordonner que nous sommes certains que tous ont bien leur place (précisons d'ailleurs que le tri est notamment réalisé dans un Private Buffer du composant Sort). C'est pourquoi ces objets bloquent le flux de données et rien ne sortira de ce dernier tant que toutes les lignes n'auront pas été traitées. Ils peuvent donc allonger considérablement le temps d'exécution du lot s'ils sont trop nombreux ou si le nombre de lignes est important.

De plus, il existe parfois des substituts qui nous permettent de ne pas avoir à utiliser ces types de transformations (un Order By dans la requête source à la place du Sort, un Group By pour se substituer au Aggregate, etc.). Voici la liste des objets bloquants :

• Aggregate ;
• Sort ;
• Fuzzy Lookup ;
• ScriptComponent (Configuration particulière) ;
• Fuzzy Grouping ;
• Term Extraction ;
• Row Sampling.

Attention à la gestion de la mémoire

Comme dit précédemment, quelques-unes de ces transformations utilisent leur Private Buffer de manière à pouvoir traiter toutes les lignes en interne (le Sort ou le Lookup par exemple). À ce moment, les buffers peuvent atteindre une taille considérable (proportionnelle au nombre de lignes et de colonnes traitées) et rien ne sera envoyé sur la sortie de l'objet tant que tout le flux n'aura pas été traité (n'oublions pas la caractéristique des transformations bloquantes).
Bien entendu, la taille des buffers est limitée par la mémoire disponible sur le serveur et lorsqu'un objet bloquant monopolise toutes les ressources, le processus est obligé d'écrire temporairement sur le disque afin que la transformation puisse se terminer. Ces écritures disque contribuent à ralentir de façon significative l'ensemble du lot. Prudence donc.

Ces transformations bloquent temporairement le flux de données. Dans la majorité des cas, elles doivent attendre que le contexte d'exécution soit favorable pour envoyer des lignes sur leur sortie. C'est le cas par exemple du Merge Join qui enverra des données sur son flux de sortie lorsque toutes les lignes possédant la même clé de jointure seront arrivées sur son entrée (c'est pour cela que le Merge Join ne prend en entrée que des buffers triés). Voici la liste des objets semi-bloquants :

• Merge Join ;
• Merge ;
• Pivot ;
• Unpivot ;
• Term Lookup ;
• Union All.

C'est le dernier type de comportement des composants du Data Flow. Un objet non bloquant ne retient pas les données que ce soit de manière partielle (composant semi-bloquant) ou totale (composant bloquant). Ils sont donc plus performants en matière de temps d'exécution. Et fort heureusement, la majorité des objets SSIS sont non bloquants.
Voici la liste des objets non bloquants :

• Conditional Split ;
• Data Conversion ;
• Derived Column ;
• Lookup ;
• Row Count ;
• Multicast ;
• OLEDB Command ;
• Script Component ;
• Slowly Changing Dimension ;
• Copy Column ;
• Export Column ;
• Import Column ;
• Character Map ;
• Audit ;
• Percent Sampling.

On parvient généralement à définir si un composant est synchrone ou pas en portant une attention toute particulière à la façon dont le buffer est utilisé.
On considérera qu'un objet est synchrone si c'est le même buffer qui est utilisé en entrée et en sortie. Il y a donc très peu de manipulations à effectuer en mémoire (pas de copie de buffer) ce qui nous rappelle étrangement le comportement des composants non bloquants. D'ailleurs ce n'est pas pour rien si les seuls composants synchrones sont les transformations non bloquantes.
Pour savoir simplement si un composant est synchrone, il faut aller regarder dans les propriétés avancées de l'objet. Prenons l'exemple d'un Data Conversion et considérons la transformation suivante.

Notons que cinq colonnes sont extraites de notre source de données (ID, Prenom, Nom, Ville, ColonneDerivee) mais qu'une seule sera traitée par l'objet Data Conversion (Prenom). Maintenant, dans l'éditeur avancé de ce même objet nous observons ceci :

En entrée du Data conversion nous n'avons que la colonne que nous cherchons à convertir et en sortie la colonne convertie. Le reste des colonnes n'est pas modifié par le composant et l'ajout de la nouvelle colonne (Copy of Prenom) se fera directement dans le buffer courant.

Inversement, on considère qu'un composant est asynchrone lorsque le buffer d'entrée est différent du buffer de sortie. Cela se produit quand la transformation ne peut se faire au sein du buffer d'entrée (souvenons-nous de l'exemple avec Sort dans la partie définissant un objet bloquant). Dans ce cas une copie de buffer est réalisée. De la même manière que précédemment, nous allons nous appuyer sur l'éditeur avancé pour montrer les spécificités des objets asynchrones. Voici la fenêtre principale du composant Sort :

Et voici ce que nous trouvons dans l'input et l'output du Sort :

Nous observons qu'en entrée nous retrouvons toutes les colonnes de notre buffer contrairement aux objets synchrones pour lesquels seules les colonnes participant à la transformation sont nécessaires (le reste du buffer étant inchangé, c'est le cas de notre colonne Prenom dans le Data conversion de l'exemple décrit plus haut).
Ici, le buffer de sortie (l'Output du Sort) sera différent du buffer d'entrée. Dans notre cas, colonnes « input » et « output » sont nommées de la même façon, mais étant donné que les deux buffers sont indépendants, il est tout à fait possible de renommer ID par Identifiant par exemple. Car il faut bien comprendre que comme les buffers sont différents, nous n'avons pas affaire aux mêmes colonnes. Ainsi la colonne Nom en entrée aura un LineageID différent de la colonne Nom en sortie (le LineageID étant un nombre permettant d'identifier la colonne dans le Data Flow) : voir ci-dessous.

Pour faire le mapping entre les colonnes en entrée et les colonnes en sortie, le moteur SSIS utilise la propriété SortColumnId (pour l'objet Sort) : ici la colonne avec le LineageID à 22 s'appuiera sur les données de la colonne avec le LineageID 38, ce qui correspond à la colonne Nom en entrée du Sort.
Les composants asynchrones sont donc plus consommateurs de ressources que les synchrones. Cette observation est corrélée avec le fait que l'on considère que tous les composants bloquants et semi-bloquants ont des sorties asynchrones.

Lors du développement des lots SSIS, il faut bien avoir à l'esprit les différentes propriétés des composants du Data Flow afin de pouvoir optimiser au mieux les performances et le temps d'exécution. Pour cela, il n'y a pas de règles simples et tout dépend du contexte d'exécution : il faudra tester différentes configurations si jamais le temps de process ne nous satisfait pas.

Je tiens à remercier Fleur-Anne pour ses conseils avisés pour ce premier article, ainsi que Bruno2r pour toutes les corrections qu'il a apportées.