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. Você pode acessar a 1ª e 2ª partes
nos links abaixo:
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.
BEGIN TRAN
-- 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))
COMMIT
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
