Sdílet prostřednictvím

Správa uchovávání historických dat v dočasných tabulkách s systémovou verzí

  • Článek

platí pro: SQL Server 2016 (13.x) a novější verze Azure SQL DatabaseAzure SQL Managed InstanceSQL Database v Microsoft Fabric

V dočasných tabulkách se systémovou verzí může tabulka historie zvýšit velikost databáze více než běžné tabulky, zejména za následujících podmínek:

  • Uchováváte historická data po dlouhou dobu
  • Máte vzor pro aktualizaci nebo odstranění náročných úprav dat.

Velká a neustále se rozrůstající tabulka historie se může stát problémem jak kvůli nákladům na úložiště, tak kvůli zátěži na výkon při časových dotazech. Vývoj zásad uchovávání dat pro správu dat v tabulce historie je důležitým aspektem plánování a správy životního cyklu každé dočasné tabulky.

Správa uchovávání dat pro tabulku historie

Správa uchovávání dat dočasných tabulek začíná určením požadované doby uchovávání pro každou dočasnou tabulku. Vaše zásady uchovávání informací by ve většině případů měly být součástí obchodní logiky aplikace pomocí dočasných tabulek. Například aplikace ve scénářích auditování dat a časové cesty mají pevné požadavky týkající se toho, jak dlouho musí být historická data k dispozici pro online dotazování.

Jakmile určíte dobu uchovávání dat, měli byste vytvořit plán pro správu historických dat. Rozhodněte se, jak a kam ukládáte historická data a jak odstranit historická data, která jsou starší než vaše požadavky na uchovávání informací. K dispozici jsou následující přístupy ke správě historických dat v tabulce dočasné historie:

U každého z těchto přístupů je logika migrace nebo čištění dat historie založená na sloupci, který odpovídá konci období v aktuální tabulce. Hodnota konce období pro každý řádek určuje okamžik, kdy se verze řádku změní na uzavřená, tj. když se zobrazí v tabulce historie. Podmínka ValidTo < DATEADD (DAYS, -30, SYSUTCDATETIME ()) například určuje, že historická data starší než jeden měsíc musí být odebrána nebo přesunuta z tabulky historie.

Příklady v tomto článku používají ukázky vytvořené v článku Vytvoření systemově verzované temporální tabulky.

Použijte přístup k rozdělení tabulek

dělené tabulky a indexy můžou vytvářet rozsáhlé tabulky lépe spravovatelné a škálovatelné. Pomocí dělení tabulek můžete implementovat vlastní čištění dat nebo offline archivaci na základě časové podmínky. Dělení tabulek také poskytuje výhody výkonu při dotazování dočasných tabulek v podmnožině historie dat pomocí odstranění oddílu.

Pomocí dělení tabulek můžete implementovat posuvné okno pro přesunutí nejstarší části historických dat z tabulky historie a zachování velikosti zachované části z hlediska věku. Posuvné okno udržuje data v tabulce historie rovna požadované době uchovávání. Přepínání dat z tabulky historie je podporováno, když je SYSTEM_VERSIONINGON, což znamená, že můžete vyčistit část dat historie bez zavedení údržbového okna nebo blokování vašich běžných pracovních zátěží.

Poznámka

Aby bylo možné provádět přepínání oddílů, musí být clusterovaný index v tabulce historie zarovnaný se schématem dělení (musí obsahovat ValidTo). Výchozí tabulka historie vytvořená systémem obsahuje clusterovaný index, který obsahuje sloupce ValidTo a ValidFrom, což je optimální pro dělení, vkládání nových dat historie a typické dočasné dotazování. Další informace naleznete v tématu časové tabulky.

Posuvné okno má dvě sady úloh, které potřebujete provést:

  • Úloha konfigurace rozdělení disku
  • Opakované úlohy údržby oddílů

Pro ilustraci předpokládejme, že chcete uchovávat historická data po dobu šesti měsíců a že chcete uchovávat každý měsíc dat v samostatném oddílu. Předpokládejme také, že jste v září 2023 aktivovali správu verzí systému.

Úloha konfigurace dělení vytvoří počáteční konfiguraci dělení pro tabulku historie. V tomto příkladu vytvoříte stejné číselné oddíly jako velikost posuvného okna v měsících a navíc předem připravený prázdný oddíl navíc (vysvětleno dále v tomto článku). Tato konfigurace zajišťuje, že systém dokáže správně ukládat nová data při prvním spuštění opakujícího se úkolu údržby oddílů a zaručuje, že nikdy nedojde k rozdělení oddílů obsahujících data, čímž se zabrání nákladným přesunům dat. Tuto úlohu byste měli provést pomocí Transact-SQL pomocí ukázkového skriptu dále v tomto článku.

Následující obrázek ukazuje počáteční konfiguraci dělení, která uchovává šest měsíců dat.

