Início / dba / Perguntas / 95025
Accepted
Worldexe
Asked: 2015-03-12 14:26:59 +0800 CST

A replicação de mesclagem do SQL Server 2012 bloqueia outras inserções/atualizações durante a sincronização

  • 772

Eu tenho uma configuração de replicação de mesclagem entre dois bancos de dados do SQL Server 2012 por meio do método Pull.

Existem várias replicações de mesclagem afetando diferentes conjuntos de tabelas (3 conjuntos de 2 a 5 tabelas cada, na verdade). Não há chaves estrangeiras ligando-as entre ou para outras tabelas nos bancos de dados.

Tanto o assinante quanto o publicador estão alterando os dados nas tabelas replicadas. Normalmente, são mais de 800 mil inserções por dia de 12 horas no editor e cerca de 300 mil no assinante; quase nenhuma atualização.

Além disso, tenho a filtragem simples habilitada - uma bit NULLcoluna é verificada por ser NULL para quase todas as tabelas mescladas; sem joins em filtros. O banco de dados principal tem cerca de 80G de dados nessas tabelas, o assinante tem cerca de 30G devido à filtragem. Existem índices em todas as colunas de filtro. As tabelas são replicadas como estão, sem colunas seletivas.

Os intervalos de chave primária são aumentados para 10 milhões na maioria das tabelas (para ter certeza de que não serão substituídos com frequência).

A replicação de esquema está habilitada, mas nenhuma alteração de esquema ocorre.

Os períodos de retenção de replicação de mesclagem são definidos para 2 dias (resultando em 3 dias de dados armazenados - cerca de aproximadamente 3 milhões de linhas na MSmerge_contentstabela).

Tarefa de replicação agendada para iniciar a cada 15 minutos.

O problema é que, às vezes, quando o trabalho de sincronização entra em ação, ele bloqueia TODAS as inserções/atualizações em TODAS as tabelas em TODAS as replicações de mesclagem neste banco de dados. Parece que ocorre apenas no final do processo de replicação, independentemente da contagem de linhas sincronizadas (para algumas execuções, pode haver apenas 1-2 linhas alteradas/adicionadas) e pode durar de 5 a 10 minutos, o que é inaceitável.

Rastreei qual procedimento está sendo executado naqueles momentos de bloqueio e peguei o relatório de bloqueio usando o SQL Server Profiler e iniciando-o manualmente (parece seguro):

<blocked-process-report monitorLoop="201058">
 <blocked-process>
  <process id="process38d8dd0c8" taskpriority="0" logused="1268" waitresource="OBJECT: 5:690456609:0 " waittime="5002" ownerId="4004024182" transactionname="UPDATE" lasttranstarted="2015-03-11T16:08:39.890" XDES="0x1b253649e8" lockMode="IX" schedulerid="1" kpid="5132" status="suspended" spid="84" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2015-03-11T16:08:39.880" lastbatchcompleted="2015-03-11T16:08:39.880" lastattention="2015-03-11T15:29:13.240" hostname="COMP-177" hostpid="2976" loginname="user" isolationlevel="read committed (2)" xactid="4004024182" currentdb="5" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
   <executionStack>
    <frame line="46" stmtstart="4398" stmtend="4936" sqlhandle="0x030005008910305b49b9150057a4000000000000000000000000000000000000000000000000000000000000"/>
    <frame line="1" stmtstart="30" sqlhandle="0x020000003f32c4168d189e55398799d8e66489e031b7fa8b0000000000000000000000000000000000000000"/>
    <frame line="1" stmtstart="30" sqlhandle="0x02000000538a8c182d9ff435ad6897538e470878728dfd940000000000000000000000000000000000000000"/>
   </executionStack>
   <inputbuf>
set nocount on;update [replicated_table] set CHECK_CI=&apos;1456&apos;,DATE_CHECK_CI=getdate(),DATE_MODIFY=getdate(),where R_ID=&apos;284598973&apos;
   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="running" spid="150" sbid="0" ecid="0" priority="-5" trancount="2" lastbatchstarted="2015-03-11T16:08:39.347" lastbatchcompleted="2015-03-11T16:08:21.833" lastattention="1900-01-01T00:00:00.833" clientapp="Microsoft SQL Server Management Studio - Query" hostname="COMP-120" hostpid="9460" loginname="userp" isolationlevel="read committed (2)" xactid="4004019824" currentdb="5" lockTimeout="4294967295" clientoption1="673319008" clientoption2="390168">
   <executionStack>
    <frame line="366" stmtstart="32078" stmtend="32458" sqlhandle="0x0300ff7fa885d0f933812f012ba3000001000000000000000000000000000000000000000000000000000000"/>
    <frame line="1" sqlhandle="0x010005002698da17601c849b1d00000000000000000000000000000000000000000000000000000000000000"/>
   </executionStack>
   <inputbuf>
