Risolvere i problemi di pulizia automatica del rilevamento modifiche

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure

Questo articolo fornisce modi per risolvere i problemi comuni riscontrati nella pulizia automatica del rilevamento modifiche.

Sintomi

In genere, se la pulizia automatica non funziona come previsto, è possibile visualizzare uno o più dei sintomi seguenti:

• Utilizzo elevato dello spazio di archiviazione da una o più tabelle laterali di rilevamento modifiche o dalla tabella di sistema syscommittab.
• Le tabelle laterali (tabelle interne il cui nome inizia con prefisso change_tracking, ad esempio, change_tracking_12345) o syscommittab o entrambe, mostrano un numero significativo di righe esterne al periodo di conservazione configurato.
• dbo.MSChange_tracking_history la tabella contiene voci con errori di pulizia specifici.
• CHANGETABLE le prestazioni sono peggiorate nel tempo.
• La pulizia automatica o la pulizia manuale segnala un utilizzo elevato della CPU.

Debug e prevenzione

Per identificare la causa radice di un problema con la pulizia automatica del rilevamento modifiche, seguire questa procedura per eseguire il debug e attenuare il problema.

Stato della pulizia automatica

Controllare se la pulizia automatica è stata eseguita. Per verificarlo, eseguire una query sulla tabella della cronologia di pulizia nello stesso database. Se la pulizia è stata eseguita, la tabella contiene voci con l'ora di inizio e di fine della pulizia. Se la pulizia non è in esecuzione, la tabella è vuota o contiene voci non aggiornate. Se la tabella di cronologia contiene voci con il tag cleanup errors nella colonna comments, la pulizia non riesce a causa di errori di pulizia a livello di tabella.

SELECT TOP 1000 * FROM dbo.MSChange_tracking_history ORDER BY start_time DESC;

La pulizia automatica viene eseguita periodicamente, con un intervallo predefinito di 30 minuti. Se la tabella di cronologia non esiste, molto probabilmente la pulizia automatica non è mai stata eseguita. In caso contrario, controllare i valori di colonna start_time e end_time. Se le ultime voci non sono recenti, ovvero sono di ore o giorni precedenti, la pulizia automatica potrebbe non essere in esecuzione. In questo caso, seguire questa procedura per risolvere i problemi.

1. La pulizia è disattivata

Controllare se la pulizia automatica è attivata per il database. In caso contrario, attivarlo e attendere almeno 30 minuti prima di esaminare la tabella di cronologia per le nuove voci. Monitorare lo stato di avanzamento nella tabella della cronologia.

SELECT * FROM sys.change_tracking_databases WHERE database_id=DB_ID('<database_name>')

Un valore diverso da zero in is_auto_cleanup_on indica che la pulizia automatica è abilitata. Il valore del periodo di conservazione controlla la durata per cui i metadati di rilevamento modifiche vengono conservati nel sistema. Il valore predefinito per il periodo di conservazione del rilevamento modifiche è 2 giorni.

Per abilitare o disattivare il rilevamento modifiche, vedere Abilitare e disattivare il rilevamento modifiche (SQL Server).

2. La pulizia è attivata ma non è in esecuzione

Se la pulizia automatica è attivata, è probabile che il thread di pulizia automatica venga arrestato a causa di errori imprevisti. Attualmente, riavviare il thread di pulizia automatica non è fattibile. È necessario avviare un failover in un server secondario (o riavviare il server in assenza di un database secondario) e verificare che l'impostazione di pulizia automatica sia abilitata per il database.

La pulizia automatica viene eseguita ma non sta facendo progressi

Se una o più tabelle laterali mostrano un consumo significativo dello spazio di archiviazione o contengono un numero grande di record oltre la conservazione configurata, seguire i passaggi descritti in questa sezione, che descrivono i rimedi per una singola tabella laterale. Gli stessi passaggi possono essere ripetuti per più tabelle, se necessario.

1. Valutare il backlog di pulizia automatica

Identificare le tabelle laterali con un backlog di grandi dimensioni di record scaduti, che richiedono una prevenzione da eseguire su di essi. Eseguire le query seguenti per identificare le tabelle laterali con record scaduti di grandi dimensioni. Ricordarsi di rimpiazzare i valori negli script di esempio, come illustrato.

