¿Cómo monitorizar el GAP en AlwaysOn?

Buenas a todos! Hoy veremos cómo monitorizar el GAP en AlwaysOn para poder reaccionar rápidamente y minimizar el impacto que tiene recuperar al punto de endurecimiento. Este punto es de crucial importancia ya que es el umbral donde el log se va vaciando, originando la parada de otros nodos por causa de este y desembocando en problemas no esperados. Más información sobre AlwaysOn en https://www.gpsos.es/2020/09/alta-disponibilidad-en-versiones-standard/

¿Cómo monitorizar el GAP en AlwaysOn?

Para monitorizar utilizaremos las tablas:

sys.dm_hadr_availability_replica_states
sys.dm_hadr_database_replica_states 
sys.availability_replicas  

Cruzando estas tablas obtenemos el número de segundos de diferencia de sincronización por BBDD así como el destino contra el que sincronizan:

     ;WITH UpTime AS
    (
    SELECT DATEDIFF(SECOND,create_date,GETDATE()) [upTime_secs]
    FROM sys.databases
    WHERE name = 'tempdb'
    ),
  AG_Stats AS 
    (
    SELECT AR.replica_server_name,
        HARS.role_desc, 
        Db_name(DRS.database_id) [DBName], 
        CAST(DRS.log_send_queue_size AS DECIMAL(19,2)) log_send_queue_size_KB, 
        (CAST(perf.cntr_value AS DECIMAL(19,2)) / CAST(UpTime.upTime_secs AS DECIMAL(19,2))) / CAST(1024 AS DECIMAL(19,2)) [log_KB_flushed_per_sec]
    FROM   sys.dm_hadr_database_replica_states DRS 
    INNER JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id 
    INNER JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id 
     AND AR.replica_id = HARS.replica_id 
    --I am calculating this as an average over the entire time that the instance has been online.
    --To capture a smaller, more recent window, you will need to:
    --1. Store the counter value.
    --2. Wait N seconds.
    --3. Recheck counter value.
    --4. Divide the difference between the two checks by N.
    INNER JOIN sys.dm_os_performance_counters perf ON perf.instance_name = Db_name(DRS.database_id)
     AND perf.counter_name like 'Log Bytes Flushed/sec%'
    CROSS APPLY UpTime
    ),
  Pri_CommitTime AS 
    (
    SELECT replica_server_name
      , DBName
      , [log_KB_flushed_per_sec]
    FROM AG_Stats
    WHERE role_desc = 'PRIMARY'
    ),
  Sec_CommitTime AS 
    (
    SELECT replica_server_name
      , DBName
      --Send queue will be NULL if secondary is not online and synchronizing
      , log_send_queue_size_KB
    FROM AG_Stats
    WHERE role_desc = 'SECONDARY'
    )
 SELECT p.replica_server_name [primary_replica]
  , p.[DBName] AS [DatabaseName]
  , s.replica_server_name [secondary_replica]
  , CAST(s.log_send_queue_size_KB / p.[log_KB_flushed_per_sec] AS BIGINT) [Sync_Lag_Secs]
 FROM Pri_CommitTime p
 LEFT JOIN Sec_CommitTime s ON [s].[DBName] = [p].[DBName] 

La salida será la siguiente:

Una vez tenemos los segundos por BBDD nos es fácil agregar una suma y programar un job para que cuando dicha suma sea superior a 5 min (300) salte. Debemos cambiar la salida del WITH con el siguiente código:

declare @TEMPPGAP int;
SELECT     sum(CAST(s.log_send_queue_size_KB / p.[log_KB_flushed_per_sec] AS   BIGINT)) [Sync_Lag_Secs] into ##TEMPPGAP
   FROM Pri_CommitTime p
   LEFT JOIN Sec_CommitTime s ON [s].[DBName] =   [p].[DBName]
    
    set   @TEMPPGAP= (select [Sync_Lag_Secs] from ##TEMPPGAP)
    
    If  ( @TEMPPGAP > 3600)
     begin
EXEC   msdb..sp_send_dbmail @profile_name='Alert Manager',
                            @recipients='alertas@gpsos.es',
                            @subject='GAP   con las réplicas',
                            @body='Revisar los nodos',
                            @query='select [Sync_Lag_Secs]   from                           ##TEMPPGAP ',
                            @query_result_header   = 1,
                            @query_no_truncate   = 0
   end      

A partir de ahora recibiremos un email cuando nuestras réplicas estén desincronizadas.

Si quieres que te echemos una mano con tu entorno ya sea de gestión o de consultoría no dudes en contactarnos!! Nos vemos en la próxima!!

No dudes en suscribirte a nuestra newsletter mensual para no perderte ninguna de nuestras publicaciones con un solo email al mes.

¿Aún no conoces Query Performance? Descubre cómo puede ayudarte en tu entorno Oracle. Más información en su página de LinkedIn.

Sígue a GPS en LinkedIn

Más información sobre AlwaysOn en https://docs.microsoft.com/es-es/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server?view=sql-server-ver15