Amministrare e monitorare Change Data Capture (SQL Server)Administer and Monitor Change Data Capture (SQL Server)

In questo argomento viene descritto come amministrare ed eseguire il monitoraggio dell'acquisizione dati delle modifiche.This topic describes how to administer and monitor change data capture.

Processo di acquisizione Capture Job

Il processo di acquisizione viene avviato eseguendo la stored procedure sp_MScdc_capture_jobsenza parametri.The capture job is initiated by running the parameterless stored procedure sp_MScdc_capture_job. Questa stored procedure estrae prima i valori configurati per maxtrans, maxscans, continuouse pollinginterval necessari per il processo di acquisizione da msdb.dbo.cdc_jobs.This stored procedure starts by extracting the configured values for maxtrans, maxscans, continuous, and pollinginterval for the capture job from msdb.dbo.cdc_jobs. Questi valori configurati vengono poi passati come parametri alla stored procedure sp_cdc_scan,These configured values are then passed as parameters to the stored procedure sp_cdc_scan. usata per chiamare sp_replcmds con cui eseguire l'analisi del log.This is used to invoke sp_replcmds to perform the log scan.

Parametri del processo di acquisizioneCapture Job Parameters

Per acquisire familiarità con il comportamento del processo di acquisizione, è necessario comprendere come i parametri configurabili vengono usati dalla stored procedure sp_cdc_scan.To understand capture job behavior, you must understand how the configurable parameters are used by sp_cdc_scan.

Parametro maxtransmaxtrans Parameter

Il parametro maxtrans specifica il numero massimo di transazioni che possono essere elaborate in un singolo ciclo di analisi del log.The maxtrans parameter specifies the maximum number of transactions that can be processed in a single scan cycle of the log. Se durante l'analisi il numero di transazioni da elaborare raggiunge tale limite, nell'analisi corrente non viene inclusa alcuna transazione aggiuntiva.If, during the scan, the number of transactions to be proessed reaches this limit, no additional transactions are included in the current scan. Al termine di un ciclo di analisi, il numero di transazioni elaborate sarà sempre minore o uguale a maxtrans.After a scan cycle is complete, the number of transactions that were processed will always be less than or equal to maxtrans.

Parametro maxscansmaxscans Parameter

Il parametro maxscans specifica il numero massimo di tentativi di cicli di analisi per svuotare il log prima dell’uscita (parametro continuous = 0) o dell'esecuzione di un'istruzione WAITFOR (parametro continuous = 1).The maxscans parameter specifies the maximum number of scan cycles that are attempted to drain the log before either returning (continuous = 0) or executing a waitfor (continuous = 1).

Parametro continouscontinous Parameter

Il parametro continuous determina se sp_cdc_scan cede il controllo in seguito allo svuotamento del log o all'esecuzione del numero massimo di cicli di analisi (modalità di esecuzione singola).The continuous parameter controls whether sp_cdc_scan relinquishes control in after either draining the log or executing the maximum number of scan cycles (one shot mode). Determina anche se l'esecuzione di sp_cdc_scan deve continuare fino a quando non viene arrestata in modo esplicito (modalità continua).It also controles whether sp_cdc_scan continues to run until explicitly stopped (continuous mode).

Modalità di esecuzione singolaOne Shot Mode

In modalità di esecuzione singola il processo di acquisizione chiede a sp_cdc_scan di eseguire un numero di analisi pari al valore indicato dal parametro maxtrans per provare a svuotare il log ed uscire.In one shot mode, the capture job requests sp_cdc_scan to perform up to maxtrans scans to try to drain the log and return. Qualsiasi transazione aggiuntiva rispetto al parametro maxtrans presente nel log verrà elaborata nelle analisi successive.Any transactions in addition to maxtrans that are present in the log will be processed in later scans.

La modalità di esecuzione singola viene utilizzata in test controllati, in cui è noto il volume di transazioni da elaborare e in cui la chiusura automatica del processo al suo completamento costituisce un aspetto vantaggioso.One shot mode is used in controlled tests, where the volume of transactions to be processed is known, and there are advantages to the fact that the job closes automatically on when it is finished. L'utilizzo della modalità di esecuzione singola non è consigliabile in un ambiente di produzione,One shot mode is not recommended for production use. in quanto t si basa sulla pianificazione del processo per gestire la frequenza di esecuzione del ciclo di analisi.This is because t relies on the job schedule to manage how frequently the scan cycle is run.

