Comment faire pour que mon processus SSIS s'exécute plus rapidement ?

Question

EDIT

Cette question a été fermée sur SO et repostée sur ServerFault.

https://serverfault.com/questions/333168/how-can-i-make-my-ssis-process-consume-more-resources-and-run-faster

J'ai un processus ETL quotidien dans SSIS qui construit mon entrepôt afin que nous puissions fournir des rapports jour après jour.

J'ai deux serveurs - un pour SSIS et l'autre pour la base de données du serveur SQL. Le serveur SSIS (SSIS-Server01) est un boîtier 8CPU, 32GB RAM. La base de données SQL Server (DB-Server) est une autre boîte de 8CPU, 32GB RAM. Les deux sont des machines virtuelles VMWare.

Dans sa forme simplifiée à l'extrême, le SSIS lit 17 millions de lignes (environ 9 Go) à partir d'une seule table sur le serveur de base de données, les déporte à 408 millions de lignes, effectue quelques recherches et une tonne de calculs, puis les agrège à environ 8 millions de lignes qui sont écrites à chaque fois dans une toute nouvelle table sur le même serveur de base de données (cette table sera ensuite déplacée dans une partition pour fournir des rapports au jour le jour).

J'ai une boucle qui traite 18 mois de données à la fois, soit un total de 10 ans de données. J'ai choisi 18 mois sur la base de mon observation de l'utilisation de la RAM sur le serveur SSIS - à 18 mois, il consomme 27 Go de RAM. Au-delà, SSIS commence à mettre en mémoire tampon sur le disque et les performances chutent.

J'utilise Le distributeur de données équilibrées de Microsoft pour envoyer les données sur 8 chemins parallèles afin de maximiser l'utilisation des ressources. Je fais une union avant de commencer à travailler sur mes agrégations.

Voici le graphique du gestionnaire de tâches du serveur SSIS

Voici un autre graphique montrant les 8 CPUs individuels

Comme vous pouvez le voir sur ces images, l'utilisation de la mémoire augmente lentement jusqu'à environ 27G à mesure que de plus en plus de lignes sont lues et traitées. Cependant l'utilisation du CPU est constante autour de 40%.

Le deuxième graphique montre que nous n'utilisons que 4 (parfois 5) processeurs sur 8.

J'essaie de faire en sorte que le processus s'exécute plus rapidement (il n'utilise que 40 % de l'unité centrale disponible).

Comment puis-je faire en sorte que ce processus soit plus efficace (moins de temps, plus de ressources) ?

Answer 1

Avez-vous déjà essayé de diviser le traitement de 18 mois en 2 ou 3 lots supplémentaires ? À moins, bien sûr, que votre schéma de partitionnement exige que les 18 mois soient réunis dans cette partition - mais il serait alors curieux de voir comment et pourquoi vous partitionnez les données avec ce schéma. Et il serait toujours possible de se séparer en lots si vous avez des validations en place lorsque vous recréez vos index/contraintes

D'après mon expérience, j'ai dû créer une tâche qui devait traiter entre 50 et 60 millions d'enregistrements et, bien que la source provienne de fichiers de données et que la destination soit une table dans le serveur, les répartir en lots s'est avéré une méthode plus rapide que de tout faire d'un coup.

Êtes-vous inquiet de la baisse de performance due au fait qu'il s'agit d'une base de données hautement transactionnelle ? Si c'est le cas, disposez-vous d'un système de redondance des données ?

[edit#01]

Re:Commentaire#01 : Désolé si je suis un peu confus ; je voulais dire que le jour prévu pour le traitement des enregistrements, il serait bon d'avoir une tâche planifiée pour votre paquet ssis exécuté à certains intervalles (donc tester combien de temps un mois est traité et prendre la moyenne et lui donner un tampon pour le temps) traitant un mois ou deux à la fois (si possible) et puis juste définir une tâche supplémentaire en haut pour calculer/déterminer quel mois doit être traité.

Juste un exemple :

< en supposant seulement que deux mois prennent moins d'une heure à terminer >

[exécution programmée] : 01:00

[ssis task 01] obtenir la valeur de l'heure de l'heure actuelle. si l'heure = 1, alors définir monthtoprocessstart = 1 et monthtoprocessend = 2.

[ssis task 02 and so on] : travailler avec des données dont les mois sont dans la plage (monthtoprocessstart et end pour l'année que vous traitez)

Si c'est plus confus, faites-le moi savoir afin que je puisse supprimer l'édition Merci

Answer 2

Au bout du compte, tout traitement est lié à l'un des quatre facteurs suivants

• Mémoire
• CPU
• Disque
• Réseau

La première étape consiste à identifier le facteur limitant, puis à déterminer si vous pouvez l'influencer (acquérir davantage ou réduire l'utilisation de)

Choix des composants

La raison pour laquelle la mémoire de votre serveur s'épuise lorsque vous faites plus de 18 mois est liée à la raison pour laquelle le traitement est si long. Le site Transformations Pivot et Agrégat sont des composants asynchrones. Chaque ligne provenant du composant source se voit allouer N octets de mémoire. Ce même seau de données visite toutes les transformations, se voit appliquer leurs opérations et est vidé à la destination. Cette mémoire est réutilisée à l'infini.

Lorsqu'un composant asynchrone entre dans l'arène, le pipeline est divisé. Le seau qui transportait cette ligne de données doit maintenant être vidé dans un nouveau seau pour compléter le pipeline. Cette copie de données entre les arbres d'exécution est une opération coûteuse en termes de temps d'exécution et de mémoire (elle peut doubler). Cela réduit également la possibilité pour le moteur de paralléliser certaines des opportunités d'exécution puisqu'il attend que les opérations asynchrones se terminent. Un autre ralentissement des opérations est dû à la nature des transformations. L'agrégat est un composant entièrement bloquant. todos les données doivent arriver et être traitées avant que la transformation ne libère une seule ligne pour les transformations en aval.

Si c'est possible, pouvez-vous pousser le pivot et/ou l'agrégat sur le serveur ? Cela devrait réduire le temps passé dans le flux de données ainsi que les ressources consommées.

Vous pouvez essayer d'augmenter le nombre d'opérations parallèles que le moteur peut choisir. L'article de Jamie , L'article de SQL CAT

Si vous voulez vraiment savoir où votre temps est passé dans le flux de données, enregistrez le OnPipelineRowsSent pour une exécution. Ensuite, vous pouvez utiliser ceci requête pour le démonter (après avoir substitué sysssislog à sysdtslog90)

Transfert de réseau

Sur la base de vos graphiques, il ne semble pas que le CPU ou la mémoire soit taxé sur l'une ou l'autre des boîtes. Je crois que vous avez indiqué que le serveur source et le serveur de destination se trouvent sur une seule machine, mais que le paquet SSIS est hébergé et traité sur une autre machine. Vous payez un coût non négligeable pour transférer ces données sur le fil et vice-versa. Est-il possible de traiter les données sur le serveur source ? Il faudrait allouer plus de ressources à cette machine et je croise les doigts pour qu'il s'agisse d'une grosse machine virtuelle et que ce ne soit pas un problème.

Si ce n'est pas possible, essayez de paramétrer l'option Taille des paquets du gestionnaire de connexion à 32767 et demandez à l'opérateur réseau si les trames jumbo vous conviennent. Ces deux conseils se trouvent dans la section Optimisez votre réseau.

Je suis nul en compteurs de disques mais vous devriez être capable de voir si les types d'attente sont liés au disque.