Come configurare e risolvere i problemi relativi al parametro SubscriptionStreams del agente di distribuzione in SQL Server

Versione originale del prodotto: SQL Server (tutte le versioni supportate)
Numero KB originale: 953199

Questo articolo fornisce altre informazioni sul parametro SubscriptionStreams, sulle procedure consigliate quando si usa questo parametro e sulla risoluzione dei problemi associata.

Introduzione

Il parametro SubscriptionStreams può essere utilizzato per controllare il numero di connessioni. In una replica transazionale in Microsoft SQL Server, è possibile usare il parametro per abilitare più connessioni usate dal agente di distribuzione per applicare batch di modifiche in parallelo a un Sottoscrittore. Questa operazione migliora notevolmente la velocità effettiva della replica. Allo stesso tempo, il agente di distribuzione può comunque mantenere molte delle stesse caratteristiche transazionali di quando il agente di distribuzione usa una singola connessione per applicare le modifiche. Se non è possibile eseguire o eseguire il commit di una delle connessioni, tutte le connessioni interromperanno il batch corrente e l'agente userà un singolo flusso per ritentare i batch non riusciti. Prima del completamento di questa fase di ripetizione dei tentativi, nel Sottoscrittore possono essere presenti incoerenze transazionali temporanee. Dopo aver eseguito il commit dei batch non riusciti, il Sottoscrittore viene riportato a uno stato di coerenza transazionale.

Quando si specifica un valore pari o superiore a 2 per il parametro SubscriptionStreams, l'ordine in cui le transazioni vengono ricevute nel Sottoscrittore può essere diverso dall'ordine in cui sono state effettuate nel server di pubblicazione. Se questo comportamento causa violazioni dei vincoli durante la sincronizzazione, è consigliabile usare l'opzione NOT FOR REPLICATION per disabilitare l'imposizione dei vincoli durante la sincronizzazione. Per altre informazioni, vedere Controllare il comportamento dei trigger e dei vincoli nella sincronizzazione.

Fattori da considerare prima di abilitare SubscriptionStreams

SubscriptionStreams si occupa principalmente della latenza dal server di distribuzione al Sottoscrittore, quindi prima di decidere di passare SubscriptionStreamsa , assicurarsi di essere effettivamente in esecuzione in latenza dal server di distribuzione al Sottoscrittore. È possibile usare i token di traccia in Monitoraggio replica o Monitor prestazioni contatori come SQLServer:Replication Dist.>Dist:Delivery Latency per avere un'idea del livello di latenza.

La latenza dal server di distribuzione al Sottoscrittore può essere causata da molti motivi, ad esempio, ma non solo, quelli seguenti:

• Blocco nel server di distribuzione o nel Sottoscrittore
• Qualsiasi collo di bottiglia, nel server di distribuzione o nel Sottoscrittore, ad esempio unità disco lente, larghezza di banda di rete lenta e statistiche non aggiornate
• Transazioni bulk provenienti dal server di pubblicazione
• La frequenza delle transazioni in ingresso dal server di pubblicazione è troppo elevata
• Trigger o indici non necessari nel database sottoscritto

L'amministratore del database (DBA) deve eseguire una chiamata e verificare se SubscriptionStreams li aiuterà o meno. Ad esempio, in caso di blocco nel Sottoscrittore, l'aumento del numero di connessioni simultanee non aiuterà, ma potrebbe peggiorare la situazione. Mentre in situazioni come la frequenza delle transazioni in ingresso da Publisher è troppo elevata e si ritiene che un singolo thread per il agente di distribuzione non sia in grado di gestire il carico in ingresso, è possibile prendere in considerazione l'aumento del valore del parametro SubscriptionStreams a >=2. Può essere utile anche in situazioni di rete lenta e disco lento. Idealmente, il valore massimo per questo parametro è 64, ma il valore consigliato (o un buon valore con cui iniziare) è uguale al numero di processori fisici nella destinazione (Sottoscrittore).

Come configurare il parametro SubscriptionStreams

