Administrar e monitorizar a captura de dados das alterações

Aplica-se a:SQL ServerAzure SQL Managed Instance

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

Para a Base de Dados SQL do Azure, que utiliza um mecanismo de trabalho diferente, consulte CDC com a Base de Dados SQL do Azure.

Capturar trabalho

O trabalho de captura é iniciado executando o procedimento armazenado sem parâmetros sp_MScdc_capture_job. Este procedimento armazenado começa por extrair os valores configurados para maxtrans, maxscans, continuouse pollinginterval do trabalho de captura de msdb.dbo.cdc_jobs. Esses valores configurados são então passados como parâmetros para o procedimento armazenado sp_cdc_scan. Isso é 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 por 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. Após a conclusão de um ciclo de verificação, o número de transações que foram processadas será sempre 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 drenar o log antes de retornar (contínuo = 0) ou executar uma espera (contínuo = 1).

parâmetro continuous

O parâmetro continuous controla se sp_cdc_scan renuncia ao controle depois de drenar o log ou executar o número máximo de ciclos de verificação (modo one-shot). Ele também controla se sp_cdc_scan continua a ser executado até ser explicitamente interrompido (modo contínuo).

Modo One-shot

No modo de captura única, o trabalho de captura sp_cdc_scan solicita a execução de até maxscans verificações para tentar drenar o log e retornar. Quaisquer transações além de maxtrans que estejam presentes no log serão processadas em verificações posteriores.

O modo one-shot é usado em testes controlados, onde o volume de transações a serem processadas é conhecido, e há vantagens no fato de que o trabalho fecha automaticamente quando é concluído. O modo one-shot não é recomendado para uso em produção. Isso ocorre porque ele depende do cronograma de trabalho para gerenciar a frequência com que o ciclo de verificação é executado.

Ao executar no modo one-shot, você pode calcular um limite superior na 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 fosse significativamente diferente de 0, a taxa de transferência média do trabalho não poderia exceder o valor obtido dividindo o máximo de transações permitidas para uma única verificação multiplicado pelo máximo permitido de verificações pelo número de segundos que separam o processamento do log.

Se o modo de execução única fosse usado para regular a análise de registos, o número de segundos entre o processamento de log teria que ser definido pela programação de tarefas. Quando esse tipo de comportamento é desejado, executar o processo de captura no modo contínuo é uma maneira melhor de reagendar a análise do log.

Modo contínuo e intervalo de sondagem

No modo contínuo, o trabalho de captura solicita que o sp_cdc_scan seja executado continuamente. 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 uma WAITFOR entre o escaneamento de logs.

Capturar personalização de tarefas

Para o trabalho de captura, você pode aplicar lógica adicional para determinar se uma nova verificação começa imediatamente ou se uma suspensão é imposta antes de iniciar uma nova verificação, em vez de depender de um intervalo de sondagem fixo. A escolha poderia ser baseada apenas na hora do dia, talvez impondo dormidas muito longas durante os horários de pico de atividade, e até mesmo mudando para um intervalo de votação de 0 no final do dia, quando é importante completar o processamento de dias e se preparar para corridas noturnas. O progresso do processo de captura também pode ser monitorado para determinar quando todas as transações confirmadas até à meia-noite foram digitalizadas e depositadas em tabelas de alterações. Isso permite que o trabalho de captura termine, para ser reiniciado por uma reinicialização diária agendada. Para personalizar o comportamento, você pode substituir a etapa de 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 funciona o processo de limpeza da captura de dados de alteração.

Estrutura do trabalho de limpeza

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

O trabalho de limpeza é iniciado executando o procedimento armazenado sem parâmetros sp_MScdc_cleanup_job. Este procedimento armazenado começa por extrair os valores de retenção e limite configurados para o trabalho de limpeza do elemento msdb.dbo.cdc_jobs. O valor de retenção é usado para calcular um novo limite mínimo para as tabelas de alterações. O número especificado de minutos é subtraído do valor máximo de tran_end_time da tabela cdc.lsn_time_mapping para obter a nova marca de água baixa expressa como um valor de data/hora. A tabela CDC.lsn_time_mapping é então usada para converter este valor de datetime em um valor correspondente de lsn. Se o mesmo tempo de confirmação for compartilhado por várias entradas na tabela, a lsn que corresponde à entrada que tem o menor lsn será escolhida como a nova marca d'água baixa. Esse valor de lsn é passado para sp_cdc_cleanup_change_tables para remover entradas existentes nas tabelas de alteração do banco de dados.

Quando uma limpeza é executada, a marca d'água baixa para todas as instâncias de captura é inicialmente atualizada em uma única transação. Em seguida, tenta remover entradas obsoletas das tabelas de alteração e da tabela cdc.lsn_time_mapping. O valor limite configurável limita quantas entradas são excluídas em uma única instrução. A falha ao executar a exclusão em qualquer tabela individual não impedirá que a operação seja tentada nas tabelas restantes.

Personalização do trabalho de limpeza

