Partilhar via

Gerencie a retenção de dados históricos em tabelas temporais versionadas pelo sistema

  • Artigo

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Banco de Dados SQL do AzureInstância Gerenciada SQL do Azurebanco de dados SQL no Microsoft Fabric

Com tabelas temporais com versão do sistema, a tabela de histórico pode aumentar o tamanho do banco de dados mais do que as tabelas regulares, particularmente nas seguintes condições:

  • Você retém dados históricos por um longo período de tempo
  • Você tem um padrão de modificação intensiva de dados por atualização ou exclusão.

Uma tabela de histórico grande e em constante crescimento pode se tornar um problema devido aos custos de armazenamento puros e à imposição de um imposto de desempenho sobre consultas temporais. O desenvolvimento de uma política de retenção de dados para gerenciar dados na tabela de histórico é um aspeto importante do planejamento e gerenciamento do ciclo de vida de cada tabela temporal.

Gerenciamento de retenção de dados para tabela de histórico

O gerenciamento da retenção de dados da tabela temporal começa com a determinação do período de retenção necessário para cada tabela temporal. Sua política de retenção, na maioria dos casos, deve fazer parte da lógica de negócios do aplicativo usando as tabelas temporais. Por exemplo, os aplicativos em auditoria de dados e cenários de viagem no tempo têm requisitos firmes sobre quanto tempo os dados históricos devem estar disponíveis para consulta on-line.

Depois de determinar o período de retenção de dados, você deve desenvolver um plano para gerenciar dados históricos. Decida como e onde você armazena seus dados históricos e como excluir dados históricos mais antigos do que seus requisitos de retenção. As seguintes abordagens para gerenciar dados históricos na tabela de histórico temporal estão disponíveis:

Com cada uma dessas abordagens, a lógica para migrar ou limpar dados do histórico é baseada na coluna que corresponde ao fim do período na tabela atual. O valor de fim de período para cada linha determina o momento em que a versão da linha se torna fechada, ou seja, quando ela pousa na tabela de histórico. Por exemplo, a condição ValidTo < DATEADD (DAYS, -30, SYSUTCDATETIME ()) especifica que os dados históricos com mais de um mês precisam ser removidos ou movidos para fora da tabela de histórico.

Os exemplos neste artigo usam os exemplos obtidos no artigo Criar uma tabela temporal com versão do sistema.

Usar a abordagem de particionamento de tabela

Tabelas particionadas e índices podem tornar tabelas grandes mais gerenciáveis e escaláveis. Com a abordagem de particionamento de tabela, pode-se implementar limpeza de dados personalizada ou arquivamento offline, com base numa condição temporal. O particionamento de tabelas também oferece benefícios de desempenho ao consultar tabelas temporais em um subconjunto do histórico de dados, usando a eliminação de partições.

Com o particionamento de tabela, é possível implementar uma janela deslizante para remover a parte mais antiga dos dados históricos da tabela de histórico e manter o tamanho da parte retida constante em relação à antiguidade. Uma janela deslizante mantém os dados na tabela de histórico iguais ao período de retenção necessário. A operação de alternar dados da tabela de histórico é suportada enquanto SYSTEM_VERSIONING é ON, o que significa que pode-se limpar uma parte dos dados do histórico sem criar uma janela de manutenção ou bloquear as suas tarefas habituais.

Observação

Para executar a comutação de partição, o índice clusterizado na tabela de histórico deve estar alinhado com o esquema de particionamento (ele deve conter ValidTo). A tabela de histórico padrão criada pelo sistema contém um índice clusterizado que inclui as colunas ValidTo e ValidFrom, o que é ideal para particionamento, inserção de novos dados de histórico e consultas temporais típicas. Para obter mais informações, consulte Tabelas temporais.

Uma janela deslizante tem dois conjuntos de tarefas que você precisa executar:

  • Uma tarefa de configuração de particionamento
  • Tarefas recorrentes de manutenção de partições

Para a ilustração, vamos supor que você deseja manter dados históricos por seis meses e que deseja manter todos os meses de dados em uma partição separada. Além disso, vamos supor que você ativou o versionamento do sistema em setembro de 2023.

