Résoudre les problèmes de mise en file d’attente de récupération (rétablissement) dans un groupe de disponibilité Always On

Résumé

Cet article vous aide à résoudre les problèmes de mise en file d’attente de la récupération (également appelée mise en file d’attente de réexécution) sur une réplica secondaire dans un groupe de disponibilité Always On de SQL Server. Il explique ce qu’est la file d’attente de récupération, comment vérifier la taille de la file d’attente redo et le taux de redo, comment interpréter les valeurs, et comment diagnostiquer et corriger les causes courantes du retard de redo, y compris les threads de redo bloqués et le redo mono-thread.

Qu’est-ce que la mise en file d’attente pour la récupération

Les modifications apportées au réplica principal d’une base de données de groupe de disponibilité sont envoyées à chaque réplica secondaire du même groupe de disponibilité. Une fois les modifications apportées à un réplica secondaire, elles sont d’abord écrites (renforcées) dans le fichier journal des transactions de la base de données du groupe de disponibilité. Microsoft SQL Server utilise ensuite l’opération de récupération ou de restauration pour appliquer ces enregistrements de journal aux fichiers de base de données.

Si les modifications arrivent et sont écrites dans le journal des transactions plus rapidement qu’elles ne peuvent être réappliquées, une file d’attente de récupération se forme. Cette file d’attente est l’ensemble des enregistrements de journalisation validés qui ne sont pas encore appliqués à la base de données.

Symptômes et effets de la mise en file d’attente de récupération

Données obsolètes sur les réplicas secondaires

Les charges de travail en lecture seule qui interrogent des réplicas secondaires peuvent renvoyer des données obsolètes. Si la récupération est mise en file d’attente, les modifications récentes apportées à la base de données de la réplique principale ne sont pas encore visibles sur la réplique secondaire lorsque vous interrogez ces mêmes données.

Les modifications parviennent au serveur secondaire et sont inscrites dans le fichier journal de la base de données, mais elles ne sont pas lisibles tant que l’opération de redo ne les a pas appliquées aux fichiers de données.

Pour plus d’informations, consultez la section Latence des données sur le réplica secondaire de l’article « Différences entre les modes de disponibilité d’un groupe de disponibilité Always On ».

Temps de basculement allongé ou RTO dépassé

L’objectif de temps de récupération (RTO) est le temps d’arrêt maximal de la base de données qu’une organisation peut tolérer et la rapidité avec laquelle l’organisation peut récupérer l’utilisation de la base de données après une panne. Si une file d’attente de récupération volumineuse existe sur un réplica secondaire lorsqu’un basculement se produit, la restauration sur le nouveau réplica principal peut prendre plus de temps que le RTO. Une fois la restauration terminée, la base de données passe au rôle principal et reflète l’état qui existait avant le basculement. Un temps de reprise plus long retarde la reprise de la production.

Les outils de diagnostic signalent un groupe de disponibilité non sain

Lorsque la mise en file d’attente est importante, le tableau de bord Always On dans SQL Server Management Studio (SSMS) peut afficher le groupe de disponibilité comme non sain.

Vérifier la mise en file d’attente pour la récupération

La file d’attente de récupération est une mesure par base de données. Vous pouvez le vérifier dans le tableau de bord Always On sur le réplica principal ou en interrogeant la vue de gestion dynamique (DMV) sys.dm_hadr_database_replica_states sur le réplica principal ou secondaire. Analyseur de performances compteurs signalent également la taille de la file d’attente de récupération et le taux de restauration. Vérifiez ces compteurs sur le réplica secondaire.

Les sections suivantes fournissent des méthodes pour surveiller activement votre file d’attente de récupération de base de données de groupe de disponibilité.

Interroger sys.dm_hadr_database_replica_states

Le sys.dm_hadr_database_replica_states DMV signale une ligne pour chaque base de données de groupe de disponibilité. La redo_queue_size colonne affiche la taille de file d’attente de récupération en kilo-octets. Pour surveiller la tendance de la taille de file d’attente de récupération toutes les 30 secondes, configurez une requête comme la suivante. Exécutez-le sur la réplique principale. Il utilise le prédicat is_local=0 pour rapporter des données sur le réplica secondaire, pour lesquelles redo_queue_size et redo_rate sont pertinents.

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

Voici à quoi ressemble la sortie.

Passer en revue la file d’attente de récupération dans le tableau de bord Always On

Pour passer en revue la file d’attente de récupération, procédez comme suit :

1. Dans SSMS Explorateur d'objets, sélectionnez et maintenez enfoncé (ou cliquez avec le bouton droit) un groupe de disponibilité pour ouvrir le menu contextuel.

