Thiago Zavaschi R2 www.zavaschi.com

30May/142

Change Tracking – O que é? Como Usar?

Olá pessoal,

Hoje comentarei sobre um recurso velho no SQL Server (desde o 2008) mas que vejo pouco utilizado e que muitas vezes os desenvolvedores fazem algumas coisas “mirabolantes” para ter resultados similares.

O Change Tracking (CT) e o Change Data Capture (CDC) são ferramentas de suporte a sincronização. O CDC abordarei no próximo post.

Os cenários em que o CT se aplica são aqueles cenários onde você trabalha com aplicações offline, aplicações ocasionalmente conectadas ou aplicações que não necessitam conhecer em tempo real que houve atualização nos dados.

O Change tracking lhe garante a informação sobre qual linha foi modificada (linha inserida, coluna atualizada, deletada, etc.). O seu “irmão” Change Data Capture armazena todo o histórico do dado modificado (por essa razão pode ser vista como uma solução mais custosa).

Em cenários de DW ambas tecnologias podem ajudar a identificar as linhas que sofreram modificação para que seja extraído do sistema transacional somente as linhas que foram modificadas e assim diminuir a carga sobre os sistemas transacionais fontes (CT e CDC são features do SQL Server, no SQL Server 2012 há a possibilidade de utilizar o CDC para Oracle – Instalador externo presente na mídia do SQL Server 2012).

Como ativo o Change Tracking?

O change tracking é uam feature ativada a nível de tabela, porém é necessário habilitar o recurso para a database que conterá as tabelas “monitoradas”.

Para isso é necessário executar os eguinte comando:

ALTER DATABASE <nome da base> SET CHANGE_TRACKING = ON;

Internamente após isso é criada uma tabela escondida (tabela Commit – sys.syscommittab) que manterá uma linha para cada transação que ocorrer nas tabelas que estiverem com o CT habilitado). Essa é uma tabela oculta e só pode ser visualizada por uma conexão DAC.

As informações de transações nesta tabela por padrão ficam armazenadas por dois dias e podem ser alteradas através dos parâmetros CHANGE_RETENTION e AUTO_CLEANUP durante o ALTER DATABASE. Isso é para evitar que a tabela cresca muito, especialmente em databases muito transacionais.

Agora é necessário ativar o CT para as tabelas que se deseja monitorar. Para tal utilizamos a seguinte sintaxe:

ALTER TABLE <tabela> ENABLE CHANGE_TRACKING;

Ao se ativar o CT para a tabela, é criado internamente uma tabela escondida chamada: sys.change_tracking_<object id>. Esta tabela é usada para o log das modificações.

Ao se ativar para a tabela, também é criada na tabela de origem uma coluna escondida (de 8 bytes) para armazenar o id da transação. Essa coluna escondida só pode ser vista através de uma conexão DAC e não é retornada caso o usuário execute um SELECT * FROM <tabela>.

Outro aspecto interessante do Change Tracking é que é possível monitorar (e recuperar) somente as colunas que sofreram modificação. Para ativar esse recurso é necessário passar um parametro adicional (opcional) no comando de alter table:

ALTER TABLE <tabela> ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);

Ativei e agora, como recupero as informações?

O CT se baseia em versões (é retirado uma versão para baseline e depois a cada nova alteração uma versão nova é gerada).

A função utilizada no auxílio da extração da informação desejada é a CHANGETABLE. CHANGE TABLE possui dois modos: VERSION e CHANGES.

Vamos a alguns exemplos para entender melhor a diferença entra ambos os modos.

USE DATABASE Temp 

CREATE TABLE Pessoas
( id int identity primary key,
nome varchar(255) not null,
nascimento date not null,
cidade varchar(255))
GO

ALTER DATABASE Temp SET CHANGE_TRACKING = ON;

INSERT INTO Pessoas VALUES
('Thiago', '01/01/1980','Curitiba'),
('Maria', '10/09/1985','São Paulo'),
('Pedro', '01/10/1991','São Paulo')

ALTER TABLE Pessoas ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);

Agora o Change Tracking está habilitado para a tabela Pessoas. O comando CHANGETABLE com VERSION é executado da seguinte forma (com CROSS APPLY):

