Administrar e monitorar a captura de dados de alteração (SQL Server)

Este tópico descreve como administrar e monitorar a captura de dados de alteração.

Trabalho 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 os valores configurados para maxtrans, maxscans, contínuo e pollinginterval para o trabalho de captura de msdb.dbo.cdc_jobs. Estes valores configurados são passados como parâmetros ao procedimento armazenado sp_cdc_scan. Ele é usado para invocar sp_replcmds para executar a verificação 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 verificação, o número de transações a serem processadas atingir esse limite, nenhuma transação adicional será incluída na verificação atual. Depois que um ciclo de verificação for concluído, o número de transações que foram processadas sempre será menor ou igual a maxtrans.

Parâmetro maxscans

O parâmetro maxscans especifica o número máximo de ciclos de varredura que são tentados para esgotar o log antes de retornar (contínuo = 0) ou executar um waitfor (contínuo = 1).

Parâmetro contínuo

O parâmetro contínuo controla se sp_cdc_scan abre mão do controle após esvaziar o log ou executar o número máximo de ciclos de verificação (modo de disparo único). Ele também controla se sp_cdc_scan continua sendo executado até ser interrompido explicitamente (modo contínuo).

Modo de Tiro Único

Em um modo de captura, o trabalho de captura solicita sp_cdc_scan executar até maxtrans scans para tentar drenar o log e retornar. Todas as transações além de maxtrans presentes no log serão processadas em verificações posteriores.

O modo único é usado em testes controlados, onde o volume de transações a serem processadas é conhecido, e há vantagens pelo fato de que o trabalho fecha automaticamente quando é concluído. Modo de captura única não é recomendado para uso em produção. Isso ocorre porque o sistema depende da programação de tarefas para gerenciar a frequência com que o ciclo de verificação é executado.

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

(maxtrans * maxscans) / number of seconds between scans

Mesmo que o tempo necessário para verificar o log e preencher as tabelas de alteração não seja significativamente diferente de 0, a taxa de transferência média do trabalho não deve exceder o valor obtido pela divisão do máximo de transações permitidas por uma verificação única multiplicada pelo máximo de transações permitidas pelo número de segundos entre o processamento do log.

Se o modo de um só disparo fosse usado para regular o escaneamento de log, o número de segundos entre o processamento de log teria que ser regido pelo cronograma de tarefas. Quando esse tipo de comportamento é desejado, executar o trabalho de captura no modo contínuo é uma maneira melhor de gerenciar o reagendamento da verificação de log.

Modo Contínuo e o 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 de log (o intervalo de consulta). Em execução nesse modo, o trabalho de captura permanece ativo, executando WAITFOR entre a varredura de logs.

Capturar configuração de tarefa

Para o trabalho de captura, você pode aplicar lógica adicional para determinar se uma nova verificação é iniciada imediatamente ou se um estado suspenso é imposto antes do início de uma nova verificação, em vez de depender de um intervalo de sondagem fixo. A opção pode se basear simplesmente na hora do dia, talvez impondo estados suspensos muito longos durante horários de pico de atividade e até mudando para um intervalo de sondagem de 0 no fechamento do dia quando é importante concluir o processamento dos dias e preparar-se 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é o meio da noite foram verificadas e depositadas em tabelas de alterações. Isso permite a reinicialização do trabalho de captura por uma reinicialização diária agendada após seu término. Ao substituir a etapa de trabalho entregue que chama sp_cdc_scan por uma chamada para um wrapper escrito pelo usuário para sp_cdc_scan, é possível obter um comportamento altamente personalizado com pouco esforço adicional.

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ções. O mecanismo de limpeza consiste em um trabalho Transact-SQL do SQL Server Agent que é criado quando a primeira tabela de banco de dados está 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.

O trabalho de limpeza é iniciado com a execução do procedimento armazenado sem-parâmetros sp_MScdc_cleanup_job. Este procedimento armazenado começa extraindo a retenção configurada e os valores de limite do trabalho de limpeza de msdb.dbo.cdc_jobs. O valor de retenção é usado para calcular uma nova marca d'água baixa para as tabelas de alterações. O número de minutos especificado é subtraído do valor máximo de tran_end_time na tabela cdc.lsn_time_mapping para obter um novo ponto mínimo de referência expresso 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 a mesma hora de confirmação for compartilhada por várias entradas na tabela, o lsn que corresponde à entrada com o menor lsn é escolhida como a nova marca d'água baixa. Esse valor de lsn é passado para sp_cdc_cleanup_change_tables para remover entradas da tabela de alterações de tabelas de alterações do banco de dados.

Quando uma limpeza é executada, a marca d'água baixa para todas as instâncias de captura é atualizada inicialmente 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 o trabalho de limpeza, a possibilidade personalização está na estratégia usada 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, a nova marca d'água baixa é calculada pela subtração do período de retenção permitido da hora de confirmação da última transação processada. Como os procedimentos de limpeza subjacentes se baseiam no lsn, em vez da hora, qualquer número de estratégias pode ser usado para determinar o menor lsn a 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 seja aplicável ao mesmo lsn para limpar todas as tabelas de alterações do banco de dados, o procedimento de limpeza subjacente também pode ser chamado para limpar no nível de captura da instância.

Monitorar o processo de captura de alterações de dados

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 verificação de log (exceto a linha com uma ID de 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 verificações que retornaram alterações e houver cinco resultados vazios em uma linha, a exibição conterá 11 linhas. A última linha tem um valor de 5 na coluna empty_scan_count. Para determinar sessões que tiveram uma verificação vazia, execute a seguinte consulta:

SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count <> 0

Determinar 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ção é habilitada em um banco de dados, é útil fazer capturas da visão sys.dm_cdc_log_scan_sessions e da visã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 exemplo da exibição de gerenciamento de sys.dm_cdc_log_scan_sessions.

Configuração a 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 configurado na etapa 1, localize a tabela custom_snapshots.cdc_log_scan_data. Essa tabela fornece um instantâneo histórico dos dados das sessões de verificação de log. Esses dados podem ser usados para analisar a latência, a taxa de transferência e outras medidas de desempenho ao longo do tempo.

Consulte Também

Rastrear alterações de dados (SQL Server)
Sobre o change data capture (SQL Server)
Habilitar e desabilitar o Change Data Capture (SQL Server)
Trabalhar com Dados de Mudança (SQL Server)