Configurazione e gestione del rilevamento delle modifiche
In questo argomento viene descritto come abilitare, disabilitare e gestire il rilevamento delle modifiche. Nell'argomento viene descritto inoltre come configurare la sicurezza e determinare gli effetti sull'archiviazione e sulle prestazioni quando si utilizza il rilevamento delle modifiche.
Attivazione del rilevamento delle modifiche per un database
Prima di utilizzare il rilevamento delle modifiche, è necessario attivarlo a livello di database. Nell'esempio seguente viene illustrato come abilitare il rilevamento delle modifiche utilizzando ALTER DATABASE:
ALTER DATABASE AdventureWorks2008R2
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
È anche possibile attivare il rilevamento delle modifiche in SQL Server Management Studio utilizzando la finestra di dialogo Proprietà database (pagina ChangeTracking).
È possibile specificare le opzioni CHANGE_RETENTION e AUTO_CLEANUP quando si abilita il rilevamento delle modifiche ed è possibile modificare i valori in qualsiasi momento dopo l'abilitazione del rilevamento.
Il valore di memorizzazione della modifica specifica il periodo di tempo durante il quale vengono mantenute le informazioni sul rilevamento delle modifiche. Le informazioni sul rilevamento delle modifiche precedenti a tale periodo di tempo vengono rimosse periodicamente. Durante l'impostazione di questo valore, considerare la frequenza di sincronizzazione delle applicazioni con le tabelle nel database. Il periodo di memorizzazione specificato deve durare almeno quanto il periodo di tempo massimo tra le sincronizzazioni. Se un'applicazione ottiene modifiche a intervalli più lunghi, i risultati restituiti potrebbero non essere corretti, poiché alcune delle informazioni sulle modifiche sono state probabilmente rimosse. Per evitare di ottenere risultati non corretti, un'applicazione può utilizzare la funzione di sistema CHANGE_TRACKING_MIN_VALID_VERSION per determinare se l'intervallo tra sincronizzazioni è stato troppo lungo.
Per abilitare o disabilitare l'attività di pulizia che rimuove le informazioni obsolete sul rilevamento delle modifiche, è possibile utilizzare l'opzione AUTO_CLEANUP. Questa procedura può risultare utile quando un problema temporaneo impedisce la sincronizzazione delle applicazioni e la procedura di rimozione delle informazioni sul rilevamento delle modifiche precedenti al periodo di memorizzazione deve essere messa in pausa finché il problema non viene risolto.
Per qualsiasi database che utilizza il rilevamento delle modifiche, tenere presente quanto segue:
Per utilizzare il rilevamento delle modifiche, il livello di compatibilità del database deve essere impostato su 90 o su un valore superiore. Se il livello di compatibilità del database è minore di 90, è possibile comunque configurare il rilevamento delle modifiche, ma la funzione CHANGETABLE, utilizzata per ottenere informazioni sul rilevamento delle modifiche, restituirà un errore.
L'utilizzo dell'isolamento dello snapshot rappresenta il modo più semplice per garantire che tutte le informazioni sul rilevamento delle modifiche siano coerenti. Per questo motivo, è consigliabile impostare l'isolamento dello snapshot per il database su ON. Per ulteriori informazioni, vedere Utilizzo del rilevamento delle modifiche.
Attivazione del rilevamento delle modifiche per una tabella
Il rilevamento delle modifiche deve essere abilitato per ciascuna tabella per cui si desidera eseguirlo. Quando il rilevamento delle modifiche è abilitato, le relative informazioni vengono gestite per tutte le righe della tabella interessate da un'operazione DML.
Nell'esempio seguente viene illustrato come attivare il rilevamento delle modifiche per una tabella utilizzando ALTER TABLE:
ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);
È anche possibile attivare il rilevamento delle modifiche per una tabella in SQL Server Management Studio utilizzando la finestra di dialogo Proprietà tabella (pagina ChangeTracking).
Quando l'opzione TRACK_COLUMNS_UPDATED è impostata su ON, nel Motore di database di SQL Server vengono archiviate informazioni aggiuntive sulle colonne aggiornate nella tabella del rilevamento delle modifiche interna. Il rilevamento a livello di colonna consente di sincronizzare solo le colonne aggiornate. Ciò può migliorare efficienza e prestazioni. Tuttavia, poiché la gestione delle informazioni sul rilevamento a livello della colonna aggiunge un ulteriore overhead di archiviazione, per impostazione predefinita questa opzione è impostata su OFF.
Disabilitazione del rilevamento delle modifiche
Il rilevamento delle modifiche deve essere prima disabilitato per tutte le tabelle di cui sono state rilevate le modifiche prima che il rilevamento possa essere impostato su OFF per il database. Per determinare le tabelle in cui è attivato il rilevamento delle modifiche per un database, utilizzare la vista del catalogo sys.change_tracking_tables.
Nell'esempio seguente viene illustrato come disabilitare il rilevamento delle modifiche per una tabella utilizzando ALTER TABLE:
ALTER TABLE Person.Person
DISABLE CHANGE_TRACKING;
Quando non vengono rilevate modifiche in nessuna tabella di un database, è possibile disabilitare il rilevamento delle modifiche per il database. Nell'esempio seguente viene illustrato come disabilitare il rilevamento delle modifiche per un database utilizzando ALTER DATABASE:
ALTER DATABASE AdventureWorks2008R2
SET CHANGE_TRACKING = OFF;
Gestione del rilevamento delle modifiche
Nelle sezioni seguenti vengono elencate le viste del catalogo, le autorizzazioni e le impostazioni per la gestione del rilevamento delle modifiche.
Viste del catalogo
Per determinare in quali tabelle e database è attivato il rilevamento delle modifiche, è possibile utilizzare le seguenti viste del catalogo:
Inoltre, la vista del catalogo sys.internal_tables elenca le tabelle interne create quando il rilevamento delle modifiche è attivato per una tabella utente.
Sicurezza
Per accedere alle informazioni sul rilevamento delle modifiche utilizzando le funzioni di rilevamento delle modifiche, l'entità deve disporre delle autorizzazioni seguenti:
Autorizzazione SELECT almeno nelle colonne di chiave primaria nella tabella di cui sono state rilevate le modifiche per la tabella in cui si sta eseguendo la query.
Autorizzazione VIEW CHANGE TRACKING nella tabella per la quale vengono ottenute le modifiche. L'autorizzazione VIEW CHANGE TRACKING è richiesta per i seguenti motivi:
I record sul rilevamento delle modifiche includono informazioni sulle righe eliminate, ovvero i valori della chiave primaria delle righe eliminate. A un'entità potrebbe essere stata concessa l'autorizzazione SELECT per una tabella con rilevamento delle modifiche dopo l'eliminazione di alcuni dati riservati. In questo caso è necessario che tale entità non sia in grado di accedere alle informazioni eliminate utilizzando il rilevamento delle modifiche.
Le informazioni sul rilevamento delle modifiche possono archiviare informazioni sulle colonne modificate dalle operazioni di aggiornamento. A un'entità potrebbe essere stata negata l'autorizzazione relativa a una colonna che contiene informazioni riservate. Tuttavia, poiché le informazioni sul rilevamento delle modifiche sono disponibili, un'entità può determinare che un valore della colonna è stato aggiornato, ma non può determinare tale valore.
Informazioni sull'overhead del rilevamento delle modifiche
Quando il rilevamento delle modifiche è attivato per una tabella, sono interessate alcune operazioni di amministrazione. Nella tabella seguente sono elencate le operazioni e gli effetti da tenere in considerazione:
Operazione |
Se il rilevamento delle modifiche è attivato |
|---|---|
DROP TABLE |
Tutte le informazioni sul rilevamento delle modifiche per la tabella eliminata vengono rimosse. |
ALTER TABLE DROP CONSTRAINT |
Il tentativo di eliminare il vincolo PRIMARY KEY avrà esito negativo. Il rilevamento delle modifiche deve essere disabilitato prima che un vincolo PRIMARY KEY possa essere eliminato. |
ALTER TABLE DROP COLUMN |
Se una colonna in fase di eliminazione fa parte della chiave primaria, l'eliminazione della colonna non è consentita, indipendentemente dal rilevamento delle modifiche. Se la colonna in fase di eliminazione non fa parte della chiave primaria, l'eliminazione della colonna viene eseguita. Tuttavia, è necessario conoscere prima l'effetto su qualsiasi applicazione che sta sincronizzando questi dati. Se il rilevamento delle modifiche a livello della colonna è attivato per la tabella, la colonna eliminata potrebbe ancora essere restituita come parte delle informazioni sul rilevamento delle modifiche. È compito dell'applicazione gestire la colonna eliminata. |
ALTER TABLE ADD COLUMN |
Se una nuova colonna viene aggiunta alla tabella con rilevamento delle modifiche, l'aggiunta della colonna non viene rilevata, ma vengono rilevati solo gli aggiornamenti e le modifiche apportati alla nuova colonna. |
ALTER TABLE ALTER COLUMN |
Le modifiche ai tipi di dati di una chiave non primaria non vengono rilevate. |
ALTER TABLE SWITCH |
Lo spostamento di una partizione non riesce se in una o in entrambe le tabelle è attivato il rilevamento delle modifiche. |
DROP INDEX o ALTER INDEX DISABLE |
L'indice che applica la chiave primaria non può essere eliminato o disabilitato. |
TRUNCATE TABLE |
Il troncamento di una tabella può essere eseguito su una tabella in cui è attivato il rilevamento delle modifiche. Tuttavia, le righe eliminate dall'operazione non vengono rilevate e viene aggiornata la versione minima valida. Quando un'applicazione verifica la versione, viene indicato che la versione è obsoleta ed è necessario eseguire la reinizializzazione. Questa situazione è la stessa che si verifica quando il rilevamento delle modifiche per la tabella viene disabilitato e successivamente riabilitato. |
L'utilizzo del rilevamento delle modifiche provoca l'aumento di overhead delle operazioni DML a causa delle informazioni sul rilevamento delle modifiche archiviate come parte dell'operazione.
Effetti sulle operazioni DML
Il rilevamento delle modifiche è stato ottimizzato per minimizzare l'overhead delle prestazioni nelle operazioni DML. L'overhead incrementale delle prestazioni associato all'utilizzo del rilevamento delle modifiche in una tabella è analogo all'overhead causato dalla creazione di un indice per una tabella e dalla relativa necessità di gestirlo.
Per ogni riga modificata da un'operazione DML, viene aggiunta una riga alla tabella del rilevamento delle modifiche interna. Il suo effetto in relazione all'operazione DML dipende da vari fattori, quali i seguenti:
Numero di colonne chiave primaria
Quantità di dati di cui è in corso la modifica nella riga della tabella utente
Numero di operazioni in esecuzione in una transazione
L'isolamento dello snapshot, se utilizzato, influisce anche sulle prestazioni per tutte le operazioni DML, indipendentemente dall'abilitazione o meno del rilevamento delle modifiche.
Effetti sull'archiviazione
I dati relativi al rilevamento delle modifiche vengono archiviati nei seguenti tipi di tabelle interne:
Tabella delle modifiche interna
È disponibile una tabella delle modifiche interna per ciascuna tabella utente per cui è abilitato il rilevamento delle modifiche.
Tabella delle transazioni interna
È disponibile una tabella delle transazioni interna per il database.
Tali tabelle interne influiscono sui requisiti di archiviazione nei modi descritti di seguito:
Per ciascuna modifica a ciascuna riga della tabella utente, viene aggiunta una riga alla tabella delle modifiche interna. Questa riga ha un basso overhead fisso più un overhead variabile uguale alle dimensioni delle colonne chiave primaria. La riga può contenere informazioni facoltative sul contesto impostate da un'applicazione. Inoltre, se il rilevamento a livello della colonna è attivato, ciascuna colonna modificata richiede 4 byte nella tabella di rilevamento.
Per ogni transazione di cui è stato eseguito il commit, viene aggiunta una riga a una tabella delle transazioni interna.
Come per le altre tabelle interne, è possibile determinare lo spazio utilizzato per le tabelle del rilevamento delle modifiche utilizzando la stored procedure sp_spaceused. I nomi delle tabelle interne possono essere ottenuti utilizzando la vista del catalogo sys.internal_tables, come illustrato nell'esempio seguente.
sp_spaceused 'sys.change_tracking_309576141';
sp_spaceused 'sys.syscommittab';