Monitorar o desempenho de Grupos de Disponibilidade AlwaysOn

Aplica-se a:SQL Server

O aspecto de desempenho dos Grupos de Disponibilidade Always On é crucial para manter o SLA (Contrato de Nível de Serviço) dos seus bancos de dados críticos. Compreender como os grupos de disponibilidade enviam logs para réplicas secundárias ajuda a estimar o RTO (objetivo de tempo de recuperação) e o RPO (objetivo de ponto de recuperação) de sua implementação de disponibilidade e identificar gargalos em grupos de disponibilidade ou réplicas que tenham um desempenho insatisfatório. 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 para 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 esse gargalo poderá ajudá-lo a se obter detalhes sobre os 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 portões de controle de fluxo na réplica primária para evitar o consumo excessivo de recursos, como recursos de rede e de memória, em todas as réplicas de disponibilidade. Essas portas de controle de fluxo não afetam o estado de integridade de 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 forem capturados na réplica primária, eles ficarão sujeitos a dois níveis de controles de fluxo. Quando é alcançado o limite de mensagem de qualquer portão, 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 poderão ser enviadas depois que as mensagens de confirmação das mensagens enviadas forem recebidas, a fim de reduzir 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 das réplicas garante que as mensagens sejam enviadas e aplicadas na ordem do LSN (número de sequência de log). Antes que uma mensagem de log seja enviada, seu LSN também foi verificado em relação ao menor número LSN reconhecido para garantir que ele seja menor que um dos limites (dependendo do tipo de mensagem). Se a diferença entre os dois números LSN for maior que o limite, as mensagens não serão enviadas. Depois que o intervalo estiver abaixo do limite novamente, as mensagens serã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 sinalizador de rastreamento 12310 na inicialização para aumentar o limite: SQL Server 2019 (15.x) CU9, SQL Server 2017 (14.x) CU18 e SQL Server 2016 (13.x) SP1 CU16. Esse sinalizador de rastreamento não pode ser usado com DBCC TRACEON.

A seguinte tabela compara os limites de mensagem:

Dois contadores de desempenho úteis, SQL Server:Réplica de Disponibilidade > Controle de fluxo/s e SQL Server:Réplica de Disponibilidade> Tempo de Controle do Fluxo (ms/s), mostram, no último segundo, o número de vezes que o controle de fluxo foi ativado e quanto tempo foi gasto aguardando pelo controle de fluxo. Um tempo maior de espera pelo controle de fluxo significa um RPO maior. Para obter mais informações sobre os tipos de problemas que podem causar um alto tempo de espera no controle de fluxo, consulte Solucionar problemas: potencial perda de dados com réplicas de grupo de disponibilidade de confirmação assíncrona.

Estimar o tempo de failover (RTO)

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

O tempo de detecção de falha, Tdetection, é o tempo necessário para o sistema detectar a falha. Esse tempo depende de configurações no nível do cluster e não de cada uma das réplicas de disponibilidade. Dependendo da condição de failover automático configurada, um failover poderá ser disparado como uma resposta imediata para um erro interno crítico do SQL Server, como spinlocks órfãos. Nesse caso, a detecção pode ser tão rápida quanto o relatório de erros sp_server_diagnostics é enviado para o WSFC (Cluster de Failover do Windows Server). O intervalo padrão é 1/3 do tempo limite de verificação de integridade. Um failover também pode ser disparado devido a um tempo limite, como a expiração do tempo limite de verificação de integridade do cluster (30 segundos por padrão) ou a expiração do tempo de concessão entre a DLL de recurso e a instância do SQL Server (20 segundos por padrão). Nesse caso, o tempo de detecção é tão longo quanto o intervalo de tempo limite. Para obter mais informações, confira 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 para estar pronta para um failover é que a fase refazer alcance o final do log. O tempo para refazer, Tredo, é calculado com a seguinte fórmula:

em que redo_queue é o valor de redo_queue_size e redo_rate é o valor de redo_rate.

O tempo de sobrecarga de failover, Toverhead, inclui o tempo necessário para realizar o failover do cluster do WSFC e colocar os bancos de dados online. Esse tempo é geralmente curto e constante.

Estimar o potencial de perda de dados (RPO)

O RPO do seu SLA depende da possível perda de dados da implementação Always On em um determinado momento. Essa possível perda de dados pode ser expressa na seguinte fórmula:

em que log_send_queue é o valor de log_send_queue_size e taxa de geração de log é o valor de SQL Server:Database > Bytes de log liberados/s.

A fila de envio de log representa todos os dados que podem ser perdidos em 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 (consulte log_send_rate). No entanto, lembre-se de que usar a taxa de envio de log só lhe dá tempo para sincronizar, enquanto o RPO mede a perda de dados com base na rapidez com que ela é gerada, não na rapidez com que ela é sincronizada.

Uma maneira mais simples de estimar o Tdata_loss é usar last_commit_time. O DMV na réplica primária relata 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 velocidade com que o log da réplica secundária é atualizado com a réplica primária. Conforme indicado anteriormente, esse cálculo não informa a possível perda de dados com base na rapidez com que o log é gerado, mas deve ser uma aproximação próxima.

Estimar o RTO e RPO com o painel do SSMS

Em Grupos de Disponibilidade AlwaysOn, o RTO e o RPO são calculados e exibidos para os bancos de dados hospedados nas réplicas secundárias. No painel SSMS (SQL Server Management Stuiod), na réplica primária, o RTO e o RPO são agrupados pela 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ó Always On High Availability, clique com o botão direito do mouse no nome do seu grupo de disponibilidade e selecione Mostrar Dashboard.

