Tabelas Temporais – Parte 2: Alteração de schema e Indexação

No post anterior, comecei a falar sobre um novo recurso presente no SQL Server 2016: System-Versioned Temporal Tables. Abordei a criação de tabelas temporais seja por alteração de uma tabela existente como por criação de uma nova tabela. Mencionei também que quando uma tabela temporal é criada, uma tabela de histórico (pré-existente ou não) é vinculada a ela. No entanto, deixei de dizer que, se a tabela de histórico já existe no momento desta vinculação, são realizadas verificações de consistência. A primeira é uma verificação de schema. A segunda verificação é realizada nos dados (DATA CONSISTENCY CHECK), mas não é obrigatória. Então, antes de entrar no tópico original desta parte, falarei sobre este assunto.

Vale ressaltar que, no momento em que este post foi escrito, a versão disponível do SQL Server é a 2016 CTP2.2. Logo, alguns detalhes podem mudar com as novas versões lançadas. Farei um esforço para manter este material atualizado de acordo com todas atualizações, mas isso vai depender da minha disponibilidade.

VERIFICAÇÃO DE SCHEMA

Algumas das verificações eu já mencionei no post anterior como a necessidade das colunas DATETIME2 NOT NULL para delimitação da validade do registro e a chave primária na tabela principal. Outras são bem óbvias: o número de colunas e os tipos de dados da tabela principal devem ser iguais aos da tabela de histórico, a tabela de histórico não pode estar num filegroup definido como READ ONLY e não pode existir uma coluna IDENTITY na history table. Além dessas verificações, a history table não pode possuir:

  • Chave primária;
  • Chaves estrangeiras;
  • Triggers;
  • Constraints de tabela ou coluna, exceto default constraints (que nunca serão usadas);
  • Change Tracking ou CDC ativados;

Além disso, se as colunas que definem o período de validade do registro na tabela principal tiverem a opção HIDDEN, na history table elas também devem possuir essa propriedade. Caso a verificação falhe em algum desses requisitos, você receberá uma mensagem de erro. Como qualquer curioso atento a detalhes, vi que não é mencionado nada sobre o nome das colunas e resolvi verificar se colunas equivalentes podem ter nomes diferentes.

CREATE TABLE [dbo].[Tabela] (
	[id] INT PRIMARY KEY,
	[colunaX] CHAR(10),
	[inicio_registro] DATETIME2(0) NOT NULL,
	[fim_registro] DATETIME2(0) NOT NULL
);
CREATE TABLE [dbo].[TabelaHistorico] (
	[id] INT NOT NULL,
	[colunaY] CHAR(10),
	[inicio_registro] DATETIME2(0) NOT NULL,
	[fim_registro] DATETIME2(0) NOT NULL
);
ALTER TABLE [dbo].[Tabela] ADD PERIOD FOR SYSTEM_TIME ([inicio_registro], [fim_registro]);
GO
ALTER TABLE [dbo].[Tabela] SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[TabelaHistorico] ) );
GO

Para minha surpresa, recebi a seguinte mensagem de erro:

Ahn... Nope.

Ahn… Nope.

Em outras palavras, apesar do BOL não mencionar, os nomes das colunas equivalentes na history table devem ser iguais aos da tabela principal.

VERIFICAÇÃO DE CONSISTÊNCIA DOS DADOS

Como havia mencionado no post anterior, no momento em que é ativado o versionamento por sistema, é realizada uma verificação de consistência dos dados. Esta verificação é realizada por padrão, mas você pode optar por ignorá-la com a opção DATA_CONSISTENCY_CHECK = OFF. Nesta verificação o SQL Server irá avaliar se a coluna “inicio_registro” é menor ou igual à “fim_registro” e se há sobreposição da validade dos registros.

Se acompanhar esta verificação da tabela Disciplina (criada no post anterior) você verá algo como a consulta abaixo:

SELECT 
	[inicio_registro],
	[fim_registro],
	T.S,
	[$tabType]
FROM (SELECT
		[inicio_registro],
		[fim_registro],
		LAG([fim_registro]) OVER (PARTITION BY [id_disciplina] ORDER BY [inicio_registro]) AS S,
		[$tabType]
	FROM (SELECT
			[id_disciplina],
			[inicio_registro],
			[fim_registro],
			0 AS [$tabType]
		FROM [dbo].[DisciplinaHistorico]
		UNION ALL
		SELECT
			[id_disciplina],
			[inicio_registro],
			[fim_registro],
			1 AS [$tabType]
		FROM [dbo].[Disciplina]) AS R
	) AS T

Com isso você já pode concluir que, sem uma indexação adequada, esta consulta pode sofrer. No caso de VLTs esta verificação pode apresentar um obstáculo, fazendo jus à utilização da opção DATA_CONSISTENCY_CHECK = OFF.

