Partager via

  • Article

Amélioration des performances du flux de données

Cette rubrique offre des suggestions pour éviter les problèmes de performances les plus fréquents lors de la conception de packages Integration Services. Cette rubrique fournit également des informations sur les fonctionnalités et les outils que vous pouvez utiliser pour résoudre des problèmes liés aux performances des packages.

Configuration du flux de données

Pour configurer la tâche de flux de données afin d'obtenir de meilleures performances, vous pouvez configurer les propriétés de la tâche, régler la taille du tampon et configurer le package pour une exécution parallèle.

Configuration des propriétés de la tâche de flux de données

Notes

Les propriétés abordées dans cette section doivent être définies séparément pour chaque tâche de flux de données d'un package.

Vous pouvez configurer les propriétés suivantes de la tâche de flux de données qui affectent toutes les performances :

  • Précisez les emplacements de stockage provisoires des données de tampon (propriété BufferTempStoragePath) et des colonnes contenant des données d'objets BLOB (Binary Large Objects) (propriété BLOBTempStoragePath). Par défaut, ces propriétés contiennent les valeurs des variables d'environnement TEMP et TMP. Vous pouvez préciser d'autres dossiers pour placer les fichiers temporaires sur un autre lecteur de disque dur ou un lecteur plus rapide, ou bien les répartir sur plusieurs lecteurs. Vous pouvez spécifier plusieurs répertoires en séparant leurs noms par un point-virgule.

  • Définissez la taille par défaut du tampon qu'utilise la tâche en définissant la propriété DefaultBufferSize, puis le nombre maximal de lignes dans chaque tampon en définissant la propriété DefaultBufferMaxRows. La taille par défaut du tampon est de 10 mégaoctets (Mo) avec une taille maximale de 100 mégaoctets. Le nombre maximal de lignes par défaut est 10 000.

  • Définissez le nombre de threads que peut utiliser la tâche au cours de l'exécution en définissant la propriété EngineThreads. Cette propriété donne une indication au moteur de flux de données sur le nombre de threads à utiliser. La valeur par défaut est 5 et la valeur minimale 3. Néanmoins, le moteur n'utilise pas plus de threads qu'il n'en faut, quelle que soit la valeur de cette propriété. Si besoin est, pour éviter des problèmes de concurrence, le moteur peut également utiliser plus de threads que le nombre spécifié dans cette propriété.

  • Indiquez si la tâche de flux de données s'exécute en mode optimisé (propriété RunInOptimizedMode). Le mode optimisé améliore les performances en supprimant les colonnes, les sorties et les composants inutilisés du flux de données.

    Notes

    Une propriété du même nom (RunInOptimizedMode) peut être définie au niveau du projet dans Business Intelligence Development Studio afin d'indiquer que la tâche de flux de données fonctionne en mode optimisé lors du débogage. La propriété du projet remplace la propriété RunInOptimizedMode des tâches de flux de données au moment de la conception.

Régler la taille des tampons

Le moteur de flux de données entame le processus de redimensionnement de ses tampons en calculant la taille estimée pour une seule ligne de données. Il multiplie ensuite la taille estimée d'une ligne par la valeur de DefaultBufferMaxRows pour obtenir une valeur de travail préliminaire de la taille du tampon.

  • Si le résultat est supérieur à la valeur de DefaultBufferSize, le moteur réduit le nombre de lignes.

  • Si le résultat est inférieur à la taille de tampon minimale calculée en interne, le moteur augmente le nombre de lignes.

  • Si le résultat obtenu se situe entre la taille de tampon minimale et la valeur de DefaultBufferSize, le moteur redimensionne le tampon le plus proche possible de la valeur de taille de ligne estimée multipliée par la valeur de DefaultBufferMaxRows.

Lorsque vous commencez à évaluer les performances de vos tâches de flux de données, utilisez les valeurs par défaut de DefaultBufferSize et DefaultBufferMaxRows. Activez la journalisation dans la tâche de flux de données et sélectionnez l'événement BufferSizeTuning pour connaître le nombre de lignes figurant dans chaque tampon.

