Solução de problemas de fila de recuperação em um grupo de disponibilidade Always On

  • Artigo

Este artigo fornece resoluções para problemas relacionados à fila de recuperação.

O que é fila de recuperação?

As alterações feitas no réplica primário em um banco de dados de grupo de disponibilidade são enviadas para todas as réplicas secundárias definidas no mesmo grupo de disponibilidade. Depois que essas alterações chegam às réplicas secundárias, elas são gravadas pela primeira vez no arquivo de log de transações do banco de dados do grupo de disponibilidade. A Microsoft SQL Server então usa a operação de recuperação ou refazer para atualizar os arquivos de banco de dados.

Se as alterações em um grupo de disponibilidade chegarem e endurecerem no arquivo de log de transações de banco de dados mais rapidamente do que podem ser recuperadas, uma fila de recuperação será formada. Essa fila é composta por transações de log de transações endurecidas que não foram recuperadas e restauradas no banco de dados.

Sintomas e efeito da fila de recuperação (refazer)

Consultar réplicas primárias e secundárias retorna resultados diferentes

Cargas de trabalho somente leitura que consultam réplicas secundárias podem consultar dados obsoletos. Se ocorrer fila de recuperação, alterações nos dados no banco de dados réplica primário podem não ser refletidas no banco de dados secundário ao consultar os mesmos dados.

Embora as alterações cheguem ao banco de dados secundário e sejam gravadas no arquivo de log de banco de dados, as alterações não serão consultadas até que sejam recuperadas e restauradas nos arquivos do banco de dados. A operação de recuperação é o que torna essas alterações legíveis.

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

O tempo de failover é maior ou o RTO é excedido

O RTO (Objetivo de Tempo de Recuperação) é o tempo máximo de inatividade do banco de dados que uma organização pode lidar. O RTO também descreve a rapidez com que a organização pode recuperar o acesso ao banco de dados após uma interrupção. Se uma fila de recuperação substancial estiver presente em um réplica secundário quando ocorrer um failover, a recuperação poderá levar mais tempo. Após a recuperação, o banco de dados fará a transição para a função primária e representará o estado do banco de dados que existia antes do failover. Um tempo de recuperação mais longo pode atrasar a rapidez com que a produção é retomada após um failover.

Vários recursos de diagnóstico relatam fila de recuperação de grupo de disponibilidade

No caso da fila de recuperação, o Always On dashboard no SSMS (SQL Server Management Studio) pode relatar um grupo de disponibilidade não íntegro.

Como marcar para a fila de recuperação (refazer)

A fila de recuperação é uma medida por banco de dados que pode ser verificada usando o Always On dashboard no réplica primário ou usando o sys.dm_hadr_database_replica_states Modo de Exibição de Gerenciamento Dinâmico (DMV) no réplica primário ou secundário. Monitor de Desempenho contraria marcar a fila de recuperação e a taxa de recuperação. Esses contadores devem ser verificados no réplica secundário.

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

Sys.dm_hadr_database_replica_states de consulta

O sys.dm_hadr_database_replica_states DMV relata uma linha para cada banco de dados do grupo de disponibilidade. Uma coluna no relatório é redo_queue_size. Esse valor é o tamanho da fila de recuperação medido em quilobytes. Você pode configurar uma consulta que se assemelha à consulta a seguir para monitorar qualquer tendência no tamanho da fila de recuperação a cada 30 segundos. A consulta é executada no réplica primário. Ele usa o is_local=0 predicado para relatar os dados do réplica secundário, em que 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 para o relatório de consulta os dados do réplica secundário em que redo_queue_size e redo_rate são relevantes.

Examinar a fila de recuperação no Painel de Always On

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

  1. Abra o painel Always On no SSMS clicando com o botão direito do mouse em um grupo de disponibilidade no SSMS Pesquisador de Objetos.

  2. 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 KB (Tamanho da Fila de Refazer) e a Taxa de Refazer (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.

  3. Para adicionar esses contadores, clique com o botão direito do mouse no cabeçalho acima dos relatórios do banco de dados e selecione na lista de colunas disponíveis.

  4. Para adicionar o KB (Tamanho da Fila de Refazer) e a Taxa de Refazer (KB/s), clique com o botão direito do mouse no cabeçalho mostrado como realçado em vermelho na captura de tela a seguir.

    Captura de tela que mostra a adição dos contadores Tamanho da Fila de Refazer (KB) e Taxa de Refazer (KB/s).

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

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

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

O tamanho da fila de recuperação é exclusivo para cada réplica secundário e banco de dados. Portanto, para examinar a fila de recuperação de um banco de dados de grupo de disponibilidade, siga estas etapas:

  1. Abra Monitor de Desempenho no réplica secundário.

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

  3. Em Contadores disponíveis, selecione SQLServer:Réplica de Banco de Dados e selecione Fila de Recuperação e Redone Bytes/s contadores.

  4. Na caixa lista Instância , selecione o banco de dados do grupo de disponibilidade que você deseja monitorar para filas de recuperação.

  5. Selecione Adicionar>OK.

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

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

Interpretando valores de fila de recuperação

Esta seção explica como você pode interpretar os valores relacionados à fila de recuperação que você determinou na seção anterior.

Quando a fila de recuperação é um problema? Quanta fila de recuperação você deve tolerar?

Você pode supor que, se a fila de recuperação estiver relatando um valor de 0, isso significa que nenhuma fila de recuperação está ocorrendo no momento desse relatório. No entanto, quando o ambiente de produção estiver ocupado, você deve esperar observar a fila de recuperação frequentemente relatar um valor diferente de zero mesmo em um ambiente AlwaysOn saudável. Durante a produção típica, você deve esperar observar que esse valor flutua entre 0 e um valor não zero.

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

  • Taxa de Refazer de Log (KB/s) (AlwaysOn dashboard)
  • Redo_rate no sys.dm_hadr_database_replica_states de DMV

Obter taxas de linha de base para a taxa de refazer

Durante o desempenho alwayson saudável, monitore a taxa de refazer em seus bancos de dados de grupo de disponibilidade ocupados. Como eles se parecem durante horários comerciais normalmente ocupados? Quais são essas taxas durante períodos de manutenção, quando transações grandes (recompilações de índice, processos de ETL) geram maior taxa de transferência de transação no sistema? Você pode comparar esses valores ao observar o crescimento da fila de recuperação para ajudar a determinar o que mudou. A carga de trabalho pode ser maior do que o normal. Se a taxa de refazer for menor, uma investigação adicional poderá ser necessária para determinar o motivo.

Os volumes de carga de trabalho importam

Quando você tem cargas de trabalho grandes (como uma instrução UPDATE em relação a um 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 recuperação, imediatamente ou ao longo do tempo. Isso é esperado quando um grande número de alterações é feita repentinamente no banco de dados do grupo de disponibilidade.

Como diagnosticar a fila de recuperação (refazer)

Depois de identificar a fila de recuperação para um banco de dados de grupo de disponibilidade secundário específico réplica, conecte-se ao réplica secundário e, em seguida, consulte sys.dm_exec_requests para determinar os wait_type threads de recuperação e.wait_time Aqui está uma consulta que pode ser executada em um loop. Você está procurando uma alta frequência de um ou mais tipos de espera e até mesmo tempos de espera para esses tipos de espera. Aqui está uma consulta de exemplo que é executada a cada segundo e relata os tipos de espera e os tempos de espera do grupo de disponibilidade, "agdb":

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 uma saída significativa do tipo de espera, a fila de recuperação deve ser observada para aumentar quando você usa um dos métodos descritos anteriormente para monitorar essa condição.

Neste exemplo, alguns tipos de espera relacionados a E/S são relatados (PAGEIOLATCH_UP, PAGEIOATCH_EX). Monitore para marcar se esses tipos de espera continuam a ter os maiores wait_times valores, conforme relatado na próxima coluna.

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

SQL Server refazer tipos de espera

Quando um tipo de espera for identificado, examine o artigo a seguir SQL Server 2016/2017: Modelo e desempenho secundários do grupo de disponibilidade réplica de refazer - Microsoft Tech Community como referência cruzada para tipos de espera comuns que causam filas de recuperação e para ajudar a resolve o problema.

Threads de refazer bloqueados em servidores de relatórios secundários

Se a solução direcionar relatórios (consulta) em bancos de dados de grupo de disponibilidade no réplica secundário, essas consultas somente leitura adquirirão bloqueios de estabilidade de esquema (Sch-S). Esses bloqueios Sch-S podem bloquear threads de refazer da aquisição de bloqueios sch-M (modificação de esquema) (também conhecidos como "bloqueios de modificação de esquema" ou LCK_M_SCH_M) para fazer alterações de DDL (linguagem de definição de dados), como ALTER TABLE ou ALTER INDEX. Um thread de refazer bloqueado não pode aplicar registros de log até que ele seja desbloqueado. Isso pode causar filas de recuperação.

Para marcar para obter evidências históricas de um refeitório bloqueado, abra o AlwaysOn_health arquivos de rastreamento Xevent no réplica secundário usando o SSMS. Procure eventos lock_redo_blocked .

Captura de tela que mostra a verificação de evidências históricas de um refeitório bloqueado.

Use Monitor de Desempenho para monitorar ativamente o impacto de refazer bloqueado na fila de recuperação. Adicione os contadores SQL Server::D atabase Replica::Redo blocked/sec e SQL Server::D atabase Replica::Recovery Queue. A captura de tela a seguir mostra um ALTER TABLE ALTER COLUMN comando executado no réplica primário enquanto uma consulta de longa duração é executada na mesma tabela no réplica secundário. O contador Redo bloqueado/s indica que o ALTER TABLE ALTER COLUMN comando é executado. Enquanto a consulta de longa duração estiver em execução na mesma tabela no réplica secundário, qualquer alteração subsequente na primária causará um aumento na fila de recuperação.

Captura de tela mostrando um monitor para o 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 refazer tenta adquirir. Para fazer isso, use a consulta descrita anteriormente para marcar os tipos de espera relatados para operações de refazer contra sys.dm_exec_requests. Você pode observar o tempo de espera crescente para o LCK_M_SCH_M no bloqueio de redação em andamento.

Captura de tela que mostra o tempo de espera crescente para o LCK_M_SCH_M.

Redimensão de thread único

SQL Server introduziu a recuperação paralela para bancos de dados de réplica secundários no Microsoft SQL Server 2016. Se você estiver enfrentando filas de recuperação ao executar o SQL Microsoft Server 2012 ou o Microsoft SQL Server 2014, poderá atualizar para uma versão posterior do programa para melhorar o desempenho de refazer em seu ambiente de produção.

Um refeitório com thread único pode ocorrer em versões ainda mais avançadas SQL Server em que a arquitetura de recuperação paralela é usada. Nessas versões, uma instância de SQL Server pode usar até 100 threads para uma reformulação paralela. Dependendo dos números de processadores e bancos de dados de grupo de disponibilidade, os threads de refazer paralelos são alocados até um máximo de 100 threads totais. Se o limite de refazer 100 threads for atingido, alguns bancos de dados no grupo de disponibilidade receberão um único thread de refazer.

Para determinar se o banco de dados do grupo de disponibilidade está usando a recuperação paralela, conecte-se ao réplica secundário e use a consulta a seguir para determinar o número de linhas (threads) que aplicam a recuperação para o banco de dados do grupo de disponibilidade. No exemplo a seguir, se o banco de dados "agdb" for um único thread 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 a recuperação paralela.

Se você verificar se o banco de dados usa um refeitório de thread único, examine o algoritmo descrito anteriormente para determinar se SQL Server está excedendo o número de 100 threads de trabalho dedicados à recuperação paralela. Essa condição pode ser a razão pela qual o banco de dados "agdb" está usando apenas um único thread para recuperação.

SQL Server 2022 agora usa um novo algoritmo de recuperação paralela para que os threads de trabalho sejam atribuídos para recuperação paralela com base na carga de trabalho. Isso elimina a chance de que um banco de dados ocupado permaneça em uma recuperação com thread único. Para obter mais informações, consulte a seção Uso de Thread por Grupos de Disponibilidade de "Pré-requisitos, restrições e recomendações para grupos de disponibilidade Always On".