ALTERAÇÃO DE SCHEMA

Suponhamos que você criou a tabela Disciplina com o versionamento de sistema com o script abaixo:

CREATE TABLE [dbo].[Disciplina] (
    [id_disciplina] INT CONSTRAINT pk_Disciplina PRIMARY KEY,
    [nome_disciplina] VARCHAR(50) NOT NULL,
    [creditos] INT NOT NULL,
    [inicio_registro] DATETIME2(0) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    [fim_registro] DATETIME2(0) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME ([inicio_registro],[fim_registro])
)WITH(SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[DisciplinaHistorico]));

Após um determinado tempo, foi solicitado que a coluna “valor_credito” seja acrescentada à tabela. Ao tentar adicionar a coluna, você é surpreendido com o seguinte erro:

Mas eu sou sysadmin!?

Ok…e agora? De acordo com o BOL:

In order the change the schema of a temporal table, you must first temporarily turn off SYSTEM_VERSIONING to enable the schema change (schema changes are blocked while SYSTEM_VERSIONING is ON to prevent the two tables becoming out of sync. While SYSTEM_VERSIONING is OFF, you can make the schema change to both tables independently and then re-enable SYSTEM_VERSIONING. Perform the above steps within a transaction to maintain data consistency.

Tirando o parêntese que nunca será fechado, este trecho explica que alterações de schema são bloqueadas enquanto o versionamento de sistema estiver habilitado e que, para manter a consistência dos dados, estas alterações devem ocorrer dentro de uma transação. Aplicando estes princípios temos:

BEGIN TRAN 
-- Desabilitar o versionamento de sistema 
ALTER TABLE [dbo].[Disciplina] SET (SYSTEM_VERSIONING = OFF);
-- Adicionar coluna a tabela principal
ALTER TABLE [dbo].[Disciplina] ADD [valor_credito] MONEY NULL;
-- Realizar mesma operação na tabela histórica
ALTER TABLE [dbo].[DisciplinaHistorico] ADD [valor_credito] MONEY NULL;
-- Habilitar o versionamento de sistema 
ALTER TABLE [dbo].[Disciplina] SET (SYSTEM_VERSIONING = ON);
--
COMMIT TRAN

Então você executa esse script e a mensagem de executado com sucesso aparece. Logo, você dá continuidade à sua rotina até que, em algum momento, esbarra com a tabela no object explorer.

WTF...?!

WTF…?!

Opa! Tem algo errado aí. Se você leu o post anterior, já identificou que a nova tabela histórica foi criada pelo SQL Server. Isso acontece quando não é determinada uma history table no momento da ativação do versionamento de sistema. Portanto, o script correto seria:

BEGIN TRAN 
-- Desabilitar o versionamento de sistema 
ALTER TABLE [dbo].[Disciplina] SET (SYSTEM_VERSIONING = OFF);
-- Adicionar coluna a tabela principal
ALTER TABLE [dbo].[Disciplina] ADD [valor_credito] MONEY NULL;
-- Realizar mesma operação na tabela histórica
ALTER TABLE [dbo].[DisciplinaHistorico] ADD [valor_credito] MONEY NULL;
-- Habilitar o versionamento de sistema 
ALTER TABLE [dbo].[Disciplina] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[DisciplinaHistorico]));
--
COMMIT TRAN

Vale ressaltar que você poderia utilizar a opção DATA_CONSISTENCY_CHECK = OFF neste exemplo sem problemas. Afinal de contas, você está isolado dentro de uma transação.

Enquanto o SYSTEM_VERSIONING = ON, as operações a seguir são proibidas tanto para tabela principal quanto para tabela histórica:

  • DROP TABLE;
  • ALTER TABLE…ADD COLUMN;
  • ALTER TABLE…ALTER COLUMN;
  • ALTER TABLE…DROP COLUMN;

No entanto, existem mais restrições específicas para cada tabela.

Tabela principal:

  • ALTER TABLE…DROP PERIOD não é permitido;
  • ALTER TABLE…SWITCH IN é permitido;

Tabela histórica:

  • ALTER TABLE…ADD PERIOD não é permitido;
  • ALTER TABLE…SWITCH OUT é permitido;

Tirando essas restrições, algumas das operações que podemos fazer com o SYSTEM_VERSIONING = ON são:

  • ALTER SCHEMA…TRANSFER;
  • ALTER TABLE…REBUILD;
  • CREATE INDEX;
  • CREATE STATISTICS;

INDEXAÇÃO DE TABELAS TEMPORAIS

Na documentação da Microsoft, é possível encontrar uma recomendação sobre indexação bem direta:

An optimal indexing strategy will include a clustered columns store index and / or a B-tree rowstore index on the current table and a clustered columnstore index on the history table for optimal storage size and performance. If you create / use your own history table, we strongly recommend that you create such an index that consists of the primary keys from the current table plus the period columns to speed up temporal querying as well as the queries that are part of the data consistency check. If your history table is a rowstore, we recommend a clustered rowstore index. The default history table has a clustered rowstore index created for you based on the primary key from the current table and the period columns. At a minimum, a non-clustered rowstore index is recommended.

Em outras palavras, as recomendações sugerem três situações distintas:

  1. Tabela principal e tabela histórica com índices clustered rowstore;
  2. Tabela principal com índice clustered rowstore e tabela histórica com índice clustered columnstore;
  3. Tabela principal e tabela histórica com índices clustered columnstore.

Pensei comigo: Excelente deixa para um benchmark! Então, comecei a preparar os scripts e acabei encontrando uma série de posts do Alexandr Volok (site|twitter) com o benchmark muito similar ao que pensei. Pude reproduzir os testes que resultaram em dados muito próximos aos dele, por isso vou apenas descrever o procedimento e divulgar seus posts e resultados. Convido você a ler a série na integra (links na seção de referência).

Na primeira parte do post, Volok define os processos a serem realizados e o escopo de cada análise. Os processos são:

  • Criar uma tabela principal com 1 milhão de registros;
  • Gerar duas séries de UPDATE e duas de DELETE, totalizando 400 mil linhas afetadas (resultando em 400 mil linhas na tabela histórica e 800 mil na principal);
  • Duas consultas de agregação (SUM) de 100 mil linhas: uma normal e outra temporal;
  • Duas consultas de agregação (SUM) através de um filtro pré-determinado: uma normal e outra temporal;

O escopo da análise destes processos se dá da seguinte forma:

  • Uso de disco (espaço de armazenamento);
  • Duração das operações de DML;
  • Duração das 4 consultas de agregação;

Os resultados da análise mostram que a situação 3, onde ambas as tabelas possuem índices clustered columnstore, atinge os melhores resultados tanto em uso de disco (aproximadamente 10 vezes menor que a situação 2) quanto em duração das consultas (de 2 a 70 vezes mais rápido que a situação 2). A situação 1 foi a pior das três analisadas, mas ainda é a única solução para quem não possui a edição Enterprise. A situação 2 foi melhor apenas na duração das operações de DML. No entanto, essa duração subiu drasticamente com o aumento do número de registros, enquanto a situação 3 se manteve muito eficiente.

No último post da série, a massa de dados é aumentada em 10 vezes. Desta vez, a situação 3 é indiscutivelmente a melhor solução de todas.

Numa análise sobre o benchmark realizado, vejo que o espaço de armazenamento da solução com dois índices clustered columnstore é impressionante e quase de fato determinante na escolha dos índices. A duração das operações DML também representa um fato significativo na escolha, apesar de não refletir um ambiente OLTP onde são realizadas várias transações pequenas. A duração das consultas é relevante quando se trata de agregações, no entanto o benchmark não realiza outro tipo de consultas. Esta análise fica para outro post.

CONCLUSÃO

Esta nova feature certamente traz diversas considerações sobre soluções implantadas “à mão” nos dias de hoje. No entanto, certos detalhes, como a necessidade dos mesmos nomes de coluna para ambas as tabelas, ainda não estão documentados. A verificação de consistência de dados pode ter um grande impacto em VLTs, principalmente quando a estratégia de indexação não for a ideal; E a alteração no esquema de uma tabela temporal possui detalhes importantes para a adoção correta desta tecnologia.

No próximo post desta série, irei falar como funcionam as operações DML numa tabela temporal e como eles são refletidos na tabela de histórico. Até lá!

REFERÊNCIAS / LINKS

[1] Temporal Tables (https://msdn.microsoft.com/en-us/library/dn935015.aspx)
[2] SQL Server 2016: Temporal Tables. Indexing. Part I. Rowstore Tables (http://www.alexvolok.com/2015/06/sql-2016-temporal-tables-indexing-part-i/)
[3] SQL Server 2016: Temporal Tables. Indexing. Part II. Columnstore Tables (http://www.alexvolok.com/2015/06/sql-2016-temporal-tables-indexing-part-ii/)
[4] SQL Server 2016: Temporal Tables. Indexing. Part III. Enlarged Dataset (http://www.alexvolok.com/2015/06/sql-2016-temporal-tables-indexing-part-i/)

LEITURA ADICIONAL

[1] Temporal Tables (http://sqlwithmanoj.com/tag/temporal-tables/)
[2] SQL Server 2016: Temporal Tables (http://www.infoq.com/news/2015/06/SQL-Server-Temporal)
[3] Time traveling with SQL Server 2016: Temporal Tables (https://devjef.wordpress.com/2015/07/13/time-traveling-with-sql-server-2016-temporal-tables/)

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s