Desde a versão 2016 do SQL Server está disponível o recurso para auditoria automática de dados através das Tabelas Temporais.

Eu mesmo publiquei em 2018 aqui no iMasters um artigo tratando deste recurso que, além de útil, é muito fácil de implementar.

Recentemente precisei implementar este recurso em um cliente. Porém, neste caso, tratava-se de uma tabela com mais de 2 bilhões de registros, o que exigiu alguns cuidados especiais, como descrevo a seguir.

Como Lidar com Tabelas Grandes

Uma solução muito usada para melhorar performance de tabelas com muitos registros é o uso de particionamento. Também tratei deste tema aqui no iMasters, em um artigo de 2017.

A ideia é subdividir a tabela em inúmeros “blocos” menores. Na prática, vemos a tabela como um só componente e a tratamos como se fosse uma tabela normal. Mas fisicamente ela está segmentada em N partes, sendo que os dados serão alocados em cada partição segundo o valor de uma coluna que chamamos de chave de particionamento.

Não é preciso dizer que o bom ou mau desempenho das operações com estas tabelas depende muito da escolha da chave de particionamento.

É comum considerar colunas com datas como candidatas para chave de particionamento. Assim, datas próximas tenderão a ser agrupadas na mesma partição.

Um efeito desta escolha é que novos dados tenderão a ser escritos na mesma partição. Em função disso, neste caso teremos uma partição “quente” para escrita, enquanto todas as demais só terão acesso de leitura.

Como Implementar Auditoria de Dados

Apesar de simples, a auditoria oferecida pelas tabelas temporais é bastante eficiente e, além de tudo, ocorre automaticamente.

Ao se criar uma tabela temporal, é automaticamente gerada uma tabela histórica com as mesmas colunas que a original.

Quando ocorre uma atualização ou exclusão de dados da tabela principal (a tabela temporal), o registro antigo é movido para a tabela secundária (chamada tabela histórica).

Ali veremos que este registro terá uma data de validade inicial: SysStartTime. Esta data informa quando aquele registro sofreu sua ação, seja ela uma inserção ou alteração. Existe também a data em que aquela versão do registro foi “eliminada”: SysEndTime, que trata de quando o registro foi alterado ou excluído da tabela
temporal.

Quando o registro é inserido, ele é escrito na tabela temporal tendo como SysStartTime a data em que ocorreu este evento. E, como se poderia prever, a coluna SysEndTime terá o valor NULO. Porém, nada acontece na tabela histórica, visto que esta é a primeira versão do registro.

Quando tal registro for alterado ou excluído, a versão antiga é movida para a tabela histórica, informando em SysEndTime a data em que esta versão foi “eliminada”. E tudo isso é controlado automaticamente pelo mecanismo do SQL Server.

Tabela Temporal Particionada

Qual problema pode acontecer ao se criar uma tabela temporal particionada? Normalmente, se estamos particionando uma tabela por ser muito grande, provavelmente a tabela histórica associada a ela também será muito grande. Consequentemente, é altamente recomendável que a tabela histórica também seja
particionada.

Porém, se executarmos o processo normal de criação de uma tabela temporal, será gerada uma tabela histórica sem partições. Não importa que seu script esteja particionando a tabela temporal. A histórica não será convertida automaticamente em tabela particionada.

Sendo assim, é necessária uma pequena adaptação do processo, criando primeiramente a tabela histórica com as partições. E depois se cria a tabela temporal particionada que irá apontar para a tabela histórica que já se criou.
Índices adicionais que sejam criados para a tabela temporal e/ou tabela histórica devem igualmente ser particionados.

Desta maneira, seu ambiente estará adequado às necessidades e à escala do seu banco de dados.

Criando os Objetos

O que dá mais trabalho na criação dessa solução é a sequência de tarefas a serem executadas.

Em primeiro lugar, é preciso criar o filegroups que serão usados. Quando se trabalha com o SQL Server nativo, é altamente recomendável que se use um filegroup para cada partição para garantir melhor gerenciamento do banco de dados. (No caso do Azure SQL Database, que é um serviço PAAS, não temos controle sobre os datafiles usados, por isso normalmente usamos o filegroup padrão, o PRIMARY)

--Criando filegroups

USE MASTER

GO



ALTER DATABASE MeuBD ADD FILEGROUP fg2018 ;

ALTER DATABASE MeuBD ADD FILEGROUP fg2019 ;

ALTER DATABASE MeuBD ADD FILEGROUP fg2020 ;

ALTER DATABASE MeuBD ADD FILEGROUP fg2021 ;

ALTER DATABASE MeuBD ADD FILEGROUP fg2022 ;

ALTER DATABASE MeuBD ADD FILEGROUP fg2023 ;

GO

No passo seguinte, criamos os datafiles usados por cada partição.

 

-- define 1 or more datafile per partition

USE MASTER

GO



ALTER DATABASE meuBD ADD FILE ( NAME = N'part2018_1', 

   FILENAME = N'C:\Data\meuDB_part2018_1.ndf' ,  

   SIZE = 100MB , MAXSIZE = 100GB , FILEGROWTH = 100MB 

) TO FILEGROUP [fg2018];



ALTER DATABASE meuBD ADD FILE ( NAME = N'part2019_1', 

   FILENAME = N'C:\Data\meuBD_part2019_1.ndf' , 

   SIZE = 100MB , MAXSIZE = 100GB , FILEGROWTH = 100MB 

) TO FILEGROUP [fg2019];



