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

Aplica-se a: SQL Server Instâ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, confira CDA com Banco de Dados SQL do Azure.

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, continuous 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 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 verificação que tentaram esgotar o log antes de retornar (continuous = 0) ou executar waitfor (continuous = 1).

Parâmetro continuous

O parâmetro continuous controla se sp_cdc_scan cede o controle após esgotar o log ou executar o número máximo de ciclos de verificação (modo mono estável). Também controla se sp_cdc_scan continua sendo executado até ser explicitamente interrompido (modo contínuo).

Modo de disparo único

No modo monoestável, o trabalho de captura solicita ao sp_cdc_scan para executar verificações maxtrans para tentar esgotar o log e retornar. Qualquer transação além de maxtrans presente no log será processada nas verificações posteriores.

O modo monoestável é usado em testes controlados, nos quais o volume de transações a serem processadas é conhecido e há vantagens no fato de o trabalho fechar automaticamente quando concluído. O modo monoestável não é recomendado para uso de 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 monoestável, você pode computar uma associação superior na taxa de transferência esperada do trabalho de captura, expressa nas 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 monoestável tiver sido usado para verificação de log regular, o número de segundos entre o processamento de log deverá ser regido pela agenda do trabalho. 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 do trabalho de captura

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é a meia-noite foram verificadas e depositadas nas 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. 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ções usa uma retenção com base na estratégia de limpeza para gerenciar o tamanho da tabela de alterações. 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.

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 especificado de minutos é abstraído do valor máximo de tran_end_time da tabela cdc.lsn_time_mapping para obter a nova marca d'água expressa como um valor de data e hora. 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

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 ajudar 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 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 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 Gerenciamento e, em seguida, expanda Coleta de Dados. Clique com o botão direito do mouse em Coletor de Dados de Desempenho de CDA 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.

Modo de upgrade de script

Quando você aplicar atualizações ou service packs cumulativos 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 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

• CDA com banco de dados SQL do Azure
• Controle de alterações de dados (SQL Server)
• Sobre a captura de dados de alterações (SQL Server)
• Habilitar e desabilitar a Captura de dados de alterações (SQL Server)
• Trabalhar com dados de alterações (SQL Server)