2. Sélectionnez Afficher le tableau de bord.

   Les bases de données du groupe de disponibilité sont répertoriées en dernier, avec certaines données signalées pour chaque base de données. La taille de file d’attente de rétablissement (Ko) et le taux de rétablissement (Ko/s) ne sont pas répertoriés par défaut, mais vous pouvez les ajouter à la vue, comme indiqué à l’étape suivante.

3. Pour ajouter ces compteurs, sélectionnez et maintenez la touche enfoncée (ou cliquez avec le bouton droit) sur l’en-tête au-dessus des rapports de base de données, puis choisissez les colonnes à afficher.

4. Pour ajouter Redo Queue Size (Ko) et Redo Rate (Ko/s), sélectionnez et maintenez appuyé (ou cliquez avec le bouton droit sur) l’en-tête surligné en rouge dans la capture d’écran suivante.

   

   Par défaut, le tableau de bord Always On actualise automatiquement la taille de la file d’attente de rétablissement (Ko) et le taux de rétablissement (Ko/s) toutes les 60 secondes.

   

Passez en revue la file d’attente de récupération dans Analyseur de performances

Chaque réplica secondaire et base de données a sa propre taille de file d’attente de récupération. Pour passer en revue la file d’attente de récupération d’une base de données de groupe de disponibilité, procédez comme suit :

1. Ouvrez le Moniteur de performances sur le réplica secondaire.

2. Sélectionnez le bouton Ajouter (compteur).

3. Sous Compteurs disponibles, sélectionnez SQLServer:Database Replica, puis sélectionnez les compteurs File de récupération et Redone Bytes/sec.

4. Dans la zone de liste Instance , sélectionnez la base de données du groupe de disponibilité que vous souhaitez surveiller pour la mise en file d’attente de récupération.

5. Sélectionnez Ajouter>OK.

   Voici à quoi peut ressembler l’augmentation de la mise en file d’attente de récupération.

   

Interpréter les valeurs de mise en file d’attente de récupération

Cette section explique comment interpréter les valeurs de mise en file d’attente de récupération que vous avez collectées dans la section précédente.

Lorsque la mise en file d’attente de récupération est un problème

Une valeur de la file d’attente de récupération de 0 signifie qu’il n’y a aucun retard de réexécution au moment du rapport. Dans un environnement de production très sollicité, la file d’attente de récupération affiche souvent une valeur non nulle, même lorsque le groupe de disponibilité est en bon état de fonctionnement. Pendant la production classique, attendez-vous que la valeur varie entre 0 et une valeur différente de zéro.

Si la file d’attente de récupération augmente au fil du temps, approfondissez l’analyse. La croissance indique que quelque chose a changé. Lorsque vous voyez une croissance soudaine, les mesures suivantes sont utiles pour la résolution des problèmes :

• Taux de restauration du journal (Ko/s) (tableau de bord Always On)
• redo_rate dans sys.dm_hadr_database_replica_states

Établir des taux de récupération de référence

Lorsque les performances d’Always On sont normales, surveillez le taux de réapplication sur les bases de données très sollicitées de votre groupe de disponibilité. Les taux de capture pendant les heures de bureau classiques et pendant les fenêtres de maintenance lorsque les transactions volumineuses (comme les reconstructions d’index ou les processus ETL) entraînent un débit plus élevé. Comparez ces bases de référence lorsque vous voyez la croissance de la file d’attente de récupération pour identifier ce qui a changé. La charge de travail peut être plus grande que d’habitude, ou le taux de restauration peut être inférieur à prévu, ce qui nécessite un examen plus approfondi.

Prendre en compte le volume de la charge de travail

Les charges de travail importantes (comme une instruction UPDATE sur un million de lignes, une reconstruction d’index sur une table de 1 To ou un lot ETL qui insère des millions de lignes) entraînent généralement une augmentation de la file d’attente de récupération, soit immédiatement, soit au fil du temps. Cette croissance est attendue lorsque de nombreuses modifications sont apportées soudainement dans la base de données du groupe de disponibilité.

Diagnostiquer la mise en file d’attente de récupération

Après avoir identifié la mise en attente de récupération pour une base de données spécifique du groupe de disponibilité de réplica secondaire, connectez-vous au réplica secondaire, puis interrogez sys.dm_exec_requests pour vérifier les wait_type et wait_time des threads de récupération. Vous recherchez une fréquence élevée d’un ou plusieurs types d’attente et des temps d’attente importants pour ces types d’attente. L’exemple de requête suivant s’exécute toutes les cinq secondes et signale les types d’attente et les temps d’attente pour la base de données du groupe de agdbdisponibilité :

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

Dans l’exemple suivant, certains types d’attente liés aux E/S sont signalés (PAGEIOLATCH_UP, PAGEIOLATCH_EX). Vérifiez si ces types d’attente continuent d’afficher les valeurs les plus importantes wait_time , comme indiqué dans la colonne suivante.

