Condividi tramite

Gestire il rilevamento delle modifiche (SQL Server)

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureDatabase 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 in quali tabelle e database è abilitato 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 è 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. A un principale potrebbe essere stata concessa l'autorizzazione SELECT per una tabella con tracciamento delle modifiche dopo l'eliminazione di alcuni dati sensibili. In questo caso, non si vuole che il principale sia in grado di accedere alle informazioni eliminate usando 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.

Comprendere il sovraccarico del rilevamento delle modifiche

Quando il rilevamento delle modifiche è abilitato 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 è abilitato
DROP TABLE Tutte le informazioni sul rilevamento delle modifiche per la tabella eliminata vengono rimosse.
ALTER TABLE DROP CONSTRAINT Un tentativo di eliminare il PRIMARY KEY vincolo ha esito negativo. Il rilevamento delle modifiche deve essere disabilitato prima che un PRIMARY KEY vincolo 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 è abilitato 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 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 Il troncamento di una tabella può essere eseguito su una tabella in cui è abilitato 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 ridurre al minimo il sovraccarico di 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. L'effetto di questa operazione relativa all'operazione DML dipende da diversi fattori, ad esempio:

  • 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 è abilitato, 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 usato per le tabelle di rilevamento delle modifiche usando la stored procedure sp_spaceused . I nomi delle tabelle interne possono essere ottenuti 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