Blog ENI : Toute la veille numérique !
Jusqu'à ce soir ! : -25€ dès 75€ sur les livres en ligne, vidéos... code FUSEE25. J'en profite !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici

Notions avancées et optimisations

Introduction

SQL Server Integration Services a su s’imposer grâce à sa facilité de prise en main et sa proximité avec SQL Server. Et s’il est vrai qu’il est possible de livrer des packages répondant à des besoins de chargement de fichiers, d’ordonnancement de requêtes SQL…, la simplicité disparaît lorsque le volume augmente. Lorsque l’utilisation d’un composant de commande OLE DB prenait moins d’une minute sur 1 000 lignes, mais prend maintenant plus de 10 heures sur 100 000 lignes, il est nécessaire de comprendre pourquoi cela se produit et quelles sont les alternatives.

Les fondements du flux de données

Les avantages de l’utilisation du flux de données sont nombreux, de la possibilité d’utiliser des sources de données hétérogènes à la simplicité de maintenance et de journalisation en comparaison des procédures stockées. Toutefois, un flux de données implique la gestion de ces sources par le moteur SSIS. Il est donc important de bien comprendre comment les données sont gérées par SSIS et comment faire en sorte que cela reste performant.

1. La mémoire tampon (buffer)

Comme décrit dans le chapitre Les bases de l’ETL, le flux de données fonctionne par lot de données : les buffers. Ce sont des zones mémoire qui stockent les lignes et les colonnes des données transitant dans le Data Flow. Tout au long de son exécution, le flux de données peut être amené à utiliser plusieurs buffers. Cette création de multiples buffers est due aux différents types de composants existant dans le Data Flow et nous y reviendrons plus tard dans ce chapitre. Schématiquement, un buffer est donc un tableau (data set) de lignes et de colonnes. Une bonne illustration de son contenu peut être obtenue grâce à la visionneuse de données.

Ces buffers peuvent avoir différentes tailles, et donc contenir plus ou moins de lignes et de colonnes. La définition de ces tailles passe par l’utilisation de plusieurs paramètres, notamment quelques propriétés du flux de données.

images/6-RI-1.PNG

Parmi celles-ci, nous retrouvons :

  • DefaultBufferMaxRows : définit le nombre de lignes maximales que pourra contenir notre buffer. Par défaut, sa valeur est à 10 000.

  • DefaultBufferSize : spécifie la taille par défaut du buffer (valeur initiale : 10 Mo). Cette propriété est ignorée lors de l’utilisation de la propriété AutoAdjustBufferSize. 

    Mais le calibrage de la taille des buffers par SSIS ne va pas dépendre que de ces propriétés. À l’initialisation du flux de données, le moteur va aller inspecter les métadonnées déclarées dans le composant source et extraire le type des colonnes afin d’estimer le poids d’une ligne...

Mesure des performances et diagnostic

Afin de valider que les optimisations sont efficaces, il faut être à même d’investiguer sur la source d’un problème. Pour ce faire, plusieurs outils sont à disposition :

  • les données de journaux présentes dans la base du catalogue SSIS (plus ou moins verbeuses en fonction du niveau de journalisation indiqué à l’exécution du package)

  • la journalisation personnalisée au niveau de chaque package ou tâche

  • le moniteur de performance (perfmon)

1. Utilisation des journaux SSISDB

Pour utiliser les journaux de la base SSISDB, il est nécessaire d’avoir a minima le rôle ssis_log_reader à partir de SQL 2016 et ssis_admin pour les versions antérieures. Lorsque l’on souhaite optimiser les packages, une première approche macroscopique consiste à identifier le poids de chaque tâche dans la durée d’exécution d’un package, afin de savoir sur quelle partie les efforts d’optimisation doivent être concentrés. Or, les journaux sont souvent difficiles à lire (surtout en cas de parallélisme dans les flux), il faut donc pouvoir extraire les informations des journaux de façon lisible. Pour ce faire, des solutions telles que le SSISReportingPack de Jamie Thomson existent, celle-ci se base sur les journaux de SSISDB afin de proposer une procédure stockée, et des rapports SSRS afin de représenter graphiquement les résultats d’exécution. Toutefois, ces solutions packagées souffrent d’un manque de mise à jour flagrant, c’est pourquoi nous nous concentrerons sur des requêtes SQL que vous pourrez porter sur l’outil de votre choix. L’essentiel étant d’être en mesure d’identifier les informations utiles.

Les vues utiles dans le cas de l’analyse du temps d’exécution des tâches (flux de contrôle et flux de données) sont les suivantes :

  • [catalog].[executions] : le point de départ de la majorité des requêtes d’analyse des logs. Cette vue permet de connaître (liste non exhaustive) :

  • execution_id : l’identifiant de l’exécution, utilisé dans de nombreuses vues de statistique d’utilisation

  • project_name...

Des packages performants

1. Utilisation des moteurs relationnels

Nous l’avons vu dans les chapitres précédents, il existe plusieurs modes d’accès aux informations lorsque nous utilisons une base de données relationnelle en tant que source (composant OLE DB). Dans ce cas de figure, nous avons également exposé le fait qu’il est préférable d’écrire soi-même la requête d’extraction des données plutôt que de sélectionner la table parmi celles disponibles dans la base. Il existe quelques bonnes pratiques sur l’écriture de ces requêtes SQL.

Nous avons déjà fait une remarque concernant le SELECT * dans le chapitre Les bases de l’ETL, section Composant Source OLE DB. Avec les éléments que nous venons de présenter concernant le moteur d’exécution des flux de données, nous avons maintenant une vision complète des problèmes que ce genre de requête peut engendrer, notamment une surconsommation de mémoire de la part du Data Flow. En effet, le moteur va prendre en compte la taille de toutes les colonnes du select afin d’estimer celle d’un buffer. De plus, cette consommation supplémentaire est variable, car elle va suivre l’évolution de la structure de la table, notamment en cas d’ajout de colonnes. Parmi les champs renvoyés par le select, il est parfois utile de réaliser des opérations de transformation. Elles peuvent être effectuées dans le flux de données, via des composants de conversion de données ou de colonne dérivée. Mais lorsque la conversion de données est simple (suppression d’espace, concaténation, conversion en date, opérations arithmétiques), il vaut mieux l’implémenter dans la requête SQL. En revanche, si celle-ci est relativement complexe et réduit fortement la lisibilité de la requête, il faut alors tester son implémentation dans le Data Flow : les développements futurs n’en seront que plus clairs.

En ce qui concerne la sélection des tables/vues, mis à part les bonnes pratiques générales sur l’écriture SQL pure (jointures, hints, etc.), notons tout de même l’utilisation...