In caso di esecuzione in modalità di esecuzione singola, è possibile calcolare un limite superiore per la velocità effettiva del processo di acquisizione, espressa in transazioni al secondo, utilizzando il calcolo seguente:When running in one shot mode, you can compute an upper bound on expected throughput of the capture job, expressed in transactions per second by using the following computation:

(maxtrans * maxscans) / number of seconds between scans

Anche se il tempo necessario per eseguire l'analisi del log e popolare le tabelle delle modifiche non differisce notevolmente da 0, la velocità effettiva media del processo non può superare il valore ottenuto dividendo per il numero di secondi che separano le elaborazioni del log il prodotto del numero massimo di transazioni consentite per una singola analisi per il numero massimo di analisi consentite.Even if the time that is required to scan the log and populate the change tables were not significantly different from 0, the average throughput of the job could not exceed the value obtained by dividing the maximum allowed transactions for a single scan multiplied by the maximum allowed scans by the number of seconds separating log processing.

Se è necessario utilizzare la modalità di esecuzione singola per regolare l'analisi del log, il numero di secondi tra le elaborazioni del log deve essere determinato dalla pianificazione del processo.If one shot mode were to be used to regulate log scanning, the number of seconds between log processing would have to be governed by the job schedule. Se si desidera ottenere questo tipo di comportamento, l'esecuzione del processo di acquisizione in modalità continua rappresenta un metodo migliore per gestire la ripianificazione dell'analisi del log.When this kind of behavior is desired, running the capture job in continuous mode is a better way to manage rescheduling the log scan.

Modalità continua e intervallo di pollingContinuous Mode and the Polling Interval

In modalità continua il processo di acquisizione richiede l'esecuzione continua di sp_cdc_scan .In continuous mode, the capture job requests that sp_cdc_scan run continuously. In questo modo, la stored procedure può gestire il proprio ciclo di attesa fornendo non solo i valori per maxtrans e maxscans ma anche un valore per il numero di secondi tra le elaborazioni del log (intervallo di polling).This lets the stored procedure manage its own wait loop by providing not only for maxtrans and maxscans but also a value for the number of seconds between log processing (the polling interval). Dal momento che viene eseguito in questa modalità, il processo di acquisizione rimane attivo, eseguendo un'istruzione WAITFOR tra le analisi del log.Running in this mode, the capture job remains active, executing a WAITFOR between log scanning.

Nota

Quando il valore dell'intervallo di polling è maggiore di 0, il limite superiore relativo alla velocità effettiva per il processo ricorrente in modalità di esecuzione singola viene applicato anche all'operazione del processo in modalità continua.When the value of the polling interval is greater than 0, the same upper limit on throughput for the recurring one shot job also applies to the job operation in continuous mode. Di conseguenza, la divisione di (maxtrans * maxscans) per un intervallo di polling diverso da zero comporterà l'applicazione di un limite superiore per il numero medio di transazioni che possono essere elaborate dal processo di acquisizione.That is, (maxtrans * maxscans) divided by a nonzero polling interval will put an upper bound on the average number of transactions that can be processed by the capture job.

Personalizzazione del processo di acquisizioneCapture Job Customization

