Administrar e monitorar a captura de dados de alterações

Aplica-se a:SQL ServerInstância Gerenciada de SQL do Azure

Este tópico descreve como administrar e monitorar a captura de dados de alterações para o SQL Server e a Instância Gerenciada de SQL do Azure.

Para o Banco de Dados SQL do Azure, que usa um mecanismo de trabalho diferente, consulte CDC com o Banco de Dados SQL do Azure.

Tarefa de captura

O trabalho de captura é iniciado com a execução do procedimento armazenado sem-parâmetros sp_MScdc_capture_job. Esse procedimento armazenado começa extraindo de msdb.dbo.cdc_jobs os valores configurados para maxtrans, maxscans, continuous e pollinginterval do trabalho de captura. Estes valores configurados são passados como parâmetros ao procedimento armazenado sp_cdc_scan. Isso é usado para invocar sp_replcmds para realizar a varredura de log.

Capturar parâmetros de trabalho

Para entender o comportamento do trabalho de captura, você deve entender como os parâmetros configuráveis são usados pelo sp_cdc_scan.

Parâmetro maxtrans

O parâmetro maxtrans especifica o número máximo de transações que podem ser processadas em um único ciclo de verificação do log. Se, durante a varredura, o número de transações a serem processadas atingir esse limite, nenhuma transação adicional será incluída na varredura atual. Após a conclusão de um ciclo de verificação, o número de transações que foram processadas sempre será menor ou igual ao maxtrans.

Parâmetro maxscans

O parâmetro maxscans especifica o número máximo de ciclos de varredura que são realizados na tentativa de esvaziar o log antes de retornar (continuous = 0) ou de executar um waitfor (continuous = 1).

Parâmetro continuous

O parâmetro continuous controla se sp_cdc_scan renuncia ao controle depois de esgotar o log ou de executar o número máximo de ciclos de varredura (modo de execução única). Também controla se sp_cdc_scan continua sendo executado até ser explicitamente interrompido (modo contínuo).

Modo de disparo único

No modo de execução única, o trabalho de captura solicita que sp_cdc_scan execute até maxscans varreduras para tentar esvaziar o log e retornar. Qualquer transação além de maxtrans presente no log será processada nas verificações posteriores.

O modo de execução única é usado em testes controlados, nos quais o volume de transações a serem processadas é conhecido, e há vantagens em a tarefa ser encerrada automaticamente quando é concluída. O modo monoestável não é recomendado para uso em produção. Isso ocorre porque ele depende da agenda de trabalho para gerenciar a frequência com que o ciclo de verificação é executado.

Ao executar no modo de execução única, você pode calcular um limite superior da taxa de transferência esperada do trabalho de captura, expresso em transações por segundo, por meio do seguinte cálculo:

(maxtrans * maxscans) / number of seconds between scans

Mesmo que o tempo necessário para examinar o log e preencher as tabelas de alterações não fosse significativamente diferente de 0, a taxa de transferência média da tarefa não poderia exceder o valor obtido dividindo-se o número máximo de transações permitidas para uma única varredura, multiplicado pelo número máximo de varreduras permitidas, pelo número de segundos entre os processamentos do log.

Se o modo de execução única fosse usado para regular a varredura dos logs, o número de segundos entre o processamento dos logs teria de ser determinado pelo agendamento da tarefa. Quando esse tipo de comportamento é desejado, executar o trabalho de captura no modo contínuo é a melhor maneira de reprogramar a verificação do log.

Modo contínuo e intervalo de sondagem

No modo contínuo, o trabalho de captura solicita a execução contínua de sp_cdc_scan. Isso permite que o procedimento armazenado gerencie seu próprio loop de espera, fornecendo não apenas maxtrans e maxscans, mas também um valor para o número de segundos entre o processamento do log (o intervalo de sondagem). No modo contínuo, o trabalho de captura permanece ativo, executando um WAITFOR entre a varredura de logs.

Personalização da tarefa de captura

