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) Azure SQL Database Azure SQL Managed Instance

Cet article aide les administrateurs à 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 ultérieures, Azure SQL Managed Instance et la base de données Azure SQL.

Examiner le magasin de versions persistantes (PVS)

Tirez parti de DMV sys.dm_tran_persistent_version_store_stats pour déterminer si la taille du PVS de la récupération de base de données accélérée (ADR) augmente plus que prévu, puis pour déterminer quel facteur empêche le nettoyage du magasin de versions persistantes (PVS).

Inclus dans l’exemple de script suivant est la colonne sys.dm_tran_persistent_version_store_stats.pvs_off_row_page_skipped_oldest_aborted_xdesid, qui a été ajoutée dans SQL Server 2022 (16.x) et qui contient le nombre de pages sautées pour la récupération en raison des transactions avortées les plus anciennes. Nombre de pages à conserver pour les transactions abandonnées si le nettoyeur de versions est lent ou invalidé.

L’exemple de requête affiche toutes les informations relatives aux processus de nettoyage et indique la taille actuelle du PVS, la plus ancienne transaction abandonnée et d’autres détails :

SELECT
 db_name(pvss.database_id) AS DBName,
 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 -- SQL Server 2022 only
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();

  1. Vérifier la taille de pvs_pct_of_database_size, notez les différences par rapport aux lignes de base courantes pendant les autres périodes d’activité de l’application. Un magasin de versions persistantes est considéré comme grand s’il est significativement 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. Utilisez les étapes suivantes comme aide pour dépanner un grand PVS.

  2. Les transactions actives et en cours d’exécution depuis longtemps dans une base de données où l’ADR est activé peuvent empêcher le nettoyage du PVS. Récupérez oldest_active_transaction_id et vérifiez si cette transaction a été active pendant une longue période en interrogeant sys.dm_tran_database_transactions avec l’ID de transaction. Recherchez les transactions à long terme et actives à l’aide d’une requête telle que l’exemple ci-dessous, qui déclare des variables pour définir des seuils pour la durée ou le volume du journal :

    DECLARE @longTxThreshold int = 1800; --number of seconds to use as a duration threshold for long-running transactions
