Por que o seu commit SQL síncrono espera a viagem de ida e volta pela VPN

A latência de escrita disparou depois que uma réplica foi movida para o segundo site, e o túnel é o componente mais novo, então ele leva a culpa. O túnel pode ser inocente: dentro de um grupo de disponibilidade com commit síncrono, a primária não pode confirmar um commit até que uma escrita remota do log seja concluída, então cada transação paga uma viagem de ida e volta, por muitos megabits que o link consiga transportar. A taxa de transferência mede bytes por segundo, a latência mede quanto dura uma troca, e um commit só precisa de uma pequena troca para terminar. Um único número resolve a discussão: a duração do commit de uma transação pequena, medida dentro do banco de dados de disponibilidade e depois de novo contra uma tabela local na mesma instância.

O que a primária realmente espera

Sob commit síncrono, a primária retém o reconhecimento do commit até que o parceiro confirme que os registros de log dele estão endurecidos. Endurecido significa gravado de forma durável no log de transações da secundária, não reexecutado nas páginas de dados dela. O redo pode ficar minutos atrás sem acrescentar um único milissegundo ao commit da primária.

A documentação da Microsoft sobre os modos de disponibilidade explica esse aperto de mão e descreve o que a plataforma faz quando um parceiro cai. Uma réplica marcada como síncrona é uma regra sobre esperar, não uma promessa permanente de que exista uma cópia remota saudável para cada commit. O caminho desconectado é definido em separado, e não é simplesmente que o commit falhe.

Então a espera que você observa é uma soma: transporte do log, agendamento nos dois hosts e a escrita no armazenamento remoto. Uma secundária com volume de log saturado acrescenta tempo que nenhum teste de velocidade mostrará. Esse disco raramente é medido quando a queixa cita a VPN.

Largura de banda não é atraso de ida e volta

Um teste de velocidade inunda o caminho brevemente e informa uma taxa. Um commit pede algo mais estreito: quanto tempo uma mensagem pequena leva para chegar ao outro lado, ser persistida e voltar. O comprimento do caminho, as filas e a perda de pacotes determinam esse número, e nenhum deles melhora quando o provedor sobe o teto.

Um túnel «rápido» pode, portanto, acrescentar uma penalidade visível a uma carga de trabalho pesada em escrita feita de muitas transações pequenas e sequenciais. O mesmo total de bytes dividido em mil commits paga a viagem de ida e volta mil vezes; uma transferência em massa paga uma vez.

Peça ao dono da rede o atraso e a perda medidos na própria rota da réplica sob carga representativa. Um ping de um laptop de escritório até o gateway VPN, ou um teste de velocidade até um nó próximo, quase não atribui nada à rota que o log realmente percorre. Faça a janela de medição coincidir com as horas em que a escrita está lenta.

Leia o estado da réplica antes de mudar qualquer coisa

Comece pelo que as instâncias relatam sobre si mesmas, porque os dois lados podem discordar sobre o quão saudável é a parceria e uma média do painel esconde qual fila está se movendo. As consultas abaixo respondem a perguntas diferentes: uma lê o estado de replicação por banco de dados, a outra lê como cada réplica está configurada. Execute as duas antes de tocar em um modo.

SELECT ag.name AS ag_name, drs.database_id, drs.synchronization_state_desc,
       drs.log_send_queue_size, drs.redo_queue_size, drs.secondary_lag_seconds
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_groups AS ag ON ag.group_id = drs.group_id;

SELECT ar.replica_server_name, ar.availability_mode_desc, ar.failover_mode_desc
FROM sys.availability_replicas AS ar;

O primeiro resultado separa o transporte do log do redo. log_send_queue_size subindo enquanto redo_queue_size fica plano significa que o log não chegou; o contrário significa que chegou e que a reprodução está atrás. Só o primeiro caso fica no caminho do commit.

O segundo resultado confirma quais réplicas estão realmente configuradas como você supõe, porque um modo alterado durante um incidente anterior tende a ficar alterado e não documentado. Leia a linha que lhe interessa em vez do padrão do grupo: availability_mode_desc e failover_mode_desc são por réplica. secondary_lag_seconds só existe no SQL Server 2016 e posteriores, então uma compilação mais antiga deixa você apenas com as colunas de fila de envio e de redo.

Depois meça uma escrita representativa em vez de confiar em uma média do painel. Execute o bloco abaixo no banco de dados de disponibilidade e depois execute-o de novo contra a mesma forma de tabela em um banco fora do grupo na mesma instância. A diferença entre as duas durações é um preço aproximado do log remoto, e em uma discussão com a equipe de rede ela se sustenta muito melhor do que um gráfico. Depois mude uma variável de cada vez, ou a diferença deixa de significar algo.

IF OBJECT_ID('dbo.CommitProbe') IS NULL
    CREATE TABLE dbo.CommitProbe (probe_id int PRIMARY KEY, touched_at datetime2(3));
IF NOT EXISTS (SELECT 1 FROM dbo.CommitProbe WHERE probe_id = 1)
    INSERT INTO dbo.CommitProbe (probe_id) VALUES (1);
SET STATISTICS TIME ON;
BEGIN TRANSACTION;
UPDATE dbo.CommitProbe SET touched_at = SYSUTCDATETIME() WHERE probe_id = 1;
COMMIT TRANSACTION;
SET STATISTICS TIME OFF;

O que o commit assíncrono compra e quanto custa

Com commit assíncrono, a primária para de esperar a confirmação do endurecimento remoto, então a viagem de ida e volta sai do caminho do commit. A latência do commit cai e a secundária fica livre para atrasar em uma medida que ninguém controla. É uma correção real para a espera e uma mudança real na promessa.

O preço é uma decisão sobre o ponto de recuperação. Se a primária morre antes que o log dela chegue a um alvo de failover que você realmente possa usar, mover o serviço para a cópia assíncrona descarta transações que o aplicativo já havia relatado como confirmadas. Nenhuma caixa de diálogo de configuração apresenta isso assim, porque é uma questão de negócio sobre perda aceitável, não um ajuste de desempenho. Escreva a janela de perda tolerada antes do incidente, não durante.

Combine o modo com quem é dono dos dados, por escrito, junto com o procedimento de failover para o caso de o alvo preferido não estar disponível. Síncrono em um documento de projeto não é um manual operacional, e assíncrono não é uma licença para parar de vigiar as filas.

Escolha uma correção que você possa defender depois

Existem quatro opções honestas. Corte as viagens de commit evitáveis onde a semântica do aplicativo permite agrupamento; conserte um gargalo de transporte ou de armazenamento que você mediu, o que pode significar um volume de log mais rápido na secundária em vez de um cano maior; aproxime a réplica para que a viagem de ida e volta encolha; ou relaxe a política de proteção e aceite uma janela de perda mais ampla. Cada uma compra latência gastando outra coisa, e a quarta gasta durabilidade. Nomeie o gasto antes de escolher.

Decida com a meta de latência da carga de trabalho e o requisito de durabilidade dela na sua frente ao mesmo tempo, porque otimizar um gasta o outro em silêncio. Depois execute de novo a mesma transação que você mediu antes da mudança. Uma correção que não consegue mover esse número é um palpite vestindo um ticket de mudança.

Guarde as leituras de antes e depois no registro de mudanças. Caso contrário, a próxima pessoa que vir escritas lentas repete a investigação desde o começo e começa culpando o túnel de novo.