Dépannage des performances du package

Mis à jour : 15 septembre 2007

Integration Services comprend des fonctionnalités et des outils que vous pouvez utiliser pour résoudre des problèmes liés aux performances des packages. Par exemple, la journalisation permet de capturer des informations sur les packages au moment de l'exécution et les compteurs de performances vous permettent de surveiller le moteur de flux de données. Cette rubrique fournit des informations sur ces fonctionnalités, ainsi que des suggestions sur la manière de concevoir des packages pour éviter des problèmes de performance courants.

Conception du flux de données pour de meilleures performances

Vous pouvez résoudre les problèmes liés aux performances des flux de données dans les packages en testant les résultats des suggestions suivantes dans votre package :

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 renvoie 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 choisi les colonnes dont vous souhaitez vous servir, vous devez vérifier la requête et vous assurer qu'elle contient uniquement les colonnes utilisées. Vous pouvez taper la requête ou utiliser le générateur de requêtes pour créer la requête. La suppression des colonnes superflues rend le flux de données dans un package plus efficace, car plus une ligne est petite, plus le nombre de lignes capables de tenir dans un seul tampon est élevé et moins les efforts consentis pour traiter l'ensemble des lignes du dataset sont importants.

Remarque :

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.

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

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, la valeur de cette propriété correspond à la valeur de la variable d'environnement TEMP. Vous pouvez préciser d'autres dossiers pour placer les fichiers temporaires sur un autre lecteur du disque dur 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 (valeur minimale de 2). 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.

  Remarque :
  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.

Mode de redimensionnement des tampons par la tâche de flux de données

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.

Réglage de la taille des tampons

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.

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

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.

Si les données source sont triées, 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, vous pouvez fournir une indication selon laquelle les données sont triées et éviter d'utiliser une transformation de tri pour satisfaire les exigences de tri requis pour certaines transformations en aval. Par exemple, les transformations de fusion et de jointure de fusion nécessitent des entrées triées. Pour fournir une indication, vous devez définir la propriété IsSorted de la sortie d'un composant de flux de données en amont avec la valeur True et spécifier les colonnes de clé de tri dans lesquelles les données sont triées. Pour plus d'informations, consultez Procédure : définir des attributs de tri sur une sortie.

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, si le flux de données utilise une transformation de multidiffusion pour copier le dataset, vous pouvez trier celui-ci une fois avant que la transformation de multidiffusion n'effectue son travail, 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.

Optimisation de la transformation de dimension à variation lente

L'Assistant Dimension à variation lente et la transformation de dimension à variation lente sont des outils conçus pour un usage 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.

Dans la transformation de dimension à variation lente, les composants les plus lents sont généralement les transformations de commande OLE DB qui effectuent des opérations UPDATE sur une seule ligne à la fois. Vous pouvez, dans la plupart des cas, améliorer les performances de la transformation de dimension à variation lente en remplaçant les transformations de commande OLE DB par des composants de destination. Ces composants de destination enregistrent toutes les lignes à mettre à jour dans une table de transit. Vous pouvez ensuite ajouter une tâche d'exécution de requêtes SQL qui effectue une seule opération UPDATE Transact-SQL reposant sur un jeu pour toutes les lignes à la fois.

Les utilisateurs expérimentés peuvent concevoir un flux de données personnalisé pour la dimension à variation lente qui est optimisée pour les grandes dimensions. Pour une discussion et un exemple de cette approche, consultez la section portant sur le scénario de dimension unique dans le livre blanc Microsoft intitulé Project REAL: Business Intelligence ETL Design Practices (en anglais).

Optimisation des agrégations dans la transformation d'agrégation

La transformation d'agrégation comprend de nombreuses propriétés que vous pouvez utiliser pour améliorer ses performances. Si vous connaissez le nombre exact ou approximatif de valeurs clés dans le dataset, vous pouvez définir les propriétés Keys et KeysScale. Vous pouvez également spécifier le nombre exact ou approximatif de clés que la transformation doit traiter pour une opération COUNT DISTINCT en définissant les propriétés CountDistinctKeys et CountDistinctScale. Grâce à l'utilisation de ces propriétés, la transformation peut éviter de réorganiser des totaux mis en cache et améliore les performances.

Si vous avez besoin de créer plusieurs agrégations dans un flux de données, créez si possible plusieurs agrégations à l'aide d'une transformation d'agrégation au lieu de plusieurs transformations. Cette approche améliore surtout les performances lorsque les agrégations sont des sous-ensembles d'autres agrégations, 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. Cependant, vous devez envisager cette approche seulement si la mémoire est une contrainte, car la réalisation de plusieurs agrégations au sein d'une transformation d'agrégation applique les opérations d'agrégation en série.

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

Configuration de la limitation du nombre de tampons dans la 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 pouvez éventuellement essayer d'utiliser un nombre de tampons légèrement différent (par exemple, 4 ou 6). Si possible, évitez d'utiliser un nombre trop insignifiant de tampons. Par exemple, le réglage de la propriété MaxBuffersPerInput à 1 au lieu de 5 a un impact significatif sur les performances. Ne réglez pas non plus la valeur de la propriété MaxBuffersPerInput à 0. Cette valeur signifie en effet l'absence de toute limitation, et il se peut que le package ne s'exécute pas selon les données chargé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.

É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. Pour plus d'informations, consultez Transformation de calcul du nombre de lignes.

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. 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.

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.

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. Un 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.

Voir aussi

Tâches

Dépannage du développement des packages

Concepts

Dépannage de l'exécution des packages
Dépannage du service Integration Services

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications