Monitorar o desempenho de grupos de disponibilidade Always On

Aplica-se a:SQL Server

O aspeto de desempenho dos Grupos de Disponibilidade Always On é crucial para manter o contrato de nível de serviço (SLA) para seus bancos de dados de missão crítica. Compreender como os grupos de disponibilidade enviam logs para réplicas secundárias pode ajudá-lo a estimar o RTO (Recovery Time Objective, objetivo de tempo de recuperação) e o RPO (Recovery Point Objective, objetivo de ponto de recuperação) da implementação de disponibilidade e identificar estrangulamentos em grupos de disponibilidade ou réplicas de baixo desempenho. Este artigo descreve o processo de sincronização, mostra como calcular algumas das principais métricas e fornece os links para alguns dos cenários comuns de solução de problemas de desempenho.

Processo de sincronização de dados

Para estimar o tempo até a sincronização completa e identificar o gargalo, você precisa entender o processo de sincronização. O gargalo de desempenho pode estar em qualquer lugar do processo, e localizar o gargalo pode ajudá-lo a se aprofundar nos problemas subjacentes. A figura e a tabela a seguir ilustram o processo de sincronização de dados:

Portões de controle de fluxo

Os grupos de disponibilidade são projetados com portas de controle de fluxo na réplica primária para evitar o consumo excessivo de recursos, como recursos de rede e memória, em todas as réplicas de disponibilidade. Essas portas de controle de fluxo não afetam o estado de integridade da sincronização das réplicas de disponibilidade, mas podem afetar o desempenho geral dos bancos de dados de disponibilidade, incluindo o RPO.

Depois que os logs são capturados na réplica primária, eles estão sujeitos a dois níveis de controles de fluxo. Quando o limite de mensagens de qualquer porta é atingido, as mensagens de log não são mais enviadas para uma réplica específica ou para um banco de dados específico. As mensagens podem ser enviadas assim que as mensagens de confirmação são recebidas para que as mensagens enviadas tragam o número de mensagens enviadas abaixo do limite.

Além das portas de controle de fluxo, há outro fator que pode impedir que as mensagens de log sejam enviadas. A sincronização de réplicas garante que as mensagens sejam enviadas e aplicadas na ordem dos números de sequência do log (LSN). Antes de uma mensagem de log ser enviada, o seu LSN também é verificado em relação ao número LSN reconhecido mais baixo para se certificar de que é menor que um dos limiares (dependendo do tipo de mensagem). Se a diferença entre os dois números LSN for maior do que o limite, as mensagens não serão enviadas. Assim que o intervalo estiver novamente abaixo do limite, as mensagens são enviadas.

O SQL Server 2022 (16.x) aumenta os limites para o número de mensagens que cada porta permite. A partir das seguintes versões, use o traço flag 12310 no arranque para aumentar o limite: SQL Server 2019 (15.x) CU9, SQL Server 2017 (14.x) CU18 e SQL Server 2016 (13.x) SP1 CU16. Este flag de traço não pode ser usado com DBCC TRACEON.

A tabela a seguir compara os limites de mensagens:

Dois contadores de desempenho úteis, SQL Server:Réplica de Disponibilidade > Controlo de Fluxo/seg e SQL Server:Réplica de Disponibilidade > Tempo do Controlo de Fluxo (ms/seg), mostram-lhe, no último segundo, quantas vezes o controlo de fluxo foi ativado e quanto tempo foi gasto à espera pelo controlo de fluxo. Maior tempo de espera no controle de fluxo se traduz em RPO mais alto. Para obter mais informações sobre os tipos de problemas que podem causar um tempo de espera elevado no controlo de fluxo, consulte Solução de problemas - perda potencial de dados com réplicas de grupos de disponibilidade com confirmação assíncrona.

Estimar o tempo de failover (RTO)

O RTO no seu SLA depende do tempo de failover da implementação Always On em qualquer momento, podendo ser expresso na seguinte fórmula:

O tempo de deteção de falha, Tdetection, é o tempo que o sistema leva para detetar a falha. Esse tempo depende das configurações no nível do cluster e não das réplicas de disponibilidade individuais. Dependendo da condição de comutação por falha automática configurada, uma comutação por falha pode ser acionada como uma resposta instantânea a um erro interno crítico do SQL Server, como spinlocks (bloqueios giratórios) órfãos. Nesse caso, a deteção pode ser tão rápida quanto o envio do relatório de erros do sp_server_diagnostics para o Cluster de Failover do Windows Server (WSFC). O intervalo padrão é 1/3 do tempo limite da verificação de integridade. Um failover também pode ser acionado devido a um tempo limite, como quando o tempo limite da verificação de integridade do cluster expira (30 segundos por padrão) ou quando o lease entre a DLL do recurso e a instância do SQL Server expira (20 segundos por padrão). Nesse caso, o tempo de deteção é tão longo quanto o intervalo de tempo limite. Para obter mais informações, consulte Política de failover flexível para failover automático de um grupo de disponibilidade (SQL Server).

A única coisa que a réplica secundária precisa fazer para estar pronta para um failover é que a reaplicação alcance o final do log. O tempo de refazer, Tredo, é calculado usando a seguinte fórmula:

onde redo_queue representa o valor em redo_queue_size e redo_rate representa o valor em redo_rate.

O tempo de sobrecarga de failover, Toverhead, inclui o tempo necessário para realizar o failover do cluster WSFC e tornar os bancos de dados acessíveis. Este tempo é geralmente curto e constante.

Estimar a perda potencial de dados (RPO)

O RPO em seu SLA depende da possível perda de dados de sua implementação Always On a qualquer momento. Esta possível perda de dados pode ser expressa na seguinte fórmula:

onde log_send_queue é o valor de log_send_queue_size e taxa de geração de log é o valor de SQL Server:Database > Log Bytes Descarregados/s.

A fila de envio de log representa todos os dados que podem ser perdidos por uma falha catastrófica. À primeira vista, é curioso que a taxa de geração de log seja usada em vez da taxa de envio de log (veja log_send_rate). No entanto, lembre-se de que usar a taxa de envio de log só dá tempo para sincronizar, enquanto o RPO mede a perda de dados com base na rapidez com que são gerados, não na velocidade com que são sincronizados.

Uma maneira mais simples de estimar Tdata_loss é usar last_commit_time. O Detran na réplica principal informa esse valor para todas as réplicas. Você pode calcular a diferença entre o valor da réplica primária e o valor da réplica secundária para estimar a rapidez com que o log na réplica secundária está alcançando a réplica primária. Como dito anteriormente, esse cálculo não informa a perda potencial de dados com base na rapidez com que o log é gerado, mas deve ser uma aproximação próxima.

Estimar RTO e RPO com o painel de instrumentos do SSMS

Em Grupos de Disponibilidade Always On, o RTO e o RPO são calculados e exibidos para os bancos de dados hospedados nas réplicas secundárias. No painel do SQL Server Management Studio (SSMS), na réplica primária, o RTO e o RPO são organizados de acordo com a réplica secundária.

Para exibir o RTO e o RPO no painel, execute as seguintes etapas:

1. No SQL Server Management Studio, expanda o nó de Alta Disponibilidade Always On, clique com o botão direito do mouse no nome do seu grupo de disponibilidade e selecione Mostrar Painel.

2. Selecione Adicionar/Remover Colunas na guia Agrupar por. Selecione ambas as opções Tempo de Recuperação Estimado (segundos) [RTO] e Perda de Dados Estimada (tempo) [RPO].

   

Cálculo do RTO do banco de dados secundário

O cálculo do tempo de recuperação determina quanto tempo é necessário para recuperar o banco de dados secundário após a ocorrência de um failover. O tempo de failover é geralmente curto e constante. O tempo de deteção depende das configurações no nível do cluster e não das réplicas de disponibilidade individuais.

Para uma base de dados secundária (DB_sec), o cálculo e a apresentação do seu RTO baseiam-se no seu redo_queue_size e redo_rate:

Exceto casos extremos, a fórmula para calcular o RTO de um banco de dados secundário é:

Cálculo do RPO do banco de dados secundário

Para um banco de dados secundário (DB_sec), o cálculo e a exibição do seu RPO são baseados nos seus is_failover_ready, last_commit_time e nos valores DB_pri do banco de dados primário correlacionado (last_commit_time). Quando o valor de DB_sec.is_failover_ready é 1, os dados entre o primário e o secundário são sincronizados, e nenhuma perda de dados ocorre após o failover. No entanto, se esse valor for 0, há uma lacuna entre o last_commit_time no banco de dados primário e o last_commit_time no banco de dados secundário.

Para a base de dados primária, o last_commit_time é o tempo em que a transação mais recente foi confirmada. Para o banco de dados secundário, o last_commit_time é o tempo de confirmação mais recente para a transação do banco de dados primário, que também foi gravado com sucesso no banco de dados secundário. Esse número é o mesmo para o banco de dados primário e secundário. No entanto, uma lacuna entre esses dois valores é a duração na qual as transações pendentes não foram reforçadas no banco de dados secundário e podem ser perdidas no caso de um failover.

Métricas de desempenho usadas em fórmulas RTO/RPO

• redo_queue_size (KB): O tamanho da fila de refazer, usado no RTO, é o tamanho dos logs de transações entre o last_received_lsn e o last_redone_lsn. O last_received_lsn valor é o ID do bloco de log que identifica o ponto até o qual todos os blocos de log foram recebidos pela réplica secundária que hospeda esse banco de dados secundário. O valor de last_redone_lsn é o número de sequência de log do último registro de log que foi refeito no banco de dados secundário. Com base nesses dois valores, podemos encontrar IDs do bloco de log inicial (last_received_lsn) e do bloco de log final (last_redone_lsn). O espaço entre esses dois blocos de log pode representar quantos blocos de log de transações ainda não foram refeitos. Isto é medido em Kilobytes(KB).

• redo_rate (KB/seg): Usado no cálculo de RTO, este é um valor cumulativo que mostra quanto do log de transações (KB) foi refeito ou reproduzido no banco de dados secundário por segundo.

• last_commit_time (datetime): Usado no RPO, esse valor tem um significado diferente entre o banco de dados primário e secundário. Para o banco de dados primário, last_commit_time é o momento em que a transação mais recente foi confirmada. Para o banco de dados secundário, a confirmação mais recente last_commit_time é para a transação no banco de dados primário que também foi protegida com êxito no banco de dados secundário. Como esse valor no secundário deve ser sincronizado com o mesmo valor no primário, qualquer lacuna entre esses dois valores é a estimativa de perda de dados (RPO).

Estimar RTO e RPO usando DMVs

É possível consultar DMVs sys.dm_hadr_database_replica_states e sys.dm_hadr_database_replica_cluster_states para estimar o RPO e o RTO de uma base de dados. As consultas abaixo criam procedimentos armazenados que realizam ambas as coisas.

Criar um procedimento armazenado para estimar o RTO

