Gestire il rilevamento delle modifiche (SQL Server)

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di Azure SQLDatabase SQL in Microsoft Fabric

Questo articolo descrive come 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.

Gestire il 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 le tabelle e i database in cui è abilitato il rilevamento delle modifiche, utilizzare le viste del catalogo seguenti:

Inoltre, la vista del catalogo sys.internal_tables elenca le tabelle interne create quando il rilevamento delle modifiche è abilitato 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:

  • SELECT autorizzazione per almeno le colonne chiave primaria nella tabella con rilevamento delle modifiche alla tabella su cui viene eseguita una query.

  • VIEW CHANGE TRACKING autorizzazione per la tabella per cui vengono ottenute le modifiche. L'autorizzazione VIEW CHANGE TRACKING è necessaria per i motivi seguenti:

    • I record di rilevamento delle modifiche includono informazioni sulle righe eliminate. I record usano i valori di chiave primaria delle righe eliminate. È possibile concedere SELECT a un'entità di sicurezza l'autorizzazione per una tabella con rilevamento delle modifiche dopo l'eliminazione di alcuni dati sensibili. In questo caso, non si desidera che tale entità di sicurezza acceda alle informazioni eliminate tramite il rilevamento delle modifiche.

    • Le informazioni sul rilevamento delle modifiche possono archiviare informazioni sulle colonne modificate dalle operazioni di aggiornamento. A un principale potrebbe essere negato l'accesso a una colonna che contiene informazioni riservate. Tuttavia, poiché sono disponibili informazioni sul rilevamento delle modifiche, un'entità può determinare che un valore di colonna viene aggiornato, ma l'entità non può determinare il valore della colonna.

Comprendere il sovraccarico del rilevamento delle modifiche

Quando si abilita il rilevamento delle modifiche per una tabella, influisce su alcune operazioni di amministrazione. Nella tabella seguente sono elencate le operazioni e gli effetti da tenere in considerazione:

Operazione Se il rilevamento delle modifiche è abilitato
DROP TABLE Tutte le informazioni di rilevamento delle modifiche per la tabella eliminata vengono rimosse.
ALTER TABLE DROP CONSTRAINT Un tentativo di eliminare il PRIMARY KEY vincolo ha esito negativo. È necessario disabilitare il rilevamento delle modifiche prima di poter eliminare un PRIMARY KEY vincolo.
ALTER TABLE DROP COLUMN Se una colonna che si sta eliminando fa parte della chiave primaria, l'eliminazione della colonna non è consentita, indipendentemente dal monitoraggio delle modifiche.

Se la colonna che si sta eliminando non fa parte della chiave primaria, l'eliminazione della colonna riesce. Tuttavia, è necessario comprendere prima di tutto l'effetto su qualsiasi applicazione che sincronizza questi dati. Se il rilevamento delle modifiche a livello della colonna è abilitato per la tabella, la colonna eliminata potrebbe ancora essere restituita come parte delle informazioni sul rilevamento delle modifiche. È responsabilità dell'applicazione gestire la colonna rimossa.
ALTER TABLE ADD COLUMN Se si aggiunge una nuova colonna alla tabella di 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 al tipo di dati di una colonna chiave non primaria non vengono rilevate.
ALTER TABLE SWITCH Il cambio di una partizione ha esito negativo se una o entrambe le tabelle sono abilitate per il rilevamento delle modifiche.
DROP INDEX, or ALTER INDEX DISABLE L'indice che applica la chiave primaria non può essere eliminato o disabilitato.
TRUNCATE TABLE È possibile troncare una tabella in cui è abilitato il rilevamento delle modifiche. Tuttavia, le righe eliminate dall'operazione non vengono rilevate e la versione minima valida viene aggiornata. Quando un'applicazione verifica la versione, viene indicato che la versione è obsoleta ed è necessario eseguire la reinizializzazione. Questa condizione equivale alla disabilitazione del rilevamento delle modifiche e alla sua successiva riattivazione per la tabella.

L'uso del rilevamento delle modifiche comporta un sovraccarico per le operazioni DML perché l'operazione archivia le informazioni di rilevamento delle modifiche.

Effetti sulle operazioni DML

Il rilevamento delle modifiche è ottimizzato per ridurre al minimo il sovraccarico delle prestazioni nelle operazioni DML. Il sovraccarico incrementale delle prestazioni fornito con l'uso del rilevamento delle modifiche in una tabella è simile al sovraccarico riscontrato durante la creazione e la gestione di un indice per una tabella.

Per ogni riga modificata da un'operazione DML, il sistema aggiunge una riga alla tabella di rilevamento modifiche interna. L'effetto di questa azione, relativo all'operazione DML, dipende da diversi fattori, ad esempio:

  • Numero di colonne chiave primaria

  • Quantità di dati modificati nella riga della tabella utente

  • Numero di operazioni eseguite in una transazione

L'isolamento dello snapshot, se usato, influisce anche sulle prestazioni per tutte le operazioni DML, indipendentemente dal fatto che il rilevamento delle modifiche sia abilitato o meno.

Effetti sull'archiviazione

I dati relativi al rilevamento delle modifiche vengono archiviati nei seguenti tipi di tabelle interne:

  • Tabella delle modifiche interna

    Ogni tabella utente con rilevamento modifiche abilitata ottiene la propria tabella delle modifiche interna.

  • Tabella delle transazioni interna

    Il database dispone di una tabella di transazioni interna.

Tali tabelle interne influiscono sui requisiti di archiviazione nei modi descritti di seguito:

  • Per ogni modifica apportata a ogni riga della tabella utente, il rilevamento delle modifiche aggiunge una riga alla tabella delle modifiche interna. Questa riga ha un piccolo overhead fisso più un overhead variabile pari alla dimensione delle colonne della chiave primaria. La riga può contenere informazioni facoltative sul contesto impostate da un'applicazione. Se si abilita il rilevamento delle colonne, ogni colonna modificata richiede 4 byte nella tabella di rilevamento.

  • Per ogni transazione sottoposta a commit, il rilevamento delle modifiche aggiunge una riga a una tabella delle transazioni interne.

Come per le altre tabelle interne, è possibile determinare lo spazio usato per le tabelle di rilevamento delle modifiche usando la stored procedure sp_spaceused . È possibile ottenere i nomi delle tabelle interne usando la vista del catalogo sys.internal_tables , come illustrato nell'esempio seguente.

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

Contenuti correlati

  • Last updated on