Gerenciar a retenção de dados históricos em tabelas temporárias com versão do sistema

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

Uma tabela temporal com controle de versão do sistema mantém todas as versões anteriores de cada linha em sua tabela de histórico. A tabela de histórico pode aumentar o tamanho do seu banco de dados mais do que as tabelas regulares sob as seguintes condições:

• Você mantém dados históricos por um longo período de tempo.
• Você tem um padrão de modificação de dados com muitas atualizações ou exclusões.

Uma tabela de histórico grande e em constante crescimento pode se tornar um problema, tanto por causa dos custos de armazenamento quanto do imposto de desempenho que ela impõe às consultas temporais. Desenvolver uma política de retenção de dados para a tabela de histórico é uma parte importante do planejamento e gerenciamento do ciclo de vida de cada tabela temporal.

Planeje uma política de retenção de dados

Para gerenciar a retenção de dados de tabelas temporais, primeiro determine o 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 da aplicação que usa as tabelas temporais. Por exemplo, aplicações em auditoria de dados e cenários de viagem no tempo têm exigências firmes sobre quanto tempo os dados históricos devem estar disponíveis para consulta online.

Depois de determinar seu período de retenção de dados, desenvolva um plano para gerenciar dados históricos. Decida como e onde armazenar seus dados históricos e como excluir dados históricos mais antigos do que seus requisitos de retenção.

Cada abordagem neste artigo atua sobre a coluna que corresponde ao fim do período na tabela atual, que é a ValidTo coluna nos exemplos que seguem. O final do valor do período para cada linha determina o momento em que a versão se torna fechada, ou seja, quando ela chega à tabela de histórico. Por exemplo, a condição ValidTo < DATEADD (DAY, -30, SYSUTCDATETIME()) corresponde a dados históricos com mais de 30 dias de idade.

Escolha uma das seguintes abordagens para executar ações nessas linhas:

Os exemplos de particionamento e limpeza personalizada neste artigo usam as amostras do artigo Criar uma tabela temporal com controle de versão do sistema.

Use uma política de retenção de histórico temporal

Aplica-se a: SQL Server 2017 (14.x) e versões posteriores, Banco de Dados SQL do Azure, Instância Gerenciada de SQL do Azure e banco de dados SQL no Microsoft Fabric.

Você pode configurar a retenção de histórico temporal no nível da tabela individual, o que permite criar políticas flexíveis de envelhecimento. Para permitir a retenção temporal, defina HISTORY_RETENTION_PERIOD durante a criação da tabela ou uma alteração de esquema.

Depois que você define a política de retenção, o Mecanismo de Banco de Dados executa uma tarefa agendada em segundo plano que encontra e remove de forma transparente linhas históricas cujo valor de fim de período é mais antigo que o período de retenção.

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 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 do banco de dados tem como padrão ON, mas você pode alterá-lo usando a instrução ALTER DATABASE. O Mecanismo de Banco de Dados também o define como OFF automaticamente após uma operação de restauração pontual no tempo (PITR), conforme descrito em Considerações sobre restauração pontual no tempo. Para habilitar a limpeza de retenção de histórico temporal para seu banco de dados, execute a instrução a seguir. Substitua <myDB> pelo banco de dados que você deseja alterar:

ALTER DATABASE [<myDB>]
    SET TEMPORAL_HISTORY_RETENTION ON;

Você pode configurar a política de retenção durante a criação da tabela especificando um valor para o HISTORY_RETENTION_PERIOD parâmetro:

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
    )
);

Com essa política em vigor, as linhas em dbo.WebsiteUserInfoHistory tornam-se elegíveis para limpeza quando atendem à seguinte condição:

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

Você pode especificar o período de retenção em DAYS, WEEKS, MONTHS, ou YEARS. Se você omitir HISTORY_RETENTION_PERIOD, a retenção será definida por padrão como INFINITE. Também é possível usar a palavras-chave INFINITE explicitamente.

Em alguns cenários, você pode querer configurar a retenção após a criação da tabela ou mudar o valor previamente configurado. 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 sinalizador de habilitação da retenção temporal no nível do banco de dados aos períodos de retenção de 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 AS T1
    OUTER APPLY (
        SELECT is_temporal_history_retention_enabled
        FROM sys.databases
        WHERE name = DB_NAME()
) AS DB
    LEFT OUTER JOIN sys.tables AS 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 do índice da tabela de histórico. Você pode configurar uma política de retenção finita apenas em tabelas de histórico com um índice rowstore clusterizado (árvore B) ou columnstore clusterizado. Uma tarefa em segundo plano realiza limpeza de dados antigos para todas as tabelas temporais com um período de retenção finito.

Índice rowstore de árvore B