1. Na réplica secundária de destino, crie o procedimento armazenado proc_calculate_RTO. Se este procedimento armazenado já existir, elimine-o primeiro e depois recrie-o.

   IF object_id(N'proc_calculate_RTO', 'p') IS NOT NULL
       DROP PROCEDURE proc_calculate_RTO;
   GO
   
   RAISERROR ('creating procedure proc_calculate_RTO', 0, 1)
       WITH NOWAIT;
   GO
   
   -- name: proc_calculate_RTO
   --
   -- description: Calculate RTO of a secondary database.
   -- 
   -- parameters: @secondary_database_name nvarchar(max): name of the secondary database.
   --
   -- security: this is a public interface object.
   --
   CREATE PROCEDURE proc_calculate_RTO
   @secondary_database_name NVARCHAR (MAX)
   AS
   BEGIN
       DECLARE @db AS sysname;
       DECLARE @is_primary_replica AS BIT;
       DECLARE @is_failover_ready AS BIT;
       DECLARE @redo_queue_size AS BIGINT;
       DECLARE @redo_rate AS BIGINT;
       DECLARE @replica_id AS UNIQUEIDENTIFIER;
       DECLARE @group_database_id AS UNIQUEIDENTIFIER;
       DECLARE @group_id AS UNIQUEIDENTIFIER;
       DECLARE @RTO AS FLOAT;
       SELECT @is_primary_replica = dbr.is_primary_replica,
              @is_failover_ready = dbcs.is_failover_ready,
              @redo_queue_size = dbr.redo_queue_size,
              @redo_rate = dbr.redo_rate,
              @replica_id = dbr.replica_id,
              @group_database_id = dbr.group_database_id,
              @group_id = dbr.group_id
       FROM sys.dm_hadr_database_replica_states AS dbr
            INNER JOIN sys.dm_hadr_database_replica_cluster_states AS dbcs
                ON dbr.replica_id = dbcs.replica_id
               AND dbr.group_database_id = dbcs.group_database_id
       WHERE dbcs.database_name = @secondary_database_name;
       IF @is_primary_replica IS NULL
          OR @is_failover_ready IS NULL
          OR @redo_queue_size IS NULL
          OR @replica_id IS NULL
          OR @group_database_id IS NULL
          OR @group_id IS NULL
           BEGIN
               PRINT 'RTO of Database ' + @secondary_database_name + ' is not available';
               RETURN;
           END
       ELSE
           IF @is_primary_replica = 1
               BEGIN
                   PRINT 'You are visiting wrong replica';
                   RETURN;
               END
       IF @redo_queue_size = 0
           SET @RTO = 0;
       ELSE
           IF @redo_rate IS NULL
              OR @redo_rate = 0
               BEGIN
                   PRINT 'RTO of Database ' + @secondary_database_name + ' is not available';
                   RETURN;
               END
           ELSE
               SET @RTO = CAST (@redo_queue_size AS FLOAT) / @redo_rate;
       PRINT 'RTO of Database ' + @secondary_database_name + ' is ' + CONVERT (VARCHAR, ceiling(@RTO));
       PRINT 'group_id of Database ' + @secondary_database_name + ' is ' + CONVERT (NVARCHAR (50), @group_id);
       PRINT 'replica_id of Database ' + @secondary_database_name + ' is ' + CONVERT (NVARCHAR (50), @replica_id);
       PRINT 'group_database_id of Database ' + @secondary_database_name + ' is ' + CONVERT (NVARCHAR (50), @group_database_id);
   END
   

2. Execute proc_calculate_RTO com o nome do banco de dados secundário de destino:

   EXECUTE proc_calculate_RTO @secondary_database_name = N'DB_sec';
   

3. A saída exibe o valor RTO do banco de dados de réplica secundária de destino. Guarde os group_id, replica_ide group_database_id para usar com o procedimento de armazenamento da estimativa de RPO.

   Saída da amostra:

   RTO of Database DB_sec' is 0
   group_id of Database DB4 is F176DD65-C3EE-4240-BA23-EA615F965C9B
   replica_id of Database DB4 is 405554F6-3FDC-4593-A650-2067F5FABFFD
   group_database_id of Database DB4 is 39F7942F-7B5E-42C5-977D-02E7FFA6C392
   

Criar um procedimento armazenado para estimar o RPO

