Comment configurer et dépanner le paramètre SubscriptionStreams du Agent de distribution dans SQL Server

Version du produit d’origine : SQL Server (toutes les versions prises en charge)
Numéro de la base de connaissances d’origine : 953199

Cet article fournit plus d’informations sur le paramètre SubscriptionStreams, les bonnes pratiques lors de l’utilisation de ce paramètre et la résolution des problèmes associés.

Introduction

Le paramètre SubscriptionStreams peut être utilisé pour contrôler le nombre de connexions. Dans une réplication transactionnelle dans Microsoft SQL Server, vous pouvez utiliser le paramètre pour activer plusieurs connexions que le Agent de distribution utilise pour appliquer des lots de modifications en parallèle à un Abonné. Cette opération améliore considérablement le débit de réplication. En même temps, l’Agent de distribution peut conserver la plupart des mêmes caractéristiques transactionnelles que lorsque le Agent de distribution utilise une seule connexion pour appliquer les modifications. Si l’une des connexions ne parvient pas à s’exécuter ou à valider, toutes les connexions abandonnent le lot actuel et l’agent utilise un flux unique pour réessayer les lots ayant échoué. Avant la fin de cette phase de nouvelle tentative, il peut y avoir des incohérences transactionnelles temporaires sur l’Abonné. Une fois que les lots ayant échoué ont été correctement commités, l’Abonné est ramené à un état de cohérence transactionnelle.

Lorsque vous spécifiez une valeur supérieure ou égale à 2 pour le paramètre SubscriptionStreams, l’ordre dans lequel les transactions sont reçues sur l’Abonné peut différer de l’ordre dans lequel elles ont été effectuées sur le serveur de publication. Si ce comportement provoque des violations de contraintes pendant la synchronisation, vous devez utiliser l’option NOT FOR REPLICATION pour désactiver l’application des contraintes pendant la synchronisation. Pour plus d’informations, consultez Contrôler le comportement des déclencheurs et des contraintes dans la synchronisation.

Facteurs à prendre en compte avant d’activer SubscriptionStreams

SubscriptionStreams prend principalement en charge la latence du serveur de distribution à l’abonné. Par conséquent, avant de décider d’opter pour SubscriptionStreams, assurez-vous que vous rencontrez bien une latence entre le serveur de distribution et l’abonné. Vous pouvez utiliser des jetons de suivi dans le Moniteur de réplication ou Analyseur de performances compteurs comme SQLServer :Replication Dist.>Dist :Delivery Latency pour avoir une idée du niveau de latence.

La latence entre le serveur de distribution et l’abonné peut être due à de nombreuses raisons telles que, mais sans s’y limiter, les suivantes :

• Blocage au niveau du serveur de distribution ou de l’abonné
• Tout goulot d’étranglement, au niveau du serveur de distribution ou de l’abonné, comme les lecteurs de disque lents, la bande passante réseau lente et les statistiques obsolètes
• Transactions en bloc provenant de Publisher
• Le taux de transactions entrantes à partir du serveur de publication est trop élevé
• Déclencheurs ou index inutiles dans la base de données abonnée

L’administrateur de base de données (DBA) doit passer un appel et tester s’il SubscriptionStreams va l’aider ou non. Par exemple, en cas de blocage sur l’Abonné, l’augmentation du nombre de connexions simultanées n’aidera pas, mais pourrait aggraver la situation. Alors que dans des situations telles que le taux de transactions entrantes de Publisher est trop élevé et que vous pensez qu’un seul thread pour le Agent de distribution est incapable de faire face à la charge entrante, vous pouvez envisager d’augmenter la valeur du paramètre SubscriptionStreams à >=2. Cela peut également être utile dans les situations de réseau lent et de disque lent. Dans l’idéal, la valeur maximale de ce paramètre est 64, mais la valeur recommandée (ou une bonne valeur pour commencer) est égale au nombre de processeurs physiques au niveau de la destination (Abonné).

Comment configurer le paramètre SubscriptionStreams