O índice rowstore clusterizado deve começar com a coluna correspondente ao final do período SYSTEM_TIME. Se tal índice não existir, você não pode configurar um período finito de retenção:

Msg 13765, Level 16, State 1
Setting finite retention period failed on system-versioned temporal table
'dbo.WebsiteUserInfo' because the history table 'dbo.WebsiteUserInfoHistory'
does not contain required clustered index. Consider creating a clustered
columnstore or B-tree index starting with the column that matches end of
SYSTEM_TIME period, on the history table.

A tabela de histórico padrão já possui um índice clusterizado compatível. Se você tentar colocar esse índice em uma tabela de histórico com um período de retenção finito, a operação falha com o seguinte erro:

Msg 13766, Level 16, State 1
Cannot drop the clustered index 'WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory'
because it is being used for automatic cleanup of aged data. Consider setting HISTORY_RETENTION_PERIOD to INFINITE on the corresponding system-versioned
temporal table if you need to drop this index.

A lógica de limpeza do índice clusterizado rowstore elimina linhas envelhecidas em blocos menores (até 10.000), minimizando a pressão sobre o log do banco de dados e o subsistema de E/S. Embora a lógica de limpeza use o índice de árvore B necessário, ela não pode garantir a ordem de exclusão para linhas mais antigas que o período de retenção. Não dependa da ordem de limpeza em seus aplicativos.

Índice columnstore clusterizado

A tarefa de limpeza para o columnstore clusterizado remove grupos de linhas inteiros de uma só vez. Cada grupo de linhas normalmente contém um milhão de linhas. Esse método é mais eficiente, especialmente quando sua carga de trabalho gera dados históricos em alta velocidade.

A compressão de dados e a limpeza de retenção tornam o índice columnstore clusterizado uma boa escolha para cenários em que sua carga de trabalho gera rapidamente uma grande quantidade de dados históricos. Esse padrão é típico de cargas de trabalho de processamento transacional intensivo que utilizam tabelas temporais para acompanhamento e auditoria de mudanças, análise de tendências ou ingestão de dados da Internet das Coisas (IoT).

A limpeza no índice columnstore clusterizado funciona de forma otimizada quando as linhas históricas chegam em ordem crescente (ordenadas pela coluna de fim de período). Essa condição é sempre o caso quando apenas o SYSTEM_VERSIONING mecanismo preenche a tabela de histórico. Se as linhas na tabela de histórico não estiverem ordenadas pela coluna de fim de período (o que pode acontecer ao migrar dados históricos existentes), recrie o índice columnstore clusterizado sobre um índice rowstore B-tree devidamente ordenado para obter o desempenho ideal.

Evite reconstruir o índice columnstore clusterizado em uma tabela de histórico com um período de retenção finito, pois a reconstrução pode alterar a ordenação dos grupos de linhas que a operação de versionamento do sistema impõe naturalmente. Se precisar reconstruir o índice columnstore clusterizado na tabela de histórico, recrie-o sobre um índice B-tree compatível para preservar a ordenação dos grupos de linhas necessária para a limpeza regular de dados. Siga a mesma abordagem se você criar uma tabela temporal com uma tabela de histórico existente que tenha um índice de coluna agrupado sem ordem garantida dos dados:

/* 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 the clustered columnstore index */
CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory
    ON WebsiteUserInfoHistory WITH (DROP_EXISTING = ON);

Quando você configura um período de retenção finito para uma tabela de histórico com um índice clusterizado de columnstore, não é possível criar índices adicionais não agrupados de árvore B nessa tabela:

CREATE NONCLUSTERED INDEX IX_WebHistNCI
    ON WebsiteUserInfoHistory(UserName);

A instrução anterior falha com o seguinte erro:

Msg 13772, Level 16, State 1
Cannot create non-clustered index on a temporal history table 'WebsiteUserInfoHistory' since it has finite retention period and clustered columnstore index defined.

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. A tarefa de limpeza exclui linhas antigas a qualquer momento e em ordem arbitrária.

A captura de tela a seguir mostra o plano de consulta para uma consulta básica. Este exemplo pressupõe um período de retenção de um MONTH na tabela WebsiteUserInfo:

SELECT *
FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

O plano de consulta inclui um filtro extra na coluna de fim de período (ValidTo) no operador Clustered Index Scan (destacado na imagem a seguir) na tabela de histórico.

Se você consultar diretamente a tabela de histórico, pode ver linhas mais antigas que o período de retenção especificado, mas sem garantia de resultados de consulta repetíveis. A captura de tela a seguir mostra o plano de consulta para uma consulta na tabela de histórico, sem filtros extras:

Não confie na lógica de negócios que lê a tabela de histórico além do período de retenção, porque você pode ter resultados inconsistentes ou inesperados. Use consultas temporais com a FOR SYSTEM_TIME cláusula para analisar dados em tabelas temporais.

Considerações da recuperação pontual

Quando você restaura um banco de dados para um ponto específico no tempo, o novo banco de dados tem a retenção temporal desativada no nível do banco de dados (is_temporal_history_retention_enabled definida para OFF). Esse comportamento permite que você inspecione linhas históricas mais antigas que o período de retenção antes que a tarefa de limpeza as remova. Para retomar a limpeza automática do banco de dados restaurado, defina TEMPORAL_HISTORY_RETENTION novamente para ON.

Uso do particionamento de tabelas

As tabelas particionadas e índices podem tornar as tabelas grandes mais gerenciáveis e escaláveis. Usando a abordagem de particionamento de tabelas, você pode implementar limpeza personalizada de dados ou arquivamento offline com base em uma condição de tempo. O particionamento de tabela também oferece benefícios de desempenho ao consultar tabelas temporais em um subconjunto de histórico de dados por meio da eliminação da partição.

Use o particionamento de tabela para implementar uma janela deslizante a fim de remover da tabela de histórico a parte mais antiga dos dados históricos, mantendo constante, por idade, o tamanho da parte retida. Uma janela deslizante mantém os dados na tabela de histórico iguais ao período de retenção exigido. A tabela de histórico permite remover dados enquanto SYSTEM_VERSIONING estiver ON, o que significa que você pode limpar parte dos dados do histórico sem exigir uma janela de manutenção nem bloquear suas cargas de trabalho normais.

Uma janela deslizante requer dois conjuntos de tarefas:

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

Para esta ilustração, suponha que você queira manter dados históricos por seis meses e que queira manter cada mês de dados em uma partição separada. Além disso, suponha 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 o mesmo número de partições que o tamanho da janela deslizante, em meses, mais uma partição vazia extra. Essa configuração garante que o sistema possa armazenar novos dados corretamente quando você inicia a tarefa recorrente de manutenção de partições. Também garante que você nunca divida partições que contenham dados, o que evita movimentos de dados caros. Defina a função de partição com RANGE LEFT em vez de RANGE RIGHT. Para mais informações, veja Considerações de Desempenho com particionamento de tabelas mais adiante neste artigo.

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

A primeira e a última partição ficam abertas nos limites inferior e superior, respectivamente, para garantir que cada nova linha tenha uma partição de destino, independentemente do valor na coluna de partição. Ao longo do tempo, novas linhas na tabela de histórico chegam em partições superiores. Quando a sexta partição fica cheia, você atinge o período de retenção desejado. Neste ponto, inicie a tarefa recorrente de manutenção da partição pela primeira vez. Programe para rodar periodicamente, uma vez por mês neste exemplo.

A imagem a seguir ilustra as tarefas recorrentes de manutenção de partições.

Cada execução da tarefa de manutenção recorrente executa os seguintes passos:

1. SWITCH OUT: Crie uma tabela de preparo e, em seguida, altere 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.

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

   Após a troca de partição, você pode, se desejar, arquivar os dados da tabela de preparação e, em seguida, excluir ou truncar essa tabela para se preparar para o próximo ciclo de manutenção.

2. MERGE RANGE: Mesclar a partição vazia 1 com a partição 2 usando a instrução ALTER PARTITION FUNCTION com MERGE RANGE. Quando você usa essa função para remover a fronteira mais baixa, você efetivamente funde a partição 1 vazia com a partição 2 anterior para formar uma nova partição 1. As outras partições também alteram efetivamente seus ordinais.

3. SPLIT RANGE: Crie uma nova partição 7 vazia usando a ALTER PARTITION FUNCTION instrução com SPLIT RANGE. Quando você usa essa função para adicionar um novo limite superior, você cria efetivamente uma partição separada para o mês seguinte.

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

Use o script Transact-SQL a seguir para criar a função de partição, o esquema de partição e recriar o índice clusterizado para que fique alinhado ao esquema de partição. Para este exemplo, você cria uma janela deslizante de seis meses com partições mensais começando em 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 o Transact-SQL para manter partições no cenário de janela deslizante

Use o seguinte script Transact-SQL para manter as partições no cenário de janela deslizante. Para esse exemplo, você troca a partição de setembro de 2023 usando MERGE RANGE, e então 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 to switch 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 to switch 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;

No entanto, a solução ideal é rodar regularmente um script genérico de Transact-SQL todo mês sem modificações. Você pode generalizar o script anterior para agir com base nos parâmetros fornecidos (o limite inferior que precisa ser fundido e o novo limite criado pela divisão da partição). Para evitar criar uma tabela de staging todos os meses, crie uma com antecedência e reutilize-a, alterando a restrição CHECK para corresponder à partição que será removida. Para mais informações, consulte como automatizar totalmente o cenário de janela deslizante.

