SQL Server - Change Tracking vs CDC: Quando e como implementar rastreamento de dados leve

Fala pessoal!

O assunto de hoje é sobre o pouco utilizado Change Tracking, um rastreamento de dados leve para identificar quais linhas de uma tabela foram inseridas/alteradas, muito útil para cargas incrementais, por exemplo.

Uma das sugestões mais comuns é utilizar Triggers, mas como vocês sabem, Triggers em tabelas grandes podem gerar muitos problemas de performance e um aumento drástico de LCK_M_IX e WRITELOG.

Outra opção é o CDC, mas o overhead de leitura do Log e o armazenamento das tabelas de “shadow” podem aumentar muito o consumo de disco.

É nesse cenário que o Change Tracking (CT) pode ser muito útil para conseguir rastrear todas as mudanças com um impacto quase imperceptível no Throughput da aplicação.

O que é o Change Tracking (CT)?

O Change Tracking é uma solução leve, projetada especificamente para cenários de sincronização unidirecional ou bidirecional. Ao contrário do CDC, que captura o “antes” e o “depois” de cada coluna, o CT apenas marca que uma linha foi alterada. Ele responde a pergunta: “Quais registros mudaram desde a minha última verificação?”.

Diferente de outras soluções, o Change Tracking não altera a estrutura das suas tabelas e não cria triggers. Ele trabalha de forma integrada ao motor do SQL Server. Quando você habilita o CT, o SQL passa a registrar as mudanças de DML (INSERT, UPDATE, DELETE) em uma estrutura interna.

O grande segredo aqui é o que ele armazena: apenas metadados da mudança e os valores da Primary Key das linhas alteradas. Se você atualizou uma coluna de descrição, o CT grava apenas que “a PK X mudou na versão Y”. Para pegar o dado novo, sua aplicação fará um JOIN da tabela de rastreio com a tabela original.

Quando usar Change Tracking?

• Sincronização de Dados para Cache: Se você precisa atualizar um Redis ou ElasticSearch apenas com o que mudou no SQL.
• ETL Incremental: Para alimentar um Data Warehouse sem precisar ler tabelas gigantescas via “Full Load” ou depender de colunas de Data_Atualizacao, que muitas vezes não possuem índice ou tecnicamente não é possível implementar essa coluna, como no caso de sistemas de terceiros. Além de conseguir tratar o problema de como tratar registros apagados.
• Aplicações Mobile Offline: Onde o dispositivo precisa baixar apenas o “delta” de alterações desde a última sincronização.

Change Tracking vs. CDC vs. Temporal Tables

É fundamental entender onde o Change Tracking se posiciona no ecossistema do SQL Server:

Vantagens do Change Tracking

• Baixo Overhead Transacional: Diferente do CDC, ele não lê o log de transação de forma assíncrona; a marcação é feita no commit da transação de forma muito otimizada.
• Simplicidade de Consumo: A função CHANGETABLE facilita absurdamente a vida do desenvolvedor que precisa apenas do Delta.
• Limpeza Automática: O AUTO_CLEANUP gerencia o purge dos dados antigos sem necessidade de Jobs complexos de manutenção.

Desvantagens do Change Tracking

• Falta de Histórico: Se um registro foi alterado 10 vezes entre duas consultas suas, o CT dirá apenas que ele mudou. Você perde os estados intermediários.
• Exigência de Primary Key: Se você tem tabelas legadas sem PK (um erro clássico de modelagem), esqueça o CT nelas.
• Custo de Escrita: Embora leve, existe uma escrita síncrona adicional. Em ambientes com altíssima pressão de escrita, cada milissegundo conta.

Como saber se o Change Tracking está ativo?

Para saber se o Change Tracking está ativo, podemos consultar os metadados do sistema para validar tanto o banco de dados quanto as tabelas específicas:

-- VERIFICA CONFIGURAÇÃO EM NÍVEL DE DATABASE
SELECT
    DB_NAME( [database_id] )      AS [Nm_Database],
    [is_auto_cleanup_on]          AS [Fl_Auto_Cleanup],
    [retention_period]            AS [Nr_Periodo_Retencao],
    [retention_period_units_desc] AS [Ds_Unidade_Retencao]
FROM
    [sys].[change_tracking_databases];


-- VERIFICA CONFIGURAÇÃO POR TABELA
SELECT
    SCHEMA_NAME( [T].[schema_id] )     AS [Nm_Schema],
    [T].[name]                         AS [Nm_Tabela],
    [CT].[is_track_columns_updated_on] AS [Fl_Track_Colunas]
