Partager via


Résolution des problèmes de file d’attente de récupération dans un groupe de disponibilité Always On

Cet article fournit des solutions aux problèmes liés à la file d’attente de récupération.

Qu’est-ce que la file d’attente de récupération ?

Les modifications apportées à la réplique principale dans une base de données de groupe de disponibilité sont envoyées à toutes les répliques secondaires définies dans le même groupe de disponibilité. Une fois ces modifications apportées aux réplicas secondaires, elles sont d’abord écrites dans le fichier du 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 mettre à jour les fichiers de base de données.

Si les modifications apportées à un groupe de disponibilité arrivent et se renforcent sur le fichier journal des transactions de base de données plus rapidement qu’elles ne peuvent être récupérées, une file d’attente de récupération est formée. Cette file d’attente est composée des transactions du journal des transactions renforcées qui n’ont pas été récupérées et restaurées dans la base de données.

Symptômes et effet de la récupération (rétablissement) en file d’attente

L’interrogation des réplicas principaux et secondaires retourne des résultats différents

Les charges de travail en lecture seule qui interrogent des réplicas secondaires peuvent interroger des données obsolètes. Si la file d’attente de récupération se produit, les modifications apportées aux données sur la base de données du réplica principal peuvent ne pas être répercutées dans la base de données secondaire lorsque vous interrogez les mêmes données.

Bien que les modifications arrivent à la base de données secondaire et soient écrites dans le fichier journal de la base de données, les modifications ne seront pas interrogées tant qu’elles ne seront pas récupérées et restaurées dans les fichiers de base de données. L’opération de récupération est ce qui rend ces modifications lisibles.

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

Le temps de basculement est plus long ou le RTO est 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 gérer. Le RTO décrit également la rapidité avec laquelle l’organisation peut récupérer l’accès à la base de données après une panne. Si une file d’attente de récupération importante est présente sur un réplica secondaire lorsqu’un basculement se produit, la récupération peut prendre plus de temps. Après la récupération, la base de données passe au rôle principal et représente l’état de la base de données qui existait avant le basculement. Une durée de récupération plus longue peut retarder la rapidité de la production après un basculement.

Différentes fonctionnalités de diagnostic signalent la mise en file d’attente de récupération du groupe de disponibilité

Dans le cas d’une file d’attente de récupération, le tableau de bord Always On dans SQL Server Management Studio (SSMS) peut signaler un groupe de disponibilité non sain.

Guide pratique pour vérifier la récupération (rétablissement) en file d’attente

La file d’attente de récupération est une mesure par base de données qui peut être vérifiée à l’aide du tableau de bord Always On sur le réplica principal ou à l’aide de la vue de gestion dynamique (DMV) sys.dm_hadr_database_replica_states sur le réplica principal ou secondaire. Analyseur de performances compteurs vérifient la file d’attente de récupération et le taux de récupération. Ces compteurs doivent être vérifiés 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é.

Sys.dm_hadr_database_replica_states de requête

Le sys.dm_hadr_database_replica_states DMV signale une ligne pour chaque base de données de groupe de disponibilité. Une colonne dans le rapport est redo_queue_size. Cette valeur est la taille de file d’attente de récupération, exprimée en kilo-octets. Vous pouvez configurer une requête semblable à la requête suivante pour surveiller toutes les 30 secondes toutes les 30 secondes toute tendance dans la file d’attente de récupération. La requête est exécutée sur le réplica principal. Il utilise le is_local=0 prédicat pour signaler les données du réplica secondaire, où redo_queue_size et redo_rate sont pertinentes.

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.

Capture d’écran de la sortie de la requête signalant les données du réplica secondaire où redo_queue_size et redo_rate sont pertinentes.

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. Ouvrez le tableau de bord Always On dans SSMS en cliquant avec le bouton droit sur un groupe de disponibilité dans l’Explorateur d’objets SSMS.

  2. Sélectionnez Afficher le tableau de bord.

    Les bases de données du groupe de disponibilité sont répertoriées en dernier, et certaines données sont signalées sur les bases de données. Bien que la taille de file d’attente de rétablissement (Ko) et le taux de rétablissement (Ko/s) ne soient pas répertoriés par défaut, vous pouvez les ajouter à cette vue, comme illustré dans la capture d’écran de l’étape suivante.

  3. Pour ajouter ces compteurs, cliquez avec le bouton droit sur l’en-tête au-dessus des rapports de base de données, puis sélectionnez-le dans la liste des colonnes disponibles.

  4. Pour ajouter la taille de file d’attente de rétablissement (Ko) et le taux de rétablissement (Ko/s), cliquez avec le bouton droit sur l’en-tête affiché en rouge dans la capture d’écran suivante.

    Capture d’écran montrant l’ajout des compteurs Redo Queue Size (Ko) et Redo Rate (Kb/s).

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

    Capture d’écran montrant les compteurs d’actualisation définis sur toutes les 60 secondes.

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