SubscriptionStreamsè uno di quei parametri non visibili in agente di distribuzione Profilo in Monitoraggio replica. È possibile specificare un valore per questo parametro dell'agente usando @subscriptionstreamssp_addsubscription (Transact-SQL) o aggiungere questo parametro alla sezione del comando del processo dell'agente di distribuzione usando la procedura seguente:

1. Aprire Monitoraggio replica, espandere Autore personale e selezionare la pubblicazione nella finestra del riquadro sinistro. Nella finestra del riquadro destro, nella sezione Tutte le sottoscrizioni , verrà visualizzato l'elenco di tutti i sottoscrittori di questa pubblicazione.

2. Fare clic con il pulsante destro del mouse sul Sottoscrittore per abilitare il parametro SubscriptionStreams e selezionare Visualizza dettagli. Verrà visualizzata una nuova finestra con i dettagli della sessione agente di distribuzione.

3. In questa nuova finestra selezionare Azione nella barra dei menu in alto e selezionare agente di distribuzione Proprietà processo. Verrà aperta la finestra Proprietà processo per il agente di distribuzione.

4. Selezionare Passaggi nella finestra del riquadro sinistro, quindi selezionare Esegui agente nella finestra del riquadro destro e quindi selezionare Modifica. Verrà visualizzata una nuova finestra.

