Správa historických dat v dočasných tabulkách pomocí zásad uchovávání informací

Platí pro:Azure SQL DatabaseAzure SQL Managed Instance

Dočasné tabulky mohou zvýšit velikost databáze více než běžné tabulky, zejména pokud uchováváte historická data po delší dobu. Zásady uchovávání historických dat jsou proto důležitým aspektem plánování a správy životního cyklu každé dočasné tabulky. Dočasné tabulky ve službě Azure SQL Database a Azure SQL Managed Instance jsou vybaveny snadno použitelným mechanismem uchovávání informací, který vám pomůže tuto úlohu dosáhnout.

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í. Použití dočasného uchovávání je jednoduché: vyžaduje nastavení pouze jednoho parametru během vytváření tabulky nebo změny schématu.

Jakmile definujete zásady uchovávání informací, Azure SQL Database a Azure SQL Managed Instance začnou pravidelně kontrolovat, jestli existují 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 SYSTEM_TIME období. Pokud je například doba uchovávání nastavená na šest měsíců, řá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 jsme předpokládali, že sloupec ValidTo odpovídá konci SYSTEM_TIME období.

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

Před konfigurací zásad uchovávání informací pro dočasnou tabulku nejprve 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 ZAPNUTO, ale uživatelé ho můžou změnit pomocí příkazu ALTER DATABASE. Je také automaticky nastaven na VYPNUTO po operaci obnovení k určitému bodu v čase. Pokud chcete pro vaši databázi povolit vyčištění dočasné historie, spusťte následující příkaz:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

Důležité

Uchovávání dočasných tabulek můžete nakonfigurovat i v případě, že je is_temporal_history_retention_enabled vypnuté, ale automatické vyčištění starých řádků se v takovém případě neaktivuje.

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

Azure SQL Database a Azure SQL Managed Instance umožňují zadat dobu uchovávání pomocí různých časových jednotek: DNY, TÝDNY, MĚSÍCE a ROKY. Pokud HISTORY_RETENTION_PERIOD vynecháte, předpokládá se uchovávání NEKONEČNO. Klíčové slovo INFINITE můžete použít také 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));

Důležité

Nastavení SYSTEM_VERSIONING na HODNOTU OFF nezachová hodnotu doby uchovávání. Nastavení SYSTEM_VERSIONING na ZAPNUTO bez HISTORY_RETENTION_PERIOD explicitně vede k tomu, že doba uchovávání NEKONEČNA.

Pokud chcete zkontrolovat aktuální stav zásad uchovávání informací, použijte následující dotaz, který spojí příznak povolení dočasného uchovávání 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

Jak staré řádky se odstraní

Proces čištění závisí na rozložení indexu tabulky historie. Je důležité si všimnout, že je možné nakonfigurovat pouze tabulky historie s clusterovaným indexem (B-tree nebo columnstore). 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ý řádek v menších blocích (až 10 tisíc), což minimalizuje tlak na protokol databáze a subsystém vstupně-výstupní operace. I když logika čištění využívá požadovaný index B-tree, pořadí odstranění řádků starších než doba uchovávání nelze pevně zaručit. Proto v aplikacích nepřebídejte žádnou závislost na pořadí čištění.

Úloha vyčištění clusterovaného columnstore odebere celé skupiny řádků najednou (obvykle každý z nich obsahuje 1 milion řádků), což je velmi efektivní, zejména pokud se historická data generují vysokým tempem.

Díky vynikající kompresi dat a efektivnímu čištění uchovávání dat je 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.

Aspekty indexu

Úloha vyčištění pro tabulky s clusterovaným indexem rowstore vyžaduje, aby index začínal sloupcem odpovídajícím konci SYSTEM_TIME období. Pokud takový index neexistuje, nemůžete nakonfigurovat konečné období uchovávání:

Msg 13765, level 16, State 1

Nastavení konečné doby uchovávání selhalo v dočasné tabulce s časovou verzí systému "temporalstagetestdb.dbo.WebsiteUserInfo", protože tabulka historie "temporalstagetestdb.dbo.WebsiteUserInfoHistory" neobsahuje požadovaný clusterovaný index. Zvažte vytvoření clusterovaného columnstore nebo indexu B-tree počínaje sloupcem, který odpovídá konci SYSTEM_TIME období v tabulce historie.

Je důležité si všimnout, že výchozí tabulka historie vytvořená službou Azure SQL Database a azure SQL Managed Instance už obsahuje clusterovaný index, který vyhovuje zásadám uchovávání informací. Pokud se pokusíte odebrat tento index v tabulce s konečným obdobím uchovávání, operace selže s následující chybou:

Msg 13766, level 16, State 1

