Gérer le suivi des modifications (SQL Server)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de données SQL dans Microsoft Fabric

Cet article explique 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.

Gérer le suivi des modifications

Les sections suivantes répertorient les vues de catalogue, les autorisations et les paramètres utiles à la gestion du suivi des modifications.

Vues du catalogue

Pour déterminer les tables et bases de données sur lesquelles le suivi des modifications est activé, utilisez les vues de catalogue suivantes :

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 :

  • SELECT autorisation sur au moins les colonnes clés primaires de la table de suivi des modifications vers la table interrogée.

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

    • Les enregistrements de suivi des modifications incluent des informations sur les lignes supprimées. Les enregistrements utilisent les valeurs de clé primaire des lignes supprimées. Un principal peut recevoir SELECT l’autorisation d’une table de suivi des modifications après la suppression de certaines données sensibles. Dans ce cas, vous ne souhaitez pas que ce principal accède aux informations supprimées à l’aide du suivi des modifications.

    • Les informations de suivi des modifications peuvent stocker des informations sur les colonnes qui sont modifiées par les opérations de mise à jour. Un principal peut se voir refuser l’accès à 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 est mise à jour, mais le principal ne peut pas déterminer la valeur de la colonne.

Comprendre la surcharge de suivi des modifications

Lorsque vous activez le suivi des modifications pour une table, il affecte certaines opérations d’administration. Le tableau suivant répertorie les opérations et les effets à considérer.

Opération 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 Une tentative de suppression de la PRIMARY KEY contrainte échoue. Vous devez désactiver le suivi des modifications avant de pouvoir supprimer une PRIMARY KEY contrainte.
ALTER TABLE DROP COLUMN Si une colonne que vous supprimez fait partie de la clé primaire, la suppression de la colonne n’est pas autorisée, quel que soit le suivi des modifications.

Si la colonne que vous supprimez ne fait pas partie de la clé primaire, la suppression de cette colonne aboutit. Toutefois, vous devez d’abord comprendre l’effet sur toute application qui synchronise 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. Il incombe à l’application de gérer la colonne supprimée.
ALTER TABLE ADD COLUMN Si vous ajoutez une nouvelle colonne à la table de suivi des modifications, l’ajout de la colonne n’est pas suivi. Seules sont suivies les mises à jour et les modifications apportées à la nouvelle colonne.
ALTER TABLE ALTER COLUMN Les modifications de type de données d’une colonne de clé non primaire ne sont pas suivies.
ALTER TABLE SWITCH Le basculement d’une partition échoue si une ou les deux tables ont activé le suivi des modifications.
DROP INDEX, or ALTER INDEX DISABLE L’index qui applique la clé primaire ne peut pas être supprimé ou désactivé.
TRUNCATE TABLE Vous pouvez tronquer une table sur laquelle le suivi des modifications est activé. Toutefois, les lignes supprimées par l’opération ne sont pas suivies et la version minimale valide 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. Cette condition équivaut à désactiver le suivi des modifications, puis à le réactiver pour la table.

L’utilisation du suivi des modifications ajoute une surcharge aux opérations DML, car l’opération stocke les informations de suivi des modifications.

Effets sur le DML

Le suivi des modifications est optimisé pour réduire la surcharge de performances sur les opérations DML. La surcharge de performances incrémentielles fournie avec l’utilisation du suivi des modifications sur une table est similaire à la surcharge que vous rencontrez lorsque vous créez et gérez un index pour une table.

Pour chaque ligne modifiée par une opération DML, le système ajoute une ligne à la table de suivi des modifications interne. L’effet de cette action, par rapport à l’opération DML, dépend de différents facteurs, tels que :

  • le nombre de colonnes clés primaire ;

  • Quantité de données modifiées dans la ligne de la table utilisateur

  • Nombre d’opérations effectuées dans une transaction

L’isolation d’instantané, si elle est utilisée, affecte également 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 :

  • Tableau interne des modifications

    Chaque table utilisateur sur laquelle le suivi des modifications est activé obtient sa propre table de modifications interne.

  • Tableau des transactions internes

    La base de données comporte une table de transactions interne.

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

  • Pour chaque modification apportée à chaque ligne de la table utilisateur, le suivi des modifications ajoute une ligne à la table de modifications interne. Cette ligne comporte un léger surcoût fixe, auquel s’ajoute un surcoût variable égal à la taille des colonnes de clé primaire. La ligne peut contenir des informations de contexte facultatives définies par une application. Si vous activez le suivi des colonnes, chaque colonne modifiée nécessite 4 octets dans la table de suivi.

  • Pour chaque transaction validée, le suivi des modifications ajoute une ligne à une table de 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 à l’aide de l’affichage catalogue sys.internal_tables , comme illustré dans l’exemple suivant.

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

Contenu connexe

  • Last updated on