Partager via

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 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 :

  • 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 qui ont été supprimées. Les enregistrements utilisent les valeurs de clé primaire des lignes qui ont été supprimées. Un principal peut avoir reçu 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 puisse accéder à ces 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.

Comprendre la surcharge de 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.

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. Le suivi des modifications doit être désactivé avant qu’une PRIMARY KEY contrainte puisse être supprimée.
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 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é 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 de cette opération par rapport à l’opération DML dépend de différents facteurs, tels que :

  • 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'

Contenu connexe

  • Last updated on