Clusterovaný index "WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory" nelze odstranit, protože se používá k automatickému vyčištění starých dat. Pokud potřebujete tento index vypustit, zvažte nastavení HISTORY_RETENTION_PERIOD na NEKONEČNO na odpovídající dočasnou tabulku verze systému.

Vyčištění clusterovaného indexu columnstore funguje optimálně, pokud jsou historické řádky vloženy ve vzestupném pořadí (seřazené po konci sloupce období), což je vždy případ, kdy je tabulka historie naplněna výhradně mechanismem SYSTEM_VERSIONIOING. Pokud řádky v tabulce historie nejsou seřazené podle sloupce konce období (což může být případ, kdy jste migrovali existující historická data), měli byste znovu vytvořit clusterovaný index columnstore nad indexem řádků stromu B, který je správně seřazený, abyste dosáhli optimálního výkonu.

Vyhněte se opětovnému sestavení clusterovaného indexu columnstore v tabulce historie s konečným obdobím uchovávání, protože může změnit pořadí ve skupinách řádků přirozeně vynucené operací správy verzí systému. Pokud potřebujete znovu sestavit clusterovaný index columnstore v tabulce historie, proveďte to tak, že ho znovu vytvoříte na základě kompatibilního indexu B-tree a zachováte pořadí v řádcích nezbytných pro běžné vyčištění dat. Stejný přístup byste měli vzít v případě, že vytvoříte dočasnou tabulku s existující tabulkou historie, která obsahuje clusterovaný index sloupců bez zaručeného pořadí dat:

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

Pokud je pro tabulku historie s clusterovaným indexem columnstore nakonfigurovaná konečná doba uchovávání, nemůžete v této tabulce vytvořit další neskupené indexy B-tree:

CREATE NONCLUSTERED INDEX IX_WebHistNCI ON WebsiteUserInfoHistory ([UserName])

Pokus o provedení výše uvedeného příkazu selže s následující chybou:

Msg 13772, level 16, State 1

Nelze vytvořit neskupený index v tabulce dočasné historie WebsiteUserInfoHistory, protože má definovanou omezenou dobu uchovávání a index clusterovaného columnstore.

Dotazování tabulek pomocí zásad uchovávání informací

Všechny dotazy v dočasné tabulce automaticky vyfiltrují historické řádky odpovídající zásadám konečného uchovávání informací, aby nedocházelo k nepředvídatelným a nekonzistentním výsledkům, protože zastaralá řádky může úloha vyčištění kdykoli a v libovolném pořadí odstranit.

Následující obrázek ukazuje plán dotazu pro jednoduchý dotaz:

SELECT * FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

Plán dotazu obsahuje další filtr použitý na konec sloupce období (ValidTo) v operátoru prohledávání clusterovaného indexu v tabulce historie (zvýrazněné). V tomto příkladu se předpokládá, že pro tabulku WebsiteUserInfo byla nastavena doba uchovávání informací o měsíci.

Pokud ale tabulku historie dotazů používáte přímo, může se zobrazit řádky, které jsou starší než zadaná doba uchovávání, ale bez záruky opakovatelných výsledků dotazu. Následující obrázek ukazuje plán provádění dotazů pro dotaz v tabulce historie bez použití dalších filtrů:

Nespoléhejte na svoji obchodní logiku na tabulku historie čtení nad rámec doby uchovávání, protože může dojít k nekonzistentním nebo neočekávaným výsledkům. Pro analýzu dat v dočasných tabulkách doporučujeme používat dočasné dotazy s klauzulí FOR SYSTEM_TIME.

Aspekty obnovení k určitému bodu v čase

Když vytváříte novou databázi obnovením existující databáze k určitému bodu v čase, je na úrovni databáze zakázané dočasné uchovávání. (is_temporal_history_retention_enabled příznak nastavený na VYPNUTO). Tato funkce umožňuje při obnovení zkoumat všechny historické řádky, aniž byste se museli starat o odebrání starých řádků, než se na ně dostanete. Můžete ho použít ke kontrole historických dat nad rámec nakonfigurovaného období uchovávání.

Řekněme, že v dočasné tabulce je zadaná doba uchovávání jednoho měsíce. Pokud byla vaše databáze vytvořena na úrovni služby Premium, budete moct vytvořit kopii databáze se stavem databáze až 35 dnů zpět v minulosti. Díky tomu můžete analyzovat historické řádky, které jsou starší než 65 dnů, dotazováním tabulky historie přímo.

Pokud chcete aktivovat dočasné čištění uchovávání informací, spusťte následující příkaz jazyka Transact-SQL po obnovení k určitému bodu v čase:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

Další kroky

Informace o používání dočasných tabulek v aplikacích najdete v tématu Začínáme s dočasnými tabulkami.

Podrobné informace o dočasných tabulkách najdete v tématu Dočasné tabulky.