FROM
    [sys].[change_tracking_tables] AS [CT]
    INNER JOIN [sys].[tables]      AS [T] ON [CT].[object_id] = [T].[object_id];

Como ativar o Change Tracking no Banco de Dados

Para utilizar o recurso, primeiro habilitamos em nível de database. Aqui definimos o período de retenção (por quanto tempo o SQL manterá o histórico de mudanças) e se a limpeza automática (Auto Cleanup) estará ativa.

-- HABILITA O CHANGE TRACKING NO DATABASE
-- RETENÇÃO DE 2 DIAS E CLEANUP ATIVADO
IF NOT EXISTS (SELECT 1 FROM [sys].[change_tracking_databases] WHERE [database_id] = DB_ID()) 
BEGIN
    ALTER DATABASE CURRENT SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
END

Como ativar o Change Tracking nas Tabelas

Após habilitar no banco, precisamos definir quais tabelas serão monitoradas.

-- HABILITA O RASTREAMENTO NA TABELA DE CLIENTES
-- O PARÂMETRO TRACK_COLUMNS_UPDATED PERMITE SABER QUAIS COLUNAS ESPECÍFICAS FORAM ALTERADAS 
IF NOT EXISTS (SELECT 1 FROM [sys].[change_tracking_tables] WHERE [object_id] = OBJECT_ID('[dbo].[Clientes]')) 
BEGIN
    ALTER TABLE [dbo].[Clientes] ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
END

Se você quiser desativar o Change Tracking de todas as tabelas do banco atual e também desativar a nível de banco de dados, pode utilizar o script abaixo:

SET NOCOUNT ON;

-----------------------------------------------------------------------
-- 1) Desliga CT de todas as tabelas (se existir alguma)
-----------------------------------------------------------------------
DECLARE @sql NVARCHAR(MAX) = N'';

SELECT
    @sql = @sql + N'
ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(ctt.object_id)) + N'.' + QUOTENAME(OBJECT_NAME(ctt.object_id)) + N'
DISABLE CHANGE_TRACKING;'
FROM sys.change_tracking_tables AS ctt;

IF (@sql <> N'')
BEGIN
    EXEC sys.sp_executesql @sql;
END;

-----------------------------------------------------------------------
-- 2) Desliga CT do banco SOMENTE se estiver ligado
-----------------------------------------------------------------------
IF EXISTS (SELECT 1 FROM sys.change_tracking_databases WHERE database_id = DB_ID())
BEGIN
    DECLARE @db SYSNAME = DB_NAME();
    DECLARE @sqlDb NVARCHAR(MAX) =
        N'ALTER DATABASE ' + QUOTENAME(@db) + N' SET CHANGE_TRACKING = OFF;';
    EXEC sys.sp_executesql @sqlDb;
END;

O Conceito de Marca d’Água (Watermark) com Change Tracking

No modelo tradicional, usamos uma data ou um ID sequencial como “marca d’água”. No Change Tracking, a nossa marca d’água é o número da versão do Change Tracking, que é incremental e global do banco de dados (não é um sequencial por tabela), que basicamente quer dizer algo como “até aqui eu já li tudo e posso esquecer o passado”.

Pense que o SQL mantém uma fita contínua de commits:

Cada número é um commit que alterou qualquer tabela com o Change Tracking ativado. Essa fita anda para frente sozinha, você não controla. O watermark é simplesmente o “onde eu parei de ler” e a tabela de controle não guarda dados, apenas o último número da fita que já foi lida com sucesso.

Quando você executa:
CHANGETABLE(CHANGES dbo.Stg_Vendas, 104)

Isso significa basicamente: “Me traga tudo o que aconteceu depois do commit 104 nessa tabela.”

O controle de watermark é importante, especialmente se for utilizar várias tabelas, porque se o watermark da tabela ficar para trás e sair dessa janela, o SQL apaga fisicamente esse pedaço da fita. Quando você solicitar os dados a partir da versão 104 e a fita começar em 108, o SQL não tem mais os commits 105–107, e você terá perda definitiva de dados, sendo necessário rodar uma carga completa (FULL).

Por isso que é importante executar o comando CHANGE_TRACKING_CURRENT_VERSION() somente depois de consumir todos os dados dessa tabela com sucesso.

O fluxo lógico de uma leitura incremental segue estes passos:

1. Obter a última versão processada: Consultamos uma tabela de controle onde guardamos até que versão que já foi lida.
2. Obter a versão atual do banco: Chamamos a função CHANGE_TRACKING_CURRENT_VERSION().
3. Ler o Delta: Usamos a função CHANGETABLE passando a versão antiga para iniciar a leitura das alterações a partir daquela versão.
4. Atualizar a tabela de controle: Salvamos a nova versão para a próxima execução.

Mãos na Massa: Script de Exemplo Completo

Abaixo, um script estruturado seguindo as melhores práticas de nomenclatura e performance, simulando um ambiente de carga e extração incremental.

Criação da tabela de teste, tabela de controle de cargas e ativação do Change Tracking

--------------------------------------------------------------------------------------
-- 1) Habilitar Change Tracking no DATABASE
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT 1 FROM [sys].[change_tracking_databases] WHERE [database_id] = DB_ID()) 
BEGIN
    ALTER DATABASE CURRENT SET CHANGE_TRACKING = ON(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
END

--------------------------------------------------------------------------------------
-- 2) Reset do ambiente (apaga a tabela caso já exista)
--------------------------------------------------------------------------------------
IF ( OBJECT_ID( '[dbo].[Stg_Vendas]' ) IS NOT NULL )
BEGIN
    
    -- VERIFICA SE O CHANGE TRACKING ESTÁ ATIVADO NA TABELA
    IF EXISTS (SELECT 1 FROM [sys].[change_tracking_tables] WHERE [object_id] = OBJECT_ID('[dbo].[Stg_Vendas]')) 
    BEGIN

        -- DESATIVA O CHANGE TRACKING
        ALTER TABLE [dbo].[Stg_Vendas] DISABLE CHANGE_TRACKING;

    END

    DROP TABLE [dbo].[Stg_Vendas];

END


--------------------------------------------------------------------------------------
-- 3) Criar tabela de teste
--------------------------------------------------------------------------------------
CREATE TABLE [dbo].[Stg_Vendas]
(
    [Nr_Id_Venda]   INT            IDENTITY(1, 1) NOT NULL,
    [Dt_Venda]      DATETIME       NOT NULL CONSTRAINT [DF_Stg_Vendas_Dt_Venda] DEFAULT (GETDATE()),
    [Nm_Produto]    VARCHAR(100)   COLLATE Latin1_General_CI_AS NOT NULL,
    [Vl_Venda]      DECIMAL(18, 2) NOT NULL,
    [Fl_Processado] BIT            NOT NULL CONSTRAINT [DF_Stg_Vendas_Fl_Processado] DEFAULT (0),
    CONSTRAINT [PK_Stg_Vendas] PRIMARY KEY CLUSTERED ([Nr_Id_Venda])
);

--------------------------------------------------------------------------------------
-- 4) Habilitar Change Tracking na tabela
--------------------------------------------------------------------------------------
ALTER TABLE [dbo].[Stg_Vendas]
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);

--------------------------------------------------------------------------------------
-- 5) Criar tabela de controle (watermark por tabela)
--------------------------------------------------------------------------------------
IF ( OBJECT_ID( '[dbo].[CT_Watermark]' ) IS NULL )
BEGIN

    CREATE TABLE [dbo].[CT_Watermark]
    (
        [Tabela]      SYSNAME NOT NULL CONSTRAINT [PK_CT_Watermark] PRIMARY KEY,
        [LastVersion] BIGINT  NOT NULL
    );
    
    -- Inicializa watermark
    INSERT INTO [dbo].[CT_Watermark] ([Tabela], [LastVersion])
    SELECT 
        'dbo.Stg_Vendas',
        CHANGE_TRACKING_CURRENT_VERSION();

END

Faz algumas alterações na tabela pra testar:

INSERT INTO [dbo].[Stg_Vendas] ([Nm_Produto], [Vl_Venda])
VALUES
    ('SQL Server License', 50000.00),
    ('Azure Subscription', 1200.50);

UPDATE [dbo].[Stg_Vendas]
SET [Vl_Venda] = 55000.00
WHERE [Nr_Id_Venda] = 1;

DELETE FROM [dbo].[Stg_Vendas]
WHERE [Nr_Id_Venda] = 2;

Agora vou fazer a leitura das alterações e atualizar a tabela de controle com a versão atual da tabela:

DECLARE @Tabela SYSNAME = N'dbo.Stg_Vendas';
DECLARE @ObjectId INT = OBJECT_ID(@Tabela);
DECLARE @LastVersion BIGINT;
DECLARE @MinValid BIGINT;
DECLARE @Current BIGINT;

SELECT @LastVersion = [LastVersion]
FROM [dbo].[CT_Watermark]
WHERE [Tabela] = @Tabela;

SELECT @MinValid = CHANGE_TRACKING_MIN_VALID_VERSION(@ObjectId);
SELECT @Current  = CHANGE_TRACKING_CURRENT_VERSION();

-- Diagnóstico
SELECT
    [Tabela]      = @Tabela,
    [LastVersion] = @LastVersion,
    [MinValid]    = @MinValid,
    [Current]     = @Current;

-- Se seu @LastVersion estiver abaixo do mínimo válido, você perdeu histórico (retenção)
IF (@LastVersion < @MinValid)
BEGIN
    PRINT '*** ATENCAO: LastVersion < MinValid. Precisa FULL LOAD e reset do watermark. ***';
END
ELSE
BEGIN

    -- Deltas (o que mudou desde o watermark)
    SELECT
        CT.[Nr_Id_Venda],
        CT.[SYS_CHANGE_VERSION],
        CT.[SYS_CHANGE_OPERATION],     -- Insert, Update, Delete
        CT.[SYS_CHANGE_COLUMNS]        -- bitmap (só faz sentido com TRACK_COLUMNS_UPDATED = ON)
    FROM
        CHANGETABLE(CHANGES [dbo].[Stg_Vendas], @LastVersion) AS CT
    ORDER BY
        CT.[SYS_CHANGE_VERSION];

END;

-- Avança watermark SOMENTE após processar com sucesso
UPDATE [dbo].[CT_Watermark]
SET [LastVersion] = CHANGE_TRACKING_CURRENT_VERSION()
WHERE [Tabela] = @Tabela;

SELECT * FROM [dbo].[CT_Watermark];

Você verá esse resultado:

Valores da coluna SYS_CHANGE_OPERATION:

• I = Insert
• U = Update
• D = Delete

Observação: Você deve ter notado que a operação de UPDATE não apareceu nas alterações do Change Tracking. Isso aconteceu porque o Change Tracking não registra um histórico completo de todas as operações, retorna o resultado final por chave desde a última versão sincronizada. Por isso, se uma linha foi atualizada e depois excluída antes da leitura do CHANGETABLE, você verá apenas a operação D, pois o UPDATE intermediário não é necessário para aplicar a sincronização.

Vamos fazer mais algumas mudanças na tabela:

-- Novas mudanças
INSERT INTO [dbo].[Stg_Vendas] ([Nm_Produto], [Vl_Venda])
VALUES ('Dirceu Resende', 999.99);

UPDATE [dbo].[Stg_Vendas]
SET [Fl_Processado] = 1
WHERE [Nr_Id_Venda] = 1;

Lendo novamente as alterações com o mesmo script utilizado anteriormente, você verá esse resultado:

Como o UPDATE ainda é válido, e a linha não foi excluída, como aconteceu no primeiro exemplo, podemos visualizar o registro da alteração (UPDATE) nos logs do Change Tracking.

Análise de Performance e Internals

Quando o Change Tracking está ativo, o SQL Server utiliza uma tabela interna (internal table) para armazenar as informações de mudança. O commit de qualquer transação na tabela rastreada agora inclui uma escrita adicional nessa tabela interna.

Principais Wait Types Relacionados:

• WRITELOG: Como o CT é síncrono, o custo da escrita da mudança deve ser contabilizado no log de transação. Se o seu disco de log já estiver saturado, o CT pode agravar a latência de escrita.
• CHECKPOINT: O processo de cleanup do CT (que remove registros antigos conforme a retenção) é executado em background e pode gerar IO.

Monitorando o espaço em disco utilizado pelo Change Tracking

Para garantir que o Change Tracking não está se tornando um vilão e consumindo muito espaço em disco, utilize a query abaixo:

SELECT
    QUOTENAME(OBJECT_SCHEMA_NAME(T.object_id)) + N'.' + QUOTENAME(T.name) AS [Nm_Tabela],
    IT.name                                                              AS [Nm_Tabela_Interna],
    CAST(SUM(A.total_pages) * 8.0 / 1024 AS DECIMAL(18,2))               AS [Nr_Tamanho_MB],
    CAST(SUM(A.used_pages)  * 8.0 / 1024 AS DECIMAL(18,2))               AS [Nr_Usado_MB],
    CAST(SUM(A.data_pages)  * 8.0 / 1024 AS DECIMAL(18,2))               AS [Nr_Dados_MB]
FROM
    sys.internal_tables AS IT
    JOIN sys.objects         AS T  ON T.object_id = IT.parent_id
    JOIN sys.partitions      AS P  ON P.object_id = IT.object_id
    JOIN sys.allocation_units AS A ON A.container_id = P.hobt_id
WHERE
    IT.internal_type_desc = N'CHANGE_TRACKING'
GROUP BY
    OBJECT_SCHEMA_NAME(T.object_id),
    T.name,
    IT.name
ORDER BY
    [Nr_Usado_MB] DESC;

Change Tracking em Ambientes de Alta Disponibilidade (AG) e Replicação

Essa é uma dúvida muito comum. Como o CT se comporta quando temos Always On Availability Groups ou operações de Restore?

Always On (AG)

O Change Tracking é totalmente compatível com Always On. As tabelas internas de rastreamento são estruturas internas persistidas e replicadas via log e, portanto, os dados de mudança são replicados para os secundários via log de transação normalmente.

Dica de Ouro: O processo de AUTO_CLEANUP roda apenas no Primário, mas o AUTO_CLEANUP NÃO é garantido nas réplicas secundárias, porque o cleanup não é um DELETE normal: Ele usa Mecanismo interno de GC (garbage collector), comandos não-logados de maneira replicável e Operações físicas locais.

Por conta disso, as tabelas internas de CT crescem infinitamente no secundário e você pode ter sys.syscommittab grande, sys.change_tracking_tables crescendo, atraso no REDO, eventual falha da réplica e outros problemas.

Uma forma de corrigir isso, é criar um job nas réplicas com esse código:

IF sys.fn_hadr_is_primary_replica(DB_NAME()) = 0
BEGIN
    EXEC sys.sp_flush_commit_table_on_demand;
END

Backup e Restore / Attach Database

Ao restaurar um banco de dados com CT ativo em outro servidor, o recurso permanece ativo. No entanto, se você restaurar um backup antigo, a versão de controle (CHANGE_TRACKING_CURRENT_VERSION) estará desatualizada em relação ao que sua aplicação de sincronização já processou, o que pode exigir um “Full Sync” inicial.

WITH CHANGE_TRACKING_CONTEXT

Uma feature pouco explorada é o WITH CHANGE_TRACKING_CONTEXT. Imagine que você tem um processo de integração que altera dados e você não quer que esse mesmo processo “re-processe” essas mudanças.

Você pode enviar um contexto:

DECLARE @Ds_Contexto VARBINARY(128) = CONVERT(VARBINARY(128), N'Integracao_Sistema_A');

WITH CHANGE_TRACKING_CONTEXT(@Ds_Contexto)
UPDATE [dbo].[Stg_Vendas]
SET [Vl_Venda] = [Vl_Venda] * 1.1
WHERE [Nr_Id_Venda] = 1;

-- Ignorar mudanças geradas por esse contexto
SELECT
    CT.[Nr_Id_Venda],
    CT.[SYS_CHANGE_OPERATION],
    CT.[SYS_CHANGE_VERSION],
    CT.[SYS_CHANGE_CONTEXT]
FROM
    CHANGETABLE(CHANGES [dbo].[Stg_Vendas], 0) AS CT
WHERE
    ISNULL(CT.[SYS_CHANGE_CONTEXT], 0x) <> @Ds_Contexto;

Isso evita loops infinitos de sincronização onde o Sistema A envia para o B, que devolve para o A. Vale lembrar que o contexto é gravado no commit, se o seu pipeline fizer múltiplas operações na mesma transação, todas herdam o mesmo contexto.

Limitações Técnicas

Antes de implementar, especialmente em produção, você precisa conhecer as limitações do Change Tracking:

• Alteração de Primary Key: O Change Tracking não permite atualizar diretamente o valor de uma PK. Na prática, um UPDATE em uma coluna que faz parte da chave primária é tratado internamente como um DELETE da chave antiga seguido de um INSERT da nova chave, e o CT refletirá esse comportamento.
• TRUNCATE TABLE: O comando TRUNCATE TABLE não gera eventos de Change Tracking. Se você truncar a tabela, o CT não registrará os deletes individuais e exigirá uma carga completa dessa tabela.
• Colunas de Large Object (LOB): O CT não rastreia quais colunas LOB (VARCHAR(MAX), VARBINARY(MAX)) foram alteradas, então mesmo com TRACK_COLUMNS_UPDATED = ON, o Change Tracking não inclui colunas LOB no bitmap de colunas alteradas (SYS_CHANGE_COLUMNS). Assim, você sabe que a linha foi alterada, mas não consegue identificar exatamente quais colunas LOB mudaram.

Espero que tenham gostado desse artigo, um grande abraço e até a próxima!