Identifier les types d’attente de récupération

Après avoir identifié un type d’attente, utilisez Modèle de réexécution et performances de la réplica secondaire du groupe de disponibilité comme référence pour les types d’attente courants qui provoquent la mise en file d’attente de la récupération, ainsi que pour obtenir des conseils sur la façon de résoudre le problème.

Processus de récupération bloqués sur les réplicas secondaires en lecture seule

Si votre solution dirige les rapports (requêtes) vers les bases de données d’un groupe de disponibilité sur une réplique secondaire, ces requêtes en lecture seule acquièrent des verrous de stabilité de schéma (Sch-S). Les verrous Sch-S peuvent empêcher les threads de restauration d’acquérir les verrous de modification de schéma (Sch-M) (également appelés verrous de modification de schéma, ou LCK_M_SCH_M) nécessaires pour appliquer des modifications du langage de définition de données (DDL) comme ALTER TABLE ou ALTER INDEX. Un thread de restauration automatique bloqué ne peut pas appliquer d’enregistrements de journal tant qu’il n’est pas débloqué, ce qui provoque la mise en file d’attente de récupération.

Pour vérifier s’il existe des traces historiques d’un redo bloqué, ouvrez les fichiers de trace d’événements étendus AlwaysOn_health sur le réplica secondaire à l’aide de SSMS. Recherchez des lock_redo_blocked événements.

Utilisez l’Analyseur de performances pour surveiller activement l’impact de la réexécution bloquée sur la file d’attente de récupération. Ajoutez les compteurs SQL Server:Database Replica\Rétablissement bloqué/s et SQL Server:Database Replica\Recovery Queue. La capture d’écran suivante montre l’exécution d’une commande ALTER TABLE ALTER COLUMN sur le réplica principal, tandis qu’une requête de longue durée s’exécute sur la même table dans le réplica secondaire. Le compteur Redo blocked/sec grimpe brusquement lorsque la commande ALTER TABLE ALTER COLUMN s’exécute. Tant que la requête de longue durée est active sur la même table du réplica secondaire, toute modification ultérieure sur le réplica principal augmente la file d’attente de récupération.

Surveillez le type d’attente du verrou de modification du schéma que le thread de restauration tente d’acquérir. Utilisez la requête précédente pour vérifier les types d’attente signalés pour les opérations de rétablissement dans sys.dm_exec_requests. Vous pouvez voir le temps d’attente pour LCK_M_SCH_M augmenter pendant que le rétablissement est bloqué.

Récupération à thread unique

SQL Server 2016 a introduit la récupération en parallèle pour les bases de données de réplicas secondaires. Si vous exécutez une version antérieure, comme SQL Server 2014 ou SQL Server 2012, effectuez une mise à niveau vers une version prise en charge pour obtenir un rétablissement parallèle et améliorer les performances de rétablissement.

Un rétablissement à thread unique peut toujours se produire dans SQL Server 2016 à SQL Server 2019, qui utilisent l’architecture de récupération parallèle. Dans ces versions, une instance de SQL Server peut utiliser jusqu’à 100 threads pour un rétablissement parallèle. Le système alloue des threads de restauration parallèles entre les bases de données du groupe de disponibilité en fonction du nombre de processeurs et de bases de données, dans la limite totale de 100 threads. Lorsque la limite de 100 threads est atteinte, le système affecte à certaines bases de données du groupe de disponibilité un seul thread de réexécution.

Pour vérifier si votre base de données d’un groupe de disponibilité utilise la récupération parallèle, connectez-vous au réplica secondaire et exécutez la requête suivante pour compter le nombre de lignes (fils d’exécution) qui appliquent la récupération à la base de données. Dans l’exemple suivant, si la agdb base de données a une seule ligne et sa commande est DB STARTUP, la charge de travail de récupération peut tirer parti de la récupération parallèle.

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')

Si votre base de données utilise un rétablissement à thread unique, passez en revue l’algorithme précédent pour vérifier si SQL Server dépasse les 100 threads de travail dédiés à la récupération parallèle. Atteindre cette limite peut expliquer pourquoi agdb n’utilise qu’un seul thread de redo.

SQL Server 2022 et versions ultérieures utilisent un algorithme de récupération parallèle qui affecte des threads de travail en fonction de la charge de travail, ce qui supprime le risque qu’une base de données occupée reste sur un rétablissement à thread unique. Pour plus d’informations, consultez la section Utilisation des threads par groupes de disponibilité « Prérequis, restrictions et recommandations pour les groupes de disponibilité Always On ».

Contenu connexe

• Surveiller les groupes de disponibilité (Transact-SQL)
• Résoudre les problèmes de configuration des groupes de disponibilité Always On (SQL Server)
• Groupes de disponibilité Always On : interopérabilité (SQL Server)