Avant de régler la taille des tampons, l'amélioration la plus importante à apporter est de réduire la taille de chaque ligne de données en supprimant les colonnes inutiles et en configurant comme il se doit les types de données.

Si vous disposez de suffisamment de mémoire, utilisez si possible un nombre plus réduit de tampons volumineux, plutôt qu'un plus grand nombre de petits tampons. En d'autres termes, vous pouvez améliorer les performances en diminuant le nombre total de tampons nécessaires à la conservation de vos données, puis en faisant tenir autant de lignes de données que possible dans un tampon. Pour déterminer la quantité optimale de tampons et leur taille, faites un essai avec les valeurs de DefaultBufferSize et DefaultBufferMaxRows et analysez simultanément les performances et les informations recueillies par l'événement BufferSizeTuning.

N'augmentez pas la taille du tampon au point de déclencher la pagination sur le disque. Cela aurait des effets plus néfastes sur les performances que la non-optimisation de la taille du tampon. Pour déterminer si la pagination est en cours, surveillez le compteur de performance « Mémoires tampon spoulées » dans le composant logiciel enfichable Performance de la console MMC (Microsoft Management Console). 

Configurer le package pour une exécution parallèle

L'exécution parallèle améliore les performances sur les ordinateurs dotés de plusieurs processeurs physiques ou logiques. Pour prendre en charge l'exécution parallèle de tâches différentes dans le package, Integration Services utilise deux propriétés : MaxConcurrentExecutables et EngineThreads.

La propriété MaxConcurrentExcecutables

La propriété MaxConcurrentExecutables est une propriété du package lui-même. Cette propriété définit le nombre de tâches pouvant s'exécuter simultanément. La valeur par défaut est -1, ce qui correspond au nombre de processeurs physiques ou logiques plus 2.

Pour comprendre comment cette propriété fonctionne, imaginez un package composé de trois tâches de flux de données. Si vous attribuez à MaxConcurrentExecutables la valeur 3, les trois tâches de flux de données peuvent s'exécuter simultanément. Toutefois, supposez que chaque tâche de flux de données comporte 10 arborescences d'exécution de la source vers la destination. Le fait d'attribuer à MaxConcurrentExecutables la valeur 3 ne garantit pas que les arborescences d'exécution à l'intérieur de chaque tâche de flux de données s'exécuteront en parallèle.

La propriété EngineThreads

La propriété EngineThreads est une propriété de chaque tâche de flux de données. Cette propriété définit le nombre de threads que le moteur de flux de données peut créer et exécuter en parallèle. La propriété EngineThreads s'applique aussi bien aux threads sources que le moteur de flux de données crée pour les sources qu'aux threads de travail que le moteur crée pour les transformations et les destinations. Par conséquent, si vous attribuez à EngineThreads la valeur 10, le moteur pourra créer jusqu'à dix threads sources et dix threads de travail.

Pour comprendre comment cette propriété fonctionne, reprenez l'exemple de package composé de trois tâches de flux de données. Chaque tâche de flux de données contient dix arborescences d'exécution de la source vers la destination. Si vous attribuez à EngineThreads la valeur 10 sur chaque tâche de flux de données, les 30 arborescences d'exécution pourront potentiellement s'exécuter simultanément.

Notes

Les threads ne sont pas traités dans cette rubrique. Toutefois, la règle générale consiste à ne pas exécuter plus de threads en parallèle que le nombre de processeurs disponibles. Si vous exécutez plus de threads que le nombre de processeurs disponibles, le changement de contexte fréquent entre les threads peut nuire aux performances.

Configuration de composants de flux de données individuels

Pour configurer des composants de flux de données individuels afin d'obtenir de meilleures performances, il y a plusieurs règles générales que vous pouvez suivre. D'autres règles spécifiques s'appliquent également à chaque type de composant de flux de données : source, transformation et destination.

Règles générales

