Solução de problemas de enfileiramento de envio de log em um grupo de disponibilidade Always On

Artigo
12/14/2024

Este artigo fornece resoluções para problemas relacionados ao enfileiramento de envio de log.

O que é enfileiramento de envio de log?

As alterações feitas em um banco de dados do grupo de disponibilidade na réplica primária (como INSERT, UPDATEe DELETE) são gravadas no log de transações e enviadas para as réplicas secundárias do grupo de disponibilidade. A Fila de Envio de Log define o número de registros de log nos arquivos de log do banco de dados primário que não foram enviados para as réplicas secundárias.

Sintomas e efeito do enfileiramento de envio de log

A fila de envio de log armazena todos os dados vulneráveis

Se a réplica primária for perdida em um desastre repentino e você fizer failover para a réplica secundária em que essas alterações ainda não chegaram, essas alterações não aparecerão na nova cópia de réplica primária do banco de dados. Isso exclui todas as alterações armazenadas quando backups completos de banco de dados e log são executados.

O aumento da fila de envio de log causa o crescimento crescente do arquivo de log de transações

Para um banco de dados definido em um grupo de disponibilidade, o Microsoft SQL Server deve reter na réplica primária todas as transações no log de transações que ainda não foram entregues às réplicas secundárias. A fila de envio de log representa a quantidade de alterações registradas na réplica primária que não podem ser truncadas durante eventos normais de truncamento de log (por exemplo, durante um backup de log de banco de dados). Uma fila de envio de log grande e crescente pode esgotar o espaço livre na unidade que hospeda o arquivo de log do banco de dados ou pode exceder o tamanho máximo do arquivo de log de transações configurado. Para obter mais informações, consulte Erro 9002 quando o log de transações é grande.

Vários recursos de diagnóstico relatam o enfileiramento de envio de log do grupo de disponibilidade

O painel Always On no SQL Server Management Studio relata o enfileiramento de envio de log. Ele pode relatar que o grupo de disponibilidade não está íntegro.

Como verificar se há enfileiramento de envio de log

A fila de envio de log é uma medida por banco de dados. Você pode verificar esse valor usando o painel Always On na réplica primária ou usando as sys.dm_hadr_database_replica_states DMV (Exibições de Gerenciamento Dinâmico) na réplica primária ou secundária. Os contadores do Monitor de Desempenho são usados para verificar se há enfileiramento de envio de log na réplica secundária.

As próximas seções fornecem métodos para monitorar ativamente a fila de envio de log do banco de dados do grupo de disponibilidade.

Consulta sys.dm_hadr_database_replica_state

A sys.dm_hadr_database_replica_states DMV relata uma linha para cada banco de dados do grupo de disponibilidade. Uma coluna nesse relatório é log_send_queue_size. Esse valor é o tamanho da fila de envio de log em KB (quilobytes). Você pode configurar uma consulta como a consulta a seguir para monitorar qualquer tendência no tamanho da fila de envio de log. A consulta é executada na réplica primária. Ele usa o predicado is_local=0 para relatar os dados da réplica secundária, onde log_send_queue_size e log_send_rate são relevantes.

WHILE 1=1
BEGIN
  SELECT drcs.database_name, ars.role_desc, drs.log_send_queue_size, drs.log_send_rate,
ars.recovery_health_desc, ars.connected_state_desc, ars.operational_state_desc, ars.synchronization_health_desc, *
  FROM sys.dm_hadr_availability_replica_states ars JOIN sys.dm_hadr_database_replica_cluster_states drcs ON ars.replica_id=drcs.replica_id
  JOIN sys.dm_hadr_database_replica_states drs ON drcs.group_database_id=drs.group_database_id
  WHERE ars.role_desc='SECONDARY' AND drs.is_local=0
  waitfor delay '00:00:30'
END

Veja abaixo a aparência da saída.

Revise a fila de envio de log no painel Always On

Para revisar a fila de envio de log, siga estas etapas:

Abra o painel Always On no SQL Server Management Studio (SSMS) clicando com o botão direito do mouse em um grupo de disponibilidade no Pesquisador de Objetos do SSMS.
Selecione Mostrar painel.

Os bancos de dados do grupo de disponibilidade são listados por último e há alguns dados relatados nos bancos de dados. Embora o Tamanho da Fila de Envio de Log (KB) e a Taxa de Envio de Log (KB/s) não estejam listados por padrão, você pode adicioná-los a essa exibição, conforme mostrado na captura de tela na próxima etapa.
Para adicionar essas colunas, clique com o botão direito do mouse no cabeçalho da coluna do banco de dados do grupo de disponibilidade e selecione na lista de colunas disponíveis.
Para adicionar o tamanho da fila de envio de log, clique com o botão direito do mouse no cabeçalho mostrado como destacado em vermelho na captura de tela a seguir.

