Solucionar problemas de fila de recuperação (redo) em um grupo de disponibilidade Always On

Resumo

Este artigo ajuda você a solucionar problemas de enfileiramento da recuperação (também chamado de enfileiramento de refazer) em uma réplica secundária de um grupo de disponibilidade do SQL Server Always On. Ele explica o que é a fila de recuperação, como verificar o tamanho da fila de redo e a taxa de redo, como interpretar os valores e como diagnosticar e corrigir causas comuns de atraso no redo, incluindo threads de redo bloqueadas e redo em thread única.

O que é enfileiramento de recuperação

As alterações feitas na réplica primária em um banco de dados do grupo de disponibilidade são enviadas para cada réplica secundária no mesmo grupo de disponibilidade. Depois que as alterações chegam a uma réplica secundária, elas são gravadas pela primeira vez (protegidas) no arquivo de log de transações do banco de dados do grupo de disponibilidade. O Microsoft SQL Server então usa a operação de recuperação ou redo para aplicar esses registros de log aos arquivos do banco de dados.

Se as alterações chegarem e endurecerem no log de transações mais rápido do que podem ser refeitas, uma fila de recuperação será formada. Essa fila é o conjunto de registros de log protegidos que ainda não foram aplicados ao banco de dados.

Sintomas e efeitos do enfileiramento de recuperação

Dados obsoletos em réplicas secundárias

Cargas de trabalho de leitura que consultam as réplicas secundárias podem receber dados desatualizados. Se houver enfileiramento de recuperação, as alterações recentes no banco de dados da réplica primária ainda não estarão visíveis na réplica secundária quando você consultar os mesmos dados.

As alterações chegam no secundário e são gravadas no arquivo de log do banco de dados, mas não podem ser lidas até que o redo as aplique aos arquivos de dados.

Para obter mais informações, consulte a seção "Latência de dados na réplica secundária" de "Diferenças entre os modos de disponibilidade para um grupo de disponibilidade Always On".

Tempo de failover mais longo ou RTO excedido

O RTO (Objetivo de Tempo de Recuperação) é o tempo de inatividade máximo do banco de dados que uma organização pode tolerar e a rapidez com que a organização pode recuperar o uso do banco de dados após uma interrupção. Se houver uma fila de recuperação grande em uma réplica secundária quando ocorrer um failover, refazer no novo primário poderá levar mais tempo do que o RTO. Após a conclusão do redo, o banco de dados assume a função primária e reflete o estado que existia antes do failover. Um tempo de retrabalho maior retarda a retomada da produção.

Ferramentas de diagnóstico informam que há problemas em um grupo de disponibilidade

Quando a fila de refazimento é significativa, o painel do Always On no SQL Server Management Studio (SSMS) pode mostrar o grupo de disponibilidade como não saudável.

Verificar se há fila de recuperação

A fila de recuperação é uma medida por banco de dados. Você pode verificá-lo no painel Always On na réplica primária ou consultando o sys.dm_hadr_database_replica_states DMV (modo de exibição de gerenciamento dinâmico) na réplica primária ou secundária. Os contadores do Monitor de Desempenho também informam o tamanho da fila de recuperação e a taxa de redo. Verifique esses contadores na réplica secundária.

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

Consultar sys.dm_hadr_database_replica_states

A DMV sys.dm_hadr_database_replica_states retorna uma linha para cada banco de dados no grupo de disponibilidade. A redo_queue_size coluna mostra o tamanho da fila de recuperação em quilobytes. Para monitorar a tendência no tamanho da fila de recuperação a cada 30 segundos, configure uma consulta como a seguinte. Execute isso na réplica primária. Ele usa o is_local=0 predicado para relatar dados para a réplica secundária, onde redo_queue_size e redo_rate são relevantes.

WHILE 1=1
BEGIN
SELECT drcs.database_name, ars.role_desc, drs.redo_queue_size, drs.redo_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 como é a saída.

Captura de tela da saída da consulta relata os dados da réplica secundária em que redo_queue_size e redo_rate são relevantes.

Analise a fila de recuperação no painel do Always On

Para revisar a fila de recuperação, siga estas etapas:

  1. No SSMS Pesquisador de Objetos, selecione e segure (ou clique com o botão direito do mouse) em um grupo de disponibilidade para abrir o menu de contexto.

  2. Selecione Mostrar painel.

    Os bancos de dados do grupo de disponibilidade são listados por último, com alguns dados relatados para cada banco de dados. Tamanho da fila de redo (KB) e Taxa de redo (KB/seg) não são listados por padrão, mas você pode adicioná-los à exibição, como mostrado na próxima etapa.

  3. Para adicionar esses contadores, selecione e segure (ou clique com o botão direito do mouse) no cabeçalho acima dos relatórios do banco de dados e escolha as colunas a serem exibidas.

  4. Para adicionar Tamanho da fila de refazer (KB) e Taxa de refazer (KB/seg), selecione e mantenha pressionado (ou clique com o botão direito) o cabeçalho destacado em vermelho na captura de tela a seguir.

    Captura de tela que mostra a adição dos contadores Redo Queue Size (KB) e Redo Rate (KB/s).

    Por padrão, o painel Always On atualiza automaticamente o Tamanho da Fila de Redo (KB) e a Taxa de Redo (KB/s) a cada 60 segundos.

    Captura de tela mostrando contadores de atualização definidos para cada 60 segundos.

Verificar a fila de recuperação no Monitor de Desempenho

Cada réplica secundária e banco de dados tem seu próprio tamanho de fila de recuperação. Para examinar a fila de recuperação de um banco de dados de grupo de disponibilidade, siga estas etapas:

  1. Abra o Monitor de Desempenho na réplica secundária.

  2. Selecione o botão Adicionar (contador).

  3. Em Contadores disponíveis, selecione SQLServer:Réplica de Banco de Dados e, em seguida, selecione os contadores Fila de recuperação e Bytes refeitos/seg.

  4. Na caixa de listagem Instância, selecione o banco de dados do grupo de disponibilidade que você deseja monitorar quanto ao enfileiramento de recuperação.

  5. Selecione Adicionar>OK.

    Veja como pode ser o aumento da formação de fila durante a recuperação.

    Captura de tela que mostra um aumento na fila de recuperação.

Interpretar valores de enfileiramento de recuperação

Esta seção explica como interpretar os valores da fila de recuperação coletados na seção anterior.

Quando a fila de recuperação é um problema

Um valor da fila de recuperação de 0 significa que não há acúmulo de operações de redo no momento da geração do relatório. Em um ambiente de produção com alta carga, a fila de recuperação geralmente apresenta um valor não zero, mesmo quando o grupo de disponibilidade está saudável. Durante a produção típica, espere que o valor flua entre 0 e um valor diferente de zero.

Se a fila de recuperação crescer ao longo do tempo, investigue mais a fundo. O crescimento indica que algo mudou. Quando você vê um crescimento repentino, as seguintes medidas são úteis para solução de problemas:

  • Taxa de redo do log (KB/sec) (painel do Always On)
  • redo_rate em sys.dm_hadr_database_replica_states

Estabelecer taxas de recuperação iniciais

Durante o funcionamento normal do Always On, monitore a taxa de redo nos bancos de dados mais ativos dos grupos de disponibilidade. Capturar taxas durante o horário comercial típico e durante as janelas de manutenção quando transações grandes (como recompilações de índice ou processos ETL) geram maior taxa de transferência. Compare esses valores de referência ao observar um aumento na fila de recuperação para identificar o que mudou. A carga de trabalho pode ser maior do que o normal ou a taxa de refazer pode ser menor do que o esperado, o que precisa de uma investigação mais aprofundada.

Considerar o volume da carga de trabalho

Grandes cargas de trabalho (como uma instrução UPDATE em um milhão de linhas, uma recriação de índice em uma tabela de 1 TB ou um lote de ETL que insere milhões de linhas) normalmente causam algum aumento na fila de recuperação, seja imediatamente ou ao longo do tempo. Esse crescimento é esperado quando muitas alterações são feitas repentinamente no banco de dados do grupo de disponibilidade.

Diagnosticar a fila de recuperação

Depois de identificar o enfileiramento de recuperação de um banco de dados específico do grupo de disponibilidade da réplica secundária, conecte-se à réplica secundária e, em seguida, consulte sys.dm_exec_requests para verificar wait_type e wait_time das threads de recuperação. Você está procurando uma alta frequência de um ou mais tipos de espera e grandes tempos de espera para esses tipos de espera. A consulta de exemplo a seguir é executada a cada cinco segundos e relata tipos de espera e tempos de espera para o banco de dados agdbdo grupo de disponibilidade:

WHILE (1=1)
BEGIN
SELECT db_name(database_id) AS dbname, command, session_id, database_id, wait_type, wait_time,
os.runnable_tasks_count, os.pending_disk_io_count FROM sys.dm_exec_requests der JOIN sys.dm_os_schedulers os
ON der.scheduler_id=os.scheduler_id
WHERE command IN('PARALLEL REDO HELP TASK', 'PARALLEL REDO TASK', 'DB STARTUP')
AND database_id= db_id('agdb')
waitfor delay '00:00:05.000'
END

Importante

Para obter um resultado significativo para o tipo de espera, a fila de recuperação deve estar crescendo ao coletar esses dados usando um dos métodos descritos anteriormente.

No exemplo a seguir, alguns tipos de espera relacionados a E/S são relatados (PAGEIOLATCH_UP, PAGEIOLATCH_EX). Monitore se esses tipos de espera continuam a mostrar os maiores wait_time valores, conforme relatado na próxima coluna.

Captura de tela mostrando os maiores tempos de espera relatados na próxima coluna.

Identificar tipos de espera de recuperação