5. Scorrere fino alla fine della sezione del comando (all'estrema destra) e aggiungere questo parametro -SubscriptionStreams 6.

6. Salvare le impostazioni e riavviare il processo di agente di distribuzione. Per implementare le modifiche è necessario riavviare agente di distribuzione.

Determinazione del numero di flussi

È possibile notare miglioramenti delle prestazioni usando il parametro SubscriptionStreams. Se si verifica un miglioramento, il miglioramento può essere nominale. È difficile determinare il tipo di miglioramento delle prestazioni che ogni sottosistema del disco sul mercato fornirà usando SubscriptionStreams. È pertanto consigliabile preparare un ambiente di test che simula l'ambiente di produzione. È possibile testare scenari che usano SubscriptionStreams usando vari valori di configurazione e uno scenario che non usa SubscriptionStreams.

È consigliabile eseguire test di carico sulla pubblicazione e sulla sottoscrizione per determinare i miglioramenti delle prestazioni che è possibile ottenere usando SubscriptionStreams. È consigliabile eseguire i test di base delle prestazioni per comprendere la velocità effettiva prevista del sottosistema del disco. Prima di eseguire ogni test, applicare molte modifiche per creare un carico nel server di pubblicazione. Quando si crea il carico, assicurarsi che agente di distribuzione non venga eseguito. Quando la replica ha una latenza sufficiente, eseguire il agente di distribuzione per testare le prestazioni per le configurazioni seguenti:

• Non usare il parametro SubscriptionStreams.
• Impostare il valore di SubscriptionStreams su uguale al numero di processori nel server. Ad esempio, se il server dispone di otto processori, impostare il valore su SubscriptionStreams 8.
• Specificare valori diversi per per SubscriptionStreams ottenere la configurazione ottimale.

Quando si esegue il test, è possibile monitorare i contatori delle prestazioni seguenti del agente di distribuzione:

• Dist: Cmds recapitati/sec
• Dist: Latenza recapito

Comportamento del agente di distribuzione dopo aver specificato il parametro SubscriptionStreams

Il agente di distribuzione mantiene il numero di sessioni/connessioni specificate in SubscriptionStreams. Il agente di distribuzione usa queste sessioni per applicare le modifiche nel Sottoscrittore.

Tuttavia, dopo aver specificato SubscriptionStreams e eseguito il agente di distribuzione per qualche tempo, il agente di distribuzione può passare all'uso di una sola sessione per applicare le modifiche al Sottoscrittore.

Motivi per cui il agente di distribuzione passare all'uso di una sola sessione

Il agente di distribuzione può passare all'uso di una sola sessione per molti motivi. Di seguito sono riportati i motivi più comuni:

• Quando il agente di distribuzione applica le modifiche, una delle sessioni genera un errore.

  Ad esempio, il agente di distribuzione inserisce una riga in una tabella figlio usando una sessione. Se si verifica prima che il agente di distribuzione inserisca la riga corrispondente nella tabella padre usando un'altra sessione, una violazione del vincolo di chiave esterna genera un messaggio di errore.

• Il thread di monitoraggio di blocco rileva il blocco. Il blocco può verificarsi per uno dei motivi seguenti:

  • Il agente di distribuzione esegue un'operazione INSERT e UPDATE su una tabella nel Sottoscrittore usando sessioni diverse. Se la tabella contiene un indice non cluster univoco, il blocco tra le due sessioni può verificarsi quando il agente di distribuzione aggiorna le chiavi di indice della tabella.

  • Nel Sottoscrittore il agente di distribuzione esegue istruzioni DML (Data Manipulation Language) su più tabelle. Se in queste tabelle è definita una vista indicizzata, può verificarsi un blocco tra le due sessioni quando la vista indicizzata aggiorna le chiavi di indice condivise.

  • Il agente di distribuzione esegue un'istruzione DML su una tabella nel Sottoscrittore usando una sessione. I trigger DML sono definiti in questa tabella. I trigger DML eseguono istruzioni DML su un'altra tabella che viene aggiornata usando un'altra sessione. In questo caso, può verificarsi un blocco tra le due sessioni.

È consigliabile non usare gli oggetti di database seguenti nel database sottoscrittore:

• Vincoli di chiave esterna
• Indici non cluster univoci
• Viste indicizzate
• Trigger DML che possono causare il blocco tra sessioni

Come determinare se il agente di distribuzione è passato all'uso di una sola sessione

A tale scopo, usare uno dei metodi seguenti:

• Metodo 1

  Eseguire una query sulla sys.dm_exec_sessions DMV (Dynamic Management View) per le sessioni di connessione al database di sottoscrizione. Se viene visualizzata una sola sessione di connessione, il agente di distribuzione potrebbe essere passato all'uso di una sola sessione. Se vengono visualizzate più sessioni di connessione, il agente di distribuzione usa ancora il numero specificato di sessioni.

  Per verificare che il agente di distribuzione sia passato all'uso di una sessione, usare il metodo 2 o il metodo 3.

• Metodo 2

  Eseguire una query sulla colonna comments della tabella msdistribution_history nel database di distribuzione. Se il risultato della query contiene la voce seguente, il agente di distribuzione è passato all'uso di una sessione:

  Il processo non è riuscito a completare l'ultimo batch in modalità multi-streaming, è stato reimpostato sulla modalità di connessione singola e sta ritentando l'operazione.

• Metodo 3

  Esaminare il file di output del agente di distribuzione. Il agente di distribuzione è passato all'uso di una sola sessione se il file di output contiene lo stesso messaggio di errore del metodo 2.

  Il file di output seguente è un esempio:

  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.
  

Come risolvere i problemi di un agente di distribuzione che passa all'uso di una sola sessione

1. Eseguire il SQL Server Profiler nel Sottoscrittore per acquisire l'evento del report Del processo bloccato e l'evento Exception. Questi eventi registrano il blocco e gli errori che si verificano quando il agente di distribuzione applica le modifiche.

   Nota

   L'evento Exception può essere causato da qualsiasi tipo di errore che può essere associato al problema. Ad esempio, l'errore può essere causato da una violazione del vincolo di chiave esterna.

2. Usare uno dei metodi in Come determinare se il agente di distribuzione è passato all'uso di una sola sezione di sessione per monitorare il agente di distribuzione.

3. Se il agente di distribuzione è passato all'uso di una sessione, arrestare la traccia.

4. Dal file di output del agente di distribuzione o dalla colonna start_time della tabella msdistribution_history, ottenere il timestamp della voce seguente:

   Il processo non è riuscito a completare l'ultimo batch in modalità multi-streaming, è stato reimpostato sulla modalità di connessione singola e sta ritentando l'operazione.

5. Aprire il file di traccia (con estensione trc) dal Sottoscrittore. Individuare uno script di blocco o un evento di eccezione il cui timestamp è identico o molto vicino al timestamp ottenuto nel passaggio 4.

6. Se si nota un'eccezione, esaminare i dettagli dell'eccezione per determinare la causa. Ad esempio, l'eccezione può essere causata da una violazione del vincolo di chiave esterna. In tal caso, è consigliabile rimuovere il vincolo di chiave esterna nel database del Sottoscrittore.

   Se si nota uno script di blocco, il problema è causato dal blocco. Di seguito è riportato uno script di blocco di esempio:

   <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> 
   

   Lo script di blocco registra una sessione bloccata e una sessione di blocco. La sessione bloccata inizia dal tag <blocked-process>. La sessione di blocco inizia dal tag <blocking-process>.

7. Individuare l'oggetto Object IdProc nella sessione bloccata e nella sessione di blocco.

   Nello script di blocco di esempio, l'oggetto Object Id di Proc nella sessione bloccata è 2000986455. L'oggetto Object Id di Proc nella sessione di blocco è 1172459501.

8. Nel database di sottoscrizione eseguire una query sulla vista sys.objects specificando che la colonna object_id sia uguale agli ID oggetto ottenuti nel passaggio 7. In questo caso, è possibile determinare i nomi degli oggetti.

   Ad esempio, eseguire la query seguente nel contesto del database di sottoscrizione:

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

   Nota

   • Il segnaposto <SubDBName> rappresenta il nome del database di sottoscrizione.
   • In genere, questi oggetti sono stored procedure usate nella replica.

9. Determinare l'indice o la vista indicizzata che causa il blocco. A tal fine, attenersi alla seguente procedura:

   1. Nello script di blocco individuare il valore della proprietà waitresource.

      Nello script di blocco di esempio il valore di waitresource è 72057594375700480.

   2. Eseguire una query sulla vista sys.partitions per ottenere l'ID oggetto e l'ID indice specificando che la colonna PARTITION_ID sia uguale al valore dell'oggetto waitresource ottenuto nel passaggio 9a.

      Ad esempio, eseguire la query seguente:

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

   3. Nel database di sottoscrizione eseguire una query sulla vista sys.indexes per determinare l'indice usando l'ID oggetto e l'ID indice ottenuti nel passaggio 9b.

      Ad esempio, eseguire la query seguente:

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

      Nota

      • Il segnaposto <objID> rappresenta l'ID oggetto ottenuto nel passaggio 9b.
      • Il segnaposto <idxID> rappresenta l'ID indice ottenuto nel passaggio 9b.

10. Se il blocco è causato da una vista indicizzata, è consigliabile eliminare la vista indicizzata. Se il blocco è causato da un indice non cluster univoco, è consigliabile eliminare l'indice e quindi ricreare un indice non univoco.

Descrizione del thread di monitoraggio di blocco

Il agente di distribuzione mantiene un thread di monitoraggio di blocco che rileva il blocco tra le sessioni. Se il thread di monitoraggio bloccante rileva il blocco tra le sessioni, il agente di distribuzione passa all'uso di una sessione per riapplicare il batch corrente di comandi che il agente di distribuzione non è stato possibile applicare in precedenza.

Per altre informazioni sul thread di monitoraggio di blocco, vedere Thread di monitoraggio di blocco.

Come il agente di distribuzione riprende più sessioni

Prima che il agente di distribuzione possa riprendere più sessioni, il agente di distribuzione deve eseguire la stored procedure sp_MSget_repl_commands per eseguire nuovamente una query sul database di distribuzione per i comandi che non sono stati applicati nel Sottoscrittore. Il agente di distribuzione deve quindi applicare tutti questi comandi nel Sottoscrittore prima che il agente di distribuzione possa riprendere più sessioni. In un ambiente di replica latente, il agente di distribuzione non può riprendere più sessioni perché il agente di distribuzione deve applicare molti comandi nel Sottoscrittore prima che il agente di distribuzione possa riprendere più sessioni.

Per tenere traccia dell'intero processo, esaminare il file di output del agente di distribuzione.