Partage via

Surveiller et résoudre les problèmes de récupération de base de données accélérée

  • Article

S’applique à : SQL Server 2019 (15.x) et versions ultérieures d’Azure SQL Database Azure SQL Managed InstanceSQL database dans Microsoft Fabric

Cet article vous aide à surveiller, diagnostiquer et résoudre les problèmes liés à récupération de base de données accélérée (ADR) dans SQL Server 2019 (15.x) et versions ultérieures, Azure SQL Managed Instance, Azure SQL Database et SQL Database dans Microsoft Fabric.

Examinez la taille du PVS

Utilisez la vue de gestion dynamique sys.dm_tran_persistent_version_store_stats pour identifier si la taille du magasin de versions persistantes (PVS) est supérieure à ce qui est prévu.

L’exemple de requête de diagnostic suivant montre les informations relatives à la taille actuelle de PVS, aux processus de nettoyage et à d’autres détails dans toutes les bases de données où la taille pvS est supérieure à zéro :

SELECT pvss.database_id,
       DB_NAME(pvss.database_id) AS database_name,
       pvss.persistent_version_store_size_kb / 1024. / 1024 AS persistent_version_store_size_gb,
       100 * pvss.persistent_version_store_size_kb / df.total_db_size_kb AS pvs_percent_of_database_size,
       df.total_db_size_kb / 1024. / 1024 AS total_db_size_gb,
       pvss.online_index_version_store_size_kb / 1024. / 1024 AS online_index_version_store_size_gb,
       pvss.current_aborted_transaction_count,
       pvss.aborted_version_cleaner_start_time,
       pvss.aborted_version_cleaner_end_time,
       pvss.oldest_aborted_transaction_id,
       pvss.oldest_active_transaction_id,
       dt.database_transaction_begin_time AS oldest_transaction_begin_time,
       asdt.session_id AS active_transaction_session_id,
       asdt.elapsed_time_seconds AS active_transaction_elapsed_time_seconds,
       pvss.pvs_off_row_page_skipped_low_water_mark,
       pvss.pvs_off_row_page_skipped_min_useful_xts,
       pvss.pvs_off_row_page_skipped_oldest_aborted_xdesid
FROM sys.dm_tran_persistent_version_store_stats AS pvss
CROSS APPLY (
            SELECT SUM(size * 8.) AS total_db_size_kb
            FROM sys.master_files AS mf
            WHERE mf.database_id = pvss.database_id
                  AND
                  mf.state = 0
                  AND
                  mf.type = 0
            ) AS df
LEFT JOIN sys.dm_tran_database_transactions AS dt
ON pvss.oldest_active_transaction_id = dt.transaction_id
   AND
   pvss.database_id = dt.database_id
LEFT JOIN sys.dm_tran_active_snapshot_database_transactions AS asdt
ON pvss.min_transaction_timestamp = asdt.transaction_sequence_num
   OR
   pvss.online_index_min_transaction_timestamp = asdt.transaction_sequence_num
WHERE pvss.persistent_version_store_size_kb > 0
ORDER BY persistent_version_store_size_kb DESC;

Vérifiez la colonne pvs_percent_of_database_size pour voir la taille du PVS par rapport à la taille totale de la base de données. Notez toute différence entre la taille pvS classique et les bases de référence observées pendant les périodes classiques d’activité de l’application. PVS est considéré comme volumineux s’il est beaucoup plus grand que la base de référence ou s’il est proche de 50 % de la taille de la base de données.

Si la taille de PVS n’est pas décroissante, suivez les étapes de résolution des problèmes suivantes pour rechercher et résoudre la raison d’une grande taille de PVS.

Conseil / Astuce

Les colonnes mentionnées dans les étapes de résolution des problèmes suivantes font référence aux colonnes du jeu de résultats de la requête de diagnostic dans cette section.

Une grande taille de PVS peut être due à l’une des raisons suivantes :

Rechercher les transactions actives de longue durée

Les transactions actives de longue durée peuvent empêcher le processus de nettoyage PVS dans des bases de données avec ADR activé. Vérifiez l’heure de début de la transaction active la plus ancienne à l’aide de la colonne oldest_transaction_begin_time. Pour rechercher des transactions longues, utilisez l’exemple de requête suivant. Vous pouvez définir des seuils pour la durée de la transaction et la quantité de journal des transactions générée :

DECLARE @LongTxThreshold int = 900;  /* The number of seconds to use as a duration threshold for long-running transactions */
DECLARE @LongTransactionLogBytes bigint = 1073741824; /* The number of bytes to use as the generated log threshold for long-running transactions */

