Zarządzanie danymi historycznymi w tabelach czasowych przy użyciu zasad przechowywania

Dotyczy: Azure SQL Database Azure SQL Managed Instance

Tabele czasowe mogą zwiększyć rozmiar bazy danych więcej niż zwykłe tabele, zwłaszcza jeśli dane historyczne są przechowywane przez dłuższy czas. W związku z tym zasady przechowywania danych historycznych są ważnym aspektem planowania i zarządzania cyklem życia każdej tabeli czasowej. Tabele czasowe w usługach Azure SQL Database i Azure SQL Managed Instance są wyposażone w łatwy w użyciu mechanizm przechowywania, który ułatwia wykonanie tego zadania.

Przechowywanie historii czasowej można skonfigurować na poziomie poszczególnych tabel, co umożliwia użytkownikom tworzenie elastycznych zasad starzenia się. Stosowanie przechowywania czasowego jest proste: wymaga ustawienia tylko jednego parametru podczas tworzenia tabeli lub zmiany schematu.

Po zdefiniowaniu zasad przechowywania usługa Azure SQL Database i usługa Azure SQL Managed Instance regularnie sprawdzają, czy istnieją wiersze historyczne, które kwalifikują się do automatycznego czyszczenia danych. Identyfikacja pasujących wierszy i ich usuwania z tabeli historii odbywa się w sposób niewidoczny w zadaniu w tle, które jest zaplanowane i uruchamiane przez system. Warunek wieku wierszy tabeli historii jest sprawdzany na podstawie kolumny reprezentującej koniec SYSTEM_TIME okresu. Jeśli na przykład okres przechowywania jest ustawiony na sześć miesięcy, wiersze tabeli kwalifikujące się do oczyszczenia spełniają następujący warunek:

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

W poprzednim przykładzie przyjęto założenie, że kolumna ValidTo odpowiada końcu okresu SYSTEM_TIME.

Jak skonfigurować zasady przechowywania

Przed skonfigurowaniem zasad przechowywania dla tabeli czasowej sprawdź najpierw, czy tymczasowe przechowywanie historyczne jest włączone na poziomie bazy danych.

SELECT is_temporal_history_retention_enabled, name
FROM sys.databases

Flaga bazy danych is_temporal_history_retention_enabled jest domyślnie ustawiona na WŁĄCZONE, ale użytkownicy mogą zmienić ją za pomocą instrukcji ALTER DATABASE. Jest ona również automatycznie ustawiana na WARTOŚĆ OFF po operacji przywracania do punktu w czasie. Aby włączyć czyszczenie przechowywania historii czasowej dla bazy danych, wykonaj następującą instrukcję:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

Ważne

Przechowywanie tabel czasowych można skonfigurować nawet wtedy, gdy is_temporal_history_retention_enabled jest wyłączona, ale automatyczne czyszczenie przestarzałych wierszy nie jest wyzwalane w takim przypadku.

Zasady przechowywania są konfigurowane podczas tworzenia tabeli, określając wartość 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
     )
 );

Usługi Azure SQL Database i Azure SQL Managed Instance umożliwiają określenie okresu przechowywania przy użyciu różnych jednostek czasu: DNI, TYGODNI, MIESIĘCY i LAT. W przypadku pominięcia HISTORY_RETENTION_PERIOD przyjmuje się, że przyjmuje się nieskończone przechowywanie. Możesz również jawnie użyć słowa kluczowego INFINITE.

W niektórych scenariuszach możesz skonfigurować przechowywanie po utworzeniu tabeli lub zmienić wcześniej skonfigurowaną wartość. W takim przypadku użyj instrukcji ALTER TABLE:

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

Ważne

Ustawienie wartości SYSTEM_VERSIONING wyłączone nie powoduje zachowania wartości okresu przechowywania. Ustawienie SYSTEM_VERSIONING na WŁ. bez HISTORY_RETENTION_PERIOD określone jawnie powoduje nieskończony okres przechowywania.

Aby przejrzeć bieżący stan zasad przechowywania, użyj następującego zapytania, które łączy flagę włączania przechowywania czasowego na poziomie bazy danych z okresami przechowywania dla poszczególnych tabel:

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 przestarzałe wiersze są usuwane

