Always On : Supervision des Groupes de Disponibilité

Compteurs et indicateurs clés à surveiller pour les Groupes de Disponibilité Always On de SQL Server.
Tags:
Categories:

Supervision proactive des Groupes de Disponibilité Always On (AG).

Compteurs de Performance

Compteurs importants à suivre et alerter :

Cluster Resources (WSFC) / Resource Failure

Mesure : nombre total (cumulatif) d’échecs de ressources WSFC (une ressource passe en état Failed / nécessite redémarrage).

Doit rester stable (ou monter très rarement).

Élevé / augmente : indique des incidents réels (timeout health check, service SQL redémarré, perte réseau, etc.).

Pistes : événements WSFC + SQL ERRORLOG + System/Application (service crash, perte heartbeat, etc.)

Cluster Resources (WSFC) / Resources Online

Mesure : nombre de ressources WSFC en ligne

Doit être égal au nombre total de ressources configurées dans le cluster.

Baisse : ressource Offline/Failed → à corréler immédiatement aux événements cluster.

SQLServer:Availability Replica / Flow Control Time (ms/sec)

Mesure : temps (en millisecondes par seconde) pendant lequel l’envoi a été throttlé par le mécanisme de flow control.

Interprétation :

  • 0 : pas de throttling.
  • > 0 : SQL ralentit volontairement l’envoi, typiquement parce que le secondaire ne suit pas (redo lent, ressources saturées, backlog).

Important : c’est un indicateur direct de pression côté secondaire/transport.

SQLServer:Availability Replica / Flow Control/sec

Mesure : nombre d’événements de flow control par seconde.

Interprétation : plus c’est élevé, plus souvent SQL applique de la régulation.

Souvent corrélé : Flow Control/sec > 0 et Flow Control Time > 0 → secondaire en difficulté (CPU, I/O redo, réseau).

SQLServer:Database Replica / Database Flow Control Delay

Par base de données

Mesure : délai (ms) imposé par flow control au niveau base (throttling spécifique à cette DB).

Interprétation :

  • 0 est la valeur normale
  • > 0 indique que cette base est limitée (secondaire en retard, pression redo/I/O).

SQLServer:Database Replica / Database Flow Controls/sec

Par base de données

Mesure : événements de flow control par seconde pour cette base.

Interprétation : si >0 de manière persistante, c’est un signe de contrainte côté secondaire (ou transport).

SQLServer:Database Replica / Log Apply Pending Queue

Par base de données

Mesure : quantité de log reçue, mais pas encore prête à être appliquée (en attente de traitement interne).

Interprétation : reflète un backlog “avant redo”.

Hausse continue : secondaire n’ingère pas assez vite (CPU, I/O, contention, réseau, compression, etc.).

SQLServer:Database Replica / Log Apply Ready Queue

Par base de données

Mesure : quantité de log prête à être appliquée (redo) mais pas encore appliquée.

Interprétation : backlog “redo”.

Hausse : redo thread(s) ne suivent pas → souvent I/O data secondaire, CPU secondaire, contention, ou requêtes lisant sur secondaire.

SQLServer:Database Replica / Log Send Queue

Par base de données

Mesure : quantité de log en attente d’envoi depuis le primaire vers le secondaire.

Interprétation : indicateur majeur de backlog côté primaire.

Hausse continue : primaire génère plus de log que ce qui est envoyé

  • réseau saturé, compression lente, ou
  • secondaire ne suit pas (flow control), ou
  • CPU primaire/threads transport saturés.

Pistes : comparer à Bytes Sent/sec, Flow Control, Resent Messages, latence.

SQLServer:Database Replica / Recovery Queue

Par base de données

Mesure : quantité de travail en attente pour le recovery (redo/undo/recovery interne) sur la base répliquée.

Interprétation : 0 en régime stable ; augmente lors de rattrapage, failover, resynchronisation.

SQLServer:Database Replica / Redo blocked/sec

Par base de données

Mesure : nombre d’événements par seconde où le redo est bloqué (ne peut pas appliquer).

Interprétation : très important ; un redo bloqué signifie que le secondaire ne peut pas avancer.

Causes fréquentes : I/O data saturées, verrous internes, contraintes ressources, opérations DDL, contention, manque CPU, requêtes lourdes sur secondaire lisible.

SQLServer:Database Replica / Transaction Delay

Par base de données

Mesure : délai (ms) subi par les transactions à cause de la réplication/commit (typiquement en synchrone).

Interprétation : 0 en asynchrone (souvent), ou faible en synchrone sain.

