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 DatabaseAzure SQL Managed Instance

Cet article aide les administrateurs à diagnostiquer les problèmes de 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 Azure SQL Database.

Examiner le magasin de versions persistantes (PVS)

Tirez parti de la DMV sys.dm_tran_persistent_version_store_stats pour déterminer si la taille du pvS de récupération de base de données accélérée (ADR) augmente plus grande 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 contient le nombre de pages ignorées pour la récupération en raison des transactions abandonné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 longues dans n’importe quelle base de données où 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 depuis longtemps en interrogeant sys.dm_tran_database_transactions en fonction de 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 de requêtes longues, consultez :

  1. Le nettoyage des versions persistantes peut être retardé en raison de longues analyses d’instantanés actives. Les instructions utilisant des niveaux d’isolation d’instantané en lecture validée (RCSI) ou d’isolement 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 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 la transaction peut être promue vers une transaction inter-bases de données. Les analyses d’instantanés peuvent donc empêcher le nettoyage des enregistrements dans le PVS ADR, ou quand ADR n’est pas présent, dans le tempdb magasin de versions. Par conséquent, en raison de ce suivi de version, les transactions longues à l’aide de SNAPSHOT ou RCSI peuvent entraîner le retard du nettoyage de la base de données dans la base de données dans l’instance, ce qui entraîne l’augmentation de la taille des PVS ADR.

    Dans la requête de résolution des problèmes d’origine en haut de cet article, la pvs_off_row_page_skipped_min_useful_xts valeur indique le nombre de pages ignorées pour la récupération en raison d’une longue analyse d’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 les retards de nettoyage PVS :

    1. Envisagez de tuer la longue session de transaction active qui retarde le nettoyage PVS, si possible. Les transactions de longue durée dans n’importe quelle base de données où ADR est activé peuvent retarder le nettoyage DE PVS ADR.
    2. Paramétrez les requêtes de longue durée pour réduire la durée des requêtes 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 d’Azure SQL Database.
    3. Passez en revue l’application pour déterminer la nature de l’analyse d’instantané active problématique. Considérez un niveau d’isolation différent, tel que READ COMMITTED, au lieu d’SNAPSHOT ou READ COMMITTED SNAPSHOT pour les requêtes de longue durée qui retardent le nettoyage DE PVS 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 d’Azure SQL Database, mais pas dans les bases de données Azure SQL singleton. Dans les pools élastiques Azure SQL Database, envisagez de déplacer des bases de données hors du pool élastique qui ont des requêtes longues à l’aide des 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 local et Azure SQL DB prennent en charge les fichiers secondaires lisibles. 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 secondaires, recherchez la session qui émet la longue requête et envisagez de tuer la session, si elle est autorisée. 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. Vérifiez la dernière fois aborted_version_cleaner_last_start_timeaborted_version_cleaner_last_end_time et vérifiez si le nettoyage des transactions abandonnée est terminé. oldest_aborted_transaction_id doit passer plus haut après la fin du nettoyage des transactions abandonnées. Si la oldest_aborted_transaction_id valeur est beaucoup inférieure oldest_active_transaction_idà , et current_abort_transaction_count a une plus grande valeur, il existe une ancienne transaction abandonnée empêchant le nettoyage 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

À compter de SQL Server 2022 (16.x), SQL Server enregistre le comportement de nettoyage DE PVS ADR dans le journal des erreurs 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