SELECT
ct.SYS_CHANGE_VERSION,
ct.SYS_CHANGE_CONTEXT,
p.*
FROM Pessoas p
CROSS APPLY CHANGETABLE
(
VERSION Pessoas,
(id),
(p.id)
) ct;

O resultado é:

image3

Vamos fazer algumas operações:

UPDATE Pessoas SET cidade = 'Guarulhos' WHERE id = 2

INSERT INTO Pessoas VALUES
('João', '09/01/1965','Porto Alegre'),
('Tatiana', '08/05/2001','Paranaguá')

UPDATE Pessoas SET nascimento = '12/31/2001' WHERE id = 5

 

Para recuperar a versão atual das modificações é necessário utilizar a função CHANGE_TRACKING_CURRENT_VERSION. E depois basta usar o CHANGETABLE no modo CHANGES. Porém as modificações recuperadas são aquelas que foram geradas do valor passado para o CHANGES em diante.

Fizemos apenas três operações apenas após ativar o Change Tracking, é por isso que a função para recuperar versão atual retorna 3. Segue um exemplo para recuperar a versão atual (repare que ela retorna um BIGINT) que se tornará a nova baseline:

DECLARE @versao bigint;
SET @versao = CHANGE_TRACKING_CURRENT_VERSION();
SELECT @versao

No entanto queremos as motificações que ocorreram desde a versão baseline (no nosso caso 0, no caso de vocês seria a versão da sincronização mais recente).

SELECT
ct.SYS_CHANGE_VERSION,
ct.SYS_CHANGE_OPERATION,
ct.SYS_CHANGE_CONTEXT,
p.*
FROM CHANGETABLE
(
CHANGES dbo.Pessoas,
0 -- Versão da baseline, modificações da versão 0 até a atual.
) ct
LEFT OUTER JOIN dbo.Pessoas p ON
p.id = ct.id;

image4

Com isso conseguimos recuperar o que foi modificado (lembrando que a tabela contém 5 linhas. Desde a versão 0 ocorreram dois inserts (I) e um update (U) – coluna SYS_CHANGE_OPERATION).

“Ok Thiago e o segundo update?”

Lembrem-se que o CT não monitora o dado e sim a operação que aconteceu comparado a versão de baseline. Comparado a versão 0 o dado com id 5 foi inserido. Ao usar a versão 2 como baseline, por exemplo, teríamos um update para esta linha:

 image14

O CHANGETABLE no modo VERSION retorna o seguinte agora:

image15

Verificando as colunas que sofreram alteração (update)

Já vimos como recuperar as operações que ocorreram, mas ainda não vimos como recuperar as colunas que sofreram alteração (utilizamos a opção TRACK_COLUMNS_UPDATED).

A informação sobre as colunas modificadas aparecem na coluna SYS_CHANGE_COLUMNS da função CHANGETABLE no modo CHANGES. E devemos utilizar junto a função CHANGE_TRACKING_IS_COLUMN_IN_MASK.

O tracking de colunas pode ser usado de tal forma que retorne NULL se a coluna não foi modificada. Se a coluna puder ser NULL então também terá que retornar uma outra coluna para dizer se houve modificação ou não.

O exemplo abaixo seria um modo de recuperar as colunas que foram alteradas. Perceba que temos duas colunas para cada coluna na tabela original. Uma com nulo ou o valor atual e outra dizendo se a coluna sofreu alteração ou não (pois como vimos existem casos em que o dado null é o dado efetivamente).

-- São necessários os IDs das colunas participantes do tracking
DECLARE @IdColunaNome int = COLUMNPROPERTY(
OBJECT_ID('dbo.Pessoas'),'nome', 'ColumnId')
DECLARE @IdColunaNascimento int = COLUMNPROPERTY(
OBJECT_ID('dbo.Pessoas'),'nascimento', 'ColumnId')
DECLARE @IdColunaCidade int = COLUMNPROPERTY(
OBJECT_ID('dbo.Pessoas'),'cidade', 'ColumnId')
DECLARE @baseline BIGINT = 2 -- para pegar somente a modificação do último update

