Data Flow SSIS jusqu'à 3× plus rapide : régler le buffer et le Fast Load

Il y a un scénario qui se répète dans presque tout projet d'ETL sur SQL Server : un package SSIS qui « a toujours marché » commence à traîner à mesure que le volume grandit. Le premier réflexe est souvent d'accuser le matériel ou de réécrire le Data Flow. La plupart du temps, le problème est bien moins coûteux à résoudre — il est dans le dimensionnement du buffer, un réglage que presque tout le monde laisse par défaut.

Dans ce guide, je montre les trois réglages au meilleur rendement par minute investie : AutoAdjustBufferSize, DefaultBufferMaxRows et le Fast Load en destination. Sur un chargement de 10 millions de lignes, cette combinaison peut faire chuter le temps d'exécution d'environ 2 minutes à 44 secondes — sans changer une seule ligne de logique.

Comment le Data Flow SSIS déplace les données

Le Data Flow ne traite pas ligne par ligne : il travaille avec des buffers — des blocs de mémoire qui transportent un ensemble de lignes à la fois, de la source à la destination. Plus le remplissage de ces buffers est efficace, moins le moteur fait de « voyages » et plus le débit est élevé.

La taille de chaque buffer est définie par deux propriétés du Data Flow Task :

• DefaultBufferMaxRows — nombre maximum de lignes par buffer. Par défaut : 10 000.
• DefaultBufferSize — limite de mémoire par buffer. Par défaut : 10 Mo.

SSIS place dans le buffer le plus grand nombre de lignes possible sans dépasser aucune des deux limites. Le détail important : ces valeurs par défaut viennent d'une époque où la mémoire serveur était rare. Sur les machines actuelles, elles sont petites et bridant la performance sans donner de signe évident.

Étape 1 — Activez AutoAdjustBufferSize

Comme les deux propriétés ci-dessus se limitent mutuellement, il est facile d'en ajuster une et que l'autre « coupe » l'effet. C'est pourquoi SQL Server 2016 a introduit la propriété AutoAdjustBufferSize.

Quand vous la définissez à True (la valeur par défaut est False), SSIS se met à calculer la taille du buffer à partir de DefaultBufferMaxRows et ignore simplement DefaultBufferSize. Autrement dit : vous dites combien de lignes vous voulez par buffer et le moteur ajuste la mémoire tout seul.

-- Propriétés du Data Flow Task
AutoAdjustBufferSize = True
DefaultBufferMaxRows = 50000

Cela élimine le bras de fer entre les deux propriétés et rend le tuning prévisible : vous raisonnez en lignes, pas en octets.

Étape 2 — Utilisez le Fast Load et maîtrisez le commit

Remplir des buffers plus grands n'aide que si la destination accepte aussi les données par lots. Sur l'OLE DB Destination, assurez-vous de :

• Access Mode = « Table or view - fast load » — utilise l'interface BULK INSERT au lieu d'insérer ligne par ligne.
• MaxInsertCommitSize = 100000 — contrôle combien de lignes entrent dans chaque transaction (commit). Des lots trop petits créent une surcharge de transaction ; des lots énormes retiennent le log et la mémoire. Une valeur de l'ordre de la centaine de milliers est généralement un bon point de départ.

-- OLE DB Destination
AccessMode = Table or view - fast load
MaxInsertCommitSize = 100000
Table lock = True   -- quand la fenêtre de chargement le permet

Étape 3 — Mesurez, ajustez et surveillez

Le gain dépend de la forme de vos données, alors mesurez avant et après. Dans un test classique documenté par la communauté, un chargement de 10 millions de lignes est passé de un peu plus de 2 minutes à 44 secondes simplement en montant DefaultBufferMaxRows à 50 000 (avec AutoAdjust activé) et le commit à 100 000.

Règles empiriques pour DefaultBufferMaxRows :

• Lignes larges (beaucoup de colonnes / types volumineux) : utilisez moins de lignes par buffer. Chaque ligne prend plus de mémoire, donc les buffers pleins débordent vite.
• Lignes étroites : utilisez plus de lignes par buffer pour tirer parti de la mémoire.
• Des buffers trop grands nuisent aussi — l'objectif est un équilibre entre remplir le buffer et maintenir un taux de commit sain.

Comment savoir si vous êtes allé trop loin : surveillez le compteur « Buffers Spooled » dans le log du Data Flow. S'il dépasse zéro, SSIS n'arrive pas à garder les buffers en mémoire et a commencé à paginer sur disque — signe clair pour réduire DefaultBufferMaxRows (ou libérer plus de mémoire pour le service).

Checklist rapide

1. Data Flow Task → AutoAdjustBufferSize = True.
2. Data Flow Task → DefaultBufferMaxRows calibré selon la largeur des lignes (commencez à 50 000).
3. OLE DB Destination → fast load + MaxInsertCommitSize (commencez à 100 000).
4. Exécutez avec des données réelles, comparez le temps et surveillez Buffers Spooled.

Conclusion

Avant de réécrire des packages ou de demander un serveur plus gros, passez dix minutes à revoir le buffer du Data Flow. AutoAdjustBufferSize, un DefaultBufferMaxRows bien calibré et le Fast Load en destination forment l'un des réglages au meilleur rapport coût-bénéfice en ETL sur SQL Server : le package reste le même, mais le chargement se termine en une fraction du temps. C'est du dimensionnement, pas de la magie — et cela vaut pour tout pipeline qui tourne encore sur les réglages d'usine.