Como configurar e solucionar problemas do parâmetro SubscriptionStreams do Agente de Distribuição no SQL Server

Versão original do produto: SQL Server (todas as versões com suporte)
Número de KB original: 953199

Este artigo fornece mais informações sobre o parâmetro SubscriptionStreams, melhores práticas ao usar esse parâmetro e solução de problemas associada.

Introdução

O parâmetro SubscriptionStreams pode ser usado para controlar o número de conexões. Em uma replicação transacional no Microsoft SQL Server, você pode usar o parâmetro para habilitar várias conexões que o Agente de Distribuição usa para aplicar lotes de alterações em paralelo a um Assinante. Essa operação aprimora muito a taxa de transferência de replicação. Ao mesmo tempo, o Agente de Distribuição ainda pode manter muitas das mesmas características transacionais que quando o Agente de Distribuição usa uma única conexão para aplicar as alterações. Se uma das conexões não for executada ou confirmada, todas as conexões anularão o lote atual e o agente usará um único fluxo para tentar novamente os lotes com falha. Antes que essa fase de repetição seja concluída, pode haver inconsistências transacionais temporárias no Assinante. Depois que os lotes com falha são confirmados com êxito, o Assinante é trazido de volta a um estado de consistência transacional.

Quando você especifica um valor de 2 ou maior para o parâmetro SubscriptionStreams, a ordem na qual as transações são recebidas no Assinante pode ser diferente da ordem na qual elas foram feitas no editor. Se esse comportamento causar violações de restrição durante a sincronização, você deverá usar a opção NOT FOR REPLICATION para desabilitar a aplicação de restrições durante a sincronização. Para obter mais informações, consulte Comportamento de Controle de Gatilhos e Restrições na Sincronização.

Fatores a serem considerados antes de habilitar SubscriptionStreams

SubscriptionStreams cuida principalmente da latência de Distribuidor para Assinante, portanto, antes de decidir ir para SubscriptionStreams, verifique se você está realmente correndo contra a latência de Distribuidor para Assinante. Você pode usar tokens de rastreamento no Replication Monitor ou Monitor de Desempenho contadores como SQLServer:Replication Dist.>Dist:Latência de Entrega para ter uma ideia do nível de latência.

A latência de Distribuidor para Assinante pode ser causada por vários motivos, como, mas não se limitando aos seguintes:

• Bloquear no Distribuidor ou no Assinante
• Qualquer gargalo, seja no Distribuidor ou no Assinante, como unidades de disco lentas, largura de banda de rede lenta e estatísticas obsoletas
• Transações em massa provenientes do Publisher
• A taxa de transações de entrada do Publisher é muito alta
• Gatilhos ou índices desnecessários no banco de dados inscrito

O DBA (Administrador de Banco de Dados) precisa fazer uma chamada e testar se SubscriptionStreams vai ajudá-los ou não. Por exemplo, em caso de bloqueio no Assinante, aumentar o número de conexões simultâneas não ajudará, mas pode piorar a situação. Considerando que em situações como a taxa de transação de entrada do Publisher é muito alta e você sente que um único thread para o Agente de Distribuição não consegue lidar com a carga de entrada, você pode considerar aumentar o valor do parâmetro SubscriptionStreams para >=2. Também pode ajudar em situações lentas de rede e disco lento. Idealmente, o valor máximo para esse parâmetro é 64, mas o valor recomendado (ou um bom valor para começar) é igual ao número de processadores físicos no Destino (Assinante).

Como configurar o parâmetro SubscriptionStreams

SubscriptionStreamsé um desses parâmetros que não está visível em Agente de Distribuição Perfil no Monitor de Replicação. Você pode especificar um valor para esse parâmetro de agente usando @subscriptionstreamssp_addsubscription (Transact-SQL) ou adicionar esse parâmetro à seção de comando de trabalho do agente de distribuição usando o seguinte procedimento:

1. Abra o Monitor de Replicação, expanda Meu Editor e selecione sua publicação na janela do painel esquerdo. Na janela do painel direito, na seção Todas as Assinaturas , você verá a lista de todos os assinantes desta publicação.

2. Clique com o botão direito do mouse no Assinante que você deseja habilitar o parâmetro SubscriptionStreams e selecione Exibir Detalhes. Uma nova janela aparecerá com os detalhes da sessão Agente de Distribuição.

3. Nesta nova janela, selecione Ação na barra de menus na parte superior e selecione Agente de Distribuição Propriedades do Trabalho. Isso abrirá a janela Propriedades do Trabalho para o Agente de Distribuição.

4. Selecione Etapas na janela do painel esquerdo e selecione Executar Agente na janela do painel direito e selecione Editar. Uma nova janela aparecerá.

5. Role até o final da seção de comando (o lado direito) e anexe este parâmetro -SubscriptionStreams 6.

6. Salve as configurações e reinicie o trabalho Agente de Distribuição. Uma reinicialização do Agente de Distribuição é necessária para implementar as alterações.

Determinando o número de fluxos