Per il processo di acquisizione è possibile applicare logica aggiuntiva per determinare se una nuova analisi debba iniziare immediatamente o se venga imposta una sospensione prima di avviare una nuova analisi anziché basarsi su un intervallo di polling fisso.For the capture job, you can apply additional logic to determine whether a new scan begins immediately or whether a sleep is imposed before it starts a new scan instead of rely on a fixed polling interval. La scelta può essere basata solo sull'ora del giorno, applicando eventualmente sospensioni prolungate durante i periodi di attività massima, e prevedere anche il passaggio a un intervallo di polling pari a zero alla fine del giorno quando è importante completare l'elaborazione giornaliera e preparare le esecuzioni notturne.The choice could be based merely on time of the day, perhaps enforcing very long sleeps during peak activity times, and even moving to a polling interval of 0 at close of day when it is important to complete the days processing and prepare for nightly runs. Può inoltre essere necessario monitorare lo stato del processo di acquisizione per determinare il momento in cui tutte le transazioni di cui è stato eseguito il commit entro mezzanotte sono state sottoposte ad analisi e inserite nelle tabelle delle modifiche.Capture process progress could also be monitored to determine when all transactions committed by mid-night had been scanned and deposited in change tables. Ciò consente il completamento del processo di acquisizione, che verrà riavviato in base a una pianificazione giornaliera.This lets the capture job end, to be restarted by a scheduled daily restart. Sostituendo la chiamata a sp_cdc_scan da parte del passaggio del processo recapitato con una chiamata a un wrapper scritto dall'utente per sp_cdc_scan, è possibile ottenere un comportamento molto personalizzato con un minimo sforzo aggiuntivo.By replacing the delivered job step calling sp_cdc_scan with a call to a user written wrapper for sp_cdc_scan, highly customized behavior can be obtained with little additional effort.

Processo di pulizia Cleanup Job

In questa sezione vengono fornite informazioni sul funzionamento del processo di pulizia di Change Data Capture.This section provides information about how the change data capture cleanup job works.

Struttura del processo di puliziaStructure of the Cleanup Job

Change Data Capture utilizza una strategia di pulizia basata su memorizzazione per gestire le dimensioni delle tabelle delle modifiche.Change data capture uses a retention based cleanup strategy to manage change table size. Il meccanismo di pulizia è costituito da un processo SQL ServerSQL Server di Transact-SQLTransact-SQL Agent creato durante l'abilitazione della prima tabella di database.The cleanup mechanism consists of a SQL ServerSQL Server Agent Transact-SQLTransact-SQL job that is created when the first database table is enabled. Un singolo processo di pulizia gestisce la pulizia per tutte le tabelle delle modifiche del database e applica lo stesso valore di memorizzazione a tutte le istanze di acquisizione definite.A single cleanup job handles cleanup for all database change tables and applies the same retention value to all defined capture instances.

Il processo di pulizia viene avviato eseguendo la stored procedure sp_MScdc_cleanup_jobsenza parametri.The cleanup job is initiated by running the parameterless stored procedure sp_MScdc_cleanup_job. Questa stored procedure estrae prima i valori di memorizzazione e soglia configurati per il processo di pulizia da msdb.dbo.cdc_jobs.This stored procedure starts by extracting the configured retention and threshold values for the cleanup job from msdb.dbo.cdc_jobs. Il valore di memorizzazione viene utilizzato per calcolare un nuovo limite minimo per le tabelle delle modifiche.The retention value is used to compute a new low watermark for the change tables. Il numero specificato di minuti viene sottratto dal valore msdb.dbo.cdc_jobs massimo della tabella cdc.lsn_time_mapping per ottenere il nuovo valore minimo espresso come valore datetime.The specified number of minutes is substracted from the maximum tran_end_time value from the cdc.lsn_time_mapping table to obtain the new low water mark expressed as a datetime value. La tabella CDC.lsn_time_mapping viene quindi usata per convertire questo valore datetime in un valore lsn corrispondente.The CDC.lsn_time_mapping table is then used to convert this datetime value to a corresponding lsn value. Se più voci della tabella condividono la stessa ora di esecuzione del commit, come nuovo limite minimo viene scelto il valore lsn corrispondente alla voce associata al valore lsn minore.If the same commit time is shared by multiple entries in the table, the lsn that corresponds to the entry that has the smallest lsn is chosen as the new low watermark. Il valore lsn viene passato a sp_cdc_cleanup_change_tables per rimuovere le voci dalle tabelle delle modifiche del database.This lsn value is passed to sp_cdc_cleanup_change_tables to remove change table entries from the database change tables.

Nota

L'utilizzo dell'ora di esecuzione del commit della transazione recente come base per il calcolo del nuovo limite minimo offre il vantaggio di poter mantenere le modifiche nelle tabelle delle modifiche per il tempo specificato.The advantage of using the commit time of the recent transaction as the base for computing the new low watermark is that it lets the changes remain in change tables for the specified time. Questa situazione si verifica anche quando è in esecuzione il processo di acquisizione sottostante.This happens even when the capture process is running behind. Tutte le voci associate alla stessa ora del commit del limite minimo corrente continuano a essere rappresentate all'interno delle tabelle delle modifiche scegliendo il valore lsn minore associato all'ora del commit condivisa per il limite minimo effettivo.All entries that have the same commit time as the current low watermark continue to be represented within the change tables by choosing the smallest lsn that has the shared commit time for the actual low watermark.

