Gerenciar dados históricos em tabelas temporais com política de retenção

Aplica-se a:Banco de dados SQL do AzureInstância Gerenciada de SQL do AzureBanco de Dados SQL no Microsoft Fabric

As tabelas temporais podem aumentar o tamanho do banco de dados mais do que as tabelas regulares, especialmente se você reter dados históricos por um período maior de tempo. Portanto, a política de retenção de dados históricos é um aspecto importante do planejamento e gerenciamento do ciclo de vida de cada tabela temporal. Tabelas temporais no Banco de Dados SQL do Azure e na Instância Gerenciada de SQL do Azure vêm com mecanismo de retenção de fácil utilização que ajuda você a realizar essa tarefa.

A retenção de histórico temporal pode ser configurada no nível da tabela individual, que possibilita que os usuários criem políticas de idade flexíveis. A aplicação de retenção temporal é simples: requer apenas um parâmetro a ser definido durante a criação da tabela ou alteração do esquema.

Depois de definir a política de retenção, o Banco de Dados SQL do Azure e a Instância Gerenciada de SQL do Azure começam a verificar regularmente se há linhas de histórico qualificadas para a limpeza automática de dados. A identificação das linhas correspondentes e sua remoção da tabela de histórico ocorrem de forma transparente na tarefa em segundo plano que é agendada e executada pelo sistema. A condição de idade das linhas da tabela de histórico é verificada com base na coluna que representa o final do SYSTEM_TIME período. Se o período de retenção for definido como seis meses, por exemplo, as linhas de tabela qualificadas para limpeza atenderão a seguinte condição:

ValidTo < DATEADD (MONTH, -6, SYSUTCDATETIME())

No exemplo anterior, presumimos que a ValidTo coluna corresponde ao final do SYSTEM_TIME período.

Como configurar a política de retenção

Antes de configurar a política de retenção para uma tabela temporal, verifique primeiro se a retenção de histórico temporal está habilitada no nível do banco de dados.

SELECT is_temporal_history_retention_enabled, [name]
FROM sys.databases;

O sinalizador is_temporal_history_retention_enabled de banco de dados é definido ON como por padrão, mas os usuários podem alterá-lo com a ALTER DATABASE instrução. Ele é automaticamente definido como OFF após a operação restauração pontual. Para habilitar a limpeza da retenção de histórico temporal para seu banco de dados, execute a seguinte instrução:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

A política de retenção é configurada durante a criação de uma tabela especificando o valor do parâmetro HISTORY_RETENTION_PERIOD:

CREATE TABLE dbo.WebsiteUserInfo
(  
    [UserID] int NOT NULL PRIMARY KEY CLUSTERED
  , [UserName] nvarchar(100) NOT NULL
  , [PagesVisited] int NOT NULL
  , [ValidFrom] datetime2 (0) GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 (0) GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )  
 WITH
 (
     SYSTEM_VERSIONING = ON
     (
        HISTORY_TABLE = dbo.WebsiteUserInfoHistory,
        HISTORY_RETENTION_PERIOD = 6 MONTHS
     )
 );

O Banco de Dados SQL do Azure e a Instância Gerenciada de SQL do Azure permitem especificar o período de retenção usando unidades de tempo diferentes: DAYS, WEEKSe MONTHSYEARS. Se HISTORY_RETENTION_PERIOD for omitido, será presumida retenção infinita. Você também pode usar INFINITE a palavra-chave explicitamente.

Em alguns cenários, talvez você queira configurar a retenção após a criação da tabela ou alterar o valor configurado anteriormente. Nesse caso, use a instrução ALTER TABLE.

ALTER TABLE dbo.WebsiteUserInfo
SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 9 MONTHS));

Para examinar o estado atual da política de retenção, use a seguinte consulta que une o sinalizador de habilitação de retenção temporal no nível do banco de dados com períodos de retenção para tabelas individuais:

SELECT DB.is_temporal_history_retention_enabled,
SCHEMA_NAME(T1.schema_id) AS TemporalTableSchema,
T1.name as TemporalTableName,  SCHEMA_NAME(T2.schema_id) AS HistoryTableSchema,
T2.name as HistoryTableName,T1.history_retention_period,
T1.history_retention_period_unit_desc
FROM sys.tables T1  
OUTER APPLY (select is_temporal_history_retention_enabled from sys.databases
where name = DB_NAME()) AS DB
LEFT JOIN sys.tables T2
ON T1.history_table_id = T2.object_id WHERE T1.temporal_type = 2;

Com que idades as linhas são excluídas

O processo de limpeza depende do layout do índice da tabela de histórico. É importante observar que somente tabelas de histórico com um índice clusterizado (árvore B ou columnstore) podem ter uma política de retenção finita configurada. Uma tarefa em segundo plano é criada para executar a limpeza de dados antigos para todas as tabelas temporais com período de retenção finito. Lógica de limpeza para o índice clusterizado de rowstore (árvore B) exclui a linha antiga em partes menores (até 10 K), minimizando a pressão no log do banco de dados e o subsistema de E/S. Embora a lógica de limpeza utilize o índice de árvore B necessário, a ordem das exclusões para as linhas mais antigas que o período de retenção não pode ser garantido com certeza. Portanto, não assuma nenhuma dependência na ordem de limpeza em seus aplicativos.

A tarefa de limpeza para o columnstore clusterizado remove todos os grupos de linhas ao mesmo tempo (normalmente contém 1 milhão de linhas cada), o que é muito eficiente, especialmente quando os dados históricos são gerados em alto ritmo.

A excelente compactação de dados e eficiente limpeza da retenção torna o índice de columnstore clusterizado uma opção ideal para cenários em que sua carga de trabalho gera rapidamente uma grande quantidade de dados históricos. Esse padrão é típico para o cargas de trabalho de processamento transacional intensas que usam tabelas temporais para controle de alterações e auditoria, análise de tendências ou ingestão de dados de IoT.

Index considerations

A tarefa de limpeza para tabelas com um índice clusterizado de rowstore requer que um índice inicie a coluna correspondente ao fim do período de SYSTEM_TIME. Se esse índice não existir, você não poderá configurar o período de retenção finito:

Msg 13765, Nível 16, Estado 1

Falha na definição do período de retenção finito na tabela temporal com versão no sistema “temporalstagetestdb.dbo.WebsiteUserInfo” porque a tabela de histórico “temporalstagetestdb.dbo.WebsiteUserInfoHistory” não contém o índice clusterizado necessário. Considere criar um índice columnstore ou árvore B clusterizado começando com a coluna correspondente ao fim do período SYSTEM_TIME na tabela de histórico.

É importante observar que a tabela de histórico padrão criada pelo Banco de Dados SQL do Azure e pela Instância Gerenciada de SQL do Azure já possui um índice clusterizado compatível com a política de retenção. Se você tentar remover o índice em uma tabela com o período de retenção finito, a operação falhará com o seguinte erro:

Msg 13766, Nível 16, Estado 1

Não é possível descartar o índice clusterizado “WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory” porque ele está sendo usado para a limpeza automática dos dados antigos. Considere definir HISTORY_RETENTION_PERIOD como INFINITO na tabela temporal com versão do sistema correspondente se você precisar remover esse índice.

A limpeza no índice columnstore clusterizado funciona de maneira ideal se as linhas históricas forem inseridas em ordem crescente (ordenadas no final da coluna do período), que sempre será o caso quando a tabela de histórico for populada exclusivamente pelo mecanismo SYSTEM_VERSIONIOING. Se as linhas da tabela de histórico não forem ordenadas pelo final da coluna do período (o que pode ser o caso se você migrar dados históricos existentes), será necessário recriar o índice columnstore clusterizado além do índice rowstore de árvore B ordenado corretamente para obter o desempenho ideal.