SubscriptionStreamsest l’un de ces paramètres qui n’est pas visible dans Agent de distribution Profil dans le Moniteur de réplication. Vous pouvez spécifier une valeur pour ce paramètre d’agent à l’aide @subscriptionstreams de sp_addsubscription (Transact-SQL) ou ajouter ce paramètre à la section de commande de travail de l’agent de distribution à l’aide de la procédure suivante :

1. Ouvrez le Moniteur de réplication, développez Mon serveur de publication, puis sélectionnez votre composition dans la fenêtre du volet gauche. Dans la fenêtre du volet droit, sous la section Tous les abonnements , vous verrez la liste de tous les abonnés à cette publication.

2. Cliquez avec le bouton droit sur l’Abonné que vous souhaitez activer le paramètre SubscriptionStreams , puis sélectionnez Afficher les détails. Une nouvelle fenêtre s’affiche avec les détails de la session Agent de distribution.

3. Dans cette nouvelle fenêtre, sélectionnez Action dans la barre de menus en haut, puis Agent de distribution Propriétés du travail. La fenêtre Propriétés du travail s’ouvre pour le Agent de distribution.

4. Sélectionnez Étapes dans la fenêtre du volet gauche, puis sélectionnez Exécuter l’agent dans la fenêtre du volet droit, puis sélectionnez Modifier. Une nouvelle fenêtre s’affiche.

5. Faites défiler jusqu’à la fin de la section de commande (tout à droite) et ajoutez ce paramètre -SubscriptionStreams 6.

6. Enregistrez les paramètres et redémarrez le travail Agent de distribution. Un redémarrage de Agent de distribution est nécessaire pour implémenter les modifications.

Détermination du nombre de flux

Vous pouvez remarquer des améliorations des performances à l’aide du paramètre SubscriptionStreams. S’il y a une amélioration, l’amélioration peut être nominale. Il est difficile de déterminer le type d’amélioration des performances que chaque sous-système de disque sur le marché fournira à l’aide SubscriptionStreamsde . Par conséquent, nous vous recommandons de préparer un environnement de test qui simule l’environnement de production. Vous pouvez tester des scénarios qui utilisent SubscriptionStreams à l’aide de différentes valeurs de configuration et d’un scénario qui n’utilise SubscriptionStreamspas .

Nous vous recommandons d’effectuer des tests de charge sur la publication et l’abonnement pour déterminer les améliorations de performances que vous pouvez obtenir à l’aide SubscriptionStreamsde . Vous devez effectuer le test de la base de référence des performances pour comprendre le débit attendu du sous-système de disque. Avant d’effectuer chaque test, appliquez de nombreuses modifications pour créer une charge sur le serveur de publication. Lors de la création de la charge, assurez-vous que Agent de distribution ne s’exécute pas. Lorsque la réplication présente une latence suffisante, exécutez la Agent de distribution pour tester les performances des configurations suivantes :

• N’utilisez pas le paramètre SubscriptionStreams.
• Définissez la valeur de SubscriptionStreams pour qu’elle soit égale au nombre de processeurs sur le serveur. Par exemple, si le serveur a huit processeurs, définissez la valeur sur SubscriptionStreams 8.
• Spécifiez différentes valeurs pour afin SubscriptionStreams d’obtenir la configuration optimale.

Lorsque vous effectuez le test, vous pouvez surveiller les compteurs de performances suivants du Agent de distribution :

• Dist : Cmds livrés/s
• Dist : Latence de remise

Comportement du Agent de distribution après avoir spécifié le paramètre SubscriptionStreams

Le Agent de distribution conserve le nombre de sessions/connexions que vous spécifiez dans SubscriptionStreams. Le Agent de distribution utilise ces sessions pour appliquer des modifications à l’Abonné.

Toutefois, une fois que vous avez spécifié SubscriptionStreams et que le Agent de distribution s’exécute pendant un certain temps, le Agent de distribution peut passer à l’utilisation d’une seule session pour appliquer des modifications à l’Abonné.

Raisons pour lesquelles le Agent de distribution passer à l’utilisation d’une seule session

L’Agent de distribution peut passer à l’utilisation d’une seule session pour de nombreuses raisons. Les raisons les plus courantes sont les suivantes :