Quando viene eseguita una pulizia, il limite minimo per tutte le istanze di acquisizione viene inizialmente aggiornato in una singola transazione.When a cleanup is performed, the low watermark for all capture instances is initially updated in a single transaction. Viene quindi effettuato il tentativo di rimozione delle voci obsolete dalle tabelle delle modifiche e dalla tabella cdc.lsn_time_mapping.It then tries to remove obsolete entries from the change tables and the cdc.lsn_time_mapping table. Il valore soglia configurabile limita il numero di voci eliminate in ogni singola istruzione.The configurable threshold value limits how many entries are deleted in any single statement. La mancata esecuzione dell'eliminazione in una singola tabella non impedirà il tentativo di eliminazione nelle altre.Failure to perform the delete on any individual table will not prevent the operation from being attempted on the remaining tables.

Personalizzazione del processo di puliziaCleanup Job Customization

Per il processo di pulizia, la possibilità di personalizzazione consiste nella strategia utilizzata per determinare le voci delle tabelle delle modifiche da ignorare.For the cleanup job, the possibility for customization is in the strategy used to determine which change table entries are to be discarded. L'unica strategia supportata nel processo di pulizia è basata sul tempo.The only supported strategy in the delivered cleanup job is a time-based one. In questa situazione, il nuovo limite minimo viene calcolato sottraendo il periodo di memorizzazione consentito dall'ora di esecuzione del commit dell'ultima transazione elaborata.In that situation, the new low watermark is computed by subtracting the allowed retention period from the commit time of the last transaction processed. Dal momento che le procedure di pulizia sottostanti sono basate su lsn anziché sul tempo, è possibile usare qualsiasi strategia per determinare il valore lsn più basso da mantenere nelle tabelle di modifica.Beacuse the underlying cleanup procedures are based on lsn instead of time, any number of strategies can be used to determine the smallest lsn to keep in the change tables. Solo alcuni di questi valori sono rigorosamente basati sul tempo.Only some of these are strictly time-based. È possibile, ad esempio, utilizzare le informazioni sui client come valida alternativa in caso di mancata esecuzione dei processi a valle che richiedono l'accesso alle tabelle delle modifiche.Knowledge about the clients, for example, could be used to provide a failsafe if downstream processes that require access to the change tables cannot run. Anche se la strategia predefinita applica lo stesso valore lsn per pulire tutte le tabelle delle modifiche dei database, è possibile chiamare la procedura di pulizia sottostante anche per eseguire la pulizia a livello di istanza di acquisizione.Also, although the default strategy applies the same lsn to clean up all the databases’ change tables, the underlying cleanup procedure, can also be called to clean up at the capture instance level.

Monitoraggio del processo Change Data Capture Monitor the Change Data Capture Process

Il monitoraggio del processo Change Data Capture consente di determinare se le modifiche vengono scritte correttamente e con una latenza ragionevole nelle tabelle delle modifiche.Monitoring the change data capture process lets you determine if changes are being written correctly and with a reasonable latency to the change tables. L'esecuzione il monitoraggio può consentire anche di identificare gli errori che si potrebbero verificare.Monitoring can also help you to identify any errors that might occur. SQL ServerSQL Server sono incluse due DMV per monitorare Change Data Capture: sys.dm_cdc_log_scan_sessions e sys.dm_cdc_errors. includes two dynamic management views to help you monitor change data capture: sys.dm_cdc_log_scan_sessions and sys.dm_cdc_errors.

Identificazione di sessioni con set di risultati vuotiIdentify Sessions with Empty Result Sets