2. Selecione Adicionar/remover colunas na guia Agrupar por. Verifique o Tempo estimado para recuperação (segundos) [RTO] e também a 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 um failover. O tempo de failover geralmente é curto e constante. O tempo de detecção depende de configurações no nível do cluster e não de cada uma das réplicas de disponibilidade.

Para um banco de dados secundário (DB_sec), o cálculo e a exibição de seu RTO baseiam-se em seu redo_queue_size e redo_rate:

Exceto em casos muito atípicos, a fórmula para calcular o RTO do 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 de seu RPO baseiam-se em seus is_failover_ready, last_commit_time e nos valores de DB_pri do banco de dados primário (last_commit_time) correlacionados. Quando o valor de DB_sec.is_failover_ready é 1, os dados entre os primários e os secundários são sincronizados e não ocorre perda de dados durante a mudança. No entanto, se esse valor for 0, haverá uma lacuna entre o last_commit_time banco de dados primário e o last_commit_time banco de dados secundário.

Para o banco de dados primário, o momento em que a transação mais recente foi confirmada é o last_commit_time. Para o banco de dados secundário, o last_commit_time é a confirmação mais recente para a transação do banco de dados primário que foi protegido com êxito no banco de dados secundário também. 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 em que as transações pendentes não foram protegidas 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 seu last_received_lsn e last_redone_lsn. O last_received_lsn valor é a 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 da sequência de logs 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. Isso é medido em quilobytes (KB).

• redo_rate (KB/s): usado no cálculo RTO, esse é um valor cumulativo que mostra quanto do KB (log de transações) 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 é a hora em que a transação mais recente foi confirmada. Para o banco de dados secundário, o last_commit_time é a confirmação mais recente para a transação no banco de dados primário que foi protegido com êxito no banco de dados secundário também. Uma vez que esse valor na réplica secundária deve ser sincronizado com o mesmo valor na réplica primária, qualquer lacuna existente entre esses dois valores é a estimativa de perda de dados (RPO).

Estimar o RTO e o RPO usando DMVs

É possível consultar as DMVs sys.dm_hadr_database_replica_states e sys.dm_hadr_database_replica_cluster_states para estimar o RPO e o RTO de um banco de dados. As consultas abaixo criam procedimentos armazenados que fazem duas 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 esse procedimento armazenado já existir, remova-o primeiro e, em seguida, 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 de RTO do banco de dados de réplica secundária de destino. Salve a group_id, a replica_id e a group_database_id para usar com o procedimento armazenado de estimativa de RPO.

   Saída de exemplo:

   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 um procedimento armazenado proc_calculate_RPO. Se ele já existir, remova-o primeiro e, em seguida, 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. Execute proc_calculate_RPO com os 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 de RPO do banco de dados de réplica secundária de destino.

Monitorar o RTO e o RPO

Esta seção demonstra como monitorar as métricas de RTO e RPO de seus grupos de disponibilidade. Esta demonstração é semelhante ao tutorial de GUI fornecido em The Always On health model, part 2: Extending the health model (O modelo de integridade do Always On, parte 2: estendendo o modelo de integridade).

Elementos do tempo de failover e dos cálculos de possível perda de dados em Estimativa do Tempo de Failover (RTO) e Estimativa de Potencial Perda de Dados (RPO) são convenientemente fornecidos como métricas de desempenho na faceta de gerenciamento de políticas Estado de Réplica do Banco de Dados. Para obter mais informações, consulte Exibir facetas de gerenciamento baseadas em políticas em um objeto do SQL Server. Você pode monitorar essas duas métricas de acordo com um agendamento e ser alertado quando as métricas excederem o RTO e RPO, respectivamente.

Os scripts demonstrados criam duas políticas de sistema que são executadas de acordo com os respectivos agendamentos, com as seguintes características:

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

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

• As duas políticas têm configurações idênticas em todas as réplicas de disponibilidade

• As políticas são avaliadas em todos os servidores, mas somente 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 primária, as políticas não serão avaliadas.

• As falhas de política são convenientemente exibidas no Painel Always On quando você as exibe na réplica primária.

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

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

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

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

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

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

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

   Essa condição falha quando o tempo de failover potencial ultrapassa 10 minutos, incluindo uma sobrecarga de 60 segundos para detecção de falha e failover.

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

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

   Essa condição de falha quando a possível perda de dados excede 1 hora.

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

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

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

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

   • Página geral:

     • Nome: CustomSecondaryDatabaseRTO

     • Verificar condição: RTO

     • Em relação aos destinos: Cada DatabaseReplicaState em IsPrimaryReplica AvailabilityGroup

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

       • Modo de avaliação: No horário programado

       • Agendamento: CollectorSchedule_Every_5min

       • Habilitado: selecionado

   • Página descrição:

     • Categoria: Avisos do banco de dados de disponibilidade

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

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

       • Texto a ser exibido: Tempo de recuperação excedido!

8. Crie uma segunda política de gerenciamento baseado em políticas usando as seguintes especificações:

   • Página geral:

     • Nome: CustomAvailabilityDatabaseRPO
     • Verificar condição: RPO
     • Em relação aos destinos: Cada DatabaseReplicaState em IsPrimaryReplica AvailabilityGroup
     • Modo de avaliação: No horário programado
     • Agendamento: CollectorSchedule_a_cada_30_minutos
     • Habilitado: selecionado

   • Página descrição:

     • Categoria: Avisos do banco de dados de disponibilidade

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

     • Texto a ser exibido: RPO excedido!

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

Você pode exibir o histórico de trabalhos 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 falhas de política. Para obter mais informações, consulte Configurar alertas para notificar administradores de políticas de 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 estendidos úteis

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