Résoudre les problèmes de propre up automatique de suivi des modifications

  • Article

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Cet article fournit des façons de résoudre les problèmes courants observés dans le suivi des modifications propre up.

Symptômes

En règle générale, si le propre up automatique ne fonctionne pas comme prévu, vous pouvez voir un ou plusieurs des symptômes suivants :

  • Consommation élevée de stockage par une ou plusieurs tables latérales de suivi des modifications ou la syscommittab table système.
  • Les tables latérales (tables internes dont le nom commence par le préfixe change_tracking, par exemple change_tracking_12345) ou syscommittab les deux, affichent un nombre significatif de lignes qui ne sont pas au-delà de la période de rétention configurée.
  • dbo.MSChange_tracking_historyla table contient des entrées avec des erreurs de propre up spécifiques.
  • CHANGETABLE les performances ont détérioré au fil du temps.
  • La propre up automatique ou manuelle propre up signale une utilisation élevée du processeur.

Débogage et atténuation

Pour identifier la cause racine d’un problème lié au suivi des modifications propre up, procédez comme suit pour déboguer et atténuer le problème.

État de propre up automatique

Vérifiez si la propre up automatique est en cours d’exécution. Pour case activée cela, interrogez la table d’historique des propre up dans la même base de données. Si le propre up est en cours d’exécution, la table comporte des entrées avec les heures de début et de fin de la propre up. Si le propre up n’a pas été en cours d’exécution, la table est vide ou a des entrées obsolètes. Si la table d’historique contient des entrées avec la balise cleanup errors dans la colonnecomments, la propre up échoue en raison d’erreurs de niveau table propre up.

SELECT TOP 1000 * FROM dbo.MSChange_tracking_history ORDER BY start_time DESC;

La propre up automatique s’exécute régulièrement avec un intervalle par défaut de 30 minutes. Si la table d’historique n’existe pas, la plupart du temps, la propre up automatique n’a jamais été exécutée. Sinon, case activée les valeurs et end_time les valeurs de start_time colonne. Si les dernières entrées ne sont pas récentes, c’est-à-dire qu’elles ont des heures ou des jours, la propre up automatique peut ne pas être en cours d’exécution. Si c’est le cas, procédez comme suit pour résoudre les problèmes.

1. Le nettoyage est désactivé

Vérifiez si le nettoyage automatique est activé pour la base de données. Si ce n’est pas le cas, activez-le et attendez au moins 30 minutes avant de consulter la table d’historique pour de nouvelles entrées. Surveillez la progression dans la table d’historique par la suite.

SELECT * FROM sys.change_tracking_databases WHERE database_id=DB_ID('<database_name>')

Une valeur is_auto_cleanup_on différente de zéro indique que la propre up automatique est activée. La valeur de la période de rétention contrôle la durée pendant laquelle les métadonnées de suivi des modifications sont conservées dans le système. La valeur par défaut de la période de rétention du suivi des modifications est de 2 jours.

Pour activer ou désactiver le suivi des modifications, consultez Activer et désactiver le suivi des modifications (SQL Server).

2. Le nettoyage est activé, mais n’est pas en cours d’exécution

Si la propre up automatique est activée, le thread de propre up automatique est probablement arrêté en raison d’erreurs inattendues. Actuellement, le redémarrage du thread de propre up automatique n’est pas possible. Vous devez lancer un basculement vers un serveur secondaire (ou redémarrer le serveur en l’absence d’un serveur secondaire) et vérifier que le paramètre de propre up automatique est activé pour la base de données.

Exécutions automatiques de propre up, mais ne progressent pas

Si une ou plusieurs tables latérales affichent une consommation de stockage importante ou contiennent un grand nombre d’enregistrements au-delà de la rétention configurée, suivez les étapes décrites dans cette section, qui décrivent des solutions pour une table latérale unique. Les mêmes étapes peuvent être répétées pour plus de tables si nécessaire.

1. Évaluer le backlog de propre up automatique