ALTER DATABASE meuBD ADD FILE ( NAME = N'part2020_1', 

   FILENAME = N'C:\Data\meuBD_part2020_1.ndf' , 

   SIZE = 100MB , MAXSIZE = 100GB , FILEGROWTH = 100MB 

) TO FILEGROUP [fg2020];



ALTER DATABASE meuBD ADD FILE ( NAME = N'part2021_1', 

  FILENAME = N'C:\Data\meuBD_part2021_1.ndf' , 

  SIZE = 100MB , MAXSIZE = 100GB , FILEGROWTH = 100MB 

) TO FILEGROUP [fg2021];



ALTER DATABASE meuBD ADD FILE ( NAME = N'part2022_1', 

  FILENAME = N'C:\Data\meuBD_part2022_1.ndf' , 

  SIZE = 100MB , MAXSIZE = 100GB , FILEGROWTH = 100MB 

) TO FILEGROUP [fg2022] ;



ALTER DATABASE meuBD ADD FILE ( NAME = N'part2023_1', 

  FILENAME = N'C:\Data\meuBD_part2023_1.ndf' , 

  SIZE = 100MB , MAXSIZE = 100GB , FILEGROWTH = 100MB 

) TO FILEGROUP [fg2023];

GO

 

No passo 3, se define a função de particionamento.

--Funcao de particionamento

USE meuBD

GO



CREATE PARTITION FUNCTION [fnPartitioningByYear](DATETIME) AS RANGE RIGHT FOR VALUES 

	(

	  -- tipo de particionamento: “RANGE RIGHT”

	  --NAO INFORMAR o primeiro intervalo (01/jan a 31/dez/2018) 

	  --dados anteriores a 01/jan/2019 serao gravados naquela particao

	  '20190101 00:00:00'

	, '20200101 00:00:00'

	, '20210101 00:00:00'

	, '20220101 00:00:00'

	, '20230101 00:00:00'

	)

GO

 

Agora é a vez de criar o esquema de particionamento dos dados.

-- Esquema de particionamento 

USE meuBD

GO



CREATE PARTITION SCHEME [schPartitioningByYear]

AS PARTITION [fnPartitioningByYear]

TO ( fg2018, fg2019, fg2020, fg2021, fg2022, fg2023)

GO

 

Finalmente podemos criar a primeira tabela, que no nosso caso dever ser necessariamente a tabela histórica. Como esta será também uma tabela particionada, é requerido que a chave de particionamento faça parte de um índice clusterizado ou chave primária.

-- Criar tabela historica

USE meuBD

GO



CREATE TABLE test_temporal_HISTORY(

       id bigint  NOT NULL,

       initdate datetime NOT NULL,

       SysStartTime datetime2(7) NOT NULL,

       SysEndTime datetime2(7) NOT NULL

) ON [schPartitioningByYear](initdate)



GO



CREATE CLUSTERED INDEX ix_test_temporal_History 

ON test_temporal_HISTORY (id , initdate)

GO

Agora, finalmente, definimos a tabela temporal, com sua sintaxe especial. Note que estamos apontando para uma tabela que já existe (e que usa o mesmo modelo de colunas e tipos de dados). Além disso, ela também está associada a um esquema de particionamento.

-- Criar tabela temporal

USE meuBD

GO



CREATE TABLE dbo.test_temporal(

       id bigint  NOT NULL,

       initdate datetime NOT NULL,

       SysStartTime datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL,

       SysEndTime datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL,

CONSTRAINT PK_test PRIMARY KEY CLUSTERED 

(

       id ASC,

       initdate

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ,

       PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])

) ON [schPartitioningByYear](initdate)

WITH

(

SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.test_temporal_HISTORY )

)

GO

Finalmente temos o ambiente pronto para uso. A consulta a seguir verifica qual o tipo de cada tabela e se elas são particionadas ou não.

--verificacao

USE meuBD

GO



SELECT db_name() + '.'  + OBJECT_SCHEMA_NAME(i.object_id) + '.' + OBJECT_NAME(i.object_id) AS TAB

	,  MIN(temporal_type_desc) AS temporal_type_desc, CASE WHEN MAX(pstat.partition_number) > 1 THEN 'PARTITIONED' ELSE 'REGULAR' END AS Partition_Type, MAX(pstat.partition_number) AS Partition_count  

FROM sys.dm_db_partition_stats   pstat

	INNER JOIN sys.indexes i  ON pstat.object_id = i.object_id AND pstat.index_id = i.index_id

	LEFT JOIN sys.partition_schemes ps on ps.data_space_id = i.data_space_id  

	LEFT JOIN sys.partition_range_values rv on rv.function_id = ps.function_id AND rv.boundary_id = pstat.partition_number

	LEFT JOIN sys.tables t on i.object_id = t.object_id 

WHERE OBJECT_SCHEMA_NAME(i.object_id) <> 'sys' 

GROUP BY i.object_id

HAVING MAX(pstat.partition_number) > 1

GO

Comentários Finais

A implementação de tabelas temporais particionadas, como se pode ver, é simples.

A questão principal aqui é se dar conta da necessidade de particionamento da tabela histórica. Se não tomarmos providências esta tabela será criada automaticamente sem partição nenhuma e muito provavelmente o assunto será esquecido.

Neste cenário, o problema só será descoberto quando a solução já estiver em produção e começar a haver problemas de performance para consultas da tabela histórica. E então os esforços necessários para correção serão muito maiores e mais impactantes.