SELECT  dbtr.database_id,
        DB_NAME(dbtr.database_id) AS database_name,
        st.session_id,
        st.transaction_id,
        atr.name,
        sess.login_time,
        dbtr.database_transaction_log_bytes_used,
        CASE WHEN GETDATE() >= DATEADD(second, @LongTxThreshold, tr.transaction_begin_time) THEN 'DurationThresholdExceeded'
             WHEN dbtr.database_transaction_log_bytes_used >= @LongTransactionLogBytes THEN 'LogThresholdExceeded'
             ELSE 'Unknown'
        END
        AS reason
FROM sys.dm_tran_active_transactions AS tr
INNER JOIN sys.dm_tran_session_transactions AS st
ON tr.transaction_id = st.transaction_id
INNER JOIN sys.dm_exec_sessions AS sess
ON st.session_id = sess.session_id
INNER JOIN sys.dm_tran_database_transactions AS dbtr
ON tr.transaction_id = dbtr.transaction_id
INNER JOIN sys.dm_tran_active_transactions AS atr
ON atr.transaction_id = st.transaction_id
WHERE GETDATE() >= DATEADD(second, @LongTxThreshold, tr.transaction_begin_time)
      OR
      dbtr.database_transaction_log_bytes_used >= @LongTransactionLogBytes;

Avec les sessions identifiées, envisagez de terminer la session, si cela est autorisé. Passez en revue l’application pour déterminer la nature des transactions problématiques afin d’éviter le problème à l’avenir.

Pour plus d’informations sur la résolution des problèmes de requêtes longues, consultez :

Rechercher les analyses actives de longue durée d'instantanés

Les analyses d'instantanés actives de longue durée peuvent empêcher le nettoyage PVS dans les bases de données qui ont ADR activé. Instructions utilisant l'isolement instantané (READ COMMITTED RCSI) ou les SNAPSHOTniveaux d'isolement reçoivent des horodatages au niveau de l'instance. Une analyse par instantané utilise l’horodatage pour déterminer la visibilité des lignes de version pour la transaction RCSI ou SNAPSHOT. Chaque instruction utilisant RCSI a son propre horodatage, tandis que l’isolation SNAPSHOT a un horodatage au niveau de la transaction.

Ces horodatages de transaction au niveau de l’instance sont utilisés même dans les transactions à base de données unique, car toute transaction peut être promue vers une transaction inter-bases de données. Les analyses d’instantanés peuvent donc empêcher le nettoyage PVS dans n’importe quelle base de données sur la même instance du moteur de base de données. De même, lorsque la fonction ADR n’est pas activée, les analyses d’instantanés peuvent empêcher le nettoyage du magasin de versions dans tempdb. Par conséquent, PVS peut augmenter en taille lorsque des transactions de longue durée qui utilisent SNAPSHOT ou RCSI sont présentes.

La colonne pvs_off_row_page_skipped_min_useful_xts affiche le nombre de pages ignorées pendant le nettoyage en raison d’une longue analyse d’instantané. Si cette colonne affiche une valeur supérieure, cela signifie qu’une longue analyse d'instantané empêche le processus de nettoyage PVS.

Utilisez l'exemple de requête suivant pour rechercher les sessions avec une transaction longue durée de type SNAPSHOT ou RCSI :

SELECT sdt.transaction_id,
       sdt.transaction_sequence_num,
       s.database_id,
       s.session_id,
       s.login_time,
       GETDATE() AS query_time,
       s.host_name,
       s.program_name,
       s.login_name,
       s.last_request_start_time
FROM sys.dm_tran_active_snapshot_database_transactions AS sdt
INNER JOIN sys.dm_exec_sessions AS s
ON sdt.session_id = s.session_id;

Pour éviter les retards de nettoyage PVS :

  • Pensez à arrêter la longue session de transaction active qui retarde le nettoyage du PVS, si cela est possible.
  • Paramétrez les requêtes de longue durée pour réduire leur durée.
  • Passez en revue l’application pour déterminer la nature de l’analyse d’instantané active problématique. Envisagez un niveau d’isolation différent, tel que READ COMMITTED, au lieu de SNAPSHOT ou RCSI pour les requêtes de longue durée qui retardent le nettoyage PVS. Ce problème se produit plus fréquemment avec le niveau d’isolation SNAPSHOT.
  • Dans les pools élastiques Azure SQL Database, envisagez de déplacer les bases de données ayant des transactions longues utilisant un isolement SNAPSHOT ou RCSI en dehors du pool élastique pour éviter les retards liés au nettoyage PVS dans d'autres bases de données du même pool.

Rechercher des requêtes de longue durée sur des réplicas secondaires

Si la base de données possède des réplicas secondaires, vérifiez si le seuil minimal secondaire progresse.

Une valeur importante dans la colonne pvs_off_row_page_skipped_low_water_mark peut être une indication d’un délai de nettoyage en raison d’une requête longue sur un réplica secondaire. En plus de maintenir le nettoyage du PVS, une requête longue sur un réplica secondaire peut également contenir le nettoyage des éléments fantômes.