diagram znázorňující počáteční konfiguraci dělení, která uchovává šest měsíců dat.

Poznámka

Informace o dopadu na výkon při používání RANGE LEFT a RANGE RIGHT při konfiguraci dělení na oddíly najdete v tématu Aspekty výkonu při dělení tabulek dále v tomto článku.

První a poslední oddíly jsou otevřené na dolních a horních hranicích, aby se zajistilo, že každý nový řádek má cílový oddíl bez ohledu na hodnotu ve sloupci dělení. Jak čas plyne, nové řádky v tabulce historie se přesunou do vyšších oddílů. Když se naplní šestý oddíl, je dosaženo cílové doby uchovávání dat. Toto je okamžik, kdy poprvé spustíte úlohu opakované údržby oddílů. V tomto příkladu je potřeba naplánovat pravidelné spouštění jednou za měsíc.

Následující obrázek znázorňuje úlohy údržby oddílů, které se opakují (viz podrobné kroky dále v této části).

diagram znázorňující opakující se úlohy údržby oddílů

Podrobné kroky pro opakující se úlohy údržby oddílů:

  1. SWITCH OUT: Vytvořte přípravnou tabulku a poté pomocí příkazu ALTER TABLE s argumentem SWITCH PARTITION přepněte oddíl mezi přípravnou tabulkou a tabulkou historie (viz příklad C. Přepínání oddílů mezi tabulkami).

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

    Po přepnutí oddílu můžete volitelně archivovat data z přechodové tabulky, a poté buď odstranit nebo zkrátit přechodovou tabulku, abyste byli připraveni na příští použití této opakující se úlohy údržby oddílů.

  2. MERGE RANGE: Sloučení prázdného oddílu 1 s oddílem 2 pomocí ALTER PARTITION FUNCTION s MERGE RANGE (viz příklad B). Odebráním nejnižší hranice pomocí této funkce efektivně sloučíte prázdný oddíl 1 s bývalým oddílem 2 vytvořit nový oddíl 1. Ostatní oddíly také efektivně mění svá pořadová čísla.

  3. SPLIT RANGE: Vytvořte nový prázdný oddíl 7 pomocí ALTER PARTITION FUNCTION s SPLIT RANGE (viz příklad A). Přidáním nové horní hranice pomocí této funkce efektivně vytvoříte samostatnou část pro nadcházející měsíc.

Použijte Transact-SQL pro vytvoření oddílů v historické tabulce

Pomocí následujícího skriptu Transact-SQL vytvořte funkci rozdělení, schéma rozdělení a znovu vytvořte clusterovaný index, který bude sladěn se schématem rozdělení a oddíly. V tomto příkladu vytvoříte šestiměsíční posuvné okno s měsíčními oddíly od září 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;

Použití Transact-SQL pro údržbu oddílů ve scénáři posuvného okna

K údržbě oddílů ve scénáři posuvného okna použijte následující skript Transact-SQL. V tomto příkladu přepnete oddíl pro září 2023 pomocí MERGE RANGEa pak přidáte nový oddíl pro březen 2024 pomocí 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