Para o trabalho de limpeza, a possibilidade de personalização está na estratégia usada para determinar quais entradas da tabela de alterações devem ser descartadas. A única estratégia apoiada no trabalho de limpeza entregue é baseada no tempo. Nessa situação, o novo nível mínimo é calculado subtraindo o período de retenção permitido do tempo de confirmação da última transação processada. Como os procedimentos de limpeza subjacentes são baseados em lsn em vez de tempo, pode-se usar qualquer número de estratégias para determinar o menor lsn a ser mantido nas tabelas de alterações. Apenas algumas delas são estritamente baseadas no tempo. O conhecimento sobre os clientes, por exemplo, pode ser usado para fornecer um sistema à prova de falhas se os processos a jusante que exigem acesso às tabelas de alteração não puderem ser executados. Além disso, embora a estratégia padrão aplique o mesmo lsn para limpar todas as tabelas de alteração dos bancos de dados, o procedimento de limpeza subjacente também pode ser invocado para realizar a limpeza ao nível das instâncias de captura.

Monitorizar 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 uma latência razoável para as tabelas de alterações. O monitoramento também pode ajudá-lo a identificar quaisquer erros que possam ocorrer. O SQL Server inclui duas visões de gerenciamento dinâmico para ajudá-lo a monitorizar a captura de dados de alteração: sys.dm_cdc_log_scan_sessions e sys.dm_cdc_errors.

Identificar sessões com conjuntos de resultados vazios

Cada linha no sys.dm_cdc_log_scan_sessions representa uma sessão de verificação de log (exceto a linha com um 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 retornar um resultado vazio. Se o conjunto de resultados estiver vazio, a coluna empty_scan_count no sys.dm_cdc_log_scan_sessions será definida como 1. Se houver conjuntos de resultados vazios consecutivos, como se o trabalho de captura estiver sendo executado continuamente, o empty_scan_count na última linha existente será incrementado. Por exemplo, se sys.dm_cdc_log_scan_sessions já contém 10 linhas para verificações que retornaram alterações e há cinco resultados vazios em uma linha, a exibição contém 11 linhas. A última linha tem um valor de 5 na coluna empty_scan_count. Para determinar as sessões que tinham 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 de sys.dm_cdc_log_scan_sessions inclui uma coluna que registra a latência de cada sessão de captura. A latência é definida como o tempo decorrido entre uma transação sendo confirmada em uma tabela de origem e a última transação capturada sendo confirmada na tabela de alterações. A coluna de latência é preenchida apenas 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 consulta a seguir 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 quão rápido ou lento o processo de captura está processando transações. Esses dados são mais úteis quando o processo de captura está sendo executado continuamente. Se o processo de captura estiver sendo executado em um cronograma, a latência pode ser alta devido ao atraso entre as transações que estão sendo confirmadas na tabela de origem e o processo de captura em execução em seu horário agendado.

Outra medida importante da eficiência do processo de captura é o rendimento. Este é 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 na coluna command_count pelo valor na coluna de duração. A consulta a seguir 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 coletor de dados para coletar dados de amostragem

O coletor de dados do SQL Server permite coletar instantâneos de dados de qualquer exibição de gerenciamento dinâmico ou de tabela e criar um data warehouse de desempenho. Quando a captura de dados de alteração está habilitada em um banco de dados, é útil tirar instantâneos da visualização sys.dm_cdc_log_scan_sessions e da visualizaçã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 vista de gestão de sys.dm_cdc_log_scan_sessions.

Configurando a coleta de dados

1. Habilite o coletor de dados e configure um data warehouse de gerenciamento. Para obter mais informações, consulte Manage Data Collection.

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 Managemente, em seguida, expanda Data Collection. Clique com o botão direito do rato em CDC Performance Data Collectore, em seguida, clique em Iniciar Conjunto de Coleta de Dados.

4. No armazém de dados que configurou no passo 1, localize a tabela custom_snapshots.cdc_log_scan_data. Esta tabela fornece um instantâneo histórico dos dados das sessões de varredura de logs. Esses dados podem ser usados para analisar latência, taxa de transferência e outras medidas de desempenho ao longo do tempo.

Modo de atualização de script

Quando você aplica atualizações cumulativas ou service packs a uma instância, na reinicialização, a instância pode entrar no modo de Atualização de Script. Nesse modo, o SQL Server pode executar uma etapa para analisar e atualizar tabelas CDC internas, o que pode resultar na recriação de objetos como índices em tabelas de captura. Dependendo da quantidade de dados envolvidos, essa etapa pode levar algum tempo ou causar alto uso do log de transações para bancos de dados CDC habilitados.

Conteúdo relacionado

• CDC com o Banco de Dados SQL do Azure
• Controlar alterações de dados (SQL Server)
• Sobre a captura de dados de alteração (SQL Server)
• Habilitar e desabilitar a captura de dados de alteração (SQL Server)
• Trabalhar com Dados de Alteração no SQL Server