Lorsque on veut optimiser les performances d’un package SSIS, une des principales recommandation est de modifier la mémoire tampon des flot de données afin de pouvoir charger autant de données que possible dans une seule taille de mémoire tampon.
Selon Microsoft, pour des performances optimales, SSIS doit être configuré pour une utilisation optimale de la mémoire en disposant des tampons avec autant de lignes que possible sans dépasser la limite interne de 100 Mo.
Paramètres de réglage de la mémoire tampon
Deux paramètres par défaut sont utilisés pour le chargement des données en mémoire et ils doivent tous être alignés correctement pour empêcher les données d’être envoyées de la mémoire sur le disque.
DefaultMaxBufferSize – la valeur par défaut est 10 Mo. La taille maximale est de 100 Mo, que SSIS stocke sous le nom MaxBufferSize. Cette valeur ne peut jamais dépasser 100 Mo. Adaptez-la donc correctement à son environnement.
DefaultMaxBufferRows – La valeur par défaut est 10 000 lignes. Ce paramètre doit être défini de la même manière que vous gagnez avec le prix est correct. Pour gagner, les lignes de mémoire tampon maximum * (fois) par défaut, la taille de la ligne doit être aussi proche que possible de DefaultMaxBufferSize sans dépasser.
Quelle taille ?
Pour savoir quoi mettre dans les DefaultMaxBufferRows, il faut connaître la taille des lignes. N’oubliez pas que pour gagner, la valeur doit être aussi proche de la taille de DefaultMaxBufferSize sans dépasser.
Si la taille de la mémoire tampon est trop grande, le package sera plus lent car les lignes seront mises en cache sur le disque.
Assurez-vous donc de ne rien rater. Quelle est la taille de la ligne? Il y a plusieurs façons de le savoir. Vous pouvez consulter chaque champ de la requête en cours de chargement par SSIS et, en fonction du type de données, ajouter la valeur de tous les champs. Heureusement, ce n’est pas le seul moyen de déterminer la taille des lignes. Si votre source de données est SQL Server, vous avez de la chance, car les tables système peuvent vous aider à déterminer sa taille. Voici un exemple de requête, en supposant que le nom de votre table est « F_Fact » .
Exec sp_SpaceUsed ‘F_Fact’
Pour déterminer la taille de votre mémoire tampon pour cette table entière, prenez le nombre de (données * 1024) / Lignes car 100 Mo est la taille maximale que vous pouvez définir.
Pour calculer la taille de la ligne, utilisez les valeurs de formule 1402424/437418 * 1024 = 3283.088, ou 3284 octets par ligne.
Si vous définissez DefaultMaxBufferRows sur 100 Mo, ce qui correspond au maximum et ce que je recommande dans la plupart des cas, il s’agit de 104857600 octets correspondant à la taille de la mémoire tampon. Taille du tampon / Taille de la ligne = DefaultMaxBufferRows. 104857600/3284 = 31929.84, définissez donc DefaultMaxBufferRows sur 31930.
Si vous utilisez les colonnes, vous pouvez obtenir les mêmes informations en consultant les colonnes syscolumns. En utilisant la longueur de colonne, il est relativement facile de déterminer la taille appropriée de votre tampon, en additionnant les longueurs de colonne. Un mot de prudence. Je ne veux pas dire que, comme les informations sont disponibles par table, il convient de choisir Table ou Vue dans la source SSIS. Au contraire.
Accédez toujours aux données en utilisant une instruction Select car elle fonctionne mieux.