• Lorsque le Agent de distribution applique des modifications, l’une des sessions génère une erreur.

  Par exemple, le Agent de distribution insère une ligne dans une table enfant à l’aide d’une session. Si cela se produit avant que le Agent de distribution insère la ligne correspondante dans la table parente à l’aide d’une autre session, une violation de contrainte de clé étrangère génère un message d’erreur.

• Le thread de surveillance de blocage détecte le blocage. Le blocage peut se produire pour l’une des raisons suivantes :

  • Le Agent de distribution effectue une INSERT opération et une UPDATE opération sur une table sur l’Abonné à l’aide de différentes sessions. Si la table contient un index non cluster unique, un blocage entre les deux sessions peut se produire lorsque le Agent de distribution met à jour les clés d’index de la table.

  • Sur l’Abonné, le Agent de distribution exécute des instructions DML (Data Manipulation Language) sur plusieurs tables. Si une vue indexée est définie sur ces tables, un blocage entre les deux sessions peut se produire lorsque la vue indexée met à jour les clés d’index partagés.

  • Le Agent de distribution exécute une instruction DML sur une table de l’Abonné à l’aide d’une session. Les déclencheurs DML sont définis sur cette table. Les déclencheurs DML exécutent des instructions DML sur une autre table en cours de mise à jour à l’aide d’une autre session. Dans ce cas, un blocage peut se produire entre les deux sessions.

Nous vous recommandons vivement de ne pas utiliser les objets de base de données suivants dans la base de données de l’abonné :

• Contraintes de clé étrangère
• Index non cluster uniques
• Vues indexées
• Déclencheurs DML pouvant provoquer un blocage entre les sessions

Comment déterminer si le Agent de distribution est passé à l’utilisation d’une seule session

Pour ce faire, utilisez l’une des méthodes suivantes :

• Méthode 1

  Interrogez la vue de gestion dynamique (DMV) sys.dm_exec_sessions pour les sessions de connexion à la base de données d’abonnement. Si vous ne voyez qu’une seule session de connexion, le Agent de distribution est peut-être passé à l’utilisation d’une seule session. Si vous voyez plusieurs sessions de connexion, le Agent de distribution utilise toujours le nombre de sessions spécifié.

  Pour vérifier que le Agent de distribution a basculé vers une session, utilisez la méthode 2 ou la méthode 3.

• Méthode 2

  Interrogez la colonne comments de la table msdistribution_history dans la base de données de distribution. Si le résultat de la requête contient l’entrée suivante, le Agent de distribution est passé à l’utilisation d’une session :

  Le processus n’a pas réussi à terminer le dernier lot en mode multi-streaming, il a été réinitialisé en mode de connexion unique et recommencez l’opération.

• Méthode 3

  Examinez le fichier de sortie du Agent de distribution. Le Agent de distribution est passé à l’utilisation d’une seule session si le fichier de sortie contient le même message d’erreur que la méthode 2.

  Le fichier de sortie suivant est un exemple :

  Date/Time 100 transaction(s) with 1181 command(s) were delivered. 
  Date/Time 100 transaction(s) with 2672 command(s) were delivered. 
  Date/Time Bucket 6 aborted the wait for Ready To Commit event, deadlock found between spid 117 and 114 
  Date/Time Bucket 1 aborted the wait for Ready To Commit event, deadlock found between spid 117 and 114 
  Date/Time Bucket 3 aborted the wait for Ready To Commit event, deadlock found between spid 117 and 114 
  Date/Time Bucket 0 aborted the wait for Ready To Commit event, deadlock found between spid 117 and 114 
  Date/Time Bucket 5 aborted the wait for Ready To Commit event, deadlock found between spid 117 and 114 
  Date/Time Bucket 2 aborted the wait for Ready To Commit event, deadlock found between spid 117 and 114 
  Date/Time Bucket 7 aborted the wait for Ready To Commit event, deadlock found between spid 117 and 114 
  Date/Time Bucket 4 aborted the wait for Ready To Commit event, due to thread shutdown event 
  ... 
  Date/Time Number of subscription streams has been reset from 8 to 1, state 4. 
  Date/Time Disconnecting from Subscriber 
  SQLInstance 
  Date/Time Disconnecting from Subscriber 
  SQLInstance 
  
  Date/Time Disconnecting from Subscriber 
  SQLInstance 
  
  Date/Time Disconnecting from Subscriber 
  SQLInstance 
  
  Date/Time Disconnecting from Subscriber 
  SQLInstance 
  
  Date/Time Disconnecting from Subscriber 
  SQLInstance 
  
  Date/Time Disconnecting from Subscriber 
  SQLInstance 
  
  Date/Time Disconnecting from Subscriber 
  SQLInstance 
  
  
  Date/Time Connecting to Subscriber 
  SQLInstance 
  
  Date/Time The process failed to complete last batch in multi-streaming mode, it has been reset to single connection mode and is retrying the operation. 
  Date/Time 21 transaction(s) with 390 command(s) were delivered.
  