Considerações sobre desempenho com o particionamento de tabela

Execute as operações MERGE RANGE e SPLIT RANGE de forma a evitar a movimentação de dados, pois a movimentação de dados pode causar uma sobrecarga significativa de desempenho. Para obter mais informações, consulte Modificar uma função de partição.

Quando você cria a 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 da 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, MERGE RANGE os dados são movidos para a partição resultante.

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

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

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

• RANGE RIGHT caso: O limite inferior da partição pertence à partição 2, que não está vazia porque a substituição esvazia apenas a partição 1. Nesse caso, MERGE RANGE causa movimentação de dados, movendo dados de uma partição 2 para 1outra. Para evitar esse movimento de dados, RANGE RIGHT no cenário da janela deslizante precisa ter partição 1, que está sempre vazia. Esse requisito significa que, se você usar RANGE RIGHT, deve criar e manter uma partição extra em relação ao RANGE LEFT caso.

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

Use um script de limpeza personalizado

Quando uma política de retenção não está disponível para sua tabela e o particionamento da tabela não é viável, você pode excluir os dados da tabela de histórico usando um script de limpeza personalizado. Esse processo só é possível quando SYSTEM_VERSIONING = OFF. Para evitar inconsistência de dados, realize a limpeza durante uma janela de manutenção (quando 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 a permissão CONTROL nas tabelas atuais e de histórico.

A lógica de limpeza é a mesma para todas as tabelas temporais, então você pode automatizá-la por meio de um procedimento genérico armazenado. Use o SQL Server Agent ou uma ferramenta diferente para agendar esse procedimento para rodar todos os dias, iterando sobre cada tabela temporal para a qual você deseja limitar o histórico de dados.

O diagrama a seguir ilustra como organizar sua lógica de limpeza 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:

• Exclua dados históricos em cada tabela temporal em várias iterações de pequenos blocos. Comece pelas linhas mais antigas e vá para as mais recentes. Evite deletar todas as linhas de uma única transação, como mostra o diagrama anterior. Embora nenhum tamanho único de bloco funcione para todos os cenários, deletar mais de 10.000 linhas em uma única transação pode impor uma penalidade significativa.

• 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.

• Calcule quantas linhas você precisa excluir de uma tabela temporal individual toda vez que você chamar o processo. Com base no resultado e no número de iterações que você quer, determine pontos de divisão dinâmica para cada invocação de procedimento.

• Planeje um atraso entre as iterações para uma única tabela, para reduzir o efeito sobre aplicações que acessam a tabela temporal.

O procedimento armazenado a seguir exclui os dados de uma única tabela temporal. Ele descobre a tabela de histórico e a coluna de fim de período a partir das visualizações do catálogo, e então executa três instruções dentro de uma transação: SET SYSTEM_VERSIONING = OFF, DELETE FROM <history_table>, e SET SYSTEM_VERSIONING = ON. Revise esse código cuidadosamente e ajuste antes de aplicá-lo no seu ambiente.

No SQL Server 2016 (13.x), as duas primeiras etapas devem ser executadas em instruções EXECUTE separadas. Caso contrário, o SQL Server gerará um erro semelhante ao seguinte exemplo:

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 AS NVARCHAR (MAX) = '';
DECLARE @deleteHistoryDataScript AS NVARCHAR (MAX) = '';
DECLARE @enableVersioningScript AS NVARCHAR (MAX) = '';
DECLARE @historyTableName AS SYSNAME;
DECLARE @historyTableSchema AS SYSNAME;
DECLARE @periodColumnName AS 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 AS t1
         INNER JOIN sys.tables AS t2
             ON t1.history_table_id = t2.object_id
         INNER JOIN sys.schemas AS s1
             ON t1.schema_id = s1.schema_id
         INNER JOIN sys.schemas AS s2
             ON t2.schema_id = s2.schema_id
         INNER JOIN sys.periods AS p
             ON p.object_id = t1.object_id
         INNER JOIN sys.columns AS 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;
    EXECUTE (@disableVersioningScript);
    EXECUTE (@deleteHistoryDataScript);
    EXECUTE (@enableVersioningScript);
COMMIT TRANSACTION;

Conteúdo relacionado

• Tabelas temporais
• Primeiros passos com tabelas temporais versionadas pelo sistema
• Verificações de consistência do sistema de tabela temporal
• Partição com tabelas temporais
• Considerações e limitações da tabela temporal
• Segurança da tabela temporal
• Tabelas temporais versionadas pelo sistema com tabelas otimizadas para memória
• Exibições e funções de metadados de tabela temporal