Ogni riga in sys.dm_cdc_log_scan_sessions rappresenta una sessione di analisi del log, ad eccezione della riga con ID 0.Every row in sys.dm_cdc_log_scan_sessions represents a log scan session (except the row with an ID of 0). Una sessione di analisi del log è equivalente a un'esecuzione di sp_cdc_scan.A log scan session is equivalent to one execution of sp_cdc_scan. Durante una sessione, l'analisi può restituire modifiche o un risultato vuoto.During a session, the scan can either return changes or return an empty result. Se il set di risultati è vuoto, la colonna empty_scan_count in sys.dm_cdc_log_scan_sessions è impostata su 1.If the result set is empty, the empty_scan_count column in sys.dm_cdc_log_scan_sessions is set to 1. Se si ottengono set di risultati vuoti consecutivi, ad esempio nel caso in cui il processo di acquisizione viene eseguito in modo continuo, il valore di empty_scan_count nell'ultima riga esistente viene incrementato.If there are consecutive empty result sets, such as if the capture job is running continuously, the empty_scan_count in the last existing row is incremented. Se ad esempio sys.dm_cdc_log_scan_sessions contiene già 10 righe per analisi che hanno restituito modifiche e in una riga sono presenti cinque risultati vuoti, la vista contiene 11 righe.For example, if sys.dm_cdc_log_scan_sessions already contains 10 rows for scans that returned changes and there are five empty results in a row, the view contains 11 rows. L'ultima riga presenta il valore 5 nella colonna empty_scan_count.The last row has a value of 5 in the empty_scan_count column. Per determinare le sessioni per cui l'analisi è risultata vuota, eseguire la query seguente:To determine sessions that had an empty scan, run the following query:

SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count <> 0

Determinazione della latenzaDetermine Latency

La vista a gestione dinamica sys.dm_cdc_log_scan_sessions include una colonna in cui viene registrata la latenza per ogni sessione di acquisizione.The sys.dm_cdc_log_scan_sessions management view includes a column that records the latency for each capture session. Con il termine latenza si definisce il tempo che trascorre tra una transazione, il cui commit viene eseguito in una tabella di origine e l'ultima transazione acquisita, il cui commit viene eseguito nella tabella delle modifiche.Latency is defined as the elapsed time between a transaction being committed on a source table and the last captured transaction being committed on the change table. La colonna della latenza viene popolata solo per le sessioni attive.The latency column is populated only for active sessions. Per le sessioni con un valore maggiore di 0 nella colonna empty_scan_count, la colonna della latenza è impostata su 0.For sessions with a value greater than 0 in the empty_scan_count column, the latency column is set to 0. La query seguente restituisce la latenza media per le sessioni più recenti:The following query returns the average latency for the most recent sessions:

SELECT latency FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0

È possibile utilizzare i dati della latenza da determinare la velocità con la quale il processo di acquisizione elabora le transazioni.You can use latency data to determine how fast or slow the capture process is processing transactions. Questi dati dimostrano tutta la loro utilità quando il processo di acquisizione viene eseguito in modo continuo.This data is most useful when the capture process is running continuously. Se il processo di acquisizione viene eseguito in base a una pianificazione, la latenza può aumentare a causa del tempo che trascorre tra le transazioni di cui viene eseguito il commit nella tabella di origine, con il processo di acquisizione che continua a essere eseguito in base alla pianificazione.If the capture process is running on a schedule, latency can be high because of the lag between transactions being committed on the source table and the capture process running at its scheduled time.

Un altro importante indicatore di prestazioni del processo di acquisizione è la velocità effettiva.Another important measure of capture process efficiency is throughput. Si tratta del numero medio di comandi al secondo elaborati durante ogni sessione.This is the average number of commands per second that are processed during each session. Per determinare la velocità effettiva di una sessione, dividere il valore presente nella colonna command_count per il valore presente nella colonna della durata.To determine the throughput of a session, divide the value in the command_count column by the value in the duration column. La query seguente restituisce la velocità effettiva media per le sessioni più recenti:The following query returns the average throughput for the most recent sessions:

SELECT command_count/duration AS [Throughput] FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0

Utilizzo dell'agente di raccolta dati per raccogliere dati di campionamentoUse Data Collector to Collect Sampling Data