Uma tarefa de configuração de particionamento cria a configuração de particionamento inicial para a tabela de histórico. Neste exemplo, você cria as mesmas partições numéricas que o tamanho da janela deslizante, em meses, além de uma partição vazia extra pré-preparada (explicada mais adiante neste artigo). Essa configuração garante que o sistema seja capaz de armazenar novos dados corretamente quando você inicia a tarefa recorrente de manutenção de partições pela primeira vez e garante que você nunca divida partições com dados para evitar movimentos de dados caros. Você deve executar essa tarefa usando Transact-SQL usando o script de exemplo mais adiante neste artigo.

A imagem a seguir mostra a configuração inicial de particionamento para manter seis meses de dados.

Diagrama mostrando a configuração inicial de particionamento para manter seis meses de dados.

Observação

Para obter as implicações de desempenho do uso de RANGE LEFT versus RANGE RIGHT ao configurar o particionamento, consulte Considerações sobre desempenho com particionamento de tabela mais adiante neste artigo.

A primeira e a última partições estão abertas nos limites inferior e superior, respetivamente, para garantir que cada nova linha tenha uma partição de destino, independentemente do valor na coluna de particionamento. Com o passar do tempo, novas linhas na tabela de histórico são colocadas em partições superiores. Quando a sexta partição é preenchida, você atinge o período de retenção desejado. Este é o momento de iniciar a tarefa recorrente de manutenção de partições pela primeira vez. Ele precisa ser programado para ser executado periodicamente, uma vez por mês neste exemplo.

A imagem a seguir ilustra as tarefas recorrentes de manutenção de partições (consulte as etapas detalhadas mais adiante nesta seção).

Diagrama mostrando as tarefas recorrentes de manutenção de partições.

As etapas detalhadas para as tarefas recorrentes de manutenção de partições são:

  1. SWITCH OUT: Crie uma tabela de preparo e, em seguida, alterne uma partição entre a tabela de histórico e a tabela de preparo usando a instrução ALTER TABLE com o argumento SWITCH PARTITION (veja o exemplo C. Alternando partições entre tabelas).

    ALTER TABLE [<history table>] SWITCH PARTITION 1 TO [<staging table>];

    Após a mudança de partição, pode-se, opcionalmente, arquivar os dados da tabela de preparo e, em seguida, eliminar ou truncar a tabela de preparo, para estar preparado para a próxima vez que precisar executar essa tarefa recorrente de manutenção de partição.

  2. MERGE RANGE: Mescle a partição vazia 1 com a partição 2 usando a função ALTER PARTITION com MERGE RANGE (Veja o exemplo B). Ao remover o limite mais baixo usando essa função, você efetivamente mescla a partição vazia 1 com a partição anterior 2 para formar uma nova partição 1. As outras partições também alteram efetivamente os seus ordinais.

  3. SPLIT RANGE: Crie uma nova partição vazia 7 usando o ALTER PARTITION FUNCTION com SPLIT RANGE (Veja o exemplo A). Ao adicionar um novo limite superior usando essa função, você efetivamente cria uma partição separada para o próximo mês.

Use Transact-SQL para criar partições na tabela de histórico

Use o seguinte script Transact-SQL para criar a função de partição, o esquema de partição e recriar o índice clusterizado de modo a estar alinhado com o esquema de partição e suas partições. Neste exemplo, você cria uma janela deslizante de seis meses com partições mensais, a partir de setembro de 2023.

BEGIN TRANSACTION

/*Create partition function*/
CREATE PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_ValidTo] (DATETIME2(7))
AS RANGE LEFT FOR VALUES (
    N'2023-09-30T23:59:59.999',
    N'2023-10-31T23:59:59.999',
    N'2023-11-30T23:59:59.999',
    N'2023-12-31T23:59:59.999',
    N'2024-01-31T23:59:59.999',
    N'2024-02-29T23:59:59.999'
);

/*Create partition scheme*/
CREATE PARTITION SCHEME [sch_Partition_DepartmentHistory_By_ValidTo]
AS PARTITION [fn_Partition_DepartmentHistory_By_ValidTo] TO (
    [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY],
    [PRIMARY], [PRIMARY], [PRIMARY]
);

