SQL Server - Change Tracking vs CDC: Cuándo y cómo implementar rastreo de datos ligero

¡Hola, chicos!

El tema de hoy trata sobre el poco utilizado Change Tracking, un ligero seguimiento de datos para identificar qué filas de una tabla se insertaron/cambiaron, muy útil para cargas incrementales, por ejemplo.

Una de las sugerencias más comunes es utilizar Triggers, pero como sabes, los Triggers en tablas grandes pueden generar muchos problemas de rendimiento y un aumento drástico en LCK_M_IX y ESCRIBIR REGISTRO.

Otra opción es CDC, pero la sobrecarga de leer el registro y almacenar tablas ocultas puede aumentar considerablemente el consumo de disco.

Es en este escenario que el Seguimiento de cambios (CT) puede resultar muy útil al poder realizar un seguimiento de todos los cambios con un impacto casi imperceptible en el rendimiento de la aplicación.

¿Qué es el seguimiento de cambios (CT)?

Change Tracking es una solución liviana diseñada específicamente para escenarios de sincronización unidireccional o bidireccional. A diferencia de CDC, que captura el “antes” y el “después” de cada columna, CT solo marca que una fila ha cambiado. Responde a la pregunta: "¿Qué registros han cambiado desde mi último control?"

A diferencia de otras soluciones, Change Tracking no cambia la estructura de sus tablas y no crea activadores. Funciona integrado con el motor SQL Server. Cuando habilita CT, SQL comienza a registrar cambios DML (INSERTAR, ACTUALIZAR, ELIMINAR) en una estructura interna.

El gran secreto aquí es lo que almacena: solo metadatos del cambio y los valores de la Clave Primaria de las líneas cambiadas. Si actualizó una columna de descripción, CT solo registra que "PK X cambió en la versión Y". Para obtener los nuevos datos, su aplicación UNIRÁ la tabla de seguimiento con la tabla original.

¿Cuándo utilizar el seguimiento de cambios?

• Sincronización de datos para caché: Si necesita actualizar Redis o ElasticSearch solo con lo que cambió en SQL.
• ETL incremental: Para alimentar un Data Warehouse sin tener que leer tablas gigantescas vía “Full Load” o depender de columnas Data_Update, que muchas veces no tienen índice o técnicamente no es posible implementar esta columna, como en el caso de sistemas de terceros. Además de poder abordar el problema de cómo manejar los registros eliminados.
• Aplicaciones móviles sin conexión: Donde el dispositivo solo necesita descargar el “delta” de cambios desde la última sincronización.

Seguimiento de cambios frente a CDC frente a tablas temporales

Es fundamental comprender dónde se ubica el seguimiento de cambios en el ecosistema de SQL Server:

Ventajas del seguimiento de cambios

• Gastos generales transaccionales bajos: a diferencia de CDC, no lee el registro de transacciones de forma asincrónica; el marcado se realiza en el commit de la transacción de forma muy optimizada.
• Simplicidad de consumo: La función CHANGETABLE hace la vida absurdamente más fácil a los desarrolladores que solo necesitan Delta.
• Limpieza automática: AUTO_CLEANUP gestiona la eliminación de datos antiguos sin necesidad de trabajos de mantenimiento complejos.

Desventajas del seguimiento de cambios

• Falta de historial: si un registro se cambió 10 veces entre dos de sus consultas, el CT simplemente dirá que cambió. Pierdes los estados intermedios.
• Requisito de clave principal: si tiene tablas heredadas sin PK (un error de modelado clásico), olvide el CT en ellas.
• Costo de escritura: aunque es liviano, hay escritura sincrónica adicional. En entornos con una presión de escritura extremadamente alta, cada milisegundo cuenta.

¿Cómo sé si el seguimiento de cambios está activo?

Para saber si Change Tracking está activo podemos consultar los metadatos del sistema para validar tanto la base de datos como tablas 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];

Cómo activar el seguimiento de cambios en la base de datos

Para utilizar la función, primero la habilitamos en el nivel de la base de datos. Aquí definimos el período de retención (cuánto tiempo SQL mantendrá el historial de cambios) y si la limpieza automática estará activa.

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