1. Ottenere la versione di pulizia non valida:

   SELECT * FROM sys.change_tracking_tables;
   

   Il valore cleanup_version delle righe restituite rappresenta la versione di pulizia non valida.

2. Eseguire la query Transact-SQL (T-SQL) dinamica seguente, che genera la query per ottenere il numero di righe scadute delle tabelle laterali. Sostituire il valore di <invalid_version> nella query con il valore ottenuto nel passaggio precedente.

   SELECT 'SELECT ''' + QUOTENAME(name) + ''', count(*) FROM [sys].' + QUOTENAME(name)
       + ' WHERE sys_change_xdes_id IN (SELECT xdes_id FROM sys.syscommittab ssct WHERE ssct.commit_ts <= <invalid_version>) UNION'
   FROM sys.internal_tables
   WHERE internal_type = 209;
   

3. Copiare il set di risultati dalla query precedente e rimuovere la parola chiave UNION dall'ultima riga. Se si esegue la query T-SQL generata tramite una connessione amministrativa dedicata (applicazione livello dati, DAC), la query restituisce i conteggi delle righe scaduti di tutte le tabelle laterali. A seconda delle dimensioni della tabella sys.syscommittab e del numero di tabelle laterali, il completamento di questa query potrebbe richiedere molto tempo.

   Importante

   Questo passaggio è necessario per procedere con i passaggi di prevenzione. Se la query precedente non viene eseguita, identificare i conteggi scaduti delle righe per le singole tabelle laterali usando le query indicate di seguito.

Eseguire i passaggi di prevenzione seguenti per le tabelle laterali, con l'ordine decrescente dei conteggi scaduti delle righe, fino a quando il conteggio delle righe scadute non scende a uno stato gestibile per la pulizia automatica.

Dopo aver identificato le tabelle laterali con conteggi di record scaduti di grandi dimensioni, raccogliere informazioni sulla latenza delle istruzioni di eliminazione della tabella laterale e sulla frequenza di eliminazione al secondo nelle ultime ore. Stimare quindi il tempo necessario per pulire la tabella laterale considerando sia il numero di righe non aggiornate che la latenza di eliminazione.

Usare il frammento di codice T-SQL seguente sostituendo i modelli di parametro con i valori appropriati.

• Eseguire una query sulla frequenza di pulizia al secondo:

  SELECT
      table_name,
      rows_cleaned_up / ISNULL(NULLIF(DATEDIFF(second, start_time, end_time), 0), 1),
      cleanup_version
  FROM dbo.MSChange_tracking_history
  WHERE table_name = '<table_name>'
  ORDER BY end_time DESC;
  

  È anche possibile usare la granularità di minuti o ore per la funzione DATEDIFF.

• Trovare il numero di righe non aggiornati nella tabella laterale. Questa query consente di trovare il numero di righe in sospeso da pulire.

  <internal_table_name> e <cleanup_version> per la tabella utente si trovano nell'output restituito nella sezione precedente. Utilizzando queste informazioni, esegui il seguente codice T-SQL tramite una connessione amministrativa dedicata (applicazione livello dati, DAC):

  SELECT '<internal_table_name>',
      COUNT(*)
  FROM sys.<internal_table_name>
  WHERE sys_change_xdes_id IN (
          SELECT xdes_id
          FROM sys.syscommittab ssct
          WHERE ssct.commit_ts <= <cleanup_version>
  );
  

  Questa query può richiedere del tempo. Nei casi in cui si verifica il timeout della query, calcolare le righe non aggiornate individuando la differenza tra le righe totali e le righe attive, ovvero le righe da pulire.

• Trovare il numero totale di righe nella tabella laterale eseguendo la query seguente:

  SELECT sum(row_count) FROM sys.dm_db_partition_stats
  WHERE object_id = OBJECT_ID('sys.<internal_table_name>')
  GROUP BY partition_id;
  

• Trovare il numero di righe attive nella tabella laterale eseguendo la query seguente:

  SELECT '<internal_table_name>', COUNT(*) FROM sys.<internal_table_name> WHERE sys_change_xdes_id
  IN (SELECT xdes_id FROM sys.syscommittab ssct WHERE ssct.commit_ts > <cleanup_version>);
  

  È possibile calcolare il tempo stimato per pulire la tabella usando la frequenza di pulizia e conteggio delle righe non aggiornate. Considerare la formula seguente:

  Tempo di pulizia in minuti = (numero di righe non aggiornate / frequenza di pulizia in minuti)

  Se il tempo necessario per completare la pulizia della tabella è accettabile, monitorare lo stato di avanzamento e lasciare che la pulizia automatica continui il suo lavoro. In caso contrario, procedere con i passaggi successivi per eseguire il drill-down.

2. Controllare i conflitti di blocco di tabella

Determinare se la pulizia non sta procedendo a causa di conflitti di escalation dei blocchi della tabella, che impediscono costantemente alla pulizia di acquisire blocchi sulla tabella laterale per eliminare le righe.

Per confermare un conflitto di blocco, eseguire il codice T-SQL seguente. Questa query recupera i record per la tabella problematica per determinare se sono presenti più voci che indicano conflitti di blocco. Alcuni conflitti sporadici distribuiti in un periodo non dovrebbero qualificarsi per i passaggi di prevenzione in corso. I conflitti devono essere ricorrenti.

SELECT TOP 1000 *
FROM dbo.MSChange_tracking_history
WHERE table_name = '<user_table_name>'
ORDER BY start_time DESC;

Se la tabella di cronologia contiene più voci nelle colonne comments con il valore Cleanup error: Lock request time out period exceeded, è chiaro che più tentativi di pulizia non sono riusciti a causa di conflitti di blocco o timeout blocco in successione. Considerare i rimedi seguenti:

• Disabilitare e abilitare il rilevamento delle modifiche nella tabella problematica. In questo modo, tutti i metadati di rilevamento mantenuti per la tabella devono essere eliminati. I dati della tabella rimangono intatti. Questo è il rimedio più rapido.

• Se l'opzione precedente non è possibile, procedere con l'esecuzione della pulizia manuale nella tabella abilitando il flag di traccia 8284, come indicato di seguito:

  DBCC TRACEON (8284, -1);
  GO
  EXEC [sys].[sp_flush_CT_internal_table_on_demand] @TableToClean = '<table_name>';
  

3. Controllare altre cause

Un'altra possibile causa del ritardo di pulizia è la lentezza delle istruzioni delete. Per determinare se è così, controllare il valore di hardened_cleanup_version. Questo valore può essere recuperato tramite una connessione amministrativa dedicata (DAC) al database in considerazione.

Trovare la versione di pulizia con protezione avanzata eseguendo la query seguente:

SELECT * FROM sys.sysobjvalues WHERE valclass = 7 AND objid = 1004;

Trovare la versione di pulizia eseguendo la query seguente:

SELECT * FROM sys.sysobjvalues WHERE valclass = 7 AND objid = 1003;

Se i valori hardened_cleanup_version e cleanup_version sono uguali, ignorare questa sezione e passare alla sezione successiva.

Se entrambi i valori sono diversi, significa che si sono verificati errori in una o più tabelle laterali. La prevenzione più rapida consiste nel disattivare e abilitare il rilevamento modifiche nella tabella problematica. In questo modo, tutti i metadati di rilevamento mantenuti per la tabella devono essere eliminati. I dati nella tabella rimangono intatti.

Se l'opzione precedente non è possibile, eseguire la pulizia manuale nella tabella.

Risolvere i problemi di syscommittab

Questa sezione illustra i passaggi per eseguire il debug e attenuare i problemi relativi alla tabella di sistema syscommittab, se usa molto spazio di archiviazione o se presenta un backlog di grandi dimensioni di righe non aggiornate.

La pulizia della tabella di sistema syscommittab dipende dalla pulizia della tabella laterale. Solo dopo la pulizia di tutte le tabelle laterali, è possibile eliminare syscommittab. Assicurarsi che tutti i passaggi nella sezione pulizia automatica vengano eseguiti ma non vengano eseguiti progressi.

Per richiamare in modo esplicito la pulizia syscommittab, utilizzare la stored procedure sys.sp_flush_commit_table_on_demand.

Nota

La stored procedure sys.sp_flush_commit_table_on_demand può richiedere tempo se si elimina un backlog di grandi dimensioni di righe.

Come illustrato nella sezione di esempio dell'articolo sys.sp_flush_commit_table_on_demand, questa stored procedure restituisce il valore di safe_cleanup_version() e il numero di righe eliminate. Se il valore restituito sembra essere 0 e se l'isolamento dello snapshot è attivato, la pulizia potrebbe non eliminare nulla da syscommittab.

Se il periodo di conservazione è maggiore di un giorno, è consigliabile eseguire nuovamente la stored procedure sys.sp_flush_commit_table_on_demand dopo aver abilitato il flag di traccia 8239 a livello globale. L'uso di questo flag di traccia quando l'isolamento dello snapshot è sempre sicuro, ma in alcuni casi potrebbe non essere necessario.

Utilizzo elevato della CPU durante la pulizia

Il problema descritto in questa sezione potrebbe essere visualizzato nelle versioni precedenti di SQL Server. Se in un database sono presenti un numero elevato di tabelle con rilevamento modifiche e la pulizia automatica o la pulizia manuale causa un utilizzo elevato della CPU. Questo problema può anche essere causato dalla tabella della cronologia, menzionata brevemente nelle sezioni precedenti.

Usare il codice T-SQL seguente per controllare il numero di righe nella tabella di cronologia:

SELECT COUNT(*) from dbo.MSChange_tracking_history;

Se il numero di righe è sufficientemente grande, provare ad aggiungere l'indice seguente se è assente. Utilizzare il seguente codice T-SQL per aggiungere l'indice:

IF NOT EXISTS (
    SELECT *
    FROM sys.indexes
    WHERE name = 'IX_MSchange_tracking_history_start_time'
        AND object_id = OBJECT_ID('dbo.MSchange_tracking_history')
)
BEGIN
    CREATE NONCLUSTERED INDEX IX_MSchange_tracking_history_start_time
    ON dbo.MSchange_tracking_history (start_time)
END

Eseguire la pulizia più spesso di 30 minuti

Tabelle specifiche possono riscontrare una frequenza elevata di modifiche e si potrebbe notare che il processo di pulizia automatica non può pulire le tabelle laterali e syscommittab nell'intervallo di 30 minuti. In questo caso, è possibile eseguire un processo di pulizia manuale con maggiore frequenza per facilitare il processo.

Per SQL Server e Istanza gestita di SQL di Azure, creare un processo in background usando sp_flush_CT_internal_table_on_demand con un valore interno più breve rispetto ai 30 minuti predefiniti. Per database SQL di Azure, è possibile usare App per la logica di Azure per pianificare questi processi.

Il codice T-SQL seguente può essere usato per creare un processo per facilitare la pulizia delle tabelle laterali per il rilevamento modifiche:

-- Loop to invoke manual cleanup procedure for cleaning up change tracking tables in a database
-- Fetch the tables enabled for change tracking
SELECT IDENTITY(INT, 1, 1) AS TableID,
    (SCHEMA_NAME(tbl.Schema_ID) + '.' + OBJECT_NAME(ctt.object_id)) AS TableName
INTO #CT_Tables
FROM sys.change_tracking_tables ctt
INNER JOIN sys.tables tbl
    ON tbl.object_id = ctt.object_id;

-- Set up the variables
DECLARE @start INT = 1,
    @end INT = (
        SELECT COUNT(*)
        FROM #CT_Tables
        ),
    @tablename VARCHAR(255);

WHILE (@start <= @end)
BEGIN
    -- Fetch the table to be cleaned up
    SELECT @tablename = TableName
    FROM #CT_Tables
    WHERE TableID = @start

    -- Execute the manual cleanup stored procedure
    EXEC sp_flush_CT_internal_table_on_demand @tablename

    -- Increment the counter
    SET @start = @start + 1;
END

DROP TABLE #CT_Tables;

Contenuto correlato

• About Change Tracking (SQL Server)
• Funzioni di rilevamento delle modifiche (Transact-SQL)
• Stored procedure di rilevamento modifiche (Transact-SQL)
• Tabelle di rilevamento modifiche (Transact-SQL)
• Visualizzare le informazioni sullo spazio allocato ai dati e ai log per un database
• Risolvere i problemi di utilizzo elevato della CPU in SQL Server