Depois de identificar um tipo de espera, use o modelo e o desempenho da réplica secundária do grupo de disponibilidade como referência cruzada para tipos de espera comuns que causam enfileiramento de recuperação e orientações sobre como corrigir o problema.

Threads de recuperação bloqueados em réplicas secundárias somente para leitura

Se sua solução direciona relatórios (consultas) para os bancos de dados do grupo de disponibilidade em uma réplica secundária, essas consultas somente para leitura adquirem bloqueios de estabilidade de esquema (Sch-S). Bloqueios Sch-S podem impedir que threads de redo adquiram bloqueios de modificação de esquema (Sch-M) — também conhecidos como bloqueios de modificação de esquema ou LCK_M_SCH_M — necessários para aplicar alterações de linguagem de definição de dados (DDL), como ALTER TABLE ou ALTER INDEX. Um thread de refazer bloqueado não pode aplicar registros de log até que seja desbloqueado, o que causa a fila de recuperação.

Para verificar se há evidências históricas de um refazer bloqueado, abra, na réplica secundária, os arquivos de rastreamento dos eventos estendidos AlwaysOn_health usando o SSMS. Procure eventos lock_redo_blocked.

Captura de tela que mostra a verificação de evidências históricas de uma recriação bloqueada.

Use o Monitor de Desempenho para monitorar ativamente o impacto do redo bloqueado na fila de recuperação. Adicione os contadores SQL Server:Database Replica\Redo blocked/sec e SQL Server:Database Replica\Recovery Queue. A captura de tela a seguir mostra a execução de um comando ALTER TABLE ALTER COLUMN na réplica primária, enquanto uma consulta de longa duração é executada na mesma tabela na réplica secundária. O contador Redo blocked/sec tem picos quando o comando ALTER TABLE ALTER COLUMN é executado. Enquanto a consulta de longa duração estiver ativa na mesma tabela na réplica secundária, quaisquer alterações subsequentes na réplica primária aumentarão a fila de recuperação.

Captura de tela que mostra o monitoramento do tipo de espera de bloqueio de modificação de esquema.

Monitore o tipo de espera de bloqueio de modificação de esquema que o thread de restauração tenta adquirir. Use a consulta anterior para verificar os tipos de espera informados para operações de redo em sys.dm_exec_requests. Você pode observar que o tempo de espera para LCK_M_SCH_M aumenta enquanto o redo está bloqueado.

Captura de tela que mostra o aumento do tempo de espera para o LCK_M_SCH_M.

Recuperação de thread única

SQL Server 2016 introduziu a recuperação paralela para bancos de dados de réplica secundários. Se você estiver usando uma versão anterior, como o SQL Server 2014 ou o SQL Server 2012, atualize para uma versão com suporte para obter o refazer paralelo e melhorar o desempenho do refazer.

O refazer de thread único ainda pode ocorrer no SQL Server 2016 até SQL Server 2019, que usam a arquitetura de recuperação paralela. Nessas versões, uma instância de SQL Server pode usar até 100 threads para refazer paralelamente. O sistema aloca threads paralelas de redo entre os bancos de dados do grupo de disponibilidade com base no número de processadores e de bancos de dados, até o limite total de 100 threads. Quando o limite de 100 threads é atingido, o sistema atribui a alguns bancos de dados no grupo de disponibilidade uma única thread de redo.

Para verificar se o banco de dados do grupo de disponibilidade está usando a recuperação paralela, conecte-se à réplica secundária e execute a consulta a seguir para contar as linhas (threads) que aplicam a recuperação para o banco de dados. No exemplo a seguir, se o agdb banco de dados tiver uma única linha e seu comando for DB STARTUP, a carga de trabalho de recuperação poderá se beneficiar da recuperação paralela.

SELECT db_name(database_id) AS dbname, command, session_id, database_id, wait_type, wait_time,
os.runnable_tasks_count, os.pending_disk_io_count FROM sys.dm_exec_requests der JOIN sys.dm_os_schedulers os
ON der.scheduler_id=os.scheduler_id
WHERE command IN ('PARALLEL REDO HELP TASK', 'PARALLEL REDO TASK', 'DB STARTUP')
AND database_id= db_id('agdb')

Captura de tela que mostra como determinar se o banco de dados do grupo de disponibilidade usa recuperação paralela.

Se o banco de dados usa redo de thread única, examine o algoritmo anterior para verificar se o SQL Server está excedendo as 100 threads de trabalho dedicadas à recuperação paralela. Chegar a esse limite pode ser o motivo pelo qual agdb está usando apenas uma única thread de redo.

SQL Server 2022 e versões posteriores usam um algoritmo de recuperação paralela que atribui threads de trabalho com base na carga de trabalho, eliminando a possibilidade de que um banco de dados com alta atividade permaneça em redo de thread única. Para obter mais informações, consulte a seção Uso do Thread por grupos de disponibilidade de "Pré-requisitos, restrições e recomendações para grupos de disponibilidade Always On".

Conteúdo relacionado

  • Last updated on