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='1456',DATE_CHECK_CI=getdate(),DATE_MODIFY=getdate(),where R_ID='284598973'
</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.