Cómo habilitar el seguimiento de cambios en tablas

Después de habilitarlo en la base de datos, debemos definir qué tablas serán monitoreadas.

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

Si desea deshabilitar el seguimiento de cambios para todas las tablas en la base de datos actual y también deshabilitarlo en el nivel de la base de datos, puede usar el siguiente script:

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;

El concepto de marca de agua (Watermark) con seguimiento de cambios

En el modelo tradicional, utilizamos una fecha o una identificación secuencial como “marca de agua”. En Change Tracking, nuestra marca de agua es el número de versión de Change Tracking, que es incremental y global para la base de datos (no secuencial por tabla), lo que básicamente significa algo como "hasta ahora lo he leído todo y puedo olvidar el pasado".

Piense en SQL como si mantuviera una cinta en funcionamiento de confirmaciones:

Cada número es una confirmación que cambió cualquier tabla con el Seguimiento de cambios habilitado. Esta cinta avanza por sí sola, no la controlas. La marca de agua es simplemente “donde dejé de leer” y la tabla de control no almacena datos, solo el último número de la cinta que se leyó exitosamente.

Cuando corres:
CAMBIABLE(CAMBIOS dbo.Stg_Sales, 104)

Básicamente, esto significa: "Tráeme todo lo que sucedió después del compromiso 104 en esta tabla".

El control de la marca de agua es importante, especialmente si va a utilizar varias tablas, porque si la marca de agua de la tabla se queda atrás y sale de esta ventana, SQL borra físicamente ese trozo de cinta. Cuando solicita datos de la versión 104 y la cinta comienza en 108, SQL ya no tiene confirmaciones 105–107 y tendrá una pérdida permanente de datos, lo que requerirá una carga COMPLETA.

Por eso es importante ejecutar el comando CHANGE_TRACKING_CURRENT_VERSION() solo después de consumir con éxito todos los datos de esta tabla.

El flujo lógico de una lectura incremental sigue estos pasos:

1. Obtener la última versión procesada: Consultamos una tabla de control donde almacenamos la versión ya leída.
2. Obtener la versión actual de la base de datos.: Llamamos a la función CHANGE_TRACKING_CURRENT_VERSION().
3. Leer el delta: Usamos la función CHANGETABLE pasando la versión anterior para comenzar a leer los cambios de esa versión.
4. Actualizar la tabla de control: Guardamos la nueva versión para la siguiente ejecución.

Práctica: guión de ejemplo completo

A continuación, un script estructurado siguiendo las mejores prácticas de nomenclatura y rendimiento, simulando un entorno de carga y extracción incremental.

Creación de la tabla de pruebas, tabla de control de carga y activación de 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

Realice algunos cambios en la tabla para probar:

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;

Ahora leeré los cambios y actualizaré la tabla de control con la versión actual de la tabla:

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

Verás este resultado:

Valores de la columna SYS_CHANGE_OPERATION:

• Yo = Insertar
• U = Actualizar
• D = Eliminar

Observación: Es posible que haya notado que la operación ACTUALIZAR no apareció en los cambios de Seguimiento de cambios. Esto sucedió porque Change Tracking no registra un historial completo de todas las operaciones, sino que devuelve el resultado final por clave desde la última versión sincronizada. Por lo tanto, si se actualizó una fila y luego se eliminó antes de leer CHANGETABLE, solo verá la operación D, ya que la ACTUALIZACIÓN intermedia no es necesaria para aplicar la sincronización.

Hagamos algunos cambios más en la tabla:

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

Al leer los cambios nuevamente con el mismo script usado anteriormente, verá este resultado:

Como la ACTUALIZACIÓN sigue siendo válida y la línea no se eliminó, como sucedió en el primer ejemplo, podemos ver el registro de cambios (ACTUALIZACIÓN) en los registros de Seguimiento de cambios.

Análisis de desempeño e internos

Cuando el seguimiento de cambios está activo, SQL Server utiliza una tabla interna para almacenar información de cambios. Confirmar cualquier transacción en la tabla rastreada ahora incluye una escritura adicional en esa tabla interna.