Proces oczyszczania zależy od układu indeksu tabeli historii. Należy zauważyć, że tylko tabele historii z indeksem klastrowanym (B-tree lub columnstore) mogą mieć skonfigurowane zasady przechowywania skończonego. Zadanie w tle jest tworzone w celu wykonania przestarzałego czyszczenia danych dla wszystkich tabel czasowych z skończonym okresem przechowywania. Logika oczyszczania dla klastrowanego indeksu magazynu wierszy (B-tree) usuwa przestarzałe wiersze w mniejszych fragmentach (maksymalnie 10 000) minimalizując presję na dziennik bazy danych i podsystem we/wy. Mimo że logika oczyszczania korzysta z wymaganego indeksu drzewa B, kolejność usuwania wierszy starszych niż okres przechowywania nie może być zdecydowanie gwarantowana. W związku z tym nie należy stosować żadnej zależności od kolejności oczyszczania w aplikacjach.

Zadanie oczyszczania klastrowanego magazynu kolumn usuwa jednocześnie całe grupy wierszy (zazwyczaj zawierają 1 milion wierszy), co jest bardzo wydajne, szczególnie w przypadku generowania danych historycznych w wysokim tempie.

Doskonała kompresja danych i wydajne czyszczenie przechowywania sprawia, że indeks klastrowanego magazynu kolumn jest doskonałym wyborem w scenariuszach, w których obciążenie szybko generuje dużą ilość danych historycznych. Ten wzorzec jest typowy dla intensywnych obciążeń przetwarzania transakcyjnego, które używają tabel czasowych do śledzenia zmian i inspekcji, analizy trendów lub pozyskiwania danych IoT.

Zagadnienia dotyczące indeksu

Zadanie oczyszczania tabel z indeksem klastrowanym magazynu wierszy wymaga, aby indeks rozpoczynał się od kolumny odpowiadającej końcu okresu SYSTEM_TIME. Jeśli taki indeks nie istnieje, nie można skonfigurować skończonego okresu przechowywania:

Msg 13765, poziom 16, stan 1

Ustawienie okresu przechowywania skończonego nie powiodło się w tabeli czasowej systemu "temporalstagetestdb.dbo.WebsiteUserInfo", ponieważ tabela historii "temporalstagetestdb.dbo.WebsiteUserInfoHistory" nie zawiera wymaganego indeksu klastrowanego. Rozważ utworzenie klastrowanego magazynu kolumn lub indeksu drzewa B, zaczynając od kolumny zgodnej z końcem okresu SYSTEM_TIME w tabeli historii.

Należy zauważyć, że domyślna tabela historii utworzona przez usługi Azure SQL Database i Azure SQL Managed Instance ma już indeks klastrowany, który jest zgodny z zasadami przechowywania. Jeśli spróbujesz usunąć ten indeks w tabeli z skończonym okresem przechowywania, operacja zakończy się niepowodzeniem z powodu następującego błędu:

Msg 13766, poziom 16, stan 1

Nie można usunąć indeksu klastrowanego "WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory", ponieważ jest on używany do automatycznego czyszczenia przestarzałych danych. Rozważ ustawienie HISTORY_RETENTION_PERIOD nieskończoność w odpowiedniej tabeli czasowej w wersji systemowej, jeśli chcesz usunąć ten indeks.

Czyszczenie w klastrowanym indeksie magazynu kolumn działa optymalnie, jeśli wiersze historyczne są wstawiane w kolejności rosnącej (uporządkowanej według końca kolumny okresu), co zawsze ma zastosowanie, gdy tabela historii jest wypełniana wyłącznie przez mechanizm SYSTEM_VERSIONIOING. Jeśli wiersze w tabeli historii nie są uporządkowane według końca kolumny okresu (co może mieć zastosowanie w przypadku migrowania istniejących danych historycznych), należy ponownie utworzyć indeks klastrowanego magazynu kolumn na podstawie indeksu magazynu wierszy drzewa B, który jest prawidłowo uporządkowany, aby uzyskać optymalną wydajność.

