Compartilhar via

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

  • Artigo

Aplica-se a: SQL Server 2016 (13.x) e posterior Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

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

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

Uma tabela de histórico grande e crescente pode se tornar um problema, tanto devido a custos de armazenamento puro, como por impor um imposto de desempenho sobre consultas temporais. O desenvolvimento de uma política de retenção de dados para o gerenciamento de dados na tabela de histórico é um aspecto importante do planejamento e do gerenciamento do ciclo de vida de cada tabela temporal.

Gerenciamento da retenção de dados da 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 ser parte da lógica de negócios do aplicativo usando as tabelas temporais. Por exemplo, aplicativos na auditoria de dados e cenários de viagem no tempo têm requisitos sólidos em termos de quanto tempo os dados históricos devem estar disponíveis para consulta online.

Depois de determinar o 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. As abordagens a seguir estão disponíveis para gerenciar dados históricos na tabela de histórico temporal:

Com cada uma dessas abordagens, a lógica para a migração ou limpeza de dados históricos baseia-se na coluna que corresponde ao término do período na tabela atual. 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 (DAYS, -30, SYSUTCDATETIME ()) especifica que dados históricos com mais de um mês precisam ser removidos ou movidos da tabela de histórico.

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

Uso da abordagem de particionamento de tabela

As tabelas particionadas e índices podem tornar as tabelas grandes mais gerenciáveis e escaláveis. Com a abordagem de particionamento de tabela, você pode implementar a limpeza de dados personalizados ou o 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.

Com o particionamento de tabela, você pode implementar uma janela deslizante para mover a parte mais antiga dos dados históricos da tabela de histórico e manter constante o tamanho da parte retida em termos de idad. 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 extração de dados da tabela de histórico é suportada enquanto SYSTEM_VERSIONING estiver ON, o que significa que você pode limpar uma parte dos dados de histórico sem introduzir uma janela de manutenção ou bloquear suas cargas de trabalho regulares.

Observação

Para executar a alternância 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, que são ótimas para o particionamento, inserindo novos dados históricos e consulta temporal típica. Para saber mais, veja Tabelas temporais.

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

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

Para fins ilustrativos, vamos supor que você queira manter dados históricos por seis meses e que queira manter todos os meses de dados em uma partição separada. Além disso, vamos supor que você ativou a versão de 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 de número que o tamanho da janela deslizante, em meses, além de uma partição vazia adicional preparada (explicado posteriormente neste artigo). Essa configuração garante que o sistema é capaz de armazenar novos dados corretamente ao iniciarmos a tarefa de manutenção de partição recorrente para a primeira hora e garante que nunca dividiremos partições contendo dados para evitar movimentos de dados dispediosos. Essa tarefa deve ser executada usando o Transact-SQL com o script de exemplo neste artigo.

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

O diagrama que mostra a configuração inicial de particionamento para manter seis meses de dados.

Observação

Para as implicações de desempenho do uso de RANGE LEFT versus RANGE RIGHT durante a configuração de particionamento, consulte Considerações de desempenho com o particionamento de tabela mais adiante neste artigo.

A primeira e a última partição são abertas em limites superiores e inferiores, respectivamente, para garantir que cada nova linha tenha a partição de destino, independentemente do valor na coluna de particionamento. Com o passar do tempo, novas linhas na tabela de histórico serão levadas para partições superiores. Quando a sexta partição ficar preenchida, você atinge o período de retenção definido como destino. Este é o momento de iniciar a tarefa recorrente de manutenção de partição pela primeira vez. Ela precisa ser agendada para ser executada periodicamente, uma vez por mês neste exemplo.

A figura a seguir ilustra as tarefas recorrentes de manutenção de partição (confira as etapas detalhadas mais adiante neste artigo).