Vous pouvez utiliser les exemples de requêtes suivants sur le réplica principal pour déterminer si des requêtes longues sur des réplicas secondaires peuvent empêcher le nettoyage du PVS. Si une tâche d'écriture s'exécute sur la réplique principale, mais que la valeur de la colonne low_water_mark_for_ghosts n'augmente pas d'une exécution de l'exemple de requête à la suivante, alors PVS et le nettoyage fantôme pourraient être retardés par une requête de longue durée sur une réplique secondaire.

SELECT database_id,
       DB_NAME(database_id) AS database_name,
       low_water_mark_for_ghosts,
       synchronization_state_desc,
       synchronization_health_desc,
       is_suspended,
       suspend_reason_desc,
       secondary_lag_seconds
FROM sys.dm_hadr_database_replica_states
WHERE is_local = 1
      AND
      is_primary_replica = 1;

Pour plus d’informations, consultez la description de la colonne low_water_mark_for_ghosts dans sys.dm_hadr_database_replica_states.

Connectez-vous à chaque réplica secondaire lisible, recherchez la session avec la requête de longue durée et envisagez de tuer la session, si possible. Pour plus d’informations, consultez Rechercher des requêtes lentes.

Vérifier un grand nombre de transactions abandonnées

Vérifiez les colonnes aborted_version_cleaner_start_time et aborted_version_cleaner_end_time pour voir si le dernier nettoyage des transactions abandonnées est achevé. oldest_aborted_transaction_id doit passer plus haut après la fin du nettoyage des transactions abandonnées. Si le oldest_aborted_transaction_id est beaucoup plus faible que oldest_active_transaction_idet que current_abort_transaction_count a une valeur plus grande, il est probable qu’une ancienne transaction abandonnée empêche le nettoyage du PVS.

Pour résoudre le délai de nettoyage PVS en raison d’un grand nombre de transactions abandonnées, tenez compte des éléments suivants :

  • Si vous utilisez SQL Server 2022 (16.x), augmentez la valeur de la configuration du serveur ADR Cleaner Thread Count. Pour plus d’informations, consultez Configuration du serveur : nombre de threads de nettoyage ADR.
  • Si possible, interrompez la charge de travail pour permettre au version cleaner de progresser.
  • Passez en revue l’application pour identifier et résoudre le problème élevé d’abandon des transactions. Les abandons peuvent provenir d’un taux élevé de blocages, de clés en double, de violations de contraintes ou de délais d’expiration de requête.
  • Optimisez la charge de travail pour réduire les verrous incompatibles avec les verrous au niveau de l’objet ou au niveau de la partition IX requis par le nettoyeur PVS. Pour plus d’informations, consultez la compatibilité des verrous .
  • Si vous utilisez SQL Server, désactivez l’ADR en tant qu’étape d’urgence uniquement pour contrôler la taille du PVS. Voir Désactiver ADR.
  • Si vous utilisez SQL Server et si le nettoyage des transactions abandonnées n’a pas été terminé récemment, vérifiez le journal des erreurs pour les messages signalant des problèmes VersionCleaner.
  • Si la taille de PVS n’est pas réduite comme prévu même après qu’un nettoyage soit terminé, vérifiez la colonne pvs_off_row_page_skipped_oldest_aborted_xdesid. Les valeurs volumineuses indiquent que l’espace est toujours utilisé par les versions de lignes à partir de transactions abandonnées.

Contrôler la taille du PVS

Si vous avez une charge de travail avec un volume élevé d’instructions DML (INSERT, UPDATE, DELETE, MERGE), par exemple, OLTP à volume élevé, et observez que la taille pvS est importante, vous devrez peut-être augmenter la valeur de la configuration du serveur ADR Cleaner Thread Count pour maintenir la taille pvS sous contrôle. Pour plus d’informations, consultez Configuration du serveur : nombre de threads de nettoyage ADR, disponible à partir de SQL Server 2022 (16.x).

Dans SQL Server 2019 (15.x) ou si l’augmentation de la valeur de ADR Cleaner Thread Count configuration n’aide pas à réduire suffisamment la taille de PVS, la charge de travail peut nécessiter une période de repos/récupération pour le processus de nettoyage PVS pour récupérer de l’espace.

Pour activer le processus de nettoyage du PVS manuellement entre les charges de travail ou pendant les fenêtres de maintenance, utilisez la procédure stockée système sys.sp_persistent_version_cleanup.

Exemple :

EXEC sys.sp_persistent_version_cleanup [WideWorldImporters];

Capture des échecs de nettoyage

À compter de SQL Server 2022 (16.x), les messages de nettoyage PVS notables sont enregistrés dans le journal des erreurs. Les statistiques de nettoyage sont également signalées par tx_mtvc2_sweep_statsl’événement étendu .

Contenu connexe