Comment améliorer la performance de vos ETL SSIS?

SSIS, le module ETL de la suite Microsoft BI, est doté de base de fonctionnalités assez avancées qui permettent d’atteindre un niveau de performance optimale dans les traitements de données.

Cependant, vous pouvez vous retrouver confrontés à des problématiques de performance nécessitant des interventions d’optimisation depuis la phase de conception des packages SSIS et à certains niveaux du processus ETL.

À travers ce blogue, je vais vous présenter quelques astuces qui vous permettront d’optimiser la performance de vos packages SSIS.

La performance globale des ETL va dépendre de plusieurs facteurs d’optimisation que nous allons catégoriser comme suit :

• Tâche de flux de données
• Sources de données
• Transformation
• Destination

1) Tâches de flux de données

• Ajustez la taille de la propriété DefaultBufferMaxSize (indique la taille par défaut du tampon de la tâche de flux de données)
  

Attention : La taille maximale est de 100 mégaoctets. Si vous dépassez cette valeur, cela produira une erreur d’overflow.

• Ajustez la taille de la propriété DefaultBufferMaxRow (indique le nombre maximal de lignes dans chaque tampon)
  

Avant de commencer à évaluer les performances de vos tâches de flux de données SSIS, gardez les valeurs par défaut DefaultBufferSize et DefaultBufferMaxRows et activez la journalisation dans la tâche de flux de données. Ceci permettra de mieux conduire vos choix de valeurs en analysant les informations recueillies par l'événement BufferSizeTuning.

Attention : Trop augmenter la taille de la mémoire tampon peut être contre performant, car cela risque de déclencher la pagination sur le disque.

Comment ce mode optimisé peut améliorer la performance de la tâche de flux de données?

En supprimant les colonnes, les sorties et les composantes inutilisées de votre flux de données.

Option supplémentaire : On peut aussi paramétrer cette propriété directement dans Business Intelligence Development Studio afin que le moteur de flux de données optimise son plan d’exécution durant la phase de débogage.

2) Sources des données

• Si votre source de données est une source OLE DB, faites attention à vos requêtes de sélection.
  

Comment le mode de sélection de données depuis la source peut impacter la performance des ETL?

Lorsque la composante source de SSIS va extraire le flux de données à la source, elle est chargée dans la mémoire tampon dont la taille est définie. Donc, plus il y a de colonnes dans votre requête de sélection, moins de lignes seront extraites de la source dépendamment de la taille du mémoire tampon allouée, et donc, vous aurez une faible performance.

Solution : sélectionnez uniquement les colonnes dont vous avez besoin dans le pipeline. (Évitez donc le select *)

• Si la source de données est un fichier plat (flat file), vous devrez penser à optimiser l’extraction des données du fichier plat.
  

Contrainte de cette optimisation du fichier plat : Vous devez être sûr que la structure de vos données est sans défaut, car SSIS ne validera plus la structure de vos données.

• Autant que possible, effectuez les conversions de type de données et le filtrage des données au niveau de la composante source OLE DB.
• Une des solutions que je veux vous recommande lorsque vous avez de gros volumes de lignes dans vos tables est de penser à l’état de fragmentation de vos index. Un index fragmenté à plus de 30 % devrait normalement être défragmenté.

3) Transformation

• Autant que possible, traitez la transformation depuis la sélection à la source.
• Utilisez le moins possible les objets SSIS de types bloquants dans vos transformations
  

La problématique de ces objets de types bloquants (voir liste en bas) est qu’ils monopolisent toutes les ressources machines.

• Aggregate
• Term Extraction
• ScriptComponent
• Sort
• Fuzzy Lookup
• Fuzzy Grouping
• Row Sampling
  

Trouvez ici quelques astuces de remplacement que vous pouvez utiliser comme alternative selon votre situation :

• Si possible, utilisez un traitement de type SQL Server Merge en remplacement de la composante Slowly Changing Dimension (SCD) type 1 et 2.
  

4) Destination

Si vos packages SSIS et SQL Server sont exécutés sur le même serveur physique, utilisez la composante Destination SQL Server au lieu de la composante Destination OLE DB afin d’améliorer les temps de chargements. Avant d’adopter ce choix, vous devez absolument être sûr que votre base de données restera en local sur le même serveur que vos packages SSIS. Sinon, vos connexions ne fonctionneront plus si vous déplacez la base de données sur un autre serveur.

5) Bonus

• Pensez à configurer le parallélisme au sein de vos packages SSIS
  

Mais attention, il est possible qu’il y ait de la contre-performance! La performance liée au parallélisme va être fortement liée au nombre de processeurs disponibles sur vos serveurs.

Si votre infrastructure réseau supporte des paquets plus importants, vous pouvez aussi augmenter la taille des paquets qui seront envoyés sur le réseau lors des transferts de données par SSIS, via la propriété PacketSize.

Nous avons vu que la performance de vos ETL SSIS, va dépendre d’un ensemble de facteurs (composantes SSIS, base de données, infrastructure) et de ce fait, dans un projet qui vise l’optimisation des ETL, tous les points cités devront faire l’objet d’un effort d’optimisation pour atteindre un niveau de performance qui correspond à vos besoins d’affaires.

J’espère que ces quelques astuces vous aideront à optimiser vos projets ETL avec SSIS!

Dans vos démarches d’optimisation SSIS, quelles sont les stratégies que vous avez implémentées pour obtenir des gains de performance?