Indépendamment du composant de flux de données, deux règles générales sont à suivre pour améliorer les performances : optimiser les requêtes et éviter les chaînes inutiles.

Optimisation des requêtes

De nombreux composants de flux de données utilisent des requêtes, soit au cours de l'extraction de données à partir de sources, soit au cours d'opérations de recherche dans le but de créer des tables de référence. La requête par défaut utilise la syntaxe SELECT * FROM <NomTable>. Ce type de requête retourne toutes les colonnes dans la table source. Le fait de disposer de toutes les colonnes au moment de la conception permet de choisir n'importe quelle colonne comme colonne de recherche, comme colonne SQL directe ou comme colonne source. Cependant, après avoir sélectionné les colonnes à utiliser, vous devez vérifier la requête et vous assurer qu'elle contient uniquement les colonnes utilisées. La suppression de colonnes superflues permet de créer une ligne plus petite et donc d'accroître l'efficacité du flux de données dans un package. Avec des lignes plus petites, vous pouvez faire tenir plus de lignes dans un tampon et, de ce fait, réduire la charge de travail nécessaire pour traiter toutes les lignes dans le dataset.

Pour construire une requête, vous pouvez taper la requête ou utiliser le générateur de requêtes.

Notes

Lorsque vous exécutez un package dans Business Intelligence Development Studio, l'onglet Progression du concepteur SSIS affiche une liste d'avertissements, y compris un avertissement pour toutes les colonnes de données qu'une source met à la disposition du flux de données mais qui ne sont pas utilisées ensuite par les composants de flux de données en aval. Vous pouvez faire appel à la propriété RunInOptimizedMode pour supprimer automatiquement ces colonnes.

Suppression des tris non nécessaires

Le tri est, par essence, une opération lente et la décision d'éviter un tri inutile peut améliorer les performances du flux de données du package.

Parfois, les données sources ont déjà été triées avant d'être utilisées par un composant en aval. Ce pré-triage peut avoir lieu soit parce que la requête SELECT utilise une clause ORDER BY, soit parce que les données ont été insérées dans la source par ordre de tri. Pour de telles données sources pré-triées, vous pouvez fournir un indicateur qui précise que les données sont triées afin d'éviter l'utilisation d'une transformation de tri pour satisfaire aux spécifications de tri de certaines transformations en aval. (Par exemple, les transformations de fusion et de jointure de fusion nécessitent des entrées triées). Pour fournir un indicateur qui précise que les données sont triées, vous devez effectuer les tâches suivantes :

  • attribuer à la propriété IsSorted sur la sortie d'un composant de flux de données en amont la valeur True ;

  • spécifier les colonnes de clé de tri sur lesquelles les données sont triées.

Pour plus d'informations, consultez Procédure : trier des données pour les transformations de fusion et de jointure de fusion.

Si vous devez trier les données dans le flux de données, vous pouvez améliorer les performances en concevant le flux de données de façon à utiliser aussi peu d'opérations de tri que possible. Par exemple, le flux de données utilise une transformation de multidiffusion pour copier le dataset. Triez le dataset une fois avant que la transformation de multidiffusion ne s'exécute au lieu de trier plusieurs sorties après la transformation.

Pour plus d'informations, consultez Transformation de tri, Transformation de fusion, Transformation de jointure de fusion et Transformation de multidiffusion.

Sources

Source OLE DB

Lorsque vous utilisez une source OLE DB pour extraire les données d'une vue, sélectionnez « Commande SQL » comme mode d'accès aux données et entrez une instruction SELECT. L'utilisation d'une instruction SELECT pour accéder aux données offre de meilleures performances que le mode d'accès aux données « Table ou vue ».

Transformations

Utilisez les suggestions de cette section pour améliorer les performances des transformations d'agrégation, de recherche floue, de regroupement probable, de recherche, de jointure de fusion et de dimension à variation lente.

Transformation d'agrégation