Você pode notar melhorias de desempenho usando o parâmetro SubscriptionStreams. Se houver uma melhoria, o aprimoramento poderá ser nominal. É difícil determinar que tipo de melhoria de desempenho cada subsistema de disco no mercado fornecerá usando SubscriptionStreams. Portanto, recomendamos que você prepare um ambiente de teste que simula o ambiente de produção. Você pode testar cenários que usam SubscriptionStreams usando vários valores de configuração e um cenário que não usa SubscriptionStreams.

Recomendamos que você execute testes de carga na publicação e assinatura para determinar as melhorias de desempenho que você pode obter usando SubscriptionStreams. Você deve executar o teste de linha de base de desempenho para entender a taxa de transferência esperada do subsistema de disco. Antes de executar cada teste, aplique muitas alterações para criar uma carga no Publisher. Ao criar a carga, verifique se Agente de Distribuição não é executado. Quando a replicação tiver latência suficiente, execute o Agente de Distribuição para testar o desempenho das seguintes configurações:

• Não use o parâmetro SubscriptionStreams.
• Defina o valor de SubscriptionStreams para ser igual ao número de processadores no servidor. Por exemplo, se o servidor tiver oito processadores, defina o valor como SubscriptionStreams 8.
• Especifique valores diferentes para SubscriptionStreams obter a configuração ideal.

Ao executar o teste, você pode monitorar os seguintes contadores de desempenho do Agente de Distribuição:

• Dist: Cmds/s entregues
• Dist: Latência de entrega

Comportamento do Agente de Distribuição depois de especificar o parâmetro SubscriptionStreams

O Agente de Distribuição mantém o número de sessões/conexões especificadas em SubscriptionStreams. O Agente de Distribuição usa essas sessões para aplicar alterações no Assinante.

No entanto, depois que você especificar SubscriptionStreams e o Agente de Distribuição for executado por algum tempo, o Agente de Distribuição poderá mudar para usar apenas uma sessão para aplicar alterações ao Assinante.

Motivos para o Agente de Distribuição mudar para usar apenas uma sessão

O Agente de Distribuição pode mudar para usar apenas uma sessão por muitos motivos. Os seguintes são os motivos mais comuns:

• Quando o Agente de Distribuição está aplicando alterações, uma das sessões gera um erro.

  Por exemplo, o Agente de Distribuição insere uma linha em uma tabela filho usando uma sessão. Se isso ocorrer antes que o Agente de Distribuição insira a linha correspondente na tabela pai usando outra sessão, uma violação de restrição de chave estrangeira gerará uma mensagem de erro.

• O thread do monitor de bloqueio detecta o bloqueio. O bloqueio pode ocorrer por um dos seguintes motivos:

  • O Agente de Distribuição executa uma INSERT operação e em UPDATE uma tabela no Assinante usando sessões diferentes. Se a tabela contiver um índice nãocluso exclusivo, o bloqueio entre as duas sessões poderá ocorrer quando o Agente de Distribuição atualizar as chaves de índice da tabela.

  • No Assinante, o Agente de Distribuição executa instruções DML (Linguagem de Manipulação de Dados) em várias tabelas. Se uma exibição indexada for definida nessas tabelas, o bloqueio entre as duas sessões poderá ocorrer quando a exibição indexada atualizar as chaves de índice compartilhadas.

  • O Agente de Distribuição executa uma instrução DML em relação a uma tabela no Assinante usando uma sessão. Os gatilhos DML são definidos nesta tabela. Os gatilhos DML executam instruções DML em outra tabela que está sendo atualizada usando outra sessão. Nessa situação, o bloqueio entre as duas sessões pode ocorrer.

É altamente recomendável que você não use os seguintes objetos de banco de dados no banco de dados de assinantes:

• Restrições de chave estrangeira
• Índices nãoclusos exclusivos
• Exibições indexadas
• Gatilhos DML que podem causar bloqueio entre sessões

Como determinar se o Agente de Distribuição mudou para usar apenas uma sessão

Para fazer isso, use um dos seguintes métodos:

• Método 1

  Consulte o sys.dm_exec_sessions de Exibição de Gerenciamento Dinâmico (DMV) para as sessões de conexão com o banco de dados de assinatura. Se você vir apenas uma sessão de conexão, o Agente de Distribuição poderá ter mudado para usando uma sessão. Se você vir mais de uma sessão de conexão, o Agente de Distribuição ainda está usando o número especificado de sessões.

  Para confirmar se o Agente de Distribuição mudou para usando uma sessão, use Método 2 ou Método 3.

• Método 2

  Consulte a coluna comments da tabela msdistribution_history no banco de dados de distribuição. Se o resultado da consulta contiver a seguinte entrada, o Agente de Distribuição mudou para usando uma sessão:

  O processo não foi concluído no último lote no modo multi streaming, ele foi redefinido para o modo de conexão única e está repetindo a operação.

• Método 3

  Examine o arquivo de saída do Agente de Distribuição. O Agente de Distribuição mudou para usar apenas uma sessão se o arquivo de saída contiver a mesma mensagem de erro que o Método 2.

  O seguinte arquivo de saída é um exemplo:

  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.
  