Para a tarefa de captura, você pode aplicar uma lógica adicional para determinar se uma nova varredura começa imediatamente ou se é aplicada uma pausa antes de iniciar uma nova varredura, em vez de depender de um intervalo fixo de sondagem. A escolha pode ser baseada simplesmente na hora do dia, talvez impondo intervalos de espera muito longos durante os períodos de pico de atividade e até mudando para um intervalo de sondagem de 0 no fim do dia, quando é importante concluir o processamento do dia e se preparar para as execuções noturnas. O progresso do processo de captura também pode ser monitorado para determinar quando todas as transações confirmadas até a meia-noite foram verificadas e depositadas nas tabelas de alterações. Isso permite que o trabalho de captura seja encerrado, para ser reiniciado por uma reinicialização diária agendada. Para personalizar o comportamento, você pode substituir a etapa do trabalho que chama sp_cdc_scan por uma chamada para um wrapper escrito pelo usuário para sp_cdc_scan.

Trabalho de limpeza

Esta seção fornece informações sobre como o trabalho de limpeza do Change Data Capture funciona.

Estrutura do trabalho de limpeza

A captura de dados de alteração usa uma estratégia de limpeza baseada em retenção para gerenciar o tamanho da tabela de alteração. No SQL Server e na Instância Gerenciada de SQL do Azure, o mecanismo de limpeza consiste em um trabalho Transact-SQL do SQL Server Agent que é criado quando a primeira tabela do banco de dados é habilitada. Um único trabalho de limpeza trata da limpeza de todas as tabelas de alterações de bancos de dados e aplica o mesmo valor de retenção a todas as instâncias de captura definidas.

A tarefa de limpeza é iniciada ao executar o procedimento armazenado sem parâmetros sp_MScdc_cleanup_job. Este procedimento armazenado começa obtendo os valores configurados de retenção e de limite para a tarefa de limpeza a partir de msdb.dbo.cdc_jobs. O valor de retenção é usado para calcular um novo limite inferior para as tabelas de alterações. O número especificado de minutos é subtraído do valor máximo de tran_end_time na tabela cdc.lsn_time_mapping para obter a nova marca de limite inferior, expressa como um valor datetime. A tabela CDC.lsn_time_mapping é usada para converter esse valor de data e hora em um valor correspondente de lsn. Se várias entradas na tabela tiverem o mesmo instante de commit, o lsn correspondente à entrada que tiver o menor lsn será escolhido como a nova marca d’água inferior. Esse valor é passado para sp_cdc_cleanup_change_tables para remover entradas das tabelas de alterações do banco de dados.

Quando uma limpeza é realizada, a marca d'água inferior de todas as instâncias de captura é inicialmente atualizada em uma única transação. Ela tenta remover entradas obsoletas das tabelas de alterações e da tabela cdc.lsn_time_mapping. O valor limite configurável restringe a quantidade de entradas excluídas em qualquer instrução única. A não execução da exclusão de qualquer tabela individual não impedirá a tentativa de operação nas tabelas restantes.

Personalização do trabalho de limpeza

Para a tarefa de limpeza, a possibilidade de personalização está na estratégia utilizada para determinar quais entradas da tabela de alterações devem ser descartadas. A única estratégia com suporte no trabalho de limpeza entregue baseia-se na hora. Nessa situação, o novo limite inferior é calculado subtraindo-se o período de retenção permitido do momento da confirmação da última transação processada. Como os procedimentos de limpeza subjacentes são baseados em lsn, em vez de tempo, é possível usar qualquer estratégia para determinar o menor lsn que deve ser mantido nas tabelas de alterações. Somente alguns deles são estritamente baseados na hora. O conhecimento sobre os clientes, por exemplo, pode ser usado para fornecer um mecanismo seguro se não for possível executar os processos de downstream que requerem acesso às tabelas de alterações. Além disso, embora a estratégia padrão use o mesmo lsn para limpar todas as tabelas de alterações dos bancos de dados, o procedimento de limpeza subjacente também pode ser chamado para executar a limpeza no nível da instância de captura.

Monitorar o processo

O monitoramento do processo de captura de dados de alteração permite determinar se as alterações estão sendo gravadas corretamente e com latência razoável nas tabelas de alteração. O monitoramento também pode ajudar a identificar os erros que podem ocorrer. O SQL Server inclui duas exibições de gerenciamento dinâmico para ajudá-lo a monitorar a captura de dados de alterações: sys.dm_cdc_log_scan_sessions e sys.dm_cdc_errors.

Identificar sessões com conjuntos de resultados vazios