Předchozí skript můžete mírně upravit a použít ho v pravidelném měsíčním procesu údržby:

  1. V kroku (1) vytvořte novou pracovní tabulku pro měsíc, který chcete odebrat (v tomto příkladu bude říjen další).
  2. V kroku (3) vytvořte a zkontrolujte omezení, které odpovídá měsíci dat, která chcete odebrat: ValidTo <= N'2023-10-31T23:59:59.999' pro říjnový oddíl.
  3. V kroku (4) rozdělte SWITCH na 1 v nově vytvořené pracovní tabulce.
  4. V kroku (6) upravte funkci oddílu tím, že sloučíte dolní hranici: MERGE RANGE(N'2023-10-31T23:59:59.999', poté co přesunete data z října.
  5. V kroku (7) rozdělte funkci oddílu a vytvořte novou horní hranici: SPLIT RANGE (N'2024-04-30T23:59:59.999' poté, co přesunete data za říjen.

Optimálním řešením by však bylo pravidelně spouštět obecný Transact-SQL skript, který spouští příslušnou akci každý měsíc beze změny. Předchozí skript můžete zobecnit tak, aby fungoval s vašimi zadanými parametry (dolní hranice, kterou je potřeba sloučit, a novou hranici vytvořenou s rozdělením oddílu). Abyste se vyhnuli vytváření pracovní tabulky každý měsíc, můžete ji vytvořit předem a znovu ji použít tak, že změníte kontrolní omezení tak, aby odpovídalo oddílu, který jste vyměnili. Pro více informací naleznete , jak lze posuvné okno plně automatizovat.

Aspekty výkonu při particování tabulek

Abyste se vyhnuli přesunu dat, musíte provádět operace MERGE a SPLIT RANGE, protože přesun dat může mít významnou režii na výkon. Další informace naleznete v tématu Úprava funkce oddílu. Uděláte to pomocí RANGE LEFT místo RANGE RIGHT, když vytváříte oddílovou funkci.

Následující diagram popisuje možnosti RANGE LEFT a RANGE RIGHT:

Diagram znázorňující možnosti OBLAST VLEVO a OBLAST VPRAVO

Když definujete funkci oddílu jako RANGE LEFT, zadané hodnoty jsou horní hranice oddílů. Při použití RANGE RIGHTpřiřazené hodnoty představují dolní hranice rozdělení. Pokud použijete operaci MERGE RANGE k odebrání hranice z definice funkce oddílu, základní implementace také odebere oddíl, který obsahuje hranici. Pokud tento oddíl není prázdný, data se přesunou do oddílu, který je výsledkem operace MERGE RANGE.

Ve scénáři posuvného okna vždy odeberete nejnižší hranici oddílu.

  • RANGE LEFT případ: Nejnižší hranice oddílu patří do oddílu 1, který je prázdný (po přepnutí oddílu ven), takže MERGE RANGE nezpůsobí žádný pohyb dat.

  • RANGE RIGHT případu: Nejnižší hranice oddílu patří oddílu 2, který není prázdný, protože oddíl 1 byl vyprázdněn přepnutím. V tomto případě dojde k přesunu dat MERGE RANGE (data z oddílu 2 se přesunou do oddílu 1). Aby se tomu zabránilo, RANGE RIGHT ve scénáři posuvného okna musí mít oddíl 1, který je vždy prázdný. To znamená, že pokud používáte RANGE RIGHT, měli byste vytvořit a udržovat jeden další oddíl ve srovnání s RANGE LEFT.

Závěr: Správa oddílů je jednodušší, když používáte RANGE LEFT v posuvném oddílu a vyhnete se přesunu dat. Definování hranic oddílů pomocí RANGE RIGHT je ale o něco jednodušší, protože nemusíte řešit problémy s kontrolou data a času.

Použití vlastního skriptu pro vyčištění

V případech, kdy dělení tabulek není možné, je dalším přístupem odstranit data z tabulky historie pomocí vlastního skriptu vyčištění. Odstranění dat z tabulky historie je možné pouze v SYSTEM_VERSIONING = OFF. Aby se zabránilo nekonzistence dat, proveďte čištění buď během časového období údržby (pokud úlohy, které upravují data nejsou aktivní), nebo v rámci transakce (efektivně blokuje jiné úlohy). Tato operace vyžaduje oprávnění CONTROL pro aktuální tabulky a tabulky historie.

Aby se co nejméně blokovaly běžné aplikace a dotazy uživatelů, odstraňujte data v menších částech se zpožděním při spuštění skriptu údržby uvnitř transakce. I když neexistuje žádná optimální velikost pro odstranění každého datového bloku pro všechny scénáře, odstranění více než 10 000 řádků v jedné transakci může představovat významnou pokutu.

Logika čištění je stejná pro každou dočasnou tabulku, takže ji můžete automatizovat prostřednictvím obecné uložené procedury, kterou plánujete pravidelně spouštět, pro každou dočasnou tabulku, pro kterou chcete omezit historii dat.

Následující diagram znázorňuje, jak by měla být logika čištění uspořádaná pro jednu tabulku, aby se snížil vliv na spuštěné úlohy.

Diagram znázorňující, jak by měla být uspořádána logika čištění pro jednu tabulku, a tím snížit dopad na probíhající úlohy.

Tady jsou některé obecné pokyny pro implementaci tohoto procesu. Naplánujte logiku čištění tak, aby se spouštěla každý den a procházela všechny časové tabulky, které potřebují vyčištění dat. K naplánování tohoto procesu použijte agenta SQL Serveru nebo jiný nástroj:

  • Odstraňte historická data v každé dočasné tabulce, od nejstarších po nejnovější řádky v několika iteracích v malých blocích, a vyhněte se odstranění všech řádků v jedné transakci, jak je znázorněno v předchozím diagramu.

  • Implementujte každou iteraci jako vyvolání obecné uložené procedury, která odebere část dat z tabulky historie (viz následující příklad kódu pro tento postup).

  • Spočítejte, kolik řádků potřebujete odstranit pro jednotlivé dočasné tabulky při každém vyvolání procesu. Na základě výsledku a počtu iterací, které chcete mít, určete dynamické rozdělené body pro každé vyvolání procedury.

  • Naplánujte dobu zpoždění mezi iteracemi jedné tabulky, abyste snížili vliv na aplikace, které přistupují k dočasné tabulce.

Uložená procedura, která odstraní data pro jednu dočasnou tabulku, může vypadat jako následující fragment kódu. Pečlivě si tento kód prohlédněte a upravte ho před použitím ve vašem prostředí.

Tento skript vygeneruje tři příkazy, které se spouští uvnitř transakce:

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

V SQL Serveru 2016 (13.x) musí první dva kroky běžet v samostatných příkazech EXEC nebo SQL Server vygeneruje chybu podobnou následujícímu příkladu:

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;

Použijte přístup k politikám uchovávání dočasné historie

platí pro: SQL Server 2017 (14.x) a novější verze a Azure SQL Database.

Uchovávání dočasné historie je možné nakonfigurovat na úrovni jednotlivých tabulek, což uživatelům umožňuje vytvářet flexibilní zásady stárnutí. Dočasné uchovávání vyžaduje, abyste během vytváření tabulky nebo změny schématu nastavili pouze jeden parametr.

Po definování zásad uchovávání informací databázový stroj začne pravidelně kontrolovat, jestli jsou historické řádky, které mají nárok na automatické vyčištění dat. Identifikace odpovídajících řádků a jejich odebrání z tabulky historie probíhá transparentně v úloze na pozadí, která je naplánována a spuštěna systémem. Věková podmínka pro řádky tabulky historie se kontroluje na základě sloupce představujícího konec období SYSTEM_TIME (v těchto příkladech je to sloupec ValidTo). Pokud je doba uchovávání nastavená na šest měsíců, například řádky tabulky, které mají nárok na vyčištění, splňují následující podmínku:

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

V předchozím příkladu sloupec ValidTo odpovídá konci období SYSTEM_TIME.

Konfigurace zásad uchovávání informací

Před konfigurací zásad uchovávání informací pro dočasnou tabulku zkontrolujte, jestli je povolené dočasné historické uchovávání na úrovni databáze:

SELECT is_temporal_history_retention_enabled, name
FROM sys.databases;

Příznak databáze is_temporal_history_retention_enabled je ve výchozím nastavení nastavený na ON, ale můžete ho změnit pomocí příkazu ALTER DATABASE. Tato hodnota se automaticky nastaví na OFF po operaci obnovení k určitému bodu v čase (PITR). Pokud chcete pro vaši databázi povolit vyčištění uchovávání dočasné historie, spusťte následující příkaz. Je nutné nahradit <myDB> databází, kterou chcete změnit:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION ON;

Zásady uchovávání informací se konfigurují během vytváření tabulky zadáním hodnoty parametru 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
    )
);