Por padrão, o painel Always On atualiza automaticamente esses dados a cada 60 segundos.

Examinar a fila de envio de log no Monitor de Desempenho

A fila de envio de log é específica para cada banco de dados de réplica secundária. Portanto, para examinar a fila de envio de log de um banco de dados de grupo de disponibilidade, siga estas etapas:

Abra o Monitor de Desempenho na réplica secundária.
Selecione o botão Adicionar (contador).
Em Contadores disponíveis, selecione os contadores SQLServer:Réplica de Banco de Dados e Fila de Envio de Log .
Na caixa de listagem Instância , selecione o banco de dados do grupo de disponibilidade que você deseja verificar quanto ao enfileiramento de envio de log.
Selecione Adicionar e OK.

Veja como pode ser o aumento do enfileiramento de envio de logs.

Interpretando valores de enfileiramento de envio de log

Esta seção explica como interpretar os valores do tamanho da fila de envio de log.

Quando o enfileiramento de envio de log é ruim? Quanto enfileiramento de envio de log deve ser tolerado?

Você pode supor que, se a fila de envio de log estiver relatando um valor de 0, isso significa que nenhum enfileiramento de envio de log está ocorrendo no momento desse relatório. No entanto, quando seu ambiente de produção está ocupado, você deve observar a fila de envio de log relatar com frequência um valor diferente de zero, mesmo em um ambiente AlwaysOn íntegro. Durante a produção típica, você deve esperar observar esse valor flutuar entre 0 e um valor diferente de zero.

Se você observar um aumento no enfileiramento de envio de log ao longo do tempo, uma investigação mais aprofundada será necessária. Essa atividade extra indica que algo mudou. Se você observar um crescimento repentino na fila de envio de log, as seguintes medidas serão úteis para solução de problemas:

Taxa de envio de log (KB/s) (painel AlwaysOn)
sys.dm_hadr_database_replica_states (DMV)
Réplica de Banco de Dados::Transações Espelhadas/s (Monitor de Desempenho)

Obter taxas de linha de base para taxa de envio de log e transações espelhadas/s

Durante o desempenho íntegro do AlwaysOn, monitore a taxa de envio de log e os valores de transações/s espelhados para seus bancos de dados de grupo de disponibilidade ocupados. Como eles se parecem durante o horário comercial normalmente movimentado? Como eles se parecem durante os períodos de manutenção, quando grandes transações geram maior taxa de transferência de transações no sistema? Você pode comparar esses valores ao observar o crescimento da fila de envio de log para ajudar a determinar o que foi alterado. A carga de trabalho pode ser maior do que o normal. Se a taxa de envio de log for menor do que o normal, talvez seja necessária uma investigação mais aprofundada para determinar o motivo.

Os volumes de carga de trabalho são importantes

Quando você tem cargas de trabalho grandes (como uma UPDATE instrução em relação a 1 milhão de linhas, uma recompilação de índice em uma tabela de 1 terabyte ou até mesmo um lote ETL que está inserindo milhões de linhas), você deve esperar ver algum crescimento da fila de envio de log, imediatamente ou ao longo do tempo. Isso é esperado quando um grande número de alterações é feito repentinamente no banco de dados do grupo de disponibilidade.

Como diagnosticar o enfileiramento de envio de log

Depois de identificar o enfileiramento de envio de log para um banco de dados de grupo de disponibilidade específico, você deve verificar várias possíveis causas raiz diferentes do problema, conforme discutido nas seções a seguir.

Importante

Para uma saída significativa do tipo de espera, verifique se há um aumento na fila de envio de log usando um dos métodos descritos nas seções anteriores ao monitorar as condições a seguir.

O sistema está muito ocupado

Verifique se a carga de trabalho na réplica primária está sobrecarregando as CPUs do sistema. Se você observar um aumento na fila de envio de logs, consulte a DMV e monitore os sys.dm_os_schedulers arquivos high runnable_tasks_count. Essa contagem indica tarefas pendentes que foram executadas naquele momento.

SELECT scheduler_address, scheduler_id, cpu_id, status, current_tasks_count, runnable_tasks_count, current_workers_count, active_workers_count
FROM sys.dm_os_schedulers

A tabela a seguir é uma amostra de resultados. Um aumento no runnable_tasks_count valor indica que um grande número de tarefas está aguardando o tempo de CPU.