1. Na réplica primária, crie o procedimento armazenado proc_calculate_RPO. Se já existir, largue-o primeiro e depois recrie-o.

   IF object_id(N'proc_calculate_RPO', 'p') IS NOT NULL
       DROP PROCEDURE proc_calculate_RPO;
   GO
   
   RAISERROR ('creating procedure proc_calculate_RPO', 0, 1)
       WITH NOWAIT;
   GO
   
   -- name: proc_calculate_RPO
   --
   -- description: Calculate RPO of a secondary database.
   -- 
   -- parameters: @group_id uniqueidentifier: group_id of the secondary database.
   --             @replica_id uniqueidentifier: replica_id of the secondary database.
   --             @group_database_id uniqueidentifier: group_database_id of the secondary database.
   --
   -- security: this is a public interface object.
   --
   CREATE PROCEDURE proc_calculate_RPO
   @group_id UNIQUEIDENTIFIER, @replica_id UNIQUEIDENTIFIER, @group_database_id UNIQUEIDENTIFIER
   AS
   BEGIN
       DECLARE @db_name AS sysname;
       DECLARE @is_primary_replica AS BIT;
       DECLARE @is_failover_ready AS BIT;
       DECLARE @is_local AS BIT;
       DECLARE @last_commit_time_sec AS DATETIME;
       DECLARE @last_commit_time_pri AS DATETIME;
       DECLARE @RPO AS NVARCHAR (MAX);
       SELECT @db_name = dbcs.database_name,
              @is_failover_ready = dbcs.is_failover_ready,
              @last_commit_time_sec = dbr.last_commit_time
       FROM sys.dm_hadr_database_replica_states AS dbr
            INNER JOIN sys.dm_hadr_database_replica_cluster_states AS dbcs
                ON dbr.replica_id = dbcs.replica_id
               AND dbr.group_database_id = dbcs.group_database_id
       WHERE dbr.group_id = @group_id
             AND dbr.replica_id = @replica_id
             AND dbr.group_database_id = @group_database_id;
       SELECT @last_commit_time_pri = dbr.last_commit_time,
              @is_local = dbr.is_local
       FROM sys.dm_hadr_database_replica_states AS dbr
            INNER JOIN sys.dm_hadr_database_replica_cluster_states AS dbcs
                ON dbr.replica_id = dbcs.replica_id
               AND dbr.group_database_id = dbcs.group_database_id
       WHERE dbr.group_id = @group_id
             AND dbr.is_primary_replica = 1
             AND dbr.group_database_id = @group_database_id;
       IF @is_local IS NULL
          OR @is_failover_ready IS NULL
           BEGIN
               PRINT 'RPO of database ' + @db_name + ' is not available';
               RETURN;
           END
       IF @is_local = 0
           BEGIN
               PRINT 'You are visiting wrong replica';
               RETURN;
           END
       IF @is_failover_ready = 1
           SET @RPO = '00:00:00';
       ELSE
           IF @last_commit_time_sec IS NULL
              OR @last_commit_time_pri IS NULL
               BEGIN
                   PRINT 'RPO of database ' + @db_name + ' is not available';
                   RETURN;
               END
           ELSE
               BEGIN
                   IF DATEDIFF(ss, @last_commit_time_sec, @last_commit_time_pri) < 0
                       BEGIN
                           PRINT 'RPO of database ' + @db_name + ' is not available';
                           RETURN;
                       END
                   ELSE
                       SET @RPO = CONVERT (VARCHAR, DATEADD(ms, datediff(ss, @last_commit_time_sec, @last_commit_time_pri) * 1000, 0), 114);
               END
       PRINT 'RPO of database ' + @db_name + ' is ' + @RPO;
   END
   
   -- secondary database's last_commit_time -- correlated primary database's last_commit_time
   

2. Executar proc_calculate_RPO com o group_id, replica_id e group_database_id do banco de dados secundário de destino.

   EXECUTE proc_calculate_RPO
       @group_id = 'F176DD65-C3EE-4240-BA23-EA615F965C9B',
       @replica_id = '405554F6-3FDC-4593-A650-2067F5FABFFD',
       @group_database_id = '39F7942F-7B5E-42C5-977D-02E7FFA6C392';
   

3. A saída exibe o valor RPO do banco de dados de réplica secundária de destino.

Monitor para RTO e RPO

Esta seção demonstra como monitorar seus grupos de disponibilidade para métricas de RTO e RPO. Esta demonstração é semelhante ao tutorial sobre a Interface Gráfica de Utilizador (GUI) apresentado em O modelo de saúde Sempre Ativo, parte 2: Ampliando o modelo de saúde.

Os elementos da estimativa do tempo de recuperação (RTO) e da estimativa da perda potencial de dados (RPO) são disponibilizados convenientemente como métricas de desempenho na faceta de gestão de políticas Estado da réplica do banco de dados. Para obter mais informações, consulte Exibir facetas do Gerenciamento Baseado em Políticas em um Objeto do SQL Server. Você pode monitorar essas duas métricas em um cronograma e ser alertado quando as métricas excederem seu RTO e RPO, respectivamente.

Os scripts demonstrados criam duas diretivas de sistema que são executadas em suas respetivas agendas, com as seguintes características:

• Uma política de RTO que falha quando o tempo de failover estimado excede 10 minutos, sendo avaliado a cada 5 minutos.

• Uma política de RPO que falha quando a perda de dados estimada excede 1 hora, avaliada a cada 30 minutos

• As duas políticas têm configuração idêntica em todas as réplicas de disponibilidade

• As políticas são avaliadas em todos os servidores, mas apenas nos grupos de disponibilidade para os quais a réplica de disponibilidade local é a réplica primária. Se a réplica de disponibilidade local não for a réplica principal, as políticas não serão avaliadas.

• As falhas de política são convenientemente exibidas no Painel Always On quando você o visualiza na réplica principal.

Para criar as políticas, siga estas instruções em todas as instâncias do servidor que participam do grupo de disponibilidade:

1. Inicie o serviço SQL Server Agent se ainda não tiver sido iniciado.

2. No SQL Server Management Studio, no menu Ferramentas , selecione Opções.

3. Na guia SQL Server Always On, selecione Habilitar política Always On definida pelo usuário e selecione OK.

   Essa configuração permite que você exiba políticas personalizadas configuradas corretamente no Painel Always On.

4. Crie uma condição de gerenciamento baseada em políticas usando as seguintes especificações:

   • Nome: RTO
   • Faceta: Estado da Réplica do Banco de Dados
   • Campo: Add(@EstimatedRecoveryTime, 60)
   • Operador: <=
   • Valor: 600

   Essa condição falha quando o tempo potencial de transferência de serviço ultrapassa 10 minutos, incluindo um acréscimo de 60 segundos para a deteção de falhas e a transferência de serviço.

5. Crie uma segunda condição de gerenciamento, , baseada em políticas,, usando as seguintes especificações:

   • Nome: RPO
   • Faceta: Estado da Réplica do Banco de Dados
   • Campo: @EstimatedDataLoss
   • Operador: <=
   • Valor: 3600

   Esta condição falha quando a perda potencial de dados excede 1 hora.

6. Crie uma terceira condição baseada em políticas de gerenciamento usando as seguintes especificações:

   • Nome: IsPrimaryReplica
   • Faceta: do Grupo de Disponibilidade
   • Campo: @LocalReplicaRole
   • Operador: =
   • Valor: Primary

   Esta condição verifica se a réplica de disponibilidade local para um determinado grupo de disponibilidade é a réplica primária.

7. Crie uma política de gerenciamento baseada em políticas usando as seguintes especificações:

   • Página Geral:

     • Nome: CustomSecondaryDatabaseRTO

     • Verificar condição: RTO

     • Contra destinos: cada DatabaseReplicaState em IsPrimaryReplica AvailabilityGroup

       Essa configuração garante que a política seja avaliada somente em grupos de disponibilidade para os quais a réplica de disponibilidade local é a réplica primária.

       • Modo de avaliação: Dentro do cronograma

       • Horário: CollectorSchedule_Every_5min

       • Ativado: selecionado

   • Descrição página:

     • Categoria: Avisos do banco de dados de disponibilidade

       Essa configuração permite que os resultados da avaliação da política sejam exibidos no Painel Always On.

       • Descrição: A réplica atual tem um RTO que excede 10 minutos, assumindo uma sobrecarga de 1 minuto para descoberta e failover. Você deve investigar problemas de desempenho na respetiva instância do servidor imediatamente.

       • Texto para exibir: RTO excedido!

8. Crie uma segunda política de gestão baseada em políticas usando as seguintes especificações:

   • Página Geral:

     • Nome: CustomAvailabilityDatabaseRPO
     • Verificar condição: RPO
     • Contra destinos: cada DatabaseReplicaState em IsPrimaryReplica AvailabilityGroup
     • Modo de avaliação: Dentro do cronograma
     • Horário: CollectorSchedule_Every_30min
     • Ativado: selecionado

   • Descrição página:

     • Categoria: Avisos do banco de dados de disponibilidade

     • Descrição: O banco de dados de disponibilidade excedeu seu RPO de 1 hora. Você deve investigar problemas de desempenho nas réplicas de disponibilidade imediatamente.

     • Texto para exibir: RPO excedido!

Quando terminar, serão criados dois novos trabalhos do SQL Server Agent, um para cada um dos agendamentos de avaliação de políticas. Esses trabalhos devem ter nomes que comecem com syspolicy_check_schedule.

Você pode visualizar o histórico do trabalho para inspecionar os resultados da avaliação. As falhas de avaliação também são registradas no log de aplicativos do Windows (no Visualizador de Eventos) com a ID de Evento 34052. Você também pode configurar o SQL Server Agent para enviar alertas sobre as falhas de política. Para obter mais informações, consulte Configurar alertas para notificar administradores de políticas sobre falhas de política.

Cenários de solução de problemas de desempenho

A tabela a seguir lista os cenários comuns de solução de problemas relacionados ao desempenho.

Eventos prolongados úteis

Os eventos estendidos a seguir são úteis ao solucionar problemas de réplicas no estado Sincronizando.