La taille de file d’attente de récupération est unique pour chaque réplica secondaire et chaque base de données. Par conséquent, 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 Analyseur 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 File d’attente de récupération et Réexécutez les compteurs octets/s .

  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 ce qui augmente la mise en file d’attente de récupération.

    Capture d’écran montrant une augmentation de la file d’attente de récupération.

Interprétation des valeurs de file d’attente de récupération

Cette section explique comment interpréter les valeurs liées à la file d’attente de récupération que vous avez déterminée dans la section précédente.

Quand la récupération met-elle en file d’attente un problème ? Combien de files d’attente de récupération devez-vous tolérer ?

Vous pouvez supposer que si la file d’attente de récupération signale une valeur 0, cela signifie qu’aucune mise en file d’attente de récupération ne se produit au moment de ce rapport. Toutefois, lorsque votre environnement de production est occupé, vous devez vous attendre à observer la file d’attente de récupération fréquemment signaler une valeur autre que zéro même dans un environnement AlwaysOn sain. Pendant la production classique, vous devez vous attendre à observer cette valeur varie entre 0 et une valeur non nulle.

Si vous observez une augmentation de la file d’attente de récupération au fil du temps, une investigation supplémentaire est justifiée. Cette activité supplémentaire indique que quelque chose a changé. Si vous observez une croissance soudaine dans la file d’attente de récupération, les mesures suivantes sont utiles pour la résolution des problèmes :

  • Taux de restauration du journal (Ko/s) (tableau de bord AlwaysOn)
  • Redo_rate dans le sys.dm_hadr_database_replica_states DMV

Obtenir les taux de référence pour le taux de restauration

Pendant les performances AlwaysOn saines, surveillez le taux de restauration sur vos bases de données de groupe de disponibilité occupées. Qu’est-ce qu’ils ressemblent pendant les heures de bureau généralement occupées ? Quels sont ces taux pendant les périodes de maintenance, quand les transactions volumineuses (reconstructions d’index, processus ETL) entraînent un débit de transactions plus élevé sur le système ? Vous pouvez comparer ces valeurs lorsque vous observez la croissance de la file d’attente de récupération pour déterminer ce qui a changé. La charge de travail peut être supérieure à la normale. Si le taux de rétablissement est inférieur, une enquête supplémentaire peut être nécessaire pour déterminer pourquoi.

Volume de charge de travail important

Lorsque vous avez des charges de travail volumineuses (par exemple, une instruction UPDATE sur un million de lignes, une reconstruction d’index sur une table de 1 téraoctets ou même un lot ETL qui insère des millions de lignes), vous devez vous attendre à voir une croissance de file d’attente de récupération, immédiatement ou au fil du temps. Cela est attendu lorsqu’un grand nombre de modifications sont apportées soudainement dans la base de données du groupe de disponibilité.

Guide pratique pour diagnostiquer la récupération (rétablissement) en file d’attente

Après avoir identifié la file d’attente de récupération pour une base de données de groupe de disponibilité de réplica secondaire spécifique, connectez-vous au réplica secondaire, puis interrogez-le sys.dm_exec_requests pour déterminer les threads de récupération et wait_time les wait_type threads de récupération. Voici une requête qui peut s’exécuter dans une boucle. Vous recherchez une fréquence élevée d’un ou plusieurs types d’attente et même des temps d’attente pour ces types d’attente. Voici un exemple de requête qui s’exécute toutes les secondes et signale les types d’attente et les temps d’attente pour le groupe de disponibilité, « agdb » :

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

Important

Pour une sortie de type d’attente significative, la file d’attente de récupération doit être observée pour augmenter lorsque vous utilisez l’une des méthodes décrites précédemment pour surveiller cette condition.

Dans cet exemple, certains types d’attente liés aux E/S sont signalés (PAGEIOLATCH_UP, PAGEIOATCH_EX). Surveillez pour vérifier si ces types d’attente continuent d’avoir les valeurs les plus importantes wait_times , comme indiqué dans la colonne suivante.

Capture d’écran montrant les temps d’attente les plus importants signalés dans la colonne suivante.

Types d’attente de rétablissement SQL Server

Lorsqu’un type d’attente est identifié, consultez l’article suivant SQL Server 2016/2017 : Modèle de rétablissement du réplica secondaire du groupe de disponibilité et performances - Microsoft Tech Community en tant que référence croisée pour les types d’attente courants qui provoquent la mise en file d’attente de récupération et pour vous aider à résoudre le problème.