scheduler_address	scheduler_id	cpu_id	status	current_tasks_count	runnable_tasks_count	current_workers_count	active_workers_count
0x000002778D 200040	0	0	VISÍVEL OFFLINE	1	0	2	1
0x000002778D 220040	1	1	VISIBLE ONLINE	108	12	115	107
0x000002778D 240040	2	2	VISIBLE ONLINE	113	2	123	113
0x000002778D 260040	3	3	VISIBLE ONLINE	105	11	116	105
0x000002778D 480040	4	4	VISIBLE ONLINE	108	15	117	108
0x000002778D 4A0040	5	5	VISIBLE ONLINE	100	25	110	99
0x000002778D 4C0040	6	6	VISIBLE ONLINE	105	23	113	105
0x000002778D 4E0040	7	7	VISIBLE	109	25	116	109
0x000002778D 700040	8	8	VISIBLE ONLINE	98	10	112	98
0x000002778D 720040	9	9	VISIBLE ONLINE	114	1	130	114
0x000002778D 740040	10	10	VISIBLE ONLINE	110	25	120	110
0x000002778D 760040	11	11	VISIBLE ONLINE	83	8	93	83
0x000002778D A00040	12	12	VISIBLE ONLINE	104	4	117	104
0x000002778D A20040	13	13	VISIBLE ONLINE	108	32	118	108
0x000002778D A40040	14	14	VISIBLE ONLINE	102	12	113	102
0x000002778D A60040	15	15	VISIBLE ONLINE	104	16	116	103

Solução: Se você detectar um aumento no runnable_task_count, reduza a carga de trabalho no sistema ou aumente o número de CPUs disponíveis para o sistema.

Latência da rede

Essa condição é especialmente comum se a réplica secundária estiver fisicamente remota da réplica primária. Os grupos de disponibilidade de vários sites permitem que os clientes implantem cópias de dados corporativos em vários sites para recuperação de desastres e relatórios. Isso disponibiliza alterações quase em tempo real para as cópias dos dados de produção em locais remotos.

Se uma réplica secundária estiver hospedada longe da réplica primária, o enfileiramento de envio de log poderá ser causado pela latência de rede e pela incapacidade de enviar alterações para o secundário remoto tão rápido quanto elas estão sendo produzidas no banco de dados de réplica primária.

Importante

O SQL Server usa uma única conexão para sincronizar as alterações das réplicas primárias para as secundárias. Portanto, se uma réplica secundária for remota, a largura do pipe não afetará a quantidade de dados que o SQL Server pode enviar. Em vez disso, esse valor depende mais da latência da rede no pipe (velocidade de conexão).

Testar a latência da rede

Verifique se as configurações de controle de fluxo contribuem para a latência da rede

Os grupos de disponibilidade do Microsoft SQL Server usam portões de controle de fluxo para evitar o consumo excessivo de recursos de rede, memória e outros recursos em todas as réplicas de disponibilidade. Esses portões de controle de fluxo não afetam o estado de integridade de sincronização das réplicas de disponibilidade. No entanto, eles podem afetar o desempenho geral de seus bancos de dados de disponibilidade, incluindo RPO.

Versões posteriores do SQL Server alteram os limites nos quais o controle de fluxo é inserido. Isso pode ajudar a aliviar o efeito que o controle de fluxo tem sobre sintomas como enfileiramento de envio de log. Para obter mais informações sobre o controle de fluxo e o histórico de alterações nos limites de controle de fluxo, consulte Portas de controle de fluxo.

Você pode monitorar o controle de fluxo usando o Monitor de Desempenho para capturar dados na réplica primária. Para monitorar o controle de fluxo do banco de dados, adicione os contadores SQLServer:Réplica do Banco de Dados e selecione os contadores Atraso do Controle de Fluxo do Banco de Dados e Controles de Fluxo do Banco de Dados/s. Na caixa de diálogo Instância , selecione o banco de dados do grupo de disponibilidade que você deseja verificar para controle de fluxo de banco de dados. Para detectar e monitorar o controle de fluxo da réplica de disponibilidade, adicione os contadores SQLServer:Availability Replica e selecione os contadores Tempo de Controle de Fluxo (ms/s) e Controle de Fluxo/s.
Verifique se a reinicialização do Windows de congestionamento contribui para a latência da rede

Os problemas de desempenho de rede que causam o enfileiramento de envio de log podem ser disparados com a configuração TCP de reinicialização do Windows de congestionamento definida como True. Essa era a configuração padrão no Windows Server 2016. Verifique se a Reinicialização da Janela de Congestionamento está definida como Falso em servidores Windows que hospedam réplicas do grupo de disponibilidade nos quais o enfileiramento de envio de log é observado.

PS C:\WINDOWS\system32> Get-NetTCPSetting | Select SettingName, CwndRestart

Para obter mais informações sobre como definir a propriedade TCP Congestion Windows Restart como False, consulte Set-NetTCPSetting (NetTCPIP).

Consulte também Monitorar o desempenho de grupos de disponibilidade AlwaysOn para obter informações sobre o processo de sincronização. Este artigo também mostra como calcular algumas das principais métricas e fornece links para alguns dos cenários comuns de solução de problemas de desempenho.

Usar ping para obter uma amostra de latência

Em uma linha de comando no node1 (réplica primária), ping node2 (réplica secundária):

C:\Users\customer>ping node2
Pinging node2.customer.corp.company.com [<ip address>] with 32 bytes of data:
Reply from 2001:4898:4018:3005:25f3:d931:2507:e353: time=94ms
Reply from 2001:4898:4018:3005:25f3:d931:2507:e353: time=97ms
Reply from 2001:4898:4018:3005:25f3:d931:2507:e353: time=94ms
Reply from 2001:4898:4018:3005:25f3:d931:2507:e353: time=119ms

Ping statistics for 2<ip address>:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 94ms, Maximum = 119ms, Average = 101ms

Teste a taxa de transferência da rede primária para a secundária usando uma ferramenta independente

Use uma ferramenta como NTttcp para detectar independentemente a taxa de transferência de rede entre as réplicas primária e secundária usando uma única conexão. A latência de rede é uma causa comum para o enfileiramento de envio de log. As etapas a seguir mostram como usar uma ferramenta independente, como NTttcp, para medir a taxa de transferência da rede.

Importante

O SQL Server envia alterações da réplica primária para a réplica secundária usando uma única conexão. Na seção a seguir, configuramos e executamos o NTttcp para usar uma única conexão (da mesma maneira que o SQL Server) para comparar a taxa de transferência com precisão.

Você pode baixar o NTttcp do Github - microsoft/ntttcp.

Para executar o NTttcp, siga estas etapas:
1. Baixe e copie a ferramenta para os servidores primários e secundários baseados no SQL Server.
2. No servidor de réplica secundária, abra uma janela de prompt de comando com privilégios elevados, altere o diretório para a pasta de ferramentas NTttcp e execute o seguinte comando:
  
  ntttcp.exe -r -m 1,0,<secondaryipaddress>-a 16 -t 60
  
  Observação
  
  Nesse comando, <secondaryipaddress> há um espaço reservado para o endereço IP real do servidor de réplica secundária.
3. No servidor de réplica primária, abra uma janela de prompt de comando com privilégios elevados, altere o diretório para a pasta de ferramentas NTttcp e execute o seguinte comando especificando novamente o endereço IP real do servidor de réplica secundária:
  
  ntttcp.exe -s -m 1,0,<secondaryipaddress>-a 16 -t 60
  
  As capturas de tela a seguir mostram o NTttcp em execução nas réplicas secundária e primária. Devido à latência da rede, a ferramenta pode enviar apenas 739 KB/s de dados. Isso é o que você pode esperar que o SQL Server seja capaz de enviar.
  
  NTttcp na réplica secundária
  
  NTttcp na réplica primária

Examinar contadores do Monitor de Desempenho

Verifique o que o NTttcp relata. Uma transação grande é executada no SQL Server na réplica primária. Depois de iniciar o Monitor de Desempenho na réplica primária, adicione o contador Interface de Rede::Bytes Enviados/s . Esse contador confirma que a réplica primária pode enviar cerca de 777 KB/s de dados. Isso é semelhante ao valor de 739 KB/s relatado pelo teste NTttcp.

Também é útil comparar o valor SQL Server::D atabases::Log Bytes Liberados/s na réplica primária com SQL Server::D atabase Replica::Log Bytes Recebidos/s para o mesmo banco de dados na réplica secundária. Em média, observamos ~ 20 MB/s de alterações criadas no banco de dados "agdb". No entanto, a réplica secundária está recebendo, em média, apenas 5,4 MB de alterações. Isso causará o enfileiramento de envio de log na réplica primária de alterações pendentes no log de transações do banco de dados que ainda não foram enviadas para a réplica secundária.

Bytes de Log de Réplica Primária Liberados/s para o banco de dados "agdb"

Bytes de Log de Réplica Secundária Recebidos/s para o banco de dados agdb

Compartilhar via