Temporal Table no SQL Server – 3ª Parte – Dsai Consultoria skip to Main Content

Temporal Table no SQL Server – 3ª Parte

Post Series: Temporal Table no SQL Server

No 1º post sobre Temporal Table falei sobre as principais características desta nova funcionalidade no SQL Server 2016, além de mostrar como habilitar.  Já no 2º post mostrei as diversas opções de consulta do histórico de alterações, além das limitações em alterações de Schema.

Neste 3º e último post sobre Temporal Table vou tratar de uma questão importante, desempenho.

 

DESEMPENHO NA ATUALIZAÇÃO

Quando penso em utilizar uma nova funcionalidade em produção o desempenho é sempre uma preocupação, por isso procuro realizar testes para avaliar.  Fiz um teste simples de atualização para verificar se Temporal Table prejudica o desempenho de atualizações, comparando com tabela sem esta funcionalidade.

Primeiro teste foi em tabela SEM Temporal Table, Script abaixo:

CREATE TABLE dbo.Cliente (
Cliente_ID int not null primary key,
Nome varchar(50) not null,
RendaMensal decimal(10,2) null,
RendaAnual as RendaMensal * 12)
go

-- Incluindo 200.000 linhas
declare @i int = 1, @Nome varchar(50) 
while @i <=200000 begin
	set @Nome = 'Nome ' + ltrim(str(@i)) 
	INSERT dbo.Cliente (Cliente_ID,Nome,RendaMensal)
	VALUES (@i,@Nome,@i + 100.00)

	set @i = @i + 1
end
go
-- 46 seg

-- Gerando linhas na tabela histórico
declare @i int = 1, @Nome varchar(50) 
while @i <= 10000 begin
	UPDATE dbo.Cliente SET RendaMensal = (RendaMensal + @i) WHERE Cliente_ID = @i

	set @i = @i + 1
end
WAITFOR DELAY '00:00:02.000'
go 20
-- 1 min e 23 seg

Em seguida realizei as mesmas atualizações em tabela COM Temporal Table, Script abaixo:

CREATE TABLE dbo.ClienteTemporal (
Cliente_ID int not null primary key,
Nome varchar(50) not null,
RendaMensal decimal(10,2) null,
RendaAnual as RendaMensal * 12,
SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN,
SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN,
PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)) 
WITH(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ClienteTemporal_Hist))
go

-- Incluindo 200.000 linhas
declare @i int = 1, @Nome varchar(50) 
while @i <=200000 begin
	set @Nome = 'Nome ' + ltrim(str(@i)) 
	INSERT dbo.ClienteTemporal (Cliente_ID,Nome,RendaMensal)
	VALUES (@i,@Nome,@i + 100.00)

	set @i = @i + 1
end
go
-- 47 seg

-- Gerando linhas na tabela histórico
declare @i int = 1, @Nome varchar(50) 
while @i <= 10000 begin
	UPDATE dbo.ClienteTemporal SET RendaMensal = (RendaMensal + @i) WHERE Cliente_ID = @i

	set @i = @i + 1
end
WAITFOR DELAY '00:00:02.000'
go 20
-- 1 min 38 seg

O resultado mostra uma pequena degradação no desempenho de atualização, que deve ser considerada em cenários onde esta atividade é importante, principalmente em carga de dados.

 

INDEXAÇÃO

Quando habilitamos Temporal Table o SQL Server cria automaticamente um índice Clustered na tabela histórico, nas colunas DATETIME2 de controle das atualizações, figura abaixo:

Executando consulta para recuperar o histórico de atualizações de um determinado registro, obtemos o seguinte plano de execução:

SELECT Cliente_ID, Nome, RendaMensal, RendaAnual, SysStartTime, SysEndTime  
FROM dbo.ClienteTemporal
FOR SYSTEM_TIME ALL 
WHERE Cliente_ID = 3 
ORDER BY Cliente_ID, SysStartTime 
-- Table 'ClienteTemporal_Hist'. Scan count 1, logical reads 597
-- Table 'ClienteTemporal'. Scan count 0, logical reads 3

Vejam o Index Scan na tabela histórico e o volume de IO (597 páginas), será que um índice Nonclustered na coluna Cliente_ID melhora o desempenho?

create index ix_ClienteTemporal_Hist_Cliente_ID on ClienteTemporal_Hist (Cliente_ID,SysStartTime,SysEndTime)
-- Table 'ClienteTemporal_Hist'. Scan count 1, logical reads 63
-- Table 'ClienteTemporal'. Scan count 0, logical reads 3

Claramente temos um ganho de desempenho, vejam a diferença de IO na tabela Histórico… redução de 597 para 63 páginas!

Na próxima consulta vou fazer filtro por intervalo de tempo em conjunto com Cliente_ID:

drop index ClienteTemporal_Hist.ix_ClienteTemporal_Hist_Cliente_ID

SELECT Cliente_ID, Nome, RendaMensal, RendaAnual, SysStartTime, SysEndTime  
FROM dbo.ClienteTemporal
FOR SYSTEM_TIME BETWEEN  '2016-09-13 14:49:03.9181422' AND '2016-09-13 14:49:40.7917191'
WHERE Cliente_ID = 3 
ORDER BY Cliente_ID, SysStartTime 
-- Table 'ClienteTemporal_Hist'. Scan count 1, logical reads 597
-- Table 'ClienteTemporal'. Scan count 0, logical reads 3

create index ix_ClienteTemporal_Hist_Cliente_ID on ClienteTemporal_Hist (Cliente_ID,SysStartTime,SysEndTime)
-- Table 'ClienteTemporal_Hist'. Scan count 1, logical reads 30
-- Table 'ClienteTemporal'. Scan count 0, logical reads 3

Podemos observar a melhora neste tipo de consulta também, redução do IO de 597 para 30 páginas.

Devemos considerar criar índices adicionais na tabela histórico para agilizar as buscas, contudo estes índices adicionais devem prejudicar o desempenho na atualização, cuidado!

EXCLUINDO O HISTÓRICO

O SQL Server 2016 não possui processo automático para limpeza periódica da tabela histórico, esperamos ter em versões futuras.  Para evitar com que a tabela histórico fique muito grande, prejudicando o desempenho e ocupando muito espaço, é necessário agendar rotina de limpeza.

-- Desabilita Temporal Table em Cliente
ALTER TABLE dbo.ClienteTemporal SET (SYSTEM_VERSIONING = OFF)

-- Exclui as linhas
DELETE FROM dbo.ClienteTemporal WHERE SysStartTime < '20150101'

-- Habilita Temporal Table em Cliente
ALTER TABLE dbo.ClienteTemporal SET (SYSTEM_VERSIONING = ON
(HISTORY_TABLE  = dbo.ClienteTemporal_Hist, DATA_CONSISTENCY_CHECK = OFF))

No Script acima desabilitamos primeiro Temporal Table, excluímos as linhas e depois habilitamos novamente. É muito importante executar as instruções dentro de uma transação, para evitar perda de registro histórico!

 

CONCLUSÃO

Temporal Table é uma nova funcionalidade do SQL Server 2016 que deve ser intensamente utilizada em produção, principalmente em auditoria, análise e recuperação de dados.

Até o próximo post.

Saudações Tricolores,
Landry

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Back To Top