Unikaj ponownego kompilowania klastrowanego indeksu magazynu kolumn w tabeli historii ze skończonym okresem przechowywania, ponieważ może zmienić kolejność w grupach wierszy naturalnie narzuconych przez operację przechowywania wersji systemu. Jeśli musisz ponownie skompilować indeks klastrowanego magazynu kolumn w tabeli historii, wykonaj to przez ponowne utworzenie go na podstawie zgodnego indeksu drzewa B, zachowując kolejność w grupach wierszy niezbędnych do zwykłego czyszczenia danych. To samo podejście należy podjąć, jeśli utworzysz tabelę czasową z istniejącą tabelą historii zawierającą indeks kolumn klastrowanych bez gwarantowanej kolejności danych:

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

Po skonfigurowaniu okresu przechowywania skończonego dla tabeli historii z klastrowanym indeksem magazynu kolumn nie można utworzyć dodatkowych indeksów nieklarowanych drzewa B w tej tabeli:

CREATE NONCLUSTERED INDEX IX_WebHistNCI ON WebsiteUserInfoHistory ([UserName])

Próba wykonania powyższej instrukcji kończy się niepowodzeniem z powodu następującego błędu:

Msg 13772, poziom 16, stan 1

Nie można utworzyć indeksu nieklastrowanego w tabeli historii czasowej "WebsiteUserInfoHistory", ponieważ ma zdefiniowany ograniczony okres przechowywania i zdefiniowany indeks klastrowanego magazynu kolumn.

Wykonywanie zapytań dotyczących tabel przy użyciu zasad przechowywania

Wszystkie zapytania w tabeli czasowej automatycznie odfiltrowują wiersze historyczne pasujące do zasad przechowywania skończonego, aby uniknąć nieprzewidywalnych i niespójnych wyników, ponieważ przestarzałe wiersze mogą zostać usunięte przez zadanie oczyszczania w dowolnym momencie i w dowolnej kolejności.

Na poniższej ilustracji przedstawiono plan zapytania dla prostego zapytania:

SELECT * FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

Plan zapytania zawiera dodatkowy filtr zastosowany do kolumny końca okresu (ValidTo) w operatorze skanowania indeksu klastrowanego w tabeli historii (wyróżnione). W tym przykładzie przyjęto założenie, że w tabeli WebsiteUserInfo ustawiono okres przechowywania miesiąc.

Jeśli jednak bezpośrednio wykonasz zapytanie w tabeli historii, możesz zobaczyć wiersze starsze niż określony okres przechowywania, ale bez żadnej gwarancji dla powtarzalnych wyników zapytania. Na poniższej ilustracji przedstawiono plan wykonywania zapytania dla zapytania w tabeli historii bez zastosowanych dodatkowych filtrów:

Nie należy polegać na logice biznesowej w przypadku odczytywania tabeli historii poza okresem przechowywania, ponieważ mogą wystąpić niespójne lub nieoczekiwane wyniki. Zalecamy używanie zapytań czasowych z klauzulą FOR SYSTEM_TIME do analizowania danych w tabelach czasowych.

Zagadnienia dotyczące przywracania do punktu w czasie

Podczas tworzenia nowej bazy danych przez przywrócenie istniejącej bazy danych do określonego punktu w czasie jego przechowywanie czasowe jest wyłączone na poziomie bazy danych. (flaga is_temporal_history_retention_enabled ustawiona na WYŁ.). Ta funkcja umożliwia sprawdzenie wszystkich wierszy historycznych po przywróceniu bez obaw, że przestarzałe wiersze zostaną usunięte przed rozpoczęciem wykonywania zapytań. Można go użyć do inspekcji danych historycznych poza skonfigurowanym okresem przechowywania.

Załóżmy, że tabela czasowa ma określony okres przechowywania miesiąc. Jeśli baza danych została utworzona w warstwie usługi Premium, możesz utworzyć kopię bazy danych ze stanem bazy danych do 35 dni wstecz w przeszłości. Dzięki niemu można analizować historyczne wiersze, które mają maksymalnie 65 dni, wysyłając zapytanie bezpośrednio do tabeli historii.

Jeśli chcesz aktywować oczyszczanie przechowywania czasowego, uruchom następującą instrukcję Języka Transact-SQL po przywróceniu do punktu w czasie:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

Następne kroki

Aby dowiedzieć się, jak używać tabel czasowych w aplikacjach, zapoznaj się z tematem Wprowadzenie do tabel czasowych.

Aby uzyskać szczegółowe informacje o tabelach czasowych, zapoznaj się z tematem Tabele czasowe.