L'agente di raccolta dati disponibile in SQL ServerSQL Server consente di raccogliere snapshot di dati da qualsiasi tabella o DMV e compilare un data warehouse contenente dati relativi alle prestazioni.The SQL ServerSQL Server data collector lets you collect snapshots of data from any table or dynamic management view and build a performance data warehouse. Quando Change Data Capture è abilitato in un database, è possibile raccogliere snapshot delle viste sys.dm_cdc_log_scan_sessions e sys.dm_cdc_errors a intervalli regolari per poterli analizzare.When change data capture is enabled on a database, it is useful to take snapshots of the sys.dm_cdc_log_scan_sessions view and the sys.dm_cdc_errors view at regular intervals for later analysis. Nella procedura seguente viene impostato un agente di raccolta dati per raccogliere dati di campionamento dalla vista a gestione dinamica sys.dm_cdc_log_scan_sessions.The following procedure sets up a data collector for collecting sample data from the sys.dm_cdc_log_scan_sessions management view.

Configurazione della raccolta datiConfiguring Data Collection

  1. Abilitare l'agente di raccolta dati e configurare un data warehouse di gestione.Enable data collector and configure a management data warehouse. Per altre informazioni, vedere Gestire raccolta dati.For more information, see Manage Data Collection.

  2. Eseguire il codice seguente per creare un agente di raccolta personalizzato per Change Data Capture.Execute the following code to create a custom collector for change data capture.

    USE msdb;  
    
    DECLARE @schedule_uid uniqueidentifier;  
    
    -- Collect and upload data every 5 minutes  
    SELECT @schedule_uid = (  
    SELECT schedule_uid from sysschedules_localserver_view   
    WHERE name = N'CollectorSchedule_Every_5min')  
    
    DECLARE @collection_set_id int;  
    
    EXEC dbo.sp_syscollector_create_collection_set  
    @name = N' CDC Performance Data Collector',  
    @schedule_uid = @schedule_uid,          
    @collection_mode = 0,                   
    @days_until_expiration = 30,                
    @description = N'This collection set collects CDC metadata',  
    @collection_set_id = @collection_set_id output;  
    
    -- Create a collection item using statistics from   
    -- the change data capture dynamic management view.  
    DECLARE @paramters xml;  
    DECLARE @collection_item_id int;  
    
    SELECT @paramters = CONVERT(xml,   
        N'<TSQLQueryCollector>  
            <Query>  
              <Value>SELECT * FROM sys.dm_cdc_log_scan_sessions</Value>  
              <OutputTable>cdc_log_scan_data</OutputTable>  
            </Query>  
          </TSQLQueryCollector>');  
    
    EXEC dbo.sp_syscollector_create_collection_item  
    @collection_set_id = @collection_set_id,  
    @collector_type_uid = N'302E93D1-3424-4BE7-AA8E-84813ECF2419',  
    @name = ' CDC Performance Data Collector',  
    @frequency = 5,   
    @parameters = @paramters,  
    @collection_item_id = @collection_item_id output;  
    
    GO  
    
  3. In SQL Server Management StudioSQL Server Management Studioespandere Gestione, quindi espandere Raccolta dati.In SQL Server Management StudioSQL Server Management Studio, expand Management, and then expand Data Collection. Fare clic con il pulsante destro del mouse su CDC Performance Data Collector, quindi fare clic su Avvia set di raccolta dati.Right click CDC Performance Data Collector, and then click Start Data Collection Set.

  4. Nel data warehouse configurato nel passaggio 1 trovare la tabella custom_snapshots.cdc_log_scan_data.In the data warehouse you configured in step 1, locate the table custom_snapshots.cdc_log_scan_data. In questa tabella viene fornito uno snapshot cronologico di dati dalle sessioni di analisi del log.This table provides a historical snapshot of data from log scan sessions. Questi dati possono essere utilizzati per analizzare nel corso del tempo latenza, velocità effettiva e altri indicatori di prestazioni.This data can be used to analyze latency, throughput, and other performance measures over time.

Vedere ancheSee Also

Rilevare le modifiche ai dati (SQL Server) Track Data Changes (SQL Server)
Informazioni su Change Data Capture (SQL Server) About Change Data Capture (SQL Server)
Abilitare e disabilitare Change Data Capture (SQL Server) Enable and Disable Change Data Capture (SQL Server)
Usare i dati delle modifiche (SQL Server)Work with Change Data (SQL Server)