O diagrama que mostra as tarefas de manutenção de partição recorrentes.

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

  1. SWITCH OUT: crie uma tabela temporária e 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 (confira o Exemplo C. Como alternar partições entre tabelas).

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

    Após a alternância de partição, você pode optar por arquivar os dados da tabela de preparo e, em seguida, remover ou truncar a tabela de preparo para já ficar preparada para a próxima vez que você precisar realizar essa tarefa de manutenção de partição recorrente.

  2. MERGE RANGE: mescle a partição vazia 1 com a partição 2 usando ALTER PARTITION FUNCTION com MERGE RANGE (veja o exemplo B). Ao remover o limite mais baixo usando esta função, você efetivamente mescla a partição vazia 1 com a partição 2 anterior para formar uma nova partição 1. As outras partições também alteraram efetivamente seus números ordinais.

  3. SPLIT RANGE: crie uma nova partição vazia 7 usando ALTER PARTITION FUNCTION com SPLIT RANGE (veja o exemplo A). Ao adicionar um novo limite superior usando essa função, 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 seguinte script Transact-SQL para criar a função de partição, o esquema de partição e recriar o índice clusterizado para ser alinhado por partição com o esquema de partição, as partições. 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 na janela de código abaixo para manter as partições no cenário de janela deslizante. Neste exemplo, você alterna a partição para setembro de 2023 usando o MERGE RANGE e, em seguida, adicionar 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 regular mensal:

  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 coincide com o mês dos dados que você deseja remover: ValidTo <= N'2023-10-31T23:59:59.999' para a partição de outubro.
  3. Na etapa (4), SWITCH a partição 1 para a tabela de preparo criada recentemente.
  4. Na etapa (6), altere a função de partição mesclando o limite inferior: MERGE RANGE(N'2023-10-31T23:59:59.999' depois que você moveu os 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' depois que você mover os dados de outubro.

No entanto, a solução ideal seria executar regularmente um script Transact-SQL genérico que executasse a ação apropriada todos os meses sem precisar de modificações. É possível generalizar o script anterior para agir sobre parâmetros fornecidos (limite inferior que precisa ser mesclado e o novo limite que será criado com 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ê alternar. Para obter mais informações, consulte como a janela deslizante pode ser totalmente automatizada.

Considerações sobre desempenho com o particionamento de tabela

Execute as operações MERGE e SPLIT RANGE para evitar qualquer movimentação de dados, já que isso pode sobrecarregar o desempenho de forma 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 RANGE LEFT e RANGE RIGHT:

O diagrama que mostra 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 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, os dados serão movidos para a partição que é o resultado da operação MERGE RANGE.

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

  • Caso RANGE LEFT: O limite da partição menor pertence à partição 1, que está vazia (depois de alternar a partição), portanto, MERGE RANGE não incorrerá em qualquer movimentação de dados.

  • Caso RANGE RIGHT: O limite inferior de partição pertence à partição 2, que não está vazia, porque a partição 1 foi esvaziada ao sair. Nesse caso, MERGE RANGE incorre em 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 deve ter a partição 1, que sempre está vazia. Isso significa que, se você usar RANGE RIGHT, deve criar e manter uma partição extra em comparação com o caso RANGE LEFT.

Conclusão: o gerenciamento de partições é mais fácil quando RANGE LEFT é usado na partição deslizante, evitando a movimentação 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.

Como usar a abordagem de script de limpeza personalizada

Em 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. Excluir dados da tabela de histórico só é possível quando SYSTEM_VERSIONING = OFF. Para evitar a 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 a permissão CONTROL nas tabelas atuais e de histórico.

Para bloquear minimamente os aplicativos regulares e consultas do usuário, exclua dados em partes menores com um atraso ao executar o script de limpeza dentro de uma transação. Embora, para todos os cenários, não haja um tamanho ideal para cada bloco de dados a ser excluído, a exclusão de mais de 10.000 linhas em uma única transação pode impor uma penalidade significativa.

A lógica de limpeza é a mesma para cada tabela temporal e, portanto, pode ser automatizada por meio de um procedimento armazenado genérico agendado 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 a lógica de limpeza deve ser organizada para uma única tabela reduzir o efeito sobre as cargas de trabalho em execução.

O diagrama que mostra como a lógica de limpeza deve ser organizada para que uma tabela reduza o efeito sobre as cargas de trabalho em execução.

Veja algumas diretrizes de alto nível para implementar o processo. Agende a lógica de limpeza para execução diária e itere todas as tabelas temporais que precisam de limpeza de dados. Use o SQL Server Agent ou outra ferramenta para agendar esse processo:

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

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

  • 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, determine pontos de divisão dinâmicos para cada chamada de procedimento.

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

Um procedimento armazenado que exclui os dados de uma única tabela temporal pode parecer como o snippet de código a seguir. Revise esse código com cuidado e ajuste-o antes de aplicá-lo ao 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. 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 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;

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

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

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 retenção temporal exige que você defina apenas um parâmetro durante a criação da tabela ou 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 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 em uma tarefa em segundo plano que é agendada e executada pelo sistema. A condição de vencimento para as linhas da tabela de histórico é verificada com base na coluna que representa o final do período SYSTEM_TIME (em nossos exemplos, na coluna ValidTo). 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, 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 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 de banco de dados is_temporal_history_retention_enabled é definido como ON por padrão, mas você pode alterá-lo com a instrução ALTER DATABASE. Este valor é automaticamente definido como OFF após a operação restauração pontual (PITR). Para habilitar a limpeza da retenção de histórico temporal para seu banco de dados, execute a instrução a seguir. Você deve substituir <myDB> pelo banco de dados que deseja alterar:

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

É possível especificar o período de retenção usando unidades de tempo diferentes: DAYS, WEEKS, MONTHS e YEARS. Se HISTORY_RETENTION_PERIOD for omitido, será presumida retenção INFINITE. Também é possível usar a palavras-chave INFINITE explicitamente.

Em alguns cenários, pode ser útil 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 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;

Como o mecanismo de banco de dados exclui linhas antigas

O processo de limpeza depende do layout do índice da tabela de histórico. Apenas 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. A lógica de limpeza para o índice clusterizado rowstore (árvore B+) exclui as linhas antigas em partes 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 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 garantida. Não assuma nenhuma dependência da 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 é mais eficiente, especialmente quando os dados históricos são gerados em alto ritmo.

Captura de tela da retenção columnstore clusterizada.

A compactação de dados e a limpeza da retenção tornam 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.

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

Conteúdo relacionado