Como solucionar problemas de um Agente de Distribuição que muda para usar apenas uma sessão

1. Execute o SQL Server Profiler no Assinante para capturar o evento de relatório de processo bloqueado e o evento Exceção. Esses eventos registram bloqueio e erros que ocorrem quando o Agente de Distribuição aplica alterações.

   Observação

   O evento Exceção pode ser causado por qualquer tipo de erro que possa estar associado ao problema. Por exemplo, o erro pode ser causado por uma violação de restrição de chave estrangeira.

2. Use um dos métodos no Como determinar se o Agente de Distribuição mudou para usar apenas uma seção de sessão para monitorar o Agente de Distribuição.

3. Se o Agente de Distribuição tiver mudado para usar uma sessão, pare o rastreamento.

4. No arquivo de saída do Agente de Distribuição ou da coluna start_time do msdistribution_history de tabela, obtenha o carimbo de hora da seguinte entrada:

   O processo não foi concluído no último lote no modo multi streaming, ele foi redefinido para o modo de conexão única e está repetindo a operação.

5. Abra o arquivo trace (.trc) do Assinante. Localize um script de bloqueio ou um evento de exceção cujo carimbo de horário seja o mesmo ou muito próximo do carimbo de hora obtido na Etapa 4.

6. Se você notar uma exceção, examine os detalhes da exceção para determinar a causa. Por exemplo, a exceção pode ser causada por uma violação de restrição de chave estrangeira. Nesse caso, recomendamos remover a restrição de chave estrangeira no banco de dados de assinantes.

   Se você notar um script de bloqueio, o problema será causado pelo bloqueio. A seguir está um script de bloqueio de exemplo:

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

   O script de bloqueio registra uma sessão bloqueada e uma sessão de bloqueio. A sessão bloqueada começa a partir da marca <blocked-process>. A sessão de bloqueio começa a partir da marca <blocking-process>.

7. Localize o Object Id do objeto Proc na sessão bloqueada e na sessão de bloqueio.

   No script de bloqueio de exemplo, o Object Id de Proc na sessão bloqueada é 2000986455. O Object Id de Proc na sessão de bloqueio é 1172459501.

8. No banco de dados de assinatura, consulte o modo de exibição sys.objects especificando a coluna object_id para ser igual às Ids de Objeto obtidas na Etapa 7. Ao fazer isso, você pode determinar os nomes de objeto.

   Por exemplo, execute a seguinte consulta no contexto do banco de dados de assinatura:

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

   Observação

   • O espaço reservado <SubDBName> representa o nome do banco de dados de assinatura.
   • Normalmente, esses objetos são procedimentos armazenados que são usados na replicação.

9. Determine o índice ou a exibição indexada que causa o bloqueio. Para fazer isso, siga estas etapas:

   1. No script de bloqueio, localize o valor da propriedade waitresource.

      No script de bloqueio de exemplo, o valor de waitresource é 72057594375700480.

   2. Consulte o modo de exibição sys.partitions para obter a ID do objeto e a ID de índice especificando a coluna PARTITION_ID a ser igual ao valor do waitresource obtido na Etapa 9a.

      Por exemplo, execute a seguinte consulta:

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

   3. No banco de dados de assinatura, consulte o modo de exibição sys.indexes para determinar o índice usando a ID do objeto e a ID de índice obtida na Etapa 9b.

      Por exemplo, execute a seguinte consulta:

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

      Observação

      • O espaço reservado <objID> representa a ID do objeto obtida na Etapa 9b.
      • O espaço reservado <idxID> representa a ID de índice obtida na Etapa 9b.

10. Se o bloqueio for causado por uma exibição indexada, recomendamos que você solte a exibição indexada. Se o bloqueio for causado por um índice nãocluso exclusivo, recomendamos que você solte o índice e crie novamente um índice não exclusivo.

Descrição do thread do monitor de bloqueio

O Agente de Distribuição mantém um thread de monitor de bloqueio que detecta o bloqueio entre as sessões. Se o thread do monitor de bloqueio detectar o bloqueio entre as sessões, o Agente de Distribuição mudar para usar uma sessão para reaplicar o lote atual de comandos que o Agente de Distribuição não pôde aplicar anteriormente.

Para obter mais informações sobre o thread do monitor de bloqueio, examine Bloquear o Thread do Monitor.

Como o Agente de Distribuição retoma várias sessões

Antes que o Agente de Distribuição possa retomar várias sessões, o Agente de Distribuição deve executar o procedimento sp_MSget_repl_commands armazenado para requerimento do banco de dados de distribuição para os comandos que não foram aplicados no Assinante. Em seguida, o Agente de Distribuição deve aplicar todos esses comandos no Assinante antes que o Agente de Distribuição possa retomar várias sessões. Em um ambiente de replicação latente, o Agente de Distribuição não pode retomar várias sessões porque o Agente de Distribuição deve aplicar muitos comandos no Assinante antes que o Agente de Distribuição possa retomar várias sessões.

Para acompanhar todo o processo, examine o arquivo de saída do Agente de Distribuição.