exec sp_MSmakegeneration   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

sys.sp_MSmakegenerationa execução bloqueia todas as outras atividades. Pelo que entendi - este procedimento está gerando uma lista de linhas a serem replicadas. Entendo que pode ser executado por um tempo - mas por que impede outras inserções/atualizações em TODAS as tabelas habilitadas para mesclagem? Não importa qual publicação é sincronizada - TODAS as tabelas habilitadas para mesclagem (mesmo de outras publicações neste banco de dados) também param de funcionar.

O objeto de espera waitresource="OBJECT: 5:690456609:0 "é MSmerge_genhistorytable. Então, de alguma forma, bloqueia naquela mesa, mas por quê?

Tentei capturar o plano de execução real desse procedimento - obtive 14 mil linhas de XML, mas não encontrei nada suspeito lá (nenhuma varredura de tabela óbvia ou outra atividade pesada).

A configuração da replicação é principalmente padrão, exceto para a generation_leveling_thresholdopção aumentada para 10.000 durante a investigação do problema. Não ajudou.

SQL Server 2012 v 11.0.5058.0

O nível de isolamento padrão é definido como READ COMMITTED SNAPSHOT; essas inserções/atualizações são apenas inserções/atualizações simples, sem alterar o nível de isolamento ou iniciar transações explícitas.

Posso sincronizar servidores com sucesso após o dia de trabalho (quando ninguém toca no banco de dados), funciona bem em 20 a 30 minutos (transferindo dados do dia inteiro de uma só vez), mas preciso de sincronização contínua (atraso de 15 a 20 minutos, no entanto).

Pesquisei o problema no Google - há muitos caras com replicação de mesclagem travada devido a muitos dados alterados (começando com 1 milhão de linhas), mas nada semelhante ao meu problema.

sql-server-2012 merge-replication

1 respostas

  1. Best Answer

    Pessoalmente, NÃO recomendo configurar a replicação de mesclagem nessas condições. Mas, como não há jeito para mim (além de fazer isso funcionar ou desenvolver minha própria solução de replicação), passei duas semanas experimentando e encontrei uma solução.

    1. Desative a opção Partições de pré-computação na janela Propriedades da publicação -> Opções de assinatura -> Filtragem . Isso faz com que sp_MSmakegeneration bloqueie apenas aquela tabela específica na qual está trabalhando no momento, nem todas as tabelas afetadas pela replicação de mesclagem. Essa opção é habilitada automaticamente se você tiver alguma filtragem de linha. Mas, na verdade, serve apenas para acelerar filtros parametrizados (não é o meu caso). Observação: desativar isso invalidará as assinaturas; você deve reinicializá-los com um novo instantâneo.
    2. Crie um trabalho simples com o comando exec dbo.sp_MSmakegeneration e agende-o a cada 1 minuto. Normalmente, é chamado como parte do processo de sincronização para enumerar as alterações locais. Mas, se você pular a chamada por 10 a 15 minutos (isso significa 20 a 30 mil linhas alteradas/inseridas no meu caso), a próxima chamada de sincronização bloqueará todas as inserções/atualizações do cliente na tabela replicada; além disso, há chances de você pegar um impasse durante sua execução, tornando impossível executá-lo com sucesso até que a carga do servidor seja reduzida. Isso, é claro, depende de seus índices de tabela específicos e uso.
    3. Programe seus trabalhos de sincronização para serem executados com frequência. Eu pessoalmente o configurei para executar a cada 3 minutos. Isso reduz o atraso de sincronização para 5 a 10 minutos. Estritamente falando, isso não é obrigatório, mas sincronizar muitas alterações é uma carga bastante pesada, portanto, é mais fácil para o servidor sincronizar partes menores.

    Usando isso, consigo sincronizar com sucesso 3 servidores, com 3-10k inserções/atualizações por minuto em cada um durante o dia de trabalho sem grandes atrasos.

    • 1

relate perguntas