SSIS : Point sur le mode Chargement rapide des destinations OLE DB (Ole Db Destination – Fast Load)

Petit rappel sur le fast load

La destination OLEDB des data flow task présente l’intérêt d’insérer par block de lignes en base de données plutôt que ligne à ligne.

Techniquement, les lignes sont donc insérées par lot, ce qui permet des performances nettement meilleures et une bonne utilisation des ressources physiques du serveur cible. Petit effet de bord, les commit étant réalisés sur des lots, lorsqu’une erreur est rencontrée tout le lot de ligne contenant celle en erreur est rejeté.

Il est donc nécessaire de comprendre comment ces lots sont délimités.

La propriété MaximumInsertCommitSize

Cette propriété indique la taille (en nombre de lignes) des lots appartenant à la même transaction lors des insertions réalisées par la destination. Conséquence directe, au moment du commit, si une des lignes du lot enfreint une contrainte, c’est tout le lot qui est rejeté.

Destination Ole Db

Il est notamment utile d’adapter cette propriété pour la gestion des erreurs.

La suite de l’article utilise un exemple basé sur adventureWorks2012.

Ma source est une source OLE Db extrayant toutes les lignes de la table Production.Product (504 lignes)

Ma destination est un nouvelle table nommée dbo.ProductBuffer qui reprend les mêmes colonnes que la table Prodution.Product et sur laquelle j’ai déclaré une clé primaire sur la colonne ProductId.

Avant de jouer mon test, j’insère dans la table dbo.ProductBuffer une ligne de ProductId : 1 pour provoquer une erreur lors de l’insertion d’un et d’un seul produit.

Voici le résultat :Flux de données avec 1 buffer en erreur

Je constate bien ici que le lot de 50 lignes contenant la seule ligne en erreur est intégralement rejeté.

Est-ce le seul paramètre qui influence la taille des transactions ?

Propriétés RowsPerBatch

Une destination OleDb configurée en Fast Load traduit cette opération par des instructions SQL Insert Bulk, ce que nous pouvons constater en posant un SQL Server Profiler par exemple lors de l’exécution de notre data flow.

La propriété RowsPerBatch de la destination OleDb permet d’associer une valeur au paramètre RowsPerBatch de l’instruction Bulk transmise au serveur destination.

Msdn (http://technet.microsoft.com/en-us/library/ms188267(v=sql.105).aspx) nous informe que ce paramètre permet au moteur SQL Server d’allouer au mieux les ressources physiques pour le volume de données à insérer. Il s’agit d’une heuristique d’allocation de ressource.

Cette valeur devrait donc être affectée au nombre de lignes qui seront réellement transmises par l’instruction bulk.

Pour la configurer finement, il faut donc connaitre effectivement le nombre d’instructions bulk requises par notre data flow.

Cette propriété n’est cependant pas censée modifier la taille des transactions.

Nombre de requêtes jouées (Taille des buffers , DefaultBufferMaxRows, DefaultBufferMaxSize)

Pour améliorer la gestion des I/O, nous pouvons être tentés d’augmenter ou diminuer suivant les cas la taille des envois vers la base. La question est alors de savoir combien de requêtes sont jouées par l’OLE DB Destination pour le flux.

Pour jouer sur la taille des lots transmis à la base (et donc l’utilisation des ressources I/O du serveur destination), il va falloir s’intéresser à la gestion des buffers dans les data flow tasks.

Il s’agit alors de comprendre la détermination de la taille des lots en fonction des propriétés MaximumInsertCommitSize de la destination et DefaultBufferMaxRows  (ou DefaultBufferMaxSize) du data flow lui-même.

Pour vérifier l’usage de ces propriétés, je reproduis mon test précédent en jouant sur les 2 propriétés. J’ai également inclus dans ce test la propriété RowsPerBatch de la destination pour valider qu’elle n’influence pas la taille des transactions.

Pour constater ce nombre de requête, j’ai utilisé un SQL Server profiler connecté à ma destination et intercepté les évènements SQL :BatchCompleted. Les commandes qui m’intéressent ont pour TextData quelque chose de la forme « Insert bulk… »

Propriété RowsPerBatch de la destination Propriété MaximumInsertCommitSize de la destination Propriété  DefaultBufferMaxRows du Data flow Nb requêtes (i.e. nb lot de ligne) Nb lignes en erreur (taille des transactions rejetées)
5000 (> nb lignes dans la source) 5000 (> nb lignes dans la source) 5000 (> nb lignes dans la source) 1 504
100 5000 (> nb lignes dans la source) 5000 (> nb lignes dans la source) 1 504
100 200 5000 (> nb lignes dans la source) 3 200
100 50 5000 (> nb lignes dans la source) 11 50
100 200 350 3 200
5000 (> nb lignes dans la source) 5000 (> nb lignes dans la source) 100 4 132
100 200 100 4 132
100 50 100 11 50

A noter qu’en ne jouant que sur la taille du buffer, je ne parviens pas à obtenir une transaction de moins de 132 lignes. Ceci est dû à une propriété non configurable dans SSIS : MinBufferSize dont la valeur est de 64Kb qui dicte la taille minimum pour les buffers et est prioritaire sur la propriété DefaultMaxBufferSize.

Conclusion

Le nombre de lignes présentes dans une transaction pour une destination OleDb (et donc le nombre de requêtes jouées sur la base) est déterminé en premier lieu par la taille des buffers utilisés (Taille définie par DefaultBufferMaxRows (ou DefautlBufferMaxSize)). Par défaut, il y aura 1 transaction réalisé par buffer. Si la taille du buffer est supérieure à la propriété MaximumInsertCommitSize de la destination OLE Db c’est alors cette propriété qui détermine la taille de la transaction.

Pour du tuning de performance exploitant la taille de buffer, n’hésitez pas à consulter ce très bon article sur lequel je suis tombé en préparant le mien : http://www.mssqltips.com/sqlservertip/3217/improve-ssis-data-flow-buffer-performance/