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 Azure
Com tabelas temporais com 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. Portanto, 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 considerada 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, a próxima etapa é desenvolver um plano para gerenciar dados históricos. Você deve decidir 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.
Observação
Os exemplos neste artigo usam esta tabela temporal Criar uma 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. Usando a abordagem de particionamento de tabela, você pode usar partições de tabela de histórico para 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 idade, mantendo 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ê criaria 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.
Observação
Confira as considerações de desempenho com o particionamento de tabela mais adiante neste artigo para as implicações de desempenho do uso de RANGE LEFT versus RANGE RIGHT durante a configuração de particionamento.
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 de manutenção de partição recorrente 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).
As etapas detalhadas para as tarefas de manutenção de partição recorrentes são:
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 (Transact-SQL) 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.
MERGE RANGE: Mescle a partição vazia1com a partição2usando ALTER PARTITION FUNCTION (Transact-SQL) comMERGE RANGE(veja o exemplo B). Ao remover o limite mais baixo usando esta função, você efetivamente mescla a partição vazia1com a partição2anterior para formar a nova partição1. As outras partições também alteraram efetivamente seus números ordinais.SPLIT RANGE: Crie uma nova partição vazia 7 usando ALTER PARTITION FUNCTION (Transact-SQL) comSPLIT 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:
- Na etapa (1) criar nova tabela de preparo para o mês que deseja remover (outubro seria o próximo neste exemplo).
- Na etapa (3) criar e verificar 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. - Na etapa (4)
SWITCHpartição1para a tabela de preparo criada recentemente. - Na etapa (6) alterar 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. - Na etapa (7) dividir 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 fosse capaz de executar a ação apropriada todos os meses sem modificar o script. É 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 tabelas de preparo todos os meses, é possível criar uma com antecedência e reutilizá-la. Para isso, você pode alterar a restrição check para corresponder à partição que será comutada. Dê uma olhada nas páginas a seguir para obter ideias sobre como a janela deslizante pode ser totalmente automatizada usando um script Transact-SQL.
Considerações sobre desempenho com o particionamento de tabela
É importante executar as operações MERGE e SPLIT RANGE para evitar qualquer movimentação de dados já que ela 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.
Vamos primeiro explicar visualmente o significado das 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ção1, que está vazia (depois de alternar a partição), portanto,MERGE RANGEnão incorrerá em qualquer movimentação de dados. - Caso
RANGE RIGHT: O limite inferior de partição pertence à partição2, que não está vazia, porque a partição1foi esvaziada ao sair. Nesse caso,MERGE RANGEincorre em movimentação de dados (os dados da partição2são movidos para a partição1). Para evitar isso,RANGE RIGHTno cenário de janela deslizante deve ter a partição1, que sempre está vazia. Isso significa que, se você usarRANGE RIGHT, deve criar e manter uma partição extra em comparação com o casoRANGE LEFT.
Conclusão: usar RANGE LEFT na partição deslizante é mais fácil para o gerenciamento de partição e evita 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 marcação de tempo 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 a 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 um efeito significativo.
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 impacto 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 nisso 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 no seguinte snippet de código (examine esse código cuidadosamente e ajuste-o antes de aplicá-lo ao seu ambiente):
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 usp_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;
/*Generate 3 statements that run inside a transaction:
(1) SET SYSTEM_VERSIONING = OFF,
(2) DELETE FROM history_table,
(3) SET SYSTEM_VERSIONING = ON
On SQL Server 2016, it is critical that (1) and (2) run in separate EXEC statements, or SQL Server generates the following error:
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>'.
*/
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 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 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 na 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. 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 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 como ON por padrão, mas os usuários podem 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 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
)
);
É 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 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;
Como o Banco de Dados SQL 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é 10K) 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 garantido firmemente. 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.
Para obter mais informações, consulte Gerenciar dados históricos em Tabelas Temporais com a política de retenção.
Conteúdo relacionado
- Tabelas temporais
- Introdução a tabelas temporais com versão do sistema
- Verificações de consistência do sistema de tabela temporal
- Particionamento com tabelas temporais
- Considerações e limitações da tabela temporal
- Segurança da tabela temporal
- Tabelas temporais com versão do sistema e tabelas com otimização de memória
- Funções e exibições de metadados de tabela temporal
Comentários
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulte https://aka.ms/ContentUserFeedback.
Enviar e exibir comentários de