Monte si le commit attend le secondaire (latence réseau, secondaire lent).

Pistes : HADR_SYNC_COMMIT, latence réseau, performance log primaire, redo secondaire.

synthèse

  • Throttling : Flow Control Time, Flow Control/sec, Database Flow Control*.
  • Backlog primaire : Log Send Queue.
  • Backlog secondaire : Log Apply Pending/Ready Queue, Redo Bytes Remaining.
  • Capacité du secondaire à suivre : Redone Bytes/sec, Redo blocked/sec.
  • Impact sur les commits (synchrone) : Group Commit Time, Transaction Delay.

Requêtes directes T-SQL

Replica States
Voir le fichier
-----------------------------------------------------------------
-- availability replicas states
--
-- rudi@babaluga.com, go ahead license
-----------------------------------------------------------------

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT
	g.name as AG,
	ar.replica_server_name as [Replica],
	rs.is_local,
	rs.role_desc as role,
	rs.operational_state_desc as operational_state,
	rs.connected_state_desc as connected_state,
	rs.recovery_health_desc as recovery_health,
	rs.synchronization_health_desc as synchronization_health
FROM sys.dm_hadr_availability_replica_states rs
JOIN sys.availability_replicas ar ON rs.replica_id = ar.replica_id
JOIN sys.availability_groups g ON ar.group_id = g.group_id
ORDER BY g.name, ar.replica_server_name, rs.role
OPTION (RECOMPILE, MAXDOP 1);

Alertez sur les colonnes :

  • operational_state – Si <> ONLINE ou si NULL, il y a un problème.
  • connected_state – Si <> CONNECTED, il y a un problème.
  • recovery_health – Si <> HEALTHY, il y a un problème.
  • synchronization_health – Si <> HEALTHY, il y a un problème.
Database Replica States
Voir le fichier
-----------------------------------------------------------------
-- AlwaysOn database replica states
-- Rudi Bruchez - rudi@babaluga.com - 2020.07.02 - version 01
-----------------------------------------------------------------

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT 
	g.name as AG
   ,ar.replica_server_name as [Replica]
   ,DB_NAME(rs.database_id) AS [db]
   ,rs.is_local
   ,rs.is_primary_replica
   ,CONCAT(rs.synchronization_state_desc, ' (', rs.synchronization_state, ')') AS synchronization_state
   ,CONCAT(rs.synchronization_health_desc, ' (', rs.synchronization_health, ')') AS synchronization_health
   ,CONCAT(rs.database_state_desc, ' (', rs.database_state, ')') AS database_state
   ,CONCAT(rs.is_suspended, IIF(rs.is_suspended = 0, '', CONCAT(rs.suspend_reason_desc, ' (', rs.suspend_reason, ')'))) AS is_suspended
   --,rs.recovery_lsn
   --,rs.truncation_lsn
   --,rs.last_sent_lsn
   ,CAST(rs.last_sent_time AS TIME(3)) AS last_sent
   --,rs.last_received_lsn
   ,CAST(rs.last_received_time AS TIME(3)) AS last_received
   --,rs.last_hardened_lsn
   ,CAST(rs.last_hardened_time AS TIME(3)) AS last_hardened
   --,rs.last_redone_lsn
   ,CAST(rs.last_redone_time AS TIME(3)) AS last_redone
   ,rs.log_send_queue_size
   ,DATEDIFF(minute, rs.last_redone_time, rs.last_hardened_time) as redo_latency_minutes
   ,rs.log_send_rate
   ,rs.redo_queue_size
   ,rs.redo_rate
   --,rs.filestream_send_rate
   --,rs.end_of_log_lsn
   --,rs.last_commit_lsn
   ,CAST(rs.last_commit_time AS TIME(3)) AS last_commit
   ,rs.low_water_mark_for_ghosts
   ,rs.secondary_lag_seconds
FROM sys.dm_hadr_database_replica_states rs
JOIN sys.availability_groups g ON rs.group_id = g.group_id
JOIN sys.availability_replicas ar ON rs.replica_id = ar.replica_id
OPTION (RECOMPILE, MAXDOP 1);

Alertez sur les colonnes :

  • synchronization_state – Si <> SYNCHRONIZED (synchrone) ou NOT SYNCHRONIZED (asynchrone), il y a un problème.
  • synchronization_health – Si <> HEALTHY, il y a un problème.
  • is_suspended – Si <> 0, il y a un problème.
Expertise SQL Server Besoin de services avec SQL Server ? Contactez-moi et parlons de votre projet