Principales tipos de espera relacionados:

• ESCRIBIR REGISTRO: Dado que CT es sincrónico, el costo de escribir el cambio debe contabilizarse en el registro de transacciones. Si su disco de registro ya está saturado, CT puede empeorar la latencia de escritura.
• CONTROL: El proceso de limpieza de CT (que elimina los registros antiguos tal como se conservan) se ejecuta en segundo plano y puede generar IO.

Supervisión del espacio en disco utilizado por el seguimiento de cambios

Para asegurarse de que Change Tracking no se convierta en un villano y consuma mucho espacio en disco, utilice la siguiente consulta:

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;

Seguimiento de cambios en entornos de alta disponibilidad (GA) y replicación

Esta es una pregunta muy común. ¿Cómo se comporta CT cuando tenemos grupos de disponibilidad Always On u operaciones de restauración?

Siempre encendido (AG)

Change Tracking es totalmente compatible con Always On. Las tablas de seguimiento internas son estructuras internas que se conservan y replican mediante el registro y, por lo tanto, los datos de cambio se replican en los secundarios mediante el registro de transacciones de forma normal.

Consejo de oro: el proceso AUTO_CLEANUP solo se ejecuta en el primario, pero AUTO_CLEANUP NO está garantizado en las réplicas secundarias, porque la limpieza no es un DELETE normal: utiliza un mecanismo de GC interno (recolector de basura), comandos no registrados de manera replicable y operaciones físicas locales.

Debido a esto, las tablas CT internas crecen infinitamente en la secundaria y puede tener grandes sys.syscommittab, sys.change_tracking_tables creciendo, retraso REDO, eventual falla de réplica y otros problemas.

Una forma de solucionar este problema es crear un trabajo en las réplicas con este código:

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

Copia de seguridad y restauración / Adjuntar base de datos

Al restaurar una base de datos con CT activo en otro servidor, el recurso permanece activo. Sin embargo, si restaura una copia de seguridad anterior, la versión de seguimiento (CHANGE_TRACKING_CURRENT_VERSION) estará desactualizada con respecto a lo que su aplicación de sincronización ya procesó, lo que puede requerir una "Sincronización completa" inicial.

CON CAMBIO_TRACKING_CONTEXT

Una característica poco explorada es CON CHANGE_TRACKING_CONTEXT. Imagine que tiene un proceso de integración que cambia datos y no desea que ese mismo proceso “reprocese” estos cambios.

Puedes enviar un 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;

Esto evita infinitos bucles de sincronización donde el Sistema A lo envía a B, que lo devuelve a A. Vale la pena recordar que el contexto se registra en la confirmación; si su canalización realiza múltiples operaciones en la misma transacción, todas heredan el mismo contexto.

Limitaciones técnicas

Antes de implementar, especialmente en producción, es necesario conocer las limitaciones del Change Tracking:

• Cambiar la clave principal: El seguimiento de cambios no le permite actualizar directamente el valor de una PK. En la práctica, una ACTUALIZACIÓN en una columna que forma parte de la clave principal se trata internamente como una ELIMINACIÓN de la clave anterior seguida de una INSERCIÓN de la clave nueva, y el CT reflejará este comportamiento.
• TABLA TRUNCADA: El comando TRUNCATE TABLE no genera eventos de seguimiento de cambios. Si trunca la tabla, CT no registrará las eliminaciones individuales y requerirá una carga completa de esa tabla.
• Columnas de objetos grandes (LOB): CT no realiza un seguimiento de qué columnas LOB (VARCHAR(MAX), VARBINARY(MAX)) se cambiaron, por lo que incluso con TRACK_COLUMNS_UPDATED = ON, el seguimiento de cambios no incluye columnas LOB en el mapa de bits de las columnas modificadas (SYS_CHANGE_COLUMNS). Entonces sabe que la fila ha cambiado, pero no puede identificar exactamente qué columnas LOB han cambiado.

Espero que hayas disfrutado de este artículo, un fuerte abrazo y ¡hasta la próxima!