Cada linha em sys.dm_cdc_log_scan_sessions representa uma sessão de varredura de log (exceto a linha com ID 0). Uma sessão de verificação de log é equivalente a uma execução de sp_cdc_scan. Durante uma sessão, a verificação pode retornar alterações ou um resultado vazio. Se o conjunto de resultados estiver vazio, a coluna empty_scan_count em sys.dm_cdc_log_scan_sessions será definida como 1. Se houver conjuntos de resultados vazios consecutivos, como se o trabalho de captura estivesse sendo executado continuamente, a coluna empty_scan_count na última linha existente será incrementada. Por exemplo, se sys.dm_cdc_log_scan_sessions já contiver 10 linhas para varreduras que retornaram alterações e houver cinco resultados vazios em uma linha, a visualização conterá 11 linhas. A última linha tem um valor de 5 na coluna empty_scan_count. Para determinar as sessões que apresentaram uma varredura vazia, execute a seguinte consulta:

SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count <> 0

Determinar a latência

A exibição de gerenciamento sys.dm_cdc_log_scan_sessions inclui uma coluna que registra a latência de cada sessão de captura. Latência é definida como o tempo decorrido entre uma transação que está sendo confirmada em uma tabela de origem e a última transação capturada que está sendo confirmada na tabela de alteração. A coluna de latência só é preenchida para sessões ativas. Para sessões com um valor maior que 0 na coluna empty_scan_count, a coluna de latência é definida como 0. A seguinte consulta retorna a latência média das sessões mais recentes:

SELECT latency FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0

Você pode usar dados de latência para determinar a velocidade com que o processo de captura está processando as transações. Esses dados são muito úteis quando o processo de captura é executado continuamente. Se o processo de captura for executado segundo uma agenda, a latência poderá ser alta devido ao atraso entre a confirmação das transações na tabela de origem e a execução do processo de captura no horário agendado.

Outra medida importante que avalia a eficiência do processo de captura é a taxa de transferência. Representa o número médio de comandos por segundo que são processados durante cada sessão. Para determinar a taxa de transferência de uma sessão, divida o valor da coluna command_count pelo valor da coluna de duração. A seguinte consulta retorna a taxa de transferência média das sessões mais recentes:

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

Usar o coletor de dados para coletar dados de amostragem

O coletor de dados do SQL Server permite que você colete instantâneos de dados de qualquer tabela ou exibição de gerenciamento dinâmico e crie um data warehouse de desempenho. Quando a captura de dados de alterações está habilitada em um banco de dados, é útil tirar instantâneos da visualização sys.dm_cdc_log_scan_sessions e da exibição sys.dm_cdc_errors em intervalos regulares para análise posterior. O procedimento a seguir configura um coletor de dados para coletar dados de amostra da exibição de gerenciamento sys.dm_cdc_log_scan_sessions.

Configuração de coleta de dados

1. Habilite o coletor de dados e configure um data warehouse de gerenciamento. Para obter mais informações, consulte Gerenciar Coleta de Dados.

2. Execute o código a seguir para criar um coletor personalizado para captura de dados de alteração.

   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 @parameters xml;  
   DECLARE @collection_item_id int;  
   
   SELECT @parameters = 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 = @parameters,  
   @collection_item_id = @collection_item_id output;
   
   GO  
   

3. No SQL Server Management Studio, expanda o Gerenciamento e expanda a Coleta de Dados. Clique com o botão direito do mouse no Coletor de Dados de Desempenho CDC e clique em Iniciar Conjunto de Coleta de Dados.

4. No data warehouse que você configurou na etapa 1, localize a tabela custom_snapshots.cdc_log_scan_data. Esta tabela apresenta uma visão geral histórica dos dados de sessões de varredura de logs. Esses dados podem ser usados para analisar a latência, a taxa de transferência e outras medidas de desempenho ao longo do tempo.

Modo de atualização de script

Ao aplicar atualizações cumulativas ou service packs a uma instância, na reinicialização, a instância pode entrar em modo de atualização de script. Nesse modo, o SQL Server pode executar uma etapa para analisar e atualizar tabelas internas de CDA, o que pode resultar na recriação de objetos como índices em tabelas de captura. Dependendo da quantidade de dados envolvidos, esta etapa pode levar algum tempo ou causar um alto uso de log de transações para bancos de dados do CDA habilitados.

Conteúdo relacionado

• Captura de dados de alteração (CDC) com Banco de Dados SQL do Azure
• Rastrear alterações de dados (SQL Server)
• O que é a CDA (captura de dados de alterações)?
• Habilitar e desabilitar a captura de dados de alterações
• Trabalhar com dados de alterações