Partager via

Résoudre les problèmes de la 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 à diagnostiquer les problèmes liés à la récupération de base de données accélérée (ADR) dans SQL Server 2019 (15.x) et versions suivantes, Azure SQL Managed Instance, Azure SQL Database et SQL Database dans Microsoft Fabric.

Examinez la taille du PVS

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

L’exemple de requête suivant montre les informations sur la taille actuelle de PVS, les processus de nettoyage et d’autres détails :

SELECT  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_pct_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,
        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.database_files
            WHERE state = 0
                  AND
                  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.database_id = DB_ID();

Vérifiez la colonne pvs_pct_of_database_size pour afficher la taille du PVS par rapport à la taille totale de la base de données. Notez toute différence par rapport à la taille pvS classique par rapport aux lignes de base observées pendant d’autres périodes d’activité d’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. Suivez les étapes de résolution des problèmes suivantes pour trouver la raison d’une grande taille de PVS.

Si la taille du PVS est supérieure à celle prévue, recherchez :

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 plus d’informations sur les transactions de longue durée, 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 = 1800;  /* 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 a log amount threshold for long-running transactions */

SELECT  dbtr.database_id,
        transess.session_id,
        transess.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 transess
ON tr.transaction_id = transess.transaction_id
INNER JOIN sys.dm_exec_sessions AS sess
ON transess.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 = transess.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 actives problématiques afin d’éviter le problème à l’avenir.

Pour plus d’informations sur la résolution des problèmes liés aux requêtes de longue durée, 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 SNAPSHOT isolation 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 inviter 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.

Dans la requête de résolution des problèmes au début de cet article, la colonne pvs_off_row_page_skipped_min_useful_xts affiche le nombre de pages ignorées pour la récupération en raison d'une longue analyse de capture instantanée. Si cette colonne affiche une valeur supérieure à la normale, cela signifie qu’un long scan d'instantané empêche le processus de nettoyage PVS.

Utilisez l’exemple de requête suivant pour rechercher la session comportant une transaction SNAPSHOT ou RCSI de longue durée.

SELECT snap.transaction_id,
        snap.transaction_sequence_num,
        session.session_id,
        session.login_time,
        GETUTCDATE() AS [now],
        session.host_name,
        session.program_name,
        session.login_name,
        session.last_request_start_time
FROM sys.dm_tran_active_snapshot_database_transactions AS snap
INNER JOIN sys.dm_exec_sessions AS session
ON snap.session_id = session.session_id
ORDER BY snap.transaction_sequence_num ASC;

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 des bases de données qui ont des transactions longues à l’aide d’une isolation SNAPSHOT ou RCSI hors du pool élastique.

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.

Exécutez les DMV suivants sur le réplica principal pour identifier les requêtes longues sur le réplica secondaire qui peuvent empêcher le nettoyage PVS :

Dans la vue DMV sys.dm_tran_persistent_version_store_stats, les colonnes pvs_off_row_page_skipped_low_water_mark peuvent également donner une indication d’un délai de nettoyage en raison d’une requête longue sur un réplica secondaire.

Connectez-vous à un réplica secondaire, recherchez la session qui exécute la longue requête et envisagez de terminer la session, si autorisé. La requête longue sur le réplica secondaire peut retarder le nettoyage PVS et empêcher le nettoyage fantôme .

Vérifier un grand nombre de transactions abandonnées

Si aucun des scénarios précédents ne s’applique à vos charges de travail, il est probable que le nettoyage soit retardé en raison d’un grand nombre de transactions abandonnées. Vérifiez les colonnes aborted_version_cleaner_last_start_time et aborted_version_cleaner_last_end_time pour voir si le dernier nettoyage des transactions abandonnées est terminé. 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 supérieure, il est probable qu'une ancienne transaction abandonnée empêche le nettoyage PVS.

Pour traiter un grand nombre de transactions abandonnées, tenez compte des éléments suivants :

  • Si possible, interrompez la charge de travail pour permettre au version cleaner de progresser.
  • Optimisez la charge de travail pour réduire les verrous au niveau de l’objet.
  • Passez en revue l’application pour identifier le problème élevé de taux 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.
  • Si vous utilisez SQL Server, désactivez ADR en tant qu’étape d’urgence uniquement pour contrôler la taille de PVS. Voir Désactiver ADR.
  • Si le nettoyage des transactions abandonnée n’a pas été terminé récemment, vérifiez le journal des erreurs pour les messages signalant VersionCleaner problèmes.
  • 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.

Démarrer manuellement le processus de nettoyage du PVS

Si vous avez une charge de travail avec un volume élevé d’instructions DML (INSERT, UPDATE, DELETE, MERGE), comme un OLTP de fort volume, cela peut nécessiter une période de repos ou de récupération pour que le processus de nettoyage de PVS récupère 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.

Par exemple:

EXEC sys.sp_persistent_version_cleanup [WideWorldImporters];

Capture des échecs de nettoyage

À compter de SQL Server 2022 (16.x), le comportement de nettoyage PVS est enregistré dans le journal des erreurs. En règle générale, un nouvel événement de journal est enregistré toutes les 10 minutes. Les statistiques de nettoyage sont également signalées par tx_mtvc2_sweep_statsl’événement étendu .

Contenu connexe