Identifiez les tables latérales qui ont un backlog important d’enregistrements arrivés à expiration, ce qui nécessite une atténuation pour les exécuter. Exécutez les requêtes suivantes pour identifier les tables latérales avec de grands nombres d’enregistrements expirés. N’oubliez pas de remplacer les valeurs dans les exemples de scripts comme indiqué.

  1. Obtenez la version de propre up non valide :

    SELECT * FROM sys.change_tracking_tables;

    La cleanup_version valeur des lignes retournées représente la version de propre up non valide.

  2. Exécutez la requête Transact-SQL (T-SQL) dynamique suivante, qui génère la requête pour obtenir le nombre de lignes expiré des tables latérales. Remplacez la valeur de <invalid_version> la requête par la valeur obtenue à l’étape précédente.

    SELECT 'SELECT ''' + QUOTENAME(name) + ''', count(*) FROM [sys].' + QUOTENAME(name)
    + ' WHERE sys_change_xdes_id IN (SELECT xdes_id FROM sys.syscommittab ssct WHERE ssct.commit_ts <= <invalid_version>) UNION'
FROM sys.internal_tables
WHERE internal_type = 209;

  3. Copiez le jeu de résultats de la requête précédente et supprimez la UNION mot clé de la dernière ligne. Si vous exécutez la requête T-SQL générée via une connexion d’administration dédiée (DAC), la requête donne le nombre de lignes expiré de toutes les tables latérales. En fonction de la taille de la sys.syscommittab table et du nombre de tables latérales, cette requête peut prendre beaucoup de temps.

    Important

    Cette étape est nécessaire pour avancer avec les étapes d’atténuation. Si la requête précédente ne parvient pas à s’exécuter, identifiez le nombre de lignes expirées pour les tables latérales individuelles à l’aide des requêtes fournies ensuite.

Effectuez les étapes d’atténuation suivantes pour les tables latérales, en ayant l’ordre décroissant des nombres de lignes expirés, jusqu’à ce que les nombres de lignes expirés descendent à un état gérable pour que l’propre up automatique se rattrape.

Une fois que vous avez identifié les tables latérales avec un grand nombre d’enregistrements expirés, collectez des informations sur la latence des instructions de suppression de table latérale et le taux de suppression par seconde au cours des dernières heures. Ensuite, estimez le temps nécessaire pour propre la table latérale en tenant compte à la fois du nombre de lignes obsolètes et de la latence de suppression.

Utilisez l’extrait de code T-SQL suivant en remplaçant les modèles de paramètres par des valeurs appropriées.

  • Interrogez le taux de propre up par seconde :

    SELECT
    table_name,
    rows_cleaned_up / ISNULL(NULLIF(DATEDIFF(second, start_time, end_time), 0), 1),
    cleanup_version
FROM dbo.MSChange_tracking_history
WHERE table_name = '<table_name>'
ORDER BY end_time DESC;

    Vous pouvez également utiliser une granularité de minute ou d’heure pour la DATEDIFF fonction.

  • Trouvez le nombre de lignes obsolètes dans la table secondaire. Cette requête vous aide à trouver le nombre de lignes en attente de nettoyage.

    La <internal_table_name> table utilisateur et <cleanup_version> la sortie retournée dans la section précédente sont les suivantes. À l’aide de ces informations, exécutez le code T-SQL suivant via une connexion d’administrateur dédiée (DAC) :

    SELECT '<internal_table_name>',
    COUNT(*)
FROM sys.<internal_table_name>
WHERE sys_change_xdes_id IN (
        SELECT xdes_id
        FROM sys.syscommittab ssct
        WHERE ssct.commit_ts <= <cleanup_version>
);

    Cette requête peut prendre un certain temps. Dans les cas où la requête expire, calculez les lignes obsolètes en recherchant la différence entre les lignes totales et les lignes actives, à propre.

  • Recherchez le nombre total de lignes dans la table latérale en exécutant la requête suivante :

    SELECT sum(row_count) FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('sys.<internal_table_name>')
GROUP BY partition_id;

  • Recherchez le nombre de lignes actives dans la table latérale en exécutant la requête suivante :

    SELECT '<internal_table_name>', COUNT(*) FROM sys.<internal_table_name> WHERE sys_change_xdes_id
IN (SELECT xdes_id FROM sys.syscommittab ssct WHERE ssct.commit_ts > <cleanup_version>);

    Vous pouvez calculer le temps estimé pour propre la table à l’aide du taux de propre up et du nombre de lignes obsolètes. Tenez compte de la formule suivante :

    Durée de propre en minutes = (nombre de lignes obsolète) / (taux de propre up en minutes)

    Si le temps nécessaire pour terminer la table propre up est acceptable, surveillez la progression et laissez la propre up automatique poursuivre son travail. Si ce n’est pas le cas, passez aux étapes suivantes pour descendre plus loin.

2. Vérifier les conflits de verrou de table

Déterminez si propre up n’est pas en cours en raison de conflits d’escalade de verrous de table, ce qui empêche propre up constamment d’acquérir des verrous sur la table latérale pour supprimer des lignes.

Pour confirmer un conflit de verrous, exécutez le code T-SQL suivant. Cette requête extrait les enregistrements de la table problématique pour déterminer s’il existe plusieurs entrées indiquant des conflits de verrous. Quelques conflits sporadiques se répartissent sur une période ne doivent pas se qualifier pour les étapes d’atténuation en cours. Les conflits doivent être récurrents.

SELECT TOP 1000 *
FROM dbo.MSChange_tracking_history
WHERE table_name = '<user_table_name>'
ORDER BY start_time DESC;

Si la table d’historique comporte plusieurs entrées dans les comments colonnes avec la valeurCleanup error: Lock request time out period exceeded, il est clair que plusieurs tentatives de propre up ont échoué en raison de conflits de verrous ou de délais d’expiration de verrous successifs. Tenez compte des solutions suivantes :

  • Désactiver et réactiver le suivi des modifications sur la table problématique. Cela entraîne la suppression de toutes les métadonnées de suivi maintenues pour la table. Les données de la table restent intactes. Il s’agit du remède le plus rapide.

  • Si l’option précédente n’est pas possible, procédez à l’exécution manuelle de propre up sur la table en activant l’indicateur de trace 8284 comme suit :

    DBCC TRACEON (8284, -1);
GO
EXEC [sys].[sp_flush_CT_internal_table_on_demand] @TableToClean = '<table_name>';

3. Vérifier d’autres causes

Une autre cause possible du retard de propre up est la lenteur des instructions delete. Pour déterminer si c’est le cas, case activée la valeur de hardened_cleanup_version. Cette valeur peut être récupérée via une connexion d’administrateur dédiée (DAC) à la base de données en considération.

Recherchez la version de propre up renforcée en exécutant la requête suivante :

SELECT * FROM sys.sysobjvalues WHERE valclass = 7 AND objid = 1004;

Recherchez la version propre up en exécutant la requête suivante :

SELECT * FROM sys.sysobjvalues WHERE valclass = 7 AND objid = 1003;

Si et cleanup_version les hardened_cleanup_version valeurs sont égales, ignorez cette section et passez à la section suivante.

Si les deux valeurs sont différentes, cela signifie qu’une ou plusieurs tables latérales ont rencontré des erreurs. L’atténuation la plus rapide consiste à désactiver et activer le suivi des modifications sur la table problématique. Cela entraîne la suppression de toutes les métadonnées de suivi maintenues pour la table. Les données de la table restent intactes.

Si l’option précédente n’est pas possible, exécutez la propre manuelle sur la table.

Résoudre les problèmes syscommittab

Cette section décrit les étapes à suivre pour déboguer et atténuer les problèmes liés à la syscommittab table système, s’il utilise beaucoup d’espace de stockage ou s’il a un gros backlog de lignes obsolètes.

La syscommittab table système propre up dépend de la table latérale propre up. Une fois que toutes les tables latérales sont propre terminées, syscommittab elles peuvent être vidées. Assurez-vous que toutes les étapes des exécutions de propre up automatique, mais qu’aucune section de progression n’est effectuée.

Pour appeler explicitement le syscommittab propre up, utilisez la procédure stockée sys.sp_flush_commit_table_on_demand.

Remarque

La sys.sp_flush_commit_table_on_demand procédure stockée peut prendre du temps s’il supprime un backlog important de lignes.

Comme indiqué dans l’exemple de section de l’article sys.sp_flush_commit_table_on_demand , cette procédure stockée retourne la valeur et safe_cleanup_version()le nombre de lignes supprimées. Si la valeur retournée semble être 0, et si instantané isolation est activée, la propre up peut ne pas supprimer quoi que ce soit .syscommittab

Si la période de rétention est supérieure à un jour, il doit être sûr de réexécuter la sys.sp_flush_commit_table_on_demand procédure stockée après avoir activé l’indicateur de trace 8239 globalement. L’utilisation de cet indicateur de trace lorsque instantané isolation est toujours sécurisée, mais dans certains cas, il peut ne pas être nécessaire.

Utilisation élevée du processeur pendant propre up

Le problème décrit dans cette section peut être vu sur les versions antérieures de SQL Server. S’il existe un grand nombre de tables de suivi des modifications dans une base de données et que la propre automatique ou manuelle propre up entraîne une utilisation élevée du processeur. Ce problème peut également être dû à la table d’historique, qui a été mentionnée brièvement dans les sections précédentes.

Utilisez le code T-SQL suivant pour case activée le nombre de lignes de la table d’historique :

SELECT COUNT(*) from dbo.MSChange_tracking_history;

Si le nombre de lignes est suffisamment grand, essayez d’ajouter l’index suivant s’il est absent. Utilisez le code T-SQL suivant pour ajouter l’index :

IF NOT EXISTS (
    SELECT *
    FROM sys.indexes
    WHERE name = 'IX_MSchange_tracking_history_start_time'
        AND object_id = OBJECT_ID('dbo.MSchange_tracking_history')
)
BEGIN
    CREATE NONCLUSTERED INDEX IX_MSchange_tracking_history_start_time
    ON dbo.MSchange_tracking_history (start_time)
END

Exécuter propre up plus fréquemment que 30 minutes

Des tables spécifiques peuvent rencontrer un taux élevé de modifications et vous pouvez constater que le travail automatique propre up ne peut pas propre les tables latérales et syscommittab dans l’intervalle de 30 minutes. Si cela se produit, vous pouvez exécuter un travail de propre manuelle avec une fréquence accrue pour faciliter le processus.

Pour SQL Server et Azure SQL Managed Instance, créez un travail en arrière-plan à l’aide sp_flush_CT_internal_table_on_demand d’un travail interne plus court que les 30 minutes par défaut. Pour Azure SQL Database, Azure Logic Apps peut être utilisé pour planifier ces travaux.

Le code T-SQL suivant peut être utilisé pour créer un travail afin d’aider à propre up les tables latérales pour le suivi des modifications :

-- Loop to invoke manual cleanup procedure for cleaning up change tracking tables in a database
-- Fetch the tables enabled for change tracking
SELECT IDENTITY(INT, 1, 1) AS TableID,
    (SCHEMA_NAME(tbl.Schema_ID) + '.' + OBJECT_NAME(ctt.object_id)) AS TableName
INTO #CT_Tables
FROM sys.change_tracking_tables ctt
INNER JOIN sys.tables tbl
    ON tbl.object_id = ctt.object_id;

-- Set up the variables
DECLARE @start INT = 1,
    @end INT = (
        SELECT COUNT(*)
        FROM #CT_Tables
        ),
    @tablename VARCHAR(255);

WHILE (@start <= @end)
BEGIN
    -- Fetch the table to be cleaned up
    SELECT @tablename = TableName
    FROM #CT_Tables
    WHERE TableID = @start

    -- Execute the manual cleanup stored procedure
    EXEC sp_flush_CT_internal_table_on_demand @tablename

    -- Increment the counter
    SET @start = @start + 1;
END

DROP TABLE #CT_Tables;

Contenu connexe