Threads de rétablissement bloqués sur des serveurs de rapports secondaires

Si votre solution dirige les rapports (interrogation) sur les bases de données de groupe de disponibilité sur le réplica secondaire, ces requêtes en lecture seule obtiennent des verrous de stabilité de schéma (Sch-S). Ces verrous Sch-S peuvent empêcher les threads de rétablir les threads d’acquérir des verrous de modification de schéma (Sch-M) (également appelés « verrous de modification de schéma » ou LCK_M_SCH_M) pour apporter des modifications de langage de définition de données (DDL) telles que ALTER TABLE ou ALTER INDEX. Un thread de rétablissement bloqué ne peut pas appliquer d’enregistrements de journal tant qu’il n’est pas débloqué. Cela peut entraîner la mise en file d’attente de récupération.

Pour rechercher la preuve historique d’un rétablissement bloqué, ouvrez les fichiers de trace AlwaysOn_health Xevent sur le réplica secondaire à l’aide de SSMS. lock_redo_blocked Recherchez des événements.

Capture d’écran montrant la vérification de la preuve historique d’un rétablissement bloqué.

Utilisez Analyseur de performances pour surveiller activement l’impact du rétablissement bloqué sur la file d’attente de récupération. Ajoutez le réplica SQL Server ::D atabase ::Redo bloqué/s et SQL Server ::D atabase Replica ::Recovery Queue counters. La capture d’écran suivante montre une ALTER TABLE ALTER COLUMN commande exécutée sur le réplica principal pendant qu’une requête longue est exécutée sur la même table sur le réplica secondaire. Le compteur Redo bloqué/s indique que la ALTER TABLE ALTER COLUMN commande est exécutée. Bien que la requête de longue durée s’exécute sur la même table sur le réplica secondaire, toutes les modifications suivantes sur le réplica principal entraînent une augmentation de la file d’attente de récupération.

Capture d’écran montrant un moniteur pour le type d’attente du verrou de modification de schéma.

Surveillez le type d’attente du verrou de modification de schéma que le thread de rétablissement tente d’acquérir. Pour ce faire, utilisez la requête décrite précédemment pour vérifier les types d’attente signalés pour les opérations sys.dm_exec_requestsde restauration par progression. Vous pouvez observer le temps d’attente croissant pour le LCK_M_SCH_M blocage de restauration continue.

Capture d’écran montrant le temps d’attente croissant de la LCK_M_SCH_M.

Restauration à thread unique

SQL Server a introduit une récupération parallèle pour les bases de données de réplica secondaire dans Microsoft SQL Server 2016. Si vous rencontrez une file d’attente de récupération lorsque vous exécutez SQL Microsoft Server 2012 ou Microsoft SQL Server 2014, vous pouvez effectuer une mise à niveau vers une version ultérieure du programme pour améliorer les performances de restauration dans votre environnement de production.

Un rétablissement à thread unique peut se produire dans des versions de SQL Server encore plus avancées dans lesquelles l’architecture de récupération parallèle est utilisée. Dans ces versions, une instance SQL Server peut utiliser jusqu’à 100 threads pour un rétablissement parallèle. En fonction du nombre de processeurs et de bases de données de groupe de disponibilité, les threads de restauration par rétablissement parallèles sont alloués jusqu’à un maximum de 100 threads totaux. Si la limite de restauration à 100 threads est atteinte, certaines bases de données du groupe de disponibilité reçoivent un seul thread de restauration automatique.

Pour déterminer si votre base de données de groupe de disponibilité utilise une récupération parallèle, connectez-vous au réplica secondaire et utilisez la requête suivante pour déterminer le nombre de lignes (threads) qui appliquent la récupération pour la base de données du groupe de disponibilité. Dans l’exemple suivant, si la base de données « agdb » est un thread unique et que 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')

Capture d’écran montrant comment déterminer si votre base de données de groupe de disponibilité utilise la récupération parallèle.

Si vous vérifiez que votre base de données utilise un rétablissement à thread unique, passez en revue l’algorithme décrit précédemment pour déterminer si SQL Server dépasse le nombre de 100 threads de travail dédiés à la récupération parallèle. Une telle condition peut être la raison pour laquelle la base de données « agdb » utilise uniquement un thread unique pour la récupération.

SQL Server 2022 utilise désormais un nouvel algorithme de récupération parallèle afin que les threads de travail soient affectés pour la récupération parallèle en fonction de la charge de travail. Cela élimine le risque qu’une base de données occupée reste dans une récupération à thread unique. Pour plus d’informations, consultez la section Utilisation des threads par groupes de disponibilité « Conditions préalables, restrictions et recommandations pour les groupes de disponibilité Always On ».