DECLARE @longTransactionLogBytes bigint = 2147483648; --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 transess.session_id <> @@spid AND 
    ( getdate() >= dateadd(second, @longTxThreshold, tr.transaction_begin_time) OR
      dbtr.database_transaction_log_bytes_used >= @longTransactionLogBytes );

    Avec la ou les sessions identifiées, envisagez de tuer la session, si cela est permis. Passez également en revue l’application pour déterminer la nature de la ou des transactions actives problématiques.

    Pour plus d’informations sur la résolution des problèmes liés aux requêtes de longue durée, consultez :

  1. Le nettoyage des versions persistantes peut être retardé en raison de longues analyses d’instantanés actives. Les instructions qui utilisent les niveaux d’isolation de capture instantanée de lecture validée (RCSI) ou SNAPSHOT reçoivent des horodatages au niveau de l’instance. Une analyse d’instantané utilise l’horodatage pour décider de la visibilité des lignes pour la transaction RCSI ou SNAPSHOT dans le PVS où la récupération de base de données accélérée est activée. Chaque instruction qui utilise RCSI a son propre horodateur, tandis que l’isolation SNAPSHOT a un horodateur au niveau de la transaction. Ces horodateurs de transaction au niveau de l’instance sont utilisés même dans les transactions d’une base de données unique, car la transaction peut être promue à une transaction entre bases de données. Les analyses d’instantanés peuvent donc empêcher le nettoyage des enregistrements dans le PVS de l’ADR ou, lorsque l’ADR n’est pas présent, dans le magasin de versions tempdb. Par conséquent, en raison de ce suivi de version, les transactions de longue durée qui utilisent SNAPSHOT ou RCSI peuvent retarder le nettoyage de la base de données dans l’instance, ce qui entraîne une augmentation de la taille du PVS de l’ADR.

    Dans la requête de dépannage originale au début de cet article, la valeur pvs_off_row_page_skipped_min_useful_xts indique le nombre de pages ignorées pour la récupération en raison d’une longue analyse de l’instantané. Si pvs_off_row_page_skipped_min_useful_xts affiche une valeur supérieure à la normale, cela signifie qu’il existe une longue analyse d’instantanés empêchant le nettoyage du PVS.

    Cet exemple de requête peut être utilisé pour décider quelle est la session problématique :

    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 de retarder le nettoyage du PVS :

    1. Pensez à arrêter la longue session de transaction active qui retarde le nettoyage du PVS, si cela est possible. Les transactions de longue durée dans une base de données où l’ADR est activé peuvent retarder le nettoyage PVS de l’ADR.
    2. Ajustez les requêtes longues pour réduire la durée de la requête et les verrous requis. Pour plus d’informations et de conseils, consultez Comprendre et résoudre le blocage dans SQL Server ou Comprendre et résoudre les problèmes de blocage dans la base de données Azure SQL.
    3. 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 READ COMMITTED SNAPSHOT pour les requêtes à longue durée d’exécution qui retardent le nettoyage du PVS de l’ADR. Ce problème se produit plus fréquemment avec le niveau d’isolation SNAPSHOT.
    4. Ce problème peut se produire dans SQL Server, Azure SQL Managed Instance et les pools élastiques de base de données Azure SQL, mais pas dans les bases de données Azure SQL singleton. Dans les pools élastiques de base de données Azure SQL, envisagez de déplacer les bases de données hors du pool élastique qui ont des requêtes à longue durée d’exécution utilisant les niveaux d’isolation READ COMMIT SNAPSHOT ou SNAPSHOT.

  2. Lorsque la taille du PVS augmente en raison de transactions à long terme sur les réplicas principaux ou secondaires, examinez les requêtes longues et résolvez le goulot d’étranglement. Le DMV sys.dm_tran_aborted_transactions affiche toutes les transactions abandonnées. Pour plus d’informations, consultez sys.dm_tran_aborted_transactions (Transact-SQL). La colonne nest_aborted indique que la transaction a été validée, mais qu’il existe des parties abandonnées (points de sauvegarde ou transactions imbriquées) qui peuvent bloquer le processus de nettoyage du PVS.

  3. Si la base de données fait partie d’un groupe de disponibilité, vérifiez la secondary_low_water_mark. C’est la même chose que la low_water_mark_for_ghosts indiquée par sys.dm_hadr_database_replica_states. Interrogez sys.dm_hadr_database_replica_states pour voir si un des réplicas contient cette valeur, car ceci empêchera également le nettoyage du magasin de versions persistantes. Le nettoyage de la version est retardé en raison des requêtes de lecture sur les réplicas secondaires lisibles. SQL Server sur site et la base de données Azure SQL prennent en charge les bases de données secondaires accessibles en lecture. Dans la DMV sys.dm_tran_persistent_version_store_stats, le pvs_off_row_page_skipped_low_water_mark peut également fournir des indications relatives à un délai de réplica secondaire. Pour plus d’informations, consultez sys.dm_tran_persistent_version_store_stats.

    La solution est identique à la mise en attente de l’analyse des instantanés. Accédez aux serveurs secondaires, recherchez la session qui émet la requête longue et envisagez d’arrêter la session, si cela est permis. Notez que la mise en attente secondaire affecte non seulement le nettoyage de la version de l’ADR, mais il peut également empêcher le nettoyage des enregistrements fantômes.

  4. Vérifiez min_transaction_timestamp (ou online_index_min_transaction_timestamp si le magasin de versions persistantes en ligne est en attente) et, sur cette base, vérifiez sys.dm_tran_active_snapshot_database_transactions pour la colonne transaction_sequence_num pour trouver la session qui a l’ancienne transaction d’instantané bloquant le nettoyage du magasin de versions persistantes.

  5. Si aucun des éléments ci-dessus ne s’applique, cela signifie que le nettoyage est bloqué par des transactions abandonnées. Recherchez l’heure la plus récente de aborted_version_cleaner_last_start_time et de aborted_version_cleaner_last_end_time pour voir si le 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 oldest_aborted_transaction_id est bien inférieur à oldest_active_transaction_id et que current_abort_transaction_count a une valeur supérieure, une ancienne transaction interrompue empêche le nettoyage du PVS. Pour traiter :

    • Si possible, arrêtez la charge de travail pour laisser le nettoyeur de version progresser.
    • Optimisez la charge de travail pour réduire l’utilisation du verrouillage au niveau de l’objet.
    • Examinez l’application pour voir s’il existe un gros problème de transactions abandonnées. Le blocage, la clé dupliquée et les autres violations de contrainte peuvent générer un taux d’abandon élevé.
    • Si vous êtes sur SQL Server, désactivez la procédure ADR en cas d’urgence uniquement pour contrôler la taille du PVS et le nombres de transactions abandonnées. Consultez Désactiver la fonctionnalité ADR.

  6. Si la transaction abandonnée a échoué récemment, recherchez dans le journal des erreurs des messages indiquant des problèmes relatifs à VersionCleaner.

  7. Surveillez le journal des erreurs SQL Server pour les entrées « PreallocatePVS ». S’il existe des entrées « PreallocatePVS », cela signifie que vous devrez peut-être augmenter la capacité de l’ADR à préallouer les pages pour les tâches en arrière-plan, car les performances peuvent être améliorées lorsque le thread ADR en arrière-plan préalloue suffisamment de pages et que le pourcentage d’allocations du PVS de premier plan est proche de 0. Vous pouvez utiliser sp_configure 'ADR Preallocation Factor' pour augmenter cette quantité. Pour plus d’informations, consultez Option de configuration du facteur de préallocation ADR du serveur.

Démarrer manuellement le processus de nettoyage du PVS

L’ADR n’est pas recommandée pour les environnements de base de données avec un nombre élevé de transactions de mise à jour/suppressions, telles qu’OLTP à volume élevé, sans période de repos/récupération pour que le processus de nettoyage du 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.

EXEC sys.sp_persistent_version_cleanup [database_name];

Par exemple,

EXEC sys.sp_persistent_version_cleanup [WideWorldImporters];

Capture des échecs de nettoyage

À partir de SQL Server 2022 (16.x), SQL Server enregistre le comportement du nettoyage du PVS de l’ADR dans le journal des erreurs de SQL Server. En règle générale, cela entraîne l’enregistrement d’un nouvel événement de journal toutes les 10 minutes.

Voir aussi

Étapes suivantes