/*Re-create index to be partition-aligned with the partitioning schema*/
CREATE CLUSTERED INDEX [ix_DepartmentHistory] ON [dbo].[DepartmentHistory] (
    ValidTo ASC,
    ValidFrom ASC
)
WITH (
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = ON,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON,
    DATA_COMPRESSION = PAGE
) ON [sch_Partition_DepartmentHistory_By_ValidTo](ValidTo);

COMMIT TRANSACTION;

Use Transact-SQL para manter partições no cenário de janela deslizante

Use o seguinte script Transact-SQL para manter partições no cenário de janela deslizante. Neste exemplo, você alterna a partição para setembro de 2023 usando MERGE RANGEe, em seguida, adiciona uma nova partição para março de 2024 usando SPLIT RANGE.

BEGIN TRANSACTION

/*(1) Create staging table */
CREATE TABLE [dbo].[staging_DepartmentHistory_September_2023] (
    DeptID INT NOT NULL,
    DeptName VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2(7) NOT NULL,
    ValidTo DATETIME2(7) NOT NULL
) ON [PRIMARY]
WITH (DATA_COMPRESSION = PAGE);

/*(2) Create index on the same filegroups as the partition that will be switched out*/
CREATE CLUSTERED INDEX [ix_staging_DepartmentHistory_September_2023]
ON [dbo].[staging_DepartmentHistory_September_2023] (
    ValidTo ASC,
    ValidFrom ASC
)
WITH (
    PAD_INDEX = OFF,
    SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY];

/*(3) Create constraints matching the partition that will be switched out*/
ALTER TABLE [dbo].[staging_DepartmentHistory_September_2023]
    WITH CHECK ADD CONSTRAINT [chk_staging_DepartmentHistory_September_2023_partition_1]
    CHECK (ValidTo <= N'2023-09-30T23:59:59.999')

ALTER TABLE [dbo].[staging_DepartmentHistory_September_2023]
    CHECK CONSTRAINT [chk_staging_DepartmentHistory_September_2023_partition_1]

/*(4) Switch partition to staging table*/
ALTER TABLE [dbo].[DepartmentHistory] SWITCH PARTITION 1
TO [dbo].[staging_DepartmentHistory_September_2023]
    WITH (WAIT_AT_LOW_PRIORITY(MAX_DURATION = 0 MINUTES, ABORT_AFTER_WAIT = NONE))

/*(5) [Commented out] Optionally archive the data and drop staging table
      INSERT INTO [ArchiveDB].[dbo].[DepartmentHistory]
      SELECT * FROM [dbo].[staging_DepartmentHistory_September_2023];
      DROP TABLE [dbo].[staging_DepartmentHIstory_September_2023];
*/

/*(6) merge range to move lower boundary one month ahead*/
ALTER PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_ValidTo]()
    MERGE RANGE(N'2023-09-30T23:59:59.999');

/*(7) Create new empty partition for "April and after" by creating new boundary point and specifying NEXT USED file group*/
ALTER PARTITION SCHEME [sch_Partition_DepartmentHistory_By_ValidTo] NEXT USED [PRIMARY]
    ALTER PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_ValidTo]()
    SPLIT RANGE(N'2024-03-31T23:59:59.999');
COMMIT TRANSACTION