La transformation d'agrégation inclut les propriétés Keys, KeysScale, CountDistinctKeys et CountDistinctScale. Ces propriétés améliorent les performances en permettant à la transformation de préallouer la quantité de mémoire dont la transformation a besoin pour les données que la transformation met en cache. Si vous savez le nombre exact ou approximatif des groupes attendus d'une opération Group by, définissez les propriétés Keys et KeysScale, respectivement. Si vous savez le nombre exact ou approximatif des valeurs distinctes attendues d'une opération Distinct count, définissez les propriétés CountDistinctKeys et CountDistinctScale, respectivement.

Si vous devez créer plusieurs agrégations dans un flux de données, songez à créer plusieurs agrégations qui utilisent une transformation d'agrégation au lieu de créer plusieurs transformations. Cette approche améliore les performances lorsqu'une agrégation est un sous-ensemble d'une autre agrégation, car la transformation peut optimiser le stockage interne et analyser une seule fois les données entrantes. Par exemple, si une agrégation utilise une clause GROUP BY et une agrégation AVG, le fait de les combiner en une seule transformation peut améliorer les performances. Toutefois, du fait que la réalisation de plusieurs agrégations au sein d'une transformation d'agrégation sérialise les opérations d'agrégation, il est possible que les performances ne s'améliorent pas lorsque plusieurs agrégations doivent être calculées indépendamment.

Pour plus d'informations, consultez Transformation d'agrégation.

Transformations de recherche floue et de regroupement probable

Pour plus d'informations sur l'optimisation des performances des transformations de recherche floue et de regroupement probable, consultez le livre blanc Présentation des transformations Fuzzy Lookup (recherche approximative) et Fuzzy Grouping (regroupement approximatif) dans les services DTS (Data Transformation Services) de SQL Server 2005.

Transformation de recherche

Réduisez la taille des données de référence en mémoire en entrant une instruction SELECT qui recherche uniquement les colonnes dont vous avez besoin. Cette approche est plus performante que la sélection d'une table ou d'une vue entière qui retourne une quantité importante de données inutiles.

Transformation de jointure de fusion

La transformation de jointure de fusion inclut la propriété MaxBuffersPerInput, qui spécifie le nombre maximal de tampons qui peuvent être actifs pour chaque entrée à un moment donné. Vous pouvez utiliser cette propriété pour régler la quantité de mémoire que les tampons consomment et par la même occasion améliorer les performances de la transformation. Plus le nombre de tampons est important, plus la transformation utilise de mémoire et plus les performances sont élevées. La valeur par défaut de la propriété MaxBuffersPerInput est 5, ce qui est le nombre de tampons qui fonctionne bien dans la plupart des scénarios. Pour régler les performances, vous voudrez peut-être essayer d'utiliser un nombre de mémoires tampons légèrement différent, comme 4 ou 6. Si possible, évitez d'utiliser un très petit nombre de mémoires tampons. Par exemple, le réglage de la propriété MaxBuffersPerInput à 1 au lieu de 5 a un impact significatif sur les performances. N'attribuez pas non plus une valeur égale ou inférieure à 0 à MaxBuffersPerInput. Cette plage de valeurs signifie en effet l'absence de toute limitation, et il se peut que le package ne s'exécute pas selon le chargement des données et la quantité de mémoire disponible.

Pour éviter tout blocage, la transformation de jointure de fusion peut augmenter provisoirement le nombre de tampons qu'elle utilise au-delà de la valeur de MaxBuffersPerInput. Lorsque la condition de blocage est résolue, la propriété MaxBuffersPerInput reprend sa valeur de configuration.

Pour plus d'informations, consultez Transformation de jointure de fusion.

Transformation de dimension à variation lente

L'Assistant Dimension à variation lente et la transformation de dimension à variation lente sont des outils à caractère général qui répondent aux besoins de la plupart des utilisateurs. Toutefois, le flux de données généré par l'Assistant n'est pas optimisé en termes de performances.