Evite recriar índices columnstore em cluster na tabela de histórico com o período de retenção finito, pois isso pode mudar a ordem dos grupos de linhas naturalmente imposta pela operação de controle de versão do sistema. Se você precisar recriar o índice columnstore clusterizado na tabela de histórico, faça-o recriando-o junto com o índice de árvore B compatível, preservando a ordem dos rowgroups necessários para a limpeza de dados regulares. A mesma abordagem deve ser usada se você criar uma tabela temporal com a tabela de histórico existente que tem um índice de coluna clusterizado sem ordem de dados garantida:

/*Create B-tree ordered by the end of period column*/
CREATE CLUSTERED INDEX IX_WebsiteUserInfoHistory ON WebsiteUserInfoHistory (ValidTo)
WITH (DROP_EXISTING = ON);
GO
/*Re-create clustered columnstore index*/
CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory ON WebsiteUserInfoHistory
WITH (DROP_EXISTING = ON);

Quando o período de retenção finito estiver configurado para a tabela de histórico com o índice columnstore clusterizado, você não poderá criar índices adicionais de árvore B não clusterizado na tabela:

CREATE NONCLUSTERED INDEX IX_WebHistNCI ON WebsiteUserInfoHistory ([UserName])

Uma tentativa de executar a instrução acima falhará com o seguinte erro:

Msg 13772, Nível 16, Estado 1

Não é possível criar índice não clusterizado em uma tabela de histórico temporal “WebsiteUserInfoHistory”, pois ele tem o período de retenção finito e o índice columnstore clusterizado definido.

Consultar tabelas com política de retenção

Todas as consultas na tabela temporal filtram automaticamente as linhas históricas que correspondem à política de retenção finita para evitar resultados imprevisíveis e inconsistentes, pois as linhas antigas podem ser excluídas pela tarefa de limpeza a qualquer momento e em qualquer ordem.

A figura a seguir mostra o plano de consulta para uma consulta simples:

SELECT * FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

O plano de consulta inclui filtro adicional aplicado ao final da coluna do período (ValidTo) no operador Clustered Index Scan na tabela de histórico (realçada). Este exemplo supõe que o período de retenção de um MÊS foi definido na tabela WebsiteUserInfo.

No entanto, se você consultar a tabela de histórico diretamente, poderá ver linhas mais antigas do que o período de retenção especificado, mas sem qualquer garantia de resultados de consulta repetíveis. A figura a seguir mostra o plano de execução de consulta para a consulta na tabela de histórico sem filtros adicionais aplicados:

Como você pode obter resultados inconsistentes ou inesperados, não confie sua lógica empresarial na leitura da tabela de histórico além do período de retenção. Recomendamos que você use consultas temporais com FOR SYSTEM_TIME cláusula para analisar dados em tabelas temporais.

Considerações da recuperação pontual

Quando você cria o novo banco de dados restaurando o banco de dados existente para um momento específico , ele tem uma retenção temporal desabilitada no nível do banco de dados. (Sinalizadoris_temporal_history_retention_enabled definido como OFF). Essa funcionalidade permite examinar todas as linhas de histórico na restauração, sem se preocupar se linhas antigas são removidas antes de você chegar a consultá-las. Você pode usá-la para inspecionar dados históricos além do período de retenção configurado.

Digamos que uma tabela temporal tenha um MONTH período de retenção especificado. Se seu banco de dados foi criado na camada de serviço Premium, você poderá criar a cópia de banco de dados com o estado do banco de dados de até 35 dias anteriores. Isso efetivamente permitiria analisar linhas históricas de até 65 dias anteriores consultando a tabela de histórico diretamente.

Se você desejar ativar a limpeza da retenção temporal, execute a seguinte instrução Transact-SQL após a restauração pontual:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

Related content

• Introdução às tabelas temporais
• Temporal tables