Você pode modificar ligeiramente o script anterior e usá-lo no processo de manutenção mensal regular:

  1. Na etapa (1), crie uma nova tabela de preparo para o mês que deseja remover (outubro seria o próximo neste exemplo).
  2. Na etapa (3), crie e verifique a restrição que corresponde ao mês de dados que você deseja remover: ValidTo <= N'2023-10-31T23:59:59.999' para uma partição de outubro.
  3. Na etapa (4), particiona SWITCH em 1 na tabela de preparação recém-criada.
  4. Na etapa (6), altere a função de partição fundindo o limite inferior: MERGE RANGE(N'2023-10-31T23:59:59.999' após a remoção dos dados de outubro.
  5. Na etapa (7), divida a função de partição, criando um novo limite superior: SPLIT RANGE (N'2024-04-30T23:59:59.999' após remover os dados de outubro.

No entanto, a solução ideal seria executar regularmente um script Transact-SQL genérico que execute a ação apropriada todos os meses sem modificações. Você pode generalizar o script anterior para agir de acordo com os parâmetros fornecidos (o limite inferior que precisa ser mesclado e o novo limite que é criado com a divisão de partição). Para evitar a criação de uma tabela de preparo todos os meses, você pode criar uma com antecedência e reutilizá-la, alterando a restrição de verificação para corresponder à partição que você alterna. Para obter mais informações, consulte como a janela deslizante pode ser totalmente automatizada.

Considerações sobre o desempenho no particionamento de tabelas

Você deve executar as operações de MERGE e SPLIT RANGE para evitar a movimentação de dados, pois a movimentação de dados pode incorrer em sobrecarga de desempenho significativa. Para obter mais informações, consulte Modificar uma função de partição. Você faz isso usando RANGE LEFT em vez de RANGE RIGHT quando você cria a função de partição.

O diagrama a seguir descreve as opções de RANGE LEFT e RANGE RIGHT:

Diagrama mostrando as opções RANGE LEFT e RANGE RIGHT.

Quando você define uma função de partição como RANGE LEFT, os valores especificados são os limites superiores das partições. Quando você usa RANGE RIGHT, os valores especificados são os limites inferiores das partições. Quando você usa a operação MERGE RANGE para remover um limite da definição de função de partição, a implementação subjacente também remove a partição que contém o limite. Se essa partição não estiver vazia, os dados serão movidos para a partição resultante de MERGE RANGE operação.

Em um cenário de janela deslizante, você sempre remove o limite de partição mais baixo .

  • RANGE LEFT caso: O limite de partição mais baixo pertence à partição 1, que está vazia (após a mudança de partição), portanto, MERGE RANGE não incorre em nenhum movimento de dados.

  • RANGE RIGHT caso: O limite de partição mais baixo pertence à partição 2, que não está vazia, porque a partição 1 foi esvaziada por permuta. Neste caso, MERGE RANGE implica movimentação de dados (os dados da partição 2 são movidos para a partição 1). Para evitar isso, RANGE RIGHT no cenário de janela deslizante precisa ter partição 1, que está sempre vazia. Isso significa que, ao usares RANGE RIGHT, deves criar e manter uma partição extra em relação ao caso de RANGE LEFT.

Conclusão: O gerenciamento de partições é mais fácil quando você usa RANGE LEFT em uma partição deslizante e evita a movimentação de dados. No entanto, definir limites de partição com RANGE RIGHT é um pouco mais fácil, porque você não precisa lidar com problemas de verificação de data e hora.

Abordagem de usar script de limpeza personalizado

Nos casos em que o particionamento de tabela não é viável, outra abordagem é excluir os dados da tabela de histórico usando um script de limpeza personalizado. A exclusão de dados da tabela de histórico só é possível quando SYSTEM_VERSIONING = OFF. Para evitar inconsistência de dados, execute a limpeza durante uma janela de manutenção (quando as cargas de trabalho que modificam dados não estão ativas) ou dentro de uma transação (bloqueando efetivamente outras cargas de trabalho). Esta operação requer permissão CONTROL nas tabelas atuais e de histórico.

Para bloquear o mínimo possível os aplicativos regulares e as consultas de utilizadores, elimine os dados em fragmentos menores, com um intervalo, ao executar o script de limpeza dentro de uma transação. Embora não haja um tamanho ideal para cada bloco de dados a ser excluído para todos os cenários, excluir mais de 10.000 linhas em uma única transação pode impor uma penalidade significativa.

A lógica de limpeza é a mesma para todas as tabelas temporais, portanto, pode ser automatizada por meio de um procedimento armazenado genérico que você agenda para ser executado periodicamente, para cada tabela temporal para a qual você deseja limitar o histórico de dados.

O diagrama a seguir ilustra como sua lógica de limpeza deve ser organizada para uma única tabela para reduzir o efeito nas cargas de trabalho em execução.

Diagrama mostrando como sua lógica de limpeza deve ser organizada para uma única tabela para reduzir o efeito nas cargas de trabalho em execução.

Aqui estão algumas diretrizes de alto nível para implementar o processo. Agende a lógica de limpeza para ser executada todos os dias e itere em todas as tabelas temporais que precisam de limpeza de dados. Use o SQL Server Agent ou uma ferramenta diferente para agendar esse processo:

  • Exclua dados históricos em cada tabela temporal, começando das linhas mais antigas para as mais recentes em várias iterações em pequenos blocos, e evite excluir todas as linhas em uma única transação, conforme mostrado no diagrama anterior.

  • Implemente cada iteração como uma invocação de um procedimento armazenado genérico, que remove uma parte dos dados da tabela de histórico (consulte o exemplo de código a seguir para este procedimento).

  • Calcule quantas linhas você precisa excluir para uma tabela temporal individual sempre que invocar o processo. Com base no resultado e no número de iterações que você deseja ter, determine pontos de divisão dinâmicos para cada chamada de procedimento.

  • Planeje ter um período de atraso entre iterações para uma única tabela, para reduzir o efeito em aplicativos que acessam a tabela temporal.

Um procedimento armazenado que exclui os dados de uma única tabela temporal pode se parecer com o trecho de código a seguir. Revise esse código cuidadosamente e ajuste-o antes de aplicá-lo em seu ambiente.

Esse script gera três instruções que são executadas dentro de uma transação:

  1. SET SYSTEM_VERSIONING = OFF
  2. DELETE FROM <history_table>
  3. SET SYSTEM_VERSIONING = ON

No SQL Server 2016 (13.x), as duas primeiras etapas devem ser executadas em instruções EXEC separadas ou o SQL Server gera um erro semelhante ao exemplo a seguir:

Msg 13560, Level 16, State 1, Line XXX
Cannot delete rows from a temporal history table '<database_name>.<history_table_schema_name>.<history_table_name>'.

DROP PROCEDURE IF EXISTS usp_CleanupHistoryData;
GO
CREATE PROCEDURE usp_CleanupHistoryData @temporalTableSchema SYSNAME,
    @temporalTableName SYSNAME,
    @cleanupOlderThanDate DATETIME2
AS
DECLARE @disableVersioningScript NVARCHAR(MAX) = '';
DECLARE @deleteHistoryDataScript NVARCHAR(MAX) = '';
DECLARE @enableVersioningScript NVARCHAR(MAX) = '';
DECLARE @historyTableName SYSNAME
DECLARE @historyTableSchema SYSNAME
DECLARE @periodColumnName SYSNAME

/*Generate script to discover history table name and end of period column for given temporal table name*/
EXECUTE sp_executesql
N'SELECT @hst_tbl_nm = t2.name,
      @hst_sch_nm = s2.name,
      @period_col_nm = c.name
  FROM sys.tables t1
  INNER JOIN sys.tables t2 ON t1.history_table_id = t2.object_id
  INNER JOIN sys.schemas s1 ON t1.schema_id = s1.schema_id
  INNER JOIN sys.schemas s2 ON t2.schema_id = s2.schema_id
  INNER JOIN sys.periods p ON p.object_id = t1.object_id
  INNER JOIN sys.columns c ON p.end_column_id = c.column_id AND c.object_id = t1.object_id
  WHERE t1.name = @tblName AND s1.name = @schName',
N'@tblName sysname,
    @schName sysname,
    @hst_tbl_nm sysname OUTPUT,
    @hst_sch_nm sysname OUTPUT,
    @period_col_nm sysname OUTPUT',
@tblName = @temporalTableName,
@schName = @temporalTableSchema,
@hst_tbl_nm = @historyTableName OUTPUT,
@hst_sch_nm = @historyTableSchema OUTPUT,
@period_col_nm = @periodColumnName OUTPUT

IF @historyTableName IS NULL OR @historyTableSchema IS NULL OR @periodColumnName IS NULL
    THROW 50010, 'History table cannot be found. Either specified table is not system-versioned temporal or you have provided incorrect argument values.', 1;

SET @disableVersioningScript = @disableVersioningScript
    + 'ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName
    + '] SET (SYSTEM_VERSIONING = OFF)'
SET @deleteHistoryDataScript = @deleteHistoryDataScript + ' DELETE FROM ['
    + @historyTableSchema + '].[' + @historyTableName + '] WHERE ['
    + @periodColumnName + '] < ' + '''' + CONVERT(VARCHAR(128), @cleanupOlderThanDate, 126) + ''''
SET @enableVersioningScript = @enableVersioningScript + ' ALTER TABLE ['
    + @temporalTableSchema + '].[' + @temporalTableName
    + '] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema
    + '].[' + @historyTableName + '], DATA_CONSISTENCY_CHECK = OFF )); '

BEGIN TRANSACTION
    EXEC (@disableVersioningScript);
    EXEC (@deleteHistoryDataScript);
    EXEC (@enableVersioningScript);
COMMIT;

Utilizar a abordagem de política de retenção de historial temporal

Aplica-se a: SQL Server 2017 (14.x) e versões posteriores e Banco de Dados SQL do Azure.

A retenção do histórico temporal pode ser configurada no nível da tabela individual, o que permite que os usuários criem políticas flexíveis de envelhecimento. A retenção temporal requer que você defina apenas um parâmetro durante a criação da tabela ou a alteração do esquema.

Depois de definir a política de retenção, o Mecanismo de Banco de Dados começa a verificar regularmente se há linhas históricas qualificadas para limpeza automática de dados. A identificação de linhas correspondentes e sua remoção da tabela de histórico ocorrem de forma transparente, em uma tarefa em segundo plano agendada e executada pelo sistema. A condição de idade para as linhas da tabela de histórico é verificada com base na coluna que representa o final do período de SYSTEM_TIME (nestes exemplos, a coluna ValidTo). Se o período de retenção for definido como seis meses, por exemplo, as linhas da tabela elegíveis para limpeza satisfazem a seguinte condição:

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

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

Como configurar a política de retenção

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

SELECT is_temporal_history_retention_enabled, name
FROM sys.databases;

O sinalizador de banco de dados is_temporal_history_retention_enabled está definido como ON por padrão, mas você pode alterá-lo com a instrução ALTER DATABASE. Esse valor é definido automaticamente como OFF após uma operação de restauração point-in-time (PITR). Para ativar a limpeza da retenção do histórico temporal no seu banco de dados, execute a instrução a seguir. Deve substituir <myDB> pela base de dados que pretende alterar:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION ON;

A política de retenção é configurada durante a criação da tabela especificando o valor para o 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
    )
);