Comment résoudre les problèmes d’un Agent de distribution qui passe à l’utilisation d’une seule session

1. Exécutez le SQL Server Profiler sur l’Abonné pour capturer l’événement de rapport de processus bloqué et l’événement Exception. Ces événements enregistrent les blocages et les erreurs qui se produisent lorsque le Agent de distribution applique des modifications.

   Remarque

   L’événement Exception peut être provoqué par n’importe quel type d’erreur qui peut être associé au problème. Par exemple, l’erreur peut être due à une violation de contrainte de clé étrangère.

2. Utilisez l’une des méthodes de la section Guide pratique pour déterminer si le Agent de distribution est passé à l’utilisation d’une seule session pour surveiller les Agent de distribution.

3. Si le Agent de distribution est passé à l’utilisation d’une session, arrêtez la trace.

4. À partir du fichier de sortie du Agent de distribution ou de la colonne start_time de la table msdistribution_history, obtenez l’horodatage de l’entrée suivante :

   Le processus n’a pas réussi à terminer le dernier lot en mode multi-streaming, il a été réinitialisé en mode de connexion unique et recommencez l’opération.

5. Ouvrez le fichier de trace (.trc) à partir de l’Abonné. Recherchez un script bloquant ou un événement d’exception dont l’horodatage est identique ou très proche de l’horodatage que vous avez obtenu à l’étape 4.

6. Si vous remarquez une exception, examinez les détails de l’exception pour en déterminer la cause. Par exemple, l’exception peut être provoquée par une violation de contrainte de clé étrangère. Si c’est le cas, nous vous recommandons de supprimer la contrainte de clé étrangère dans la base de données de l’Abonné.

   Si vous remarquez un script bloquant, le problème est dû au blocage. Voici un exemple de script bloquant :

   <blocked-process-report monitorLoop="41589"> 
       <blocked-process> 
           <process id="process3a6d438" taskpriority="0" logused="24592" waitresource="KEY: 6:72057594375700480 (0100e420fa5a)" waittime="9937" ownerId="568644832" transactionname="user_transaction" lasttranstarted="2008-05-05T04:55:04.430" XDES="0xa5619e370" lockMode="X" schedulerid="11" kpid="6104" status="suspended" spid="58" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2008-05-05T04:55:04.553" lastbatchcompleted="2008-05-05T04:55:04.430" clientapp=<DistributionAgentProgram> hostname=<servername> hostpid="3980" loginname=<SQLAgentAcct>  isolationlevel="read committed (2)" xactid="568644832" currentdb="6" lockTimeout="4294967295" clientoption1="671090784" clientoption2="128056"> 
               <executionStack> 
               <frame line="5" stmtstart="642" stmtend="1600" sqlhandle="0x0300060057a14477a8c6dd00609a00000100000000000000"/> 
               </executionStack> 
               <inputbuf> 
               Proc [Database Id = 6 Object Id = 2000986455]
               </inputbuf> 
           </process> 
       </blocked-process> 
       <blocking-process> 
           <process status="sleeping" spid="68" sbid="0" ecid="0" priority="0" transcount="1" lastbatchstarted="2008-05-05T04:55:04.570" lastbatchcompleted="2008-05-05T04:55:05.103" clientapp=<DistributionAgentProgram> hostname=<servername> hostpid="3980" loginname=<SQLAgentAcct> isolationlevel="read committed (2)" xactid="568644998" currentdb="6" lockTimeout="4294967295" clientoption1="671090784" clientoption2="128056"> 
           <executionStack/> 
           <inputbuf> 
           Proc [Database Id = 6 Object Id = 1172459501]
           </inputbuf> 
           </process> 
       </blocking-process> 
   </blocked-process-report> 
   

   Le script de blocage enregistre une session bloquée et une session bloquante. La session bloquée démarre à partir de la balise <blocked-process>. La session de blocage démarre à partir de la balise <blocking-process>.

7. Recherchez le Object Id de l’objet Proc dans la session bloquée et dans la session bloquante.

   Dans l’exemple de script de blocage, le Object Id de Proc dans la session bloquée est 2000986455. Le Object Id de Proc dans la session de blocage est 1172459501.

8. Dans la base de données d’abonnement, interrogez la vue sys.objects en spécifiant que la colonne object_id doit être égale aux ID d’objet que vous avez obtenus à l’étape 7. Dans ce cas, vous pouvez déterminer les noms d’objets.

   Par exemple, exécutez la requête suivante dans le contexte de la base de données d’abonnement :

   USE <SubDBName> 
   GO 
   SELECT name FROM sys.objects 
   WHERE object_id = 1172459501 OR object_id = 2000986455 
   

   Remarque

   • L’espace réservé <SubDBName> représente le nom de la base de données d’abonnement.
   • En règle générale, ces objets sont des procédures stockées utilisées dans la réplication.

9. Déterminez l’index ou la vue indexée qui provoque le blocage. Pour cela, procédez comme suit :

   1. Dans le script de blocage, recherchez la valeur de la propriété waitresource.

      Dans l’exemple de script de blocage, la valeur de waitresource est 72057594375700480.

   2. Interrogez la vue sys.partitions pour obtenir l’ID d’objet et l’ID d’index en spécifiant que la colonne PARTITION_ID doit être égale à la valeur du que vous avez obtenue à l’étape waitresource 9a.

      Par exemple, exécutez la requête suivante :

      SELECT object_id, index_id FROM SYS.PARTITIONS WHERE PARTITION_ID=72057594375700480
      

   3. Dans la base de données d’abonnement, interrogez la vue sys.indexes pour déterminer l’index à l’aide de l’ID d’objet et de l’ID d’index que vous avez obtenus à l’étape 9b.

      Par exemple, exécutez la requête suivante :

      USE <SubDBName> 
      GO 
      SELECT name, type_desc, is_unique FROM sys.indexes 
      WHERE object_id = <objID> and index_id = <idxID>
      

      Remarque

      • L’espace réservé <objID> représente l’ID d’objet que vous avez obtenu à l’étape 9b.
      • L’espace réservé <idxID> représente l’ID d’index que vous avez obtenu à l’étape 9b.

10. Si le blocage est dû à une vue indexée, nous vous recommandons de supprimer la vue indexée. Si le blocage est dû à un index non cluster unique, nous vous recommandons de supprimer l’index, puis de recréer un index non unique.

Description du thread de surveillance de blocage

Le Agent de distribution gère un thread de surveillance de blocage qui détecte le blocage entre les sessions. Si le thread du moniteur de blocage détecte un blocage entre les sessions, le Agent de distribution passe à l’utilisation d’une session pour réappliquer le lot actuel de commandes que le Agent de distribution n’a pas pu appliquer précédemment.

Pour plus d’informations sur le thread de surveillance de blocage, consultez Thread de moniteur de blocage.

Comment le Agent de distribution reprend plusieurs sessions

Avant que le Agent de distribution puisse reprendre plusieurs sessions, le Agent de distribution doit exécuter la procédure sp_MSget_repl_commands stockée pour demander à nouveau la base de données de distribution pour les commandes qui n’ont pas été appliquées sur l’Abonné. Ensuite, le Agent de distribution doit appliquer toutes ces commandes sur l’Abonné avant que le Agent de distribution puisse reprendre plusieurs sessions. Dans un environnement de réplication latente, le Agent de distribution ne peut pas reprendre plusieurs sessions, car le Agent de distribution doit appliquer de nombreuses commandes sur l’Abonné avant que le Agent de distribution puisse reprendre plusieurs sessions.

Pour suivre l’ensemble du processus, examinez le fichier de sortie du Agent de distribution.