[SSIS] Montée en charge, quelques conseils.

On me demande souvent comment vérifier la montée en charge d’un lot SSIS. Je l’explique comme cela: contrairement à beaucoup de processus d’optimisation, les bottlenecks ici ne sont pas nombreux, il y en a un possible par lettre de l’acronyme:

E: vitesse de lecture de la source de données
T: performances du serveur de transformation
L: vitesse d’écriture de la destination de données

On peut donc les résumer en deux parties: performances d’I/O et performances du processus ETL.

Diagnostiquer son ETL

En ce qui concerne SSIS, une chose à retenir est que ce produit en tant qu’ETL et pas ELT est fait, conçu, optimisé pour travailler en mémoire. Une des premières chose à vérifier est que cela est bien le cas.

Vérifier le « Swap »!

Pour cela, le compteur Buffer Spooled vous donne les buffers écrits sur disque: ces buffers démontrent une chose: votre système manque de mémoire et de la même manière qu’un OS « swappe » dans ce cas, votre ETL est en train d’écrire ses données de travail sur disque. Cela implique des compteurs d’I/O disque élevés, et donc un CPU en hausse dans perfmon et des performances en baisse. Comme souvent, la mémoire est la cause matérielle de ce genre de défaillance.

Concevoir en pensant à la mémoire.

Mais ce manque de mémoire peut aussi être dû a des erreurs de conception. SSIS est optimisé en mémoire donc à vous de l’économiser! Déjà, sélectionnez uniquement les colonnes utiles, puis sur les sources non SQL Server, vérifiez que les types SSIS alloués à ces colonnes de données ne soient pas trop grands! Quelques octets en trop dans vos types et potentiellement des dizaines de Mo partent en fumée.
De même, évitez autant que faire se peut les transformations asynchrones – qui créent des buffers supplémentaires en sortie – surtout celles dites bloquantes – qui attendent l’arrivée de toutes les entrées pour émettre la première sortie.
Un aggregate SSIS doit ramener en mémoire toutes les lignes pour calculer son aggrégation: s’il est sur une seule source, pourquoi ne pas le faire dans le SGBDR, il est conçu pour ça, idem pour un tri! Le flux redevient alors non-bloquant, les sorties écrivent en même temps que les entrées lisent et la mémoire utilisée chute.

Idem pour les transformations semi-bloquantes (ralentissant le flux). Faites des casts Unicode/ANSI dans le moteur. Ce ne sont que des exemples mais l’économie effectuée ici est critique!
Enfin stimulez le parallélisme: n’hésitez pas autant que possible a segmenter vos traitements en plusieurs data-flows pour forcer la création de processus distincts: à vous de faire très attention aux problématiques de Lock à ce moment là, j’ai beaucoup rencontré de packages générateurs de deadlocks…

Diagnostiquer les sources et les destinations

Le problème vient très souvent du « T », puisqu’il est rare dans le cas de SGBDR de disposer d’architectures proposant des performances médiocres en I/O. Cette croyance peut cependant vous faire passer à côté de problèmes bien réels sur ces systèmes.

Le réseau, les providers

Vérifier que votre réseau est bien paramétré, testez en local sur la base vos requêtes ETL et métrez les par rapport à un accès distant, par exemple en regardan les compteurs de lecture des dataflows dans perfmon.
Utilisez de plus autant que possible des providers OLE DB ou ADO.NET (il en existe pour presque tous les SGBDR).

Limiter le log!

Attention au mode de recovery (ou ses équivalents chez les concurrents). Un mode de recovery full logge énormément. Essayez de basculer en mode Bulk pour de gros chargements.
Pour la même raison, évitez les opérations mono-ligne (OLE DB Command) et préférez leur des opérations set-based: un TRUNCATE ne logge pas toutes les lignes supprimées par exemple. (Et pour la dernière fois – on peut rêver – un truncate peut être inclus dans une transaction et donc être rollbacké!)

Paramétrer finement ses destinations.

En ce qui concerne les locks, faites attention aux accès concurrents dans des problématiques de parallélisme (voir ci-dessus). Une des manières de résoudre cela est par exemple de committer aussi vite que possible vos données en descendant le commit size des destinations OLE DB. Mais cela va induire d’un autre côté plus d’ouverture de transactions et donc plus de commits. Dans ce genre de cas il n’y a pas de règle unique, cela dépend du genre de table et du type d’accès:
– Problématiques de lock escalation: réduire le batch autour d’une valeur faible mais suffisante, je mets souvent 10000 lignes.

Sinon dans les autres cas:
– Table non-cluster avec insertion massive: essayer de réduire le nombre de tran jusqu’à sa limite de 1.
– Table cluster: une valeur dépendant de beaucoup de choses: j’ai constaté que généralement un batch de l’ordre du million est un bon compromis. On peut cependant décider d’une stratégie de ALTER INDEX NOCHECK / WITH CHECK avant et après le data flows, qui dans les problématiques d’insertion en masse donne de très bon résultats.

Voilà, n’hésitez pas à compléter ces petites guidelines.

A bientôt!

Edit 28 Mai 2009:

Fabrice Michelonnet apporte une précision très intéressante sur l’inutilité flagrante de tenter de supprimer des colonnes avec des Union All, la duplication de buffers induite par le composant UA étant en général plus couteuse en ressources que de garder les colonnes inutiles dans le flux. C’est ici. Merci Fab😉

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s