En général, les composants les plus lents de la transformation de dimension à variation lente sont les transformations de commande OLE DB qui effectuent des mises à jour sur une ligne à la fois. Par conséquent, le moyen le plus efficace pour améliorer les performances de la transformation de dimension à variation lente consiste à remplacer les transformations de commande OLE DB. Vous pouvez remplacer ces transformations par des composants de destination qui enregistrent toutes les lignes à mettre à jour dans une table de transit. Ensuite, vous pouvez ajouter une tâche d'exécution SQL qui effectue une opération UPDATE Transact-SQL basée sur un jeu unique sur toutes les lignes en même temps.

Les utilisateurs expérimentés peuvent concevoir un flux de données personnalisé pour le traitement des dimensions à variation lente qui est optimisé pour les grandes dimensions. Pour en savoir plus et obtenir un exemple de cette approche, consultez la section « Unique dimension scenario » dans le livre blanc Project REAL: Business Intelligence ETL Design Practices (en anglais).

Destinations

Pour obtenir de meilleures performances avec les destinations, songez à utiliser une destination SQL Server et à tester les performances de la destination.

Destination SQL Server

Lorsqu'un package charge des données dans une instance de SQL Server sur le même ordinateur, utilisez une destination SQL Server. Cette destination est optimisée pour les chargements en masse à haute vitesse.

Évaluation des performances des destinations

L'enregistrement des données sur les destinations peut être plus long que prévu. Pour déterminer si la lenteur provient de l'incapacité de la destination à traiter rapidement des données, vous pouvez provisoirement remplacer la destination par une transformation de calcul du nombre de lignes. Si le débit en sortie s'améliore significativement, il est probable que la destination chargeant les données est la cause du ralentissement.

Analyse des performances du package

Integration Services comprend des outils et des fonctionnalités que vous pouvez utiliser pour analyser les performances d'un package. Par exemple, la journalisation permet de capturer des informations sur un package au moment de l'exécution et les compteurs de performances vous permettent de surveiller le moteur de flux de données. Utilisez les suggestions suivantes pour déterminer quelles parties du package ont la plus grande incidence sur les performances. 

Vérification des informations de l'onglet Progression

Le concepteur SSIS fournit des informations sur le flux de contrôle et le flux de données lorsque vous exécutez un package dans Business Intelligence Development Studio. L'onglet Progression énumère les tâches et les conteneurs par ordre d'exécution et indique les heures de début et de fin, les avertissements et les messages d'erreur pour chaque tâche et chaque conteneur, y compris le package lui-même. Il répertorie également les composants de flux de données par ordre d'exécution et dévoile des informations sur la progression (sous forme de pourcentage) et le nombre de lignes traitées.

Pour activer ou désactiver l'affichage de messages sous l'onglet Progression, basculez l'option Création de rapports de progression de débogage dans le menu SSIS. La désactivation du rapport de progression peut aider à améliorer les performances lors de l'exécution d'un package complexe dans BI Development Studio.

Configuration de la journalisation dans le package

Integration Services inclut différents modules fournisseurs d'informations pour permettre aux packages d'enregistrer des informations au moment de l'exécution dans différents types de fichiers ou dans SQL Server. Vous pouvez activer les entrées de journal pour les packages et les objets de package individuels tels que les tâches et les conteneurs. Integration Services inclut une grande variété de tâches et de conteneurs, et chaque tâche et conteneur possède son propre ensemble d'entrées de journal descriptives. Par exemple, un package qui inclut une tâche d'exécution SQL peut écrire une entrée de journal qui indique l'instruction SQL exécutée par la tâche, y compris les valeurs de paramètres de l'instruction.

Les entrées de journal comprennent des informations telles que les dates de début et de fin des packages et des objets de package, rendant possible l'identification des tâches et des conteneurs qui s'exécutent lentement. Pour plus d'informations, consultez Journalisation de l'exécution des packages, Implémentation de la journalisation dans les packages et Messages personnalisés pour la journalisation.

Configuration de la journalisation pour les tâches de flux de données

