Cette vidéo explique comment améliorer les performances du flux de données dans un package Integration Services. Dans cette vidéo, vous allez apprendre à paramétrer les phases suivantes du flux de données :

Extraction
Transformation
Chargement

Vous pouvez appliquer ces conseils de paramétrage des performances lorsque vous concevez, développez et exécutez le flux de données.

Remerciements pour la vidéo

Merci à Thomas Kejser pour sa contribution aux documents de la série SSIS : conception et paramétrage des performances (série de vidéos liées à SQL Server). Cette vidéo est la deuxième de la série.

Merci à Carla Sabotta et Douglas Laudenschlager pour leurs conseils et précieux commentaires.

Transcription de la vidéo

Horodateur de la vidéo	Audio
00:00	Bonjour, je m'appelle David Noor et je suis directeur principal du développement sur SQL Server Integration Services ici chez Microsoft. Bienvenue dans cette vidéo sur le paramétrage du flux de données de votre package SSIS dans l'entreprise. Cette vidéo constitue la deuxième partie d'une série en quatre parties intitulée SSIS : conception et paramétrage des performances. Dans la première partie de la série, Denny a analysé la meilleure façon de mesurer et de présenter les performances des packages SSIS. Dans cette partie, nous nous baserons sur cette analyse et étudierons la façon d'améliorer les performances du flux de données de votre package SSIS. Nous commencerons par identifier les thèmes communs dans chaque flux de données et la manière de choisir les parties de votre flux que vous devez traiter en premier lorsque vous tentez d'améliorer les performances. Une fois que vous avez compris où sont situés les problèmes de performances, vous pouvez faire beaucoup de choses dans votre flux de données pour faire en sorte que les opérations soient plus rapides et efficaces. Nous allons examiner une série de conseils spécifiques que vous pouvez appliquer lors de la conception, du développement, voire de l'exécution de votre flux de données. Allons-y ! La plupart des flux de données de packages sont axés sur l'extraction, la transformation et le chargement de données critiques, opérations désignées parfois sous le terme ETL. Le paramétrage de votre flux de données revient à régler chacune des phases du flux de données : extraction, transformation et chargement. Pour l'extraction, nous parlerons du paramétrage des composants sources SSIS et des requêtes qu'ils exécutent pour vous offrir les meilleures performances avec un impact minimal sur les systèmes sources. Le paramétrage des transformations dans votre flux de données revient à examiner le travail que vous devez accomplir et à choisir l'approche optimale pour y parvenir en supprimant les étapes inutiles, parfois même en modifiant les requêtes sources d'origine pour automatiser les transformations. Enfin, le paramétrage du chargement de votre flux de données revient à régler les composants de destination SSIS, les réseaux, le verrouillage et d'autres facteurs qui peuvent gêner la progression du flux lorsqu'il tente de charger des données dans votre destination. Dans cette vidéo, nous discuterons des conseils pratiques que vous pouvez utiliser pour rechercher les occasions de paramétrage dans votre flux de données dans l'ensemble des trois phases. Avant d'aborder ces conseils, il est important de garder à l'esprit qu'il n'existe habituellement pas d'arme fatale lorsqu'il s'agit de traiter des problèmes de performances. Vous ne trouverez ici aucun conseil susceptible de résoudre tous les problèmes de performances que votre flux de données peut rencontrer. Ceux que nous indiquons fonctionnent dans la plupart des cas, mais il n'est pas question de répondre à votre problème par un grand nombre de conseils. Vous avez toujours intérêt à comprendre l'objectif de vos flux de données, à vous assurer qu'ils sont correctement conçus pour l'atteindre, à mesurer leurs performances actuelles et à apporter de façon itérative des modifications à votre flux pour évaluer ensuite si la modification a amélioré les performances. Par ailleurs, lorsque vous vous penchez sur un problème de performance, il est facile de se limiter à une technologie ou un composant particulier, parfois en raison de la connaissance de la technologie ou du code. Cependant, quelle que soit la raison, essayez d'envisager votre flux de données dans le contexte d'un écosystème entier, plus vraisemblablement plusieurs bases de données relationnelles, un réseau, des systèmes d'exploitation, des systèmes de fichiers, de nombreux éléments. Plus vous en savez sur l'intégralité de cet écosystème, plus vous avez une idée précise des défis réels en termes de performances relatifs à chaque élément et vous serez capable d'apporter des modifications plus efficaces et moins risquées.
03:38	Nous commencerons au même endroit que votre flux de données, par l'extraction. Lors de l'utilisation de SQL Server ou de toute autre base de données avec une interface en bloc comme source, vous devez expérimenter une augmentation de la taille du paquet. Normalement, la valeur 4096 par défaut de SQL Server fonctionne bien, mais dans la mesure où votre extraction va déplacer de grandes quantités de données, une valeur plus élevée serait plus utile. Pour que ce paramètre soit pris en compte, vous allez demander à votre administrateur réseau d'activer également « Trames Jumbo » sur votre réseau. Vous souhaitez en tester l'effet sur votre package. Si vous utilisez le même gestionnaire de connexions pour les opérations en bloc comme une source OLE DB et pour les opérations d'une seule ligne (comme une commande OLE DB), vous pouvez envisager de créer un second gestionnaire de connexions pour les opérations de commande OLE DB et utiliser une taille de paquet inférieure ici. Comme indiqué, si votre ETL est exécuté sur Windows 2008 et que vous travaillez sur un ordinateur à plusieurs noyaux avec différentes cartes réseau, vous pouvez obtenir des performances réseau un peu meilleures en définissant des affinités entre les cartes réseau et les noyaux individuels. Pour plus d'informations, recherchez un billet de blog (en anglais) intitulé Scaling Heavy Network Traffic with Windows sur le site MSDN. L'un des éléments les plus simples à rechercher lors du paramétrage de l'extraction est le réglage des requêtes que vous utilisez. Il est tentant parfois de sélectionner uniquement la table qui est l'objet de l'extraction et d'autoriser la récupération de toutes les colonnes, mais vous obtiendrez de bien meilleurs résultats si vous sélectionnez uniquement les colonnes dont vous avez réellement besoin. Non seulement le trafic réseau sera moins important et vous utiliserez moins de mémoire, mais le serveur de base de données peut procéder à beaucoup moins d'opérations d'E/S pour satisfaire votre requête. Comme illustré ici, pour SQL Server, vous pouvez également utiliser un indicateur pour signaler qu'aucun verrou partagé ne doit être émis lors de la sélection afin que votre requête lise des données éventuellement non validées ou incorrectes. Procédez ainsi lorsque vous devez absolument obtenir les meilleures performances et vous êtes certain que la lecture de données incorrectes se déroulera toujours sans problème pour votre travail ETL. Les recherches peuvent être considérées comme une extraction ou transformation. Dans les deux cas, vous devez utiliser certaines des idées illustrées à la diapositive précédente. Il est ici encore plus essentiel de sélectionner uniquement les colonnes nécessaires, non seulement pour l'optimisation du réseau, mais également pour celle du cache en mémoire. Si vous utilisez SQL Server 2008, l'une des meilleures fonctionnalités pour les performances est l'ajout du nouveau cache de recherche partagé. Le partage d'un cache de recherche vous permet d'extraire vos données de référence une fois, puis de les réutiliser dans plusieurs opérations de recherche dans votre package, ou même entre plusieurs packages en enregistrant le cache dans un fichier. Si plusieurs composants de recherche font référence à la même table, vous allez employer cette nouvelle option en tant que moyen d'améliorer significativement la vitesse de vos packages. Son utilisation peut être assez simple. Créez un gestionnaire de connexions du cache dans votre package, remplissez le cache à l'aide de la nouvelle transformation du cache, puis changez vos recherches pour faire référence à cette connexion du cache pour les données de référence.
06:29	Passons à la deuxième phase des opérations ETL : la transformation. Dans la transformation, vous utilisez des données déjà chargées dans le flux de données et tentez de les mettre correctement en forme avant leur chargement. Dans SSIS, chaque transformation apparaît sous la forme d'un objet que vous pouvez faire glisser dans votre conception, mais les transformations ne sont pas toutes identiques. Examinons les trois types de transformations présents dans SSIS : Les transformations synchrones, ou basées sur des lignes, fonctionnent dans les mémoires tampons de données sur place. Elles n'effectuent pas de copies des mémoires tampons de données ou lignes qu'elles traversent. Elles transforment simplement les données directement dans la mémoire tampon. Les transformations synchrones sont ainsi assez rapides. Des exemples de transformations synchrones sont la conversion de données, la colonne dérivée et la recherche. Les transformations asynchrones partiellement bloquantes sont différentes. Lorsque les données arrivent pour être transformées, la transformation doit les garder pour effectuer son travail. Pour ce faire, elle copie les données entrantes dans les mémoires tampons internes et utilise de la mémoire, parfois en grande quantité. Comme les données continuent d'affluer, ces transformations poursuivent leur traitement et écrivent des données de sortie. Une fois les données de sortie écrites, elles libèrent la mémoire interne correspondante qu'elles gardaient. Lorsque le flux de données est terminé, elles libèrent toute la mémoire qu'elles ont utilisée. Toutefois, jusqu'à ce moment, elles utilisent en général une quantité importante de vos données. Merge, MergeJoin et Union All sont des exemples de ces types de transformations. Lorsque vous avez ces transformations dans votre flux de données, recherchez des moyens d'optimisation. Existe-t-il des copies redondantes de celles-ci ? Existe-t-il des transformations MergeJoin ou Union que vous pourriez « faire monter » dans votre système source ? J'ai déjà vu des packages pour lesquels, au lieu d'effectuer une jointure dans une requête source, toutes les données de deux tables ont été extraites à l'aide de sources OLE DB, puis une transformation Merge Join dans le package a été utilisée pour effectuer la jointure, même si une jointure SQL aurait pu tout simplement être écrite au niveau de la source, ce qui aurait été beaucoup plus rapide en raison de la capacité de la base de données à optimiser cette requête. Recherchez ces occasions pour consolider et réduire le nombre de transformations asynchrones. Dans SQL Server 2008, nous avons beaucoup travaillé au niveau du planificateur de tâches du flux de données pour que les flux de données complexes deviennent plus performants et pour mieux utiliser les processeurs disponibles. Si vous avez commencé à utiliser SSIS dans SQL Server 2005, vous avez peut-être introduit une transformation Union All dans votre flux de données pour séparer et mettre en parallèle de façon artificielle des arborescences d'exécution. Vous ne devriez plus avoir besoin de faire cela. En raison des améliorations apportées à SQL Server 2008, cela n'est plus nécessaire ni recommandé. Supprimez ces composants Union All artificiels et vous devriez obtenir de meilleures performances. Le troisième groupe, les transformations asynchrones bloquantes, sont comme des versions extrêmes du groupe précédent : elles doivent garder TOUTES les données d'entrée avant de pouvoir écrire des données de sortie. L'utilisation de ces transformations dans les flux de données avec des lots volumineux de données peut souvent ralentir le flux de façon significative. Si vous les utilisez dans des flux de données importants, assurez-vous de ne pas le faire de manière redondante. Si vous disposez de deux agrégats ou de deux tris des mêmes données dans le même flux, retravaillez votre package pour essayer de n'en avoir qu'un. Maintenant que les notions de base ont été couvertes, examinons quelques autres conseils spécifiques : Il est assez courant pour les flux de données de devoir transformer le type de données d'une colonne. Essayez de le faire juste une fois et utilisez des types aussi étroits que possible pour que les mémoires tampons de données restent compactes. La conversion de données utilise également du temps processeur et, si l'intégralité de votre flux de données peut utiliser un type pour une colonne donnée, envisagez de convertir cette colonne au niveau de votre source de données à l'aide des fonctions CAST ou CONVERT de SQL, ou des fonctions équivalentes dans le langage SQL de vos bases de données. Je l'ai déjà mentionné dans une diapositive précédente, mais vous allez optimiser les transformations en réfléchissant à l'endroit où elles doivent figurer, et n'ayez pas peur de tirer parti de SQL au niveau de vos sources. Par exemple, si vous effectuez des tris sur vos données sources, envisagez de les faire monter dans les clauses ORDER BY du SQL de votre source. Votre base de données peut être en mesure de vous retourner les données triées plus efficacement que le flux de données. Enregistrez ici le composant de tri pour les cas où vous devez trier des données fusionnées à partir de plusieurs sources. De même, il est possible de procéder plus rapidement à certaines agrégations au niveau de la source à l'aide d'une fonction GROUP BY et d'une fonction d'agrégation SQL. Si vous utilisez SQL Server 2008 et le composant de dimension à variation lente, examinez la nouvelle fonction MERGE dans SQL Server. MERGE peut accomplir la plupart des mêmes tâches que la dimension à variation lente, mais sans le même nombre de boucles réseau. N'oubliez pas non plus l'utilité de la fonction INSERT INTO de SQL. Si votre flux de données est simple et que la source et la destination se trouvent sur la même instance de base de données, vous pouvez être capable d'effectuer le même déplacement de données beaucoup plus rapidement via une seule instruction SQL, ce qui fait que le déplacement se produit entièrement sur la base de données. Dans ces cas, INSERT INTO peut exécuter un ordre de grandeur plus rapidement qu'un flux de données, car les données ne doivent jamais quitter le serveur. Dernière chose, mais non la moindre, si vous procédez à des charges incrémentielles, envisagez la possibilité d'un rechargement simple. Certains systèmes passent un temps considérable à faire de la détection delta pour éviter de recharger des données, mais les E/S et processeur utilisés à cet effet ralentissent au final le travail.
11:59	Passons à la phase de chargement. Lors du chargement dans SQL Server, vous disposez de deux options performantes : La première option est un composant de destination SQL Server. Ce composant utilise de la mémoire partagée entre le flux de données et le moteur de base de données pour charger rapidement les données, mais fonctionne uniquement si votre flux est toujours exécuté sur le même ordinateur que SQL Server. Par ailleurs, la destination SQL Server a quelques limitations documentées en ce qui concerne la gestion des erreurs. L'autre option de chargement rapide dans SQL Server est la destination OLE DB, qui s'exécute souvent presque aussi vite que la destination SQL. Dans tous ces cas, l'utilisation d'une taille de validation égale à 0 produit le chargement le plus rapide. Il est assez courant de supprimer également des index dans votre système de destination lors de chargements importants, mais vous devez utiliser certaines consignes pour connaître le moment opportun. Une instruction courante est de choisir de supprimer les index en fonction de l'augmentation en pourcentage que la charge est supposée entraîner et des types d'index qui figurent sur la table : Si vous avez un seul index cluster sur votre table, ne le supprimez pas. Les données de la table sont triées selon cette clé et le temps nécessaire à la suppression, l'insertion et la reconstruction ne sera jamais virtuellement inférieur à l'exécution de la charge avec l'index cluster en place. Si vous disposez d'un seul index non cluster sur votre table, envisagez de le supprimer si votre charge représente une augmentation d'environ cent pour cent de la taille des données. Il s'agit là d'une méthode établie et non d'une valeur exacte, mais ce n'est en gros pas la peine de supprimer et de reconstruire l'index si vous n'allez pas doubler la taille de la table. Si plusieurs index figurent sur votre table, il est un peu plus difficile d'établir une méthode. J'ai tendance à penser à la plage d'augmentations de 10 %. Par exemple, des charges inférieures à 10 % du volume actuel peuvent probablement laisser les index en place. Cependant, vous avez intérêt ici à tester et évaluer. Si le chargement est effectué dans une table où d'autres activités sont en cours, vous devez concevoir une stratégie. Les chargements en masse vont probablement verrouiller la plus grande partie, si ce n'est pas l'intégralité, de la table de destination. Vous devez donc être sûr que cela ne pose pas de problème, ou utilisez le partitionnement. Si le chargement doit être effectué dans une base de données opérationnelle, vous pouvez configurer une partition à charger afin que les données opérationnelles en cours puissent rester actives. Si la charge se déroule lentement, vous devez veiller à extraire toute autre activité qui a lieu sur la table ou partition, et vous assurer que rien d'autre ne la gêne. Pour obtenir un excellent guide sur l'augmentation des performances des chargements en masse et partitions, recherchez l'article SQLCAT (en anglais) intitulé The Data Loading Performance Guide, disponible sur MSDN. Par ailleurs, lors des rechargements, veillez à utiliser TRUNCATE et non DELETE pour effacer les données afin que la suppression ne soit pas transactionnelle. Les destinations qui utilisent une connexion réseau sont également soumises aux mêmes problèmes réseau décrits précédemment. Envisagez d'augmenter la taille du paquet et d'activer « Trames Jumbo » sur votre réseau pour obtenir des performances optimales au moment du chargement.
15:02	Voilà, c'est tout pour la deuxième partie de cette série sur les performances. Un grand merci à Thomas pour toutes ses informations utiles qui ont permis d'élaborer cette série de vidéos ainsi qu'à Carla et Douglas pour toute leur aide lors de la création de cette série. Pour plus d'informations sur ces sujets, consultez la page (en anglais) Top 10 SQL Server Integration Services Best Practices. Nous vous remercions d'avoir regardé cette vidéo. Veillez à consulter les trois autres parties de cette série de vidéos intitulée SSIS : conception et paramétrage des performances. Nous espérons que vous avez trouvée cette vidéo intéressante et aimerions vraiment savoir ce que vous en pensez. Sur la page d'accueil de la vidéo, recherchez le lien pour évaluer et commenter dans l'angle supérieur gauche.

Partager via

Paramétrage du flux de données de votre package SSIS dans l'entreprise (Vidéo liée à SQL Server)

Résumé de la vidéo

Remerciements pour la vidéo

Transcription de la vidéo

Voir aussi

Autres ressources

Aide et informations

Ressources supplémentaires