Gérer le suivi des modifications (SQL Server)

  • Article

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance

Cette rubrique décrit comment gérer le suivi des modifications. Elle explique également comment configurer la sécurité et déterminer l'impact de l'utilisation du suivi des modifications sur le stockage et les performances.

Gestion du suivi des modifications

Les sections suivantes répertorient les affichages catalogue, les autorisations et les paramètres qui relèvent de la gestion du suivi des modifications.

Affichages catalogue

Pour déterminer quelles tables et bases de données font l'objet d'un suivi des modifications, vous pouvez utiliser les affichages catalogue suivants :

En outre, l’affichage catalogue sys.internal_tables répertorie les tables internes créées lorsque le suivi des modifications est activé pour une table utilisateur.

Sécurité

Pour accéder aux informations de suivi des modifications à l'aide des fonctions de suivi des modifications, le principal doit posséder les autorisations suivantes :

  • Autorisation SELECT sur au moins les colonnes clés primaire de la table faisant l'objet d'un suivi des modifications pour la table interrogée.

  • Autorisation VIEW CHANGE TRACKING sur la table pour laquelle les modifications sont obtenues. L'autorisation VIEW CHANGE TRACKING est obligatoire pour les raisons suivantes :

    • Les enregistrements de suivi des modifications incluent des informations sur les lignes supprimées et plus particulièrement sur les valeurs de clés primaires des lignes qui ont été supprimées. Un principal pourrait avoir reçu, après la suppression de certaines données sensibles, une autorisation SELECT pour une table faisant l'objet d'un suivi des modifications. Dans ce cas, vous ne souhaiteriez pas que ce principal soit en mesure d'accéder aux informations supprimées à l'aide du suivi des modifications.

    • Les informations de suivi des modifications peuvent stocker des informations sur les colonnes qui ont été modifiées par des opérations de mise à jour. Un principal pourrait se voir refuser l'autorisation d'accéder à une colonne qui contient des informations sensibles. Toutefois, étant donné que les informations de suivi des modifications sont disponibles, un principal peut déterminer qu'une valeur de colonne a été mise à jour, mais le principal ne peut pas déterminer la valeur de la colonne.

Présentation de la charge de traitement liée au suivi des modifications

Lorsque le suivi des modifications est activé pour une table, certaines opérations d'administration sont affectées. Le tableau suivant répertorie les opérations et les effets à considérer.

Operation Lorsque le suivi des modifications est activé
DROP TABLE Toutes les informations de suivi des modifications pour la table supprimée sont supprimées.
ALTER TABLE DROP CONSTRAINT Toute tentative de supprimer la contrainte PRIMARY KEY échoue. Le suivi des modifications doit être désactivé avant de supprimer une contrainte PRIMARY KEY.
ALTER TABLE DROP COLUMN Si une colonne supprimée fait partie de la clé primaire, la suppression de la colonne n'est pas autorisée, indépendamment du suivi des modifications.

Si la colonne supprimée ne fait pas partie de la clé primaire, la suppression de la colonne réussit. Toutefois, il est préférable de bien comprendre au préalable l'effet sur toutes les applications qui synchronisent ces données. Si le suivi des modifications de colonnes est activé pour la table, la colonne supprimée peut quand même être retournée dans le cadre des informations de suivi des modifications. L'application est chargée de gérer la colonne supprimée.
ALTER TABLE ADD COLUMN Si une nouvelle colonne est ajoutée à la table faisant l'objet d'un suivi des modifications, cet ajout ne fait pas l'objet d'un suivi des modifications. Seules sont suivies les mises à jour et les modifications apportées à la nouvelle colonne.
ALTER TABLE ALTER COLUMN Les modifications des types de données dans les colonnes clés non primaires ne font pas l'objet d'un suivi.
ALTER TABLE SWITCH Le basculement de partition échoue si le suivi des modifications est activé pour l'une des tables ou les deux.
DROP INDEX ou ALTER INDEX DISABLE L'index qui applique la clé primaire ne peut pas être supprimé ni désactivé.
TRUNCATE TABLE La troncature d'une table peut être effectuée sur une table pour laquelle le suivi des modifications est activé. Toutefois, les lignes supprimées par l'opération ne sont pas suivies, et la version valide minimale est mise à jour. Lorsqu'une application vérifie sa version, le contrôle indique que la version est trop ancienne et qu'une réinitialisation est requise. Cela revient au même qu'une désactivation du suivi des modifications, suivie d'une nouvelle activation pour la table.

L'utilisation du suivi des modifications ajoute à la charge de traitement des opérations DML en raison des informations stockées dans le cadre de l'opération.

Effets sur les opérations DML

Le suivi des modifications a été optimisé afin de réduire la surcharge des performances sur les opérations DML. Les diminutions de performances incrémentielles associées à l'utilisation du suivi des modifications sur une table sont semblables aux charges de traitement générées lorsqu'un index est créé pour une table et doit être géré.

Pour chaque ligne modifiée par une opération DML, une ligne est ajoutée à la table de suivi des modifications interne. L'effet produit en fonction de l'opération DML dépend de différents facteurs, tels que les suivants :

  • le nombre de colonnes clés primaire ;

  • la quantité de données modifiées dans la ligne de la table utilisateur ;

  • le nombre d'opérations effectuées dans une transaction.

L'isolement d'instantané, s'il est utilisé, produit également un effet sur les performances de toutes les opérations DML, que le suivi des modifications soit activé ou non.

Effets sur le stockage

Les données de suivi des modifications sont stockées dans les types suivants de tables internes :

  • Table des modifications interne

    Il existe une seule table des modifications interne pour chaque table utilisateur où le suivi des modifications est activé.

  • Table des transactions interne

    Il existe une seule table des transactions interne pour la base de données.

Ces tables internes affectent les besoins de stockage des manières suivantes :

  • Pour chaque modification apportée à chaque ligne dans la table utilisateur, une ligne est ajoutée à la table des modifications interne. Cette ligne a une faible charge fixe, plus une charge variable égale à la taille des colonnes clés primaire. La ligne peut contenir des informations de contexte facultatives définies par une application. En outre, si le suivi des colonnes est activé, chaque colonne modifiée requiert 4 octets dans la table de suivi.

  • Pour chaque transaction validée, une ligne est ajoutée à une table des transactions interne.

Comme avec d’autres tables internes, vous pouvez déterminer l’espace utilisé pour les tables de suivi des modifications en utilisant la procédure stockée sp_spaceused . Vous pouvez obtenir les noms des tables internes en utilisant l’affichage catalogue sys.internal_tables , comme l’illustre l’exemple suivant.

sp_spaceused 'sys.change_tracking_309576141'  
sp_spaceused 'sys.syscommittab'

Voir aussi

Suivre les modifications de données (SQL Server)
ALTER TABLE (Transact-SQL)
Propriétés de la base de données (page Suivi des modifications)
Options SET d’ALTER DATABASE (Transact-SQL)
sys.change_tracking_databases (Transact-SQL)
sys.change_tracking_tables (Transact-SQL)
Suivre les modifications de données (SQL Server)
À propos du suivi des modifications (SQL Server)
Utiliser les données modifiées (SQL Server)