Résoudre les problèmes de propre up automatique de suivi des modifications
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 exemplechange_tracking_12345
) ousyscommittab
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_history
la 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é.
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.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;
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 lasys.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
- À propos du suivi des modifications (SQL Server)
- Fonctions de suivi des modifications (Transact-SQL)
- Procédures stockées De suivi des modifications (Transact-SQL)
- Tables de suivi des modifications (Transact-SQL)
- Afficher les informations sur l’espace occupé par les données et par le journal d’une base de données
- Résoudre les problèmes d'utilisation élevée du processeur dans SQL Server
Commentaires
https://aka.ms/ContentUserFeedback.
Prochainement : Tout au long de l'année 2024, nous supprimerons progressivement les GitHub Issues en tant que mécanisme de retour d'information pour le contenu et nous les remplacerons par un nouveau système de retour d'information. Pour plus d’informations, voir:Soumettre et afficher des commentaires pour