SELECT
CT.id, -- Colunas que sempre terão valores, ou as que não se alteram, no nosso caso só uma.
CASE -- coluna de nome
WHEN CHANGE_TRACKING_IS_COLUMN_IN_MASK(
@IdColunaNome, CT.SYS_CHANGE_COLUMNS) = 1
THEN P.nome
ELSE NULL
END AS CT_Nome,
CHANGE_TRACKING_IS_COLUMN_IN_MASK(
@IdColunaNome, CT.SYS_CHANGE_COLUMNS) AS
CT_Nome_Mudou,
CASE -- coluna de nascimento
WHEN CHANGE_TRACKING_IS_COLUMN_IN_MASK(
@IdColunaNascimento, CT.SYS_CHANGE_COLUMNS) = 1
THEN P.nascimento
ELSE NULL
END AS CT_Nascimento,
CHANGE_TRACKING_IS_COLUMN_IN_MASK(
@IdColunaNascimento, CT.SYS_CHANGE_COLUMNS) AS
CT_Nascimento_Mudou,
CASE -- coluna de cidade
WHEN CHANGE_TRACKING_IS_COLUMN_IN_MASK(
@IdColunaCidade, CT.SYS_CHANGE_COLUMNS) = 1
THEN P.cidade
ELSE NULL
END AS CT_Cidade,
CHANGE_TRACKING_IS_COLUMN_IN_MASK(
@IdColunaCidade, CT.SYS_CHANGE_COLUMNS) AS
CT_Cidade_Mudou,
CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
CT.SYS_CHANGE_CONTEXT
FROM
dbo.Pessoas AS P
INNER JOIN
CHANGETABLE(CHANGES dbo.Pessoas, @baseline) AS CT
ON
P.id = CT.id AND
CT.SYS_CHANGE_OPERATION = 'U' -- pegar somente os updates

O resultado disso é o seguinte (omiti um pedaço para efeito de legibilidade):

image26

Importante!! Como garantir consistência dos resultados!!!

Nem tudo são flores na vida de Joseph Klimber. Para usar este recurso de maneira consistente é necessário a utilização do SNAPSHOT isolation level. Desta maneira alterações for a do escopo da atual transação não serão consideradas.

O procedimento para garantir a consistência é o seguinte:

1. Coloque a transação para usar snapshot isolation level e inicie a transação

2. Valide a última versão de sincronização com a função CHANGE_TRACKING_MIN_VALID_VERSION().

3. Obtenha a versão para ser usada na próxima vez com CHANGE_TRACKING_CURRENT_VERSION().

4. Obtenha as modificações na tabela usando CHANGETABLE (CHANGES…).

5. Obtenha as modificações de outras tabelas que sejam necessárias.

6. De um commit na transação.

DMVs Auxiliares

Existem algumas DMVs que podem auxiliar na identificação das databases e das tabelas que possuem o recurso ativo:

sys.change_tracking_databases – Retorna a listagem das databases na instância que possuem o recurso de CT ativo.

sys.change_tracking_tables – Retorna a lista de tabelas na database que possuem o recurso ativo.

 

Bom pessoal, o que eu queria mostrar hoje era isso. Aguardo sugestões.

Até a próxima!

Comments (2) Trackbacks (0)
  1. “Nem tudo são flores na vida de Joseph Klimber”, rsss
    Excelente post Zava :)
    Gosto muito destas features… lembro bem como antes dependíamos de recursos como triggers para viabilizar resultados como estes :)

    Abraço,

    Mendes

  2. Preciso de ajuda com uma solução

    Estou analisando uma solução de Bi de uma industria que tem ‘muitas’ roles de segurança para restringir acesso a dados.

    Algumas roles são de centro de custo, outras por divisão, e até cell data para algumas roles de sub-divisão.

    Me diz uma coisa… qual o custo disso? como funciona o Engine?

    Exemplo, eu vou fazer um relatório para área de distribuição…o SSAS Verifica que o usuário esta na Role de distribuição, e filtra o acesso a todos os dados de consumo… mais se eu fizesse um relatório com o dataset filtrado só para distribuição seria mais rápido? mesmo assim o SSAS não vai ser o usuário no AD e aplicar a Role?

    Qual o custo da Role, comparando a um Dataset filtrado?

    O que me sugere de arquitetura?


Leave a comment


No trackbacks yet.