La tâche de flux de données fournit un grand nombre d'entrées de journal personnalisées à l'aide desquelles vous pouvez analyser et améliorer les performances. Vous pouvez, par exemple, analyser les composants susceptibles de provoquer des fuites de mémoire ou contrôler le temps nécessaire à l'exécution d'une tâche en particulier. Pour obtenir une liste de ces entrées de journal personnalisées et un exemple de sortie de journalisation, consultez Tâche de flux de données.

Utiliser l'événement PipelineComponentTime

L'entrée de journal personnalisée la plus utile est peut-être l'événement PipelineComponentTime. Cette entrée de journal signale le nombre de millisecondes que chaque composant dans le flux de données passe sur chacune des cinq étapes de traitement majeures. Le tableau suivante décrit ces étapes de traitement. Les développeurs Integration Services reconnaîtront ces étapes comme les méthodes principales d'un PipelineComponent.

Étape

Description

Validate

Le composant recherche des valeurs de propriété et des paramètres de configuration valides.

PreExecute

Le composant effectue un traitement unique avant de commencer à traiter les lignes de données.

PostExecute

Le composant effectue un traitement unique après avoir traité toutes les lignes de données.

ProcessInput

Le composant de transformation ou de destination traite les lignes de données entrantes qu'une source ou une transformation en amont lui a passées.

PrimeOutput

Le composant source ou de transformation remplit les tampons de données à passer à un composant de transformation ou de destination en aval.

Lorsque vous activez l'événement PipelineComponentTime, Integration Services consigne un message pour chaque étape de traitement effectuée par chaque composant. Les entrées de journal suivantes illustrent un sous-ensemble des messages consignés par l'exemple de package Integration Services CalculatedColumns :

The component "Calculate LineItemTotalCost" (3522) spent 356 milliseconds in ProcessInput.

The component "Sum Quantity and LineItemTotalCost" (3619) spent 79 milliseconds in ProcessInput.

The component "Calculate Average Cost" (3662) spent 16 milliseconds in ProcessInput.

The component "Sort by ProductID" (3717) spent 125 milliseconds in ProcessInput.

The component "Load Data" (3773) spent 0 milliseconds in ProcessInput.

The component "Extract Data" (3869) spent 688 milliseconds in PrimeOutput filling buffers on output "OLE DB Source Output" (3879).

The component "Sum Quantity and LineItemTotalCost" (3619) spent 141 milliseconds in PrimeOutput filling buffers on output "Aggregate Output 1" (3621).

The component "Sort by ProductID" (3717) spent 16 milliseconds in PrimeOutput filling buffers on output "Sort Output" (3719).

Ces entrées de journal montrent que la tâche de flux de données a passé la plupart du temps dans les étapes suivantes, répertoriées ici dans l'ordre décroissant :

  • La source OLE DB nommée "Extract Data" a passé 688 ms à charger des données.

  • La transformation de colonne dérivée nommée "Calcule LineItemTotalCost" a passé 356 ms à effectuer des calculs sur les lignes entrantes.

  • La transformation d'agrégation nommée "Sum Quantity and LineItemTotalCost" a passé un total de 220 ms (141 ms dans PrimeOutput et 79 ms dans ProcessInput) à effectuer des calculs et à passer les données à la transformation suivante.

Analyse des performances du moteur de flux de données

Integration Services inclut un ensemble de compteurs de performances pour l'analyse des performances du moteur de flux de données. Par exemple, vous pouvez suivre la quantité totale de mémoire (en octets) que tous les tampons utilisent et vérifier si la mémoire des composants est insuffisante. Une mémoire tampon est un bloc de mémoire utilisé par un composant pour stocker des données. Pour plus d'informations, consultez Analyse des performances du moteur de flux de données.

Ressources externes

Icône Integration Services (petite) Rester à jour avec Integration Services

Pour obtenir les derniers téléchargements, articles, exemples et vidéos de Microsoft, ainsi que des solutions sélectionnées par la communauté, visitez la page Integration Services sur MSDN :


Pour recevoir une notification automatique de ces mises à jour, abonnez-vous aux flux RSS disponibles sur la page.