Le Blog de Pi-R (Pierre Cambier)

Optimisation des performances de la réplication avec SQL Server Compact, problématique de la volumétrie : PARTIE 3/3 (Autres pistes)

Dans la seconde partie de cet article, nous avons montré comment optimiser les synchronisations des terminaux et notamment comment améliorer l’utilisation des “Precompute partitions” en retravaillant le système de filtres.

D’autres actions peuvent également être entreprises si l’on souhaite aller plus loin en matière d’optimisations. Cette ultime partie de mon article expose plusieurs pistes pour améliorer davantage les performances…

Optimiser les mises à jour ou les synchronisations ?

Nous pouvons donc distinguer 2 types d’actions :

• Celles qui vont permettre d’optimiser les mises à jour volumineuses. Ces instructions devront être lancées avant les traitements de nuit par exemple dans le cadre de l’utilisation des “Precompute partitions”.
• Celles qui vont permettre de préparer au mieux le système pour accélérer les phases de synchronisations des terminaux. Ces traitements devront être exécutés juste après les mises à jour en masse.

Certaines actions permettent de préparer au mieux les mises à jour en masse sur la base de données (s’effectuant par exemple lors des traitements de nuits). Les modifications sur la base, effectuées par ces actions, devront être rétablies pour que le système soit de nouveau prêt à prendre en charge les synchronisations de terminaux portables.

Les statistiques :

Pour optimiser au mieux le temps d’exécution des requêtes SQL sur la base centrale, il est possible d’activer la génération automatique de statistiques. Les statistiques SQL Server permettent au système de connaitre les meilleurs “plans d’exécution” pour l’exécution d’une requête particulière. Cette option s’active dans les propriétés de la base de données (page “Options”) :

Cette option n’altère pas les performances des synchronisations.

Les indexes :

Pour optimiser toujours plus le temps d’exécution des requêtes suite à l’activation des “Precompute partitions”, nous pouvons prêter plus attention aux indexes posés sur les différentes tables articles.

Pour que les performances des requêtes soient optimales lors de mises à jour en masse :

• On peut placer des indexes de type “nonclustered index” sur tous les champs utilisés dans les jointures SQL du système de filtres.
• On peut désactiver temporairement les indexes fonctionnels (c’est à dire ceux qui servent uniquement pour le fonctionnel de l’application mobile) avant d’exécuter des mises à jour en masse sur la base.

Les indexes que nous désactivons doivent évidemment être réactivés juste après les mises à jour.

Remarque :

Il est également important de reconstruire régulièrement les indexes pour garder des performances optimales. En effet, il ne faut pas oublier que certaines lourdes modifications de données sur la base peuvent considérablement fragmenter les indexes. Cette fragmentation aura des effets négatifs sur la réplication SQL mais également sur l’application mobile (Les requêtes SELECT jouées sur la base mobile répliquée seront alors plus lentes).

Le degré de parallélisme :

SQL Server détecte le degré de parallélisme optimal, qui correspond au nombre de processeurs employés pour exécuter une seule instruction. Lors de l’exécution de requêtes SQL, le serveur prend en compte les plans d'exécution parallèle. Ce parallélisme, c'est-à-dire l’utilisation d’un maximum de processeurs, joue un rôle très important pour les temps de réponses des requêtes SQL. Pour permettre au serveur de déterminer le degré maximal de parallélisme, il faut définir cette option à 0.

L’option “max degree of parallelism” avec sa valeur 0, permet de maximiser le parallélisme avant des mises à jour volumineuses.

Cette option doit être modifiée à l’aide de la procédure stockée sp_configure :

exec sp_configure 'show advanced options', 1
reconfigure with override
exec sp_configure 'max degree of parallelism', 0
reconfigure with override

Inversement, pour optimiser les synchronisations de données, il est préférable de minimiser le parallélisme.

Le parallélisme peut avoir cette fois des effets négatifs lors des phases de synchronisations. En effet, des verrous (locks) peuvent régulièrement être positionnés sur les tables systèmes à cause du multi threading.

En choisissant d’utiliser un seul processeur pour les phases de synchros, nous limitons ainsi le nombre de threads et donc le nombre de verrou (locks) qui seront posés sur les tables systèmes.

Pour supprimer la génération de plans parallèles, il est nécessaire d’attribuer la valeur 1 à l'option “max degree of parallelism”.

Période de rétention :

La période de rétention correspond à un délai durant lequel les métadonnées liées aux synchronisations sont stockées et historisées dans des tables systèmes.

Une fois la période de rétention dépassée, les données sont purgées et les abonnements liés à la publication deviennent obsolètes et doivent être réinitialisés (voir article ici).

Aussi, pour éviter d’effectuer une réinitialisation, chaque terminal doit répliquer au moins une fois pendant le délai de rétention. Si un PDA ne réplique pas au moins une fois durant cette période, la réinitialisation de son abonnement (base locale) sera inévitable.

La période de rétention doit être paramétrée de manière habile, selon les besoins métiers :

• Si la période de rétention est trop courte, les terminaux sur le terrain risquent de devoir souvent réinitialiser leurs abonnements (très couteux).
• Si la période de rétention est trop longue, la volumétrie des métadonnées stockées en tables systèmes deviendrait alors trop importante et risquerait de fortement réduire les performances.

La configuration de la période de rétention est possible sur la page “General” des propriétés de la publication :

La période de rétention que l’on définit est très importante pour les performances du système. En effet, si la période de rétention est trop longue, la volumétrie des métadonnées stockées en tables systèmes deviendrait alors trop importante et risquerait de fortement réduire le temps d’exécution des mises à jour de données.

Profil de distribution :

La notion de “profil de distribution” représente un ensemble d’options utilisées par le distributor lors des phases de transferts de données vers les terminaux portables.

Lors de la configuration initiale du distributor, un profil par défaut lui est automatiquement attribué.

Il existe une astuce pour accélérer les phases de transferts dans le cas où de nombreuses lignes de données doivent fréquemment être distribuées vers les abonnés (= terminaux mobiles). En effet, il est possible de changer la taille des blocs de données lues et distribuées par le distributeur. Le fait d’augmenter la taille maximale des blocs (batch) peut accélérer les phases de synchronisations dans le sens où plus de données sont transférées en même temps.

Dans les propriétés du profil de distribution, nous pouvons ainsi modifier la valeur des options “DownloadReadChangesPerBatch” et “DownloadWriteChangesPerBatch” :

Le fait de définir la valeur 1000 au lieu de 100 pour ces 2 options aura un effet positif sur les temps de transferts lorsque le nombre de données à transférer sur un terminal sera de l’ordre de plusieurs centaines de lignes.

En effet, la distribution se basera alors sur des blocs de 1000 lignes au lieu de 100 pour transférer les données. Les phases de transfert des synchronisations seront alors accélérées dans le cas d’une grande volumétrie de données à descendre.

Pi-R.