Dobu uchovávání můžete zadat pomocí různých časových jednotek: DAYS, WEEKS, MONTHSa YEARS. Pokud HISTORY_RETENTION_PERIOD vynecháte, předpokládá se uchování INFINITE. Klíčové slovo INFINITE můžete také použít explicitně.

V některých scénářích můžete chtít po vytvoření tabulky nakonfigurovat uchovávání informací nebo změnit dříve nakonfigurovanou hodnotu. V takovém případě použijte příkaz ALTER TABLE:

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

Pokud chcete zkontrolovat aktuální stav zásad uchovávání informací, použijte následující ukázku. Tento dotaz spojí příznak dočasného uchovávání informací na úrovni databáze s obdobími uchovávání pro jednotlivé tabulky:

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;

Odstranění starých řádků databázovým strojem

Proces čištění závisí na rozložení indexu tabulky historie. Pouze tabulky historie s clusterovaným indexem (strom B+ nebo columnstore) mohou mít nakonfigurovanou konečnou politiku uchovávání dat. Vytvoří se úloha na pozadí pro vyčištění starých dat pro všechny dočasné tabulky s omezenou dobou uchovávání. Logika vyčištění clusterovaného indexu rowstore (B+ tree) odstraní staré řádky v menších blocích (až 10 000), což minimalizuje tlak na protokol databáze a subsystém vstupně-výstupní operace. I když logika čištění používá požadovaný index stromu B+, pořadí odstranění řádků starších než doba uchovávání není možné zaručit. V aplikacích nespoléhejte na pořadí úklidu.

Úloha vyčištění clusterovaného columnstore odstraní celé skupiny řádků najednou (které obvykle obsahují 1 milion řádků), což je efektivnější, zejména pokud se historická data generují vysokým tempem.

snímek obrazovky s uchováváním clusterovaného úložiště sloupců

Komprese dat a vyčištění uchovávání dat činí clusterovaný index columnstore ideální volbou pro scénáře, kdy vaše úloha rychle generuje velké množství historických dat. Tento model je typický pro úlohy náročného transakčního zpracování, které používají dočasné tabulky pro sledování změn a auditování, analýzu trendu nebo příjem dat IoT.

Další informace naleznete v tématu Správa historických dat v dočasných tabulkách pomocí zásad uchovávání informací.

Související obsah