Você pode especificar o período de retenção usando diferentes unidades de tempo: DAYS, WEEKS, MONTHSe YEARS. Se HISTORY_RETENTION_PERIOD for omitido, assume-se a retenção de INFINITE. Você também pode usar a palavra-chave INFINITE explicitamente.

Em alguns cenários, convém 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 revisar o estado atual da política de retenção, use o exemplo a seguir. Esta consulta associa o indicador de ativação de retenção temporal ao nível do banco de dados com os 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;

Como o Mecanismo de Banco de Dados exclui linhas antigas

O processo de limpeza depende do layout de índice da tabela de histórico. 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 um período de retenção finito. A lógica de limpeza para o índice clusterizado rowstore (árvore B+) exclui linhas antigas em blocos menores (até 10.000), minimizando a pressão no log do banco de dados e no subsistema de E/S. Embora a lógica de limpeza utilize o índice de árvore B+ necessário, não se pode garantir a ordem das eliminações para as linhas que são mais antigas do que o período de retenção. Não dependa da ordem de limpeza em seus aplicativos.

A tarefa de limpeza para o armazenamento em colunas clusterizado remove grupos de linhas inteiros de uma só vez (normalmente contendo 1 milhão de linhas cada), o que é mais eficiente, especialmente quando os dados históricos são gerados a um ritmo alto.

Captura de ecrã da retenção columnstore clusterizada.

A compactação de dados e a limpeza de retenção tornam o índice columnstore clusterizado uma escolha perfeita para cenários onde a carga de trabalho gera rapidamente uma grande quantidade de dados históricos. Esse padrão é típico para cargas de trabalho de processamento transacional intensivo que usam tabelas temporais para controle e auditoria de alterações, análise de tendências ou ingestão de dados de IoT.

Para obter mais informações, consulte Gerenciar dados históricos em tabelas temporais com política de retenção.

Conteúdo relacionado