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

Post Views 19 views

Neste artigo

This entry is part 22 of 22 in the series Security and Auditing

Reading time 11 minutes

Hey guys!

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.

Observation: O Change Tracking rastreia toda e qualquer operação DML, mesmo que o valor final da coluna seja idêntico ao original (o famoso “update dummy”). Como o Change Tracking não armazena o histórico de valores antigos, apenas registra a Primary Key da linha alterada e o tipo da operação (Insert, Update ou Delete), se você precisa de auditoria completa com valores anteriores, a solução correta seria Temporal Tables ou CDC.

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:

Recurso	Mecanismo	Armazena Valor Antigo?	Histórico Completo	Overhead de Log	Storage Extra	Ideal para	Pode perder histórico?	Consumo incremental	Impacto
Change Tracking	Síncrono (Commit)	No	No	Mínimo	Muito baixo	Sync / Lakehouse / Replicação	Sim (janela de retenção)	Watermark (CHANGETABLE)	Baixíssimo
CDC	Assíncrono (Log Reader)	Yes	Yes	Alto	Alto	Auditoria / Replay / Compliance	No	LSN	Alto
Temporal Tables	Síncrono (Versionamento)	Yes	Yes	Médio	Muito alto	Histórico funcional / SCD2	No	Tempo (ValidFrom / ValidTo)	Médio

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];

-- 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

-- 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

Important: Nunca defina uma retenção de Change Tracking muito alta (ex: 30 dias) em tabelas com milhões de updates diários. Isso fará com que a tabela interna cresça muito, afetando a performance de leitura do próprio CT e pode aumentar IO/Log, impactar cleanup, e degradar a consulta do CHANGETABLE e o throughput geral

Alerta Crítico: Ao desabilitar o Change Tracking no banco de dados, todas as estruturas internas de rastreamento são removidas imediatamente e todo o histórico de mudanças é definitivamente perdido. Não existe “Undo”. Ao reativar o recurso, será necessário habilitar o CT tabela por tabela novamente e iniciar uma nova carga completa (full load) para restabelecer a consistência dos dados sincronizados.

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

-- 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;

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;

Important: Para habilitar o Change Tracking, a tabela obrigatoriamente deve possuir uma Primary Key definida. Sem PK, o SQL Server não tem como rastrear a identidade da linha alterada.

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:

Obter a última versão processada: Consultamos uma tabela de controle onde guardamos até que versão que já foi lida.
Obter a versão atual do banco: Chamamos a função CHANGE_TRACKING_CURRENT_VERSION().
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.
Atualizar a tabela de controle: Salvamos a nova versão para a próxima execução.

Important: Antes de consumir os deltas, sempre valide se o seu watermark for menor que esse valor. Se isso acontecer, parte do histórico já foi apagado pela política de retenção e você deve executar uma carga completa da tabela antes de continuar.

Para verificar a menor versão válida do watermark da tabela, você pode utilizar o comando SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N’dbo.MinhaTabela’))

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

--------------------------------------------------------------------------------------

-- 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;

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];

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

Observation: 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;

-- 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;

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.

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;

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.

Destaque de Performance: O rastreamento de colunas (TRACK_COLUMNS_UPDATED) adiciona um custo extra de armazenamento e CPU. Só habilite se sua lógica de sincronização realmente precisar saber qual campo mudou para evitar updates desnecessários no destino.

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

Subscribe to blog by email

Blog Views

Microsoft MVP

Post Archive

Categories

Recent posts

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

O que é o Change Tracking (CT)?

Quando usar Change Tracking?

Change Tracking vs. CDC vs. Temporal Tables

Como saber se o Change Tracking está ativo?

Como ativar o Change Tracking no Banco de Dados

Como ativar o Change Tracking nas Tabelas

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

Mãos na Massa: Script de Exemplo Completo

Análise de Performance e Internals

Monitorando o espaço em disco utilizado pelo Change Tracking

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

Always On (AG)

Backup e Restore / Attach Database

WITH CHANGE_TRACKING_CONTEXT

Limitações Técnicas

You may also like...

Leave a Reply Cancel reply

O que é o Change Tracking (CT)?

Quando usar Change Tracking?

Change Tracking vs. CDC vs. Temporal Tables

Como saber se o Change Tracking está ativo?

Como ativar o Change Tracking no Banco de Dados

Como ativar o Change Tracking nas Tabelas

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

Mãos na Massa: Script de Exemplo Completo

Análise de Performance e Internals

Monitorando o espaço em disco utilizado pelo Change Tracking

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

Always On (AG)

Backup e Restore / Attach Database

WITH CHANGE_TRACKING_CONTEXT

Limitações Técnicas

You may also like...

SQL Server – Como enviar avisos e mensagens de erro para o banco pelo CLR (C#)

SQL Server – Como consultar e consumir feeds RSS do WordPress utilizando CLR ou xp_cmdshell (cURL)

SQL Server – Como parar, iniciar e listar os serviços do Windows remotamente

Leave a Reply Cancel reply