Udostępnij za pośrednictwem

Zarządzanie retencją danych historycznych w systemowo wersjonowanych tabelach czasowych

  • Artykuł

Dotyczy: SQL Server 2016 (13.x) i nowsze wersje Azure SQL DatabaseAzure SQL Managed InstanceSQL Database w usłudze Microsoft Fabric

W przypadku tabel czasowych z wersją systemową tabela historii może zwiększyć rozmiar bazy danych więcej niż zwykłe tabele, szczególnie w następujących warunkach:

  • Dane historyczne są przechowywane przez długi czas
  • Masz wzorzec aktualizacji lub usuwania dużych modyfikacji danych

Duża i stale rosnąca tabela historii może stać się problemem zarówno z powodu kosztów przechowywania, jak i obniżenia wydajności przy wykonywaniu zapytań czasowych. Opracowywanie zasad przechowywania danych na potrzeby zarządzania danymi w tabeli historii jest ważnym aspektem planowania i zarządzania cyklem życia każdej tabeli czasowej.

Zarządzanie przechowywaniem danych dla tabeli historii

Zarządzanie przechowywaniem danych tabeli czasowej rozpoczyna się od określenia wymaganego okresu przechowywania dla każdej tabeli czasowej. Zasady przechowywania, w większości przypadków, powinny być częścią logiki biznesowej aplikacji przy użyciu tabel czasowych. Na przykład aplikacje w scenariuszach inspekcji danych i podróży w czasie mają zdecydowane wymagania dotyczące tego, jak długo dane historyczne muszą być dostępne do wykonywania zapytań online.

Po określeniu okresu przechowywania danych należy opracować plan zarządzania danymi historycznymi. Zdecyduj, jak i gdzie są przechowywane dane historyczne oraz jak usunąć dane historyczne starsze niż wymagania dotyczące przechowywania. Dostępne są następujące podejścia do zarządzania danymi historycznymi w tabeli historii czasowej:

W przypadku każdego z tych podejść logika migracji lub czyszczenia danych historii jest oparta na kolumnie odpowiadającej zakończeniu okresu w bieżącej tabeli. Wartość końcowa okresu dla każdego wiersza określa moment, w którym wersja wiersza staje się zamknięta, czyli trafia do tabeli historii. Na przykład warunek ValidTo < DATEADD (DAYS, -30, SYSUTCDATETIME ()) określa, że dane historyczne starsze niż jeden miesiąc muszą zostać usunięte lub przeniesione z tabeli historii.

W tym artykule użyto przykładów stworzonych w artykule Tworzenie tabeli czasowej z wersjonowaniem systemowym.

Korzystanie z metody partycjonowania tabel

partycjonowane tabele i indeksy mogą zwiększyć możliwości zarządzania i skalowalności dużych tabel. Dzięki podejściu do partycjonowania tabel można zaimplementować niestandardowe czyszczenie danych lub archiwizowanie w trybie offline na podstawie warunku czasu. Partycjonowanie tabel zapewnia również korzyści wydajnościowe podczas wykonywania zapytań na tabelach czasowych w podzbiorze historii danych, poprzez użycie eliminacji partycji.

Dzięki partycjonowaniu tabel można zaimplementować przesuwane okno, aby przenieść najstarszą część danych historycznych z tabeli historycznej i zachować rozmiar zachowanej części stały pod względem wieku. Okno przesuwane przechowuje dane w tabeli historii równe wymaganemu okresowi przechowywania. Operacja usuwania danych z tabeli historii jest możliwa, gdy SYSTEM_VERSIONING jest ON, co oznacza, że można wyczyścić część danych historycznych bez konieczności wprowadzania okna konserwacyjnego lub blokowania standardowych obciążeń.

Notatka

Aby można było przełączać partycje, indeks klastrowany w tabeli historii musi być zgodny ze schematem partycjonowania (musi zawierać ValidTo). Domyślna tabela historii utworzona przez system zawiera indeks klastrowany zawierający kolumny ValidTo i ValidFrom, które są optymalne dla partycjonowania, wstawiania nowych danych historii i typowych zapytań czasowych. Aby uzyskać więcej informacji, zobacz Tabele czasowe.

Okno przesuwne ma dwa zestawy zadań, które należy wykonać:

  • Zadanie konfiguracji partycjonowania
  • Cykliczne zadania konserwacji partycji

Na ilustracji załóżmy, że chcesz przechowywać dane historyczne przez sześć miesięcy i przechowywać dane co miesiąc w oddzielnej partycji. Załóżmy również, że aktywowano przechowywanie wersji systemu we wrześniu 2023 r.

Zadanie konfiguracji partycjonowania tworzy początkową konfigurację partycjonowania dla tabeli historii. W tym przykładzie utworzysz te same partycje liczbowe co rozmiar okna przesuwanego w miesiącach oraz dodatkową pustą partycję wstępnie przygotowaną (wyjaśniono w dalszej części tego artykułu). Ta konfiguracja gwarantuje, że system będzie mógł prawidłowo przechowywać nowe dane podczas uruchamiania zadania konserwacji partycji cyklicznej po raz pierwszy i gwarantuje, że nigdy nie dzielisz partycji z danymi, aby uniknąć kosztownych przenoszenia danych. To zadanie należy wykonać przy użyciu Transact-SQL przy użyciu przykładowego skryptu w dalszej części tego artykułu.

Na poniższej ilustracji przedstawiono początkową konfigurację partycjonowania w celu przechowywania sześciu miesięcy danych.

Diagram przedstawiający początkową konfigurację partycjonowania w celu przechowywania sześciu miesięcy danych.

Notatka

Aby dowiedzieć się o wpływie na wydajność korzystania z RANGE LEFT versus RANGE RIGHT przy konfiguracji partycjonowania, zobacz Zagadnienia dotyczące wydajności związane z partycjonowaniem tabel w dalszej części tego artykułu.

Pierwsze i ostatnie partycje są otwarte odpowiednio na dolnej i górnej granicy, aby upewnić się, że każdy nowy wiersz ma partycję docelową niezależnie od wartości w kolumnie partycjonowania. Wraz z upływem czasu nowe wiersze w tabeli historii lądują w wyższych partycjach. Gdy szósta partycja zostanie wypełniona, osiągniesz docelowy okres przechowywania. Jest to moment, aby uruchomić zadanie konserwacji partycji cyklicznej po raz pierwszy. Należy zaplanować, aby w tym przykładzie uruchamiało się to okresowo raz na miesiąc.

Na poniższej ilustracji przedstawiono cykliczne zadania konserwacji partycji (zobacz szczegółowe kroki w dalszej części tej sekcji).

Diagram przedstawiający cykliczne zadania konserwacji partycji.

Szczegółowe kroki dla cyklicznych zadań konserwacji partycji to:

  1. SWITCH OUT: Utwórz tabelę przejściową, a następnie przełącz partycję między tabelą historii a tabelą przejściową przy użyciu instrukcji ALTER TABLE z argumentem SWITCH PARTITION (zobacz przykład C. Przełączanie partycji między tabelami).

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

    Po przełączeniu partycji możesz zarchiwizować dane z tabeli przejściowej, a następnie usunąć lub obciąć tę tabelę, aby przygotować ją na kolejne wykonanie tego powtarzalnego zadania konserwacji partycji.

  2. MERGE RANGE: scal pustą partycję 1 z partycją 2 przy użyciu instrukcji ALTER PARTITION FUNCTION z MERGE RANGE (zobacz przykład B). Usuwając najniższą granicę przy użyciu tej funkcji, można skutecznie scalić pustą partycję 1 z poprzednią partycją 2, aby utworzyć nową partycję 1. Inne partycje również skutecznie zmieniają ich reguły.

  3. SPLIT RANGE: utwórz nową pustą partycję 7 przy użyciu ALTER PARTITION FUNCTION z SPLIT RANGE (zobacz przykład A). Dodając nową górną granicę przy użyciu tej funkcji, można skutecznie utworzyć oddzielną partycję dla nadchodzącego miesiąca.

Tworzenie partycji w tabeli historii przy użyciu Transact-SQL

Użyj następującego skryptu Transact-SQL, aby utworzyć funkcję partycji, schemat partycji i odbudować indeks klastrowany tak, aby był zgodny z schematem partycji i partycjami. W tym przykładzie utworzysz sześciomiesięczne okno przesuwne z partycjami miesięcznymi, począwszy od września 2023 r.

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;

Użyj Transact-SQL do utrzymania partycji w scenariuszu okna przesuwnego

Użyj następującego skryptu Transact-SQL, aby zachować partycje w scenariuszu okna przesuwnego. W tym przykładzie przełączysz partycję z września 2023 r. przy użyciu MERGE RANGE, a następnie dodasz nową partycję z marca 2024 r. przy użyciu 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

Poprzedni skrypt można nieco zmodyfikować i użyć go w regularnym miesięcznym procesie konserwacji:

  1. W kroku (1) utwórz nową tabelę przejściową dla miesiąca, który chcesz usunąć (październik będzie następny w tym przykładzie).
  2. W kroku (3) utwórz i sprawdź ograniczenie zgodne z miesiącem, w którym chcesz usunąć dane: ValidTo <= N'2023-10-31T23:59:59.999' dla partycji październikowej.
  3. W kroku (4) SWITCH1 partycji do nowo utworzonej tabeli przejściowej.
  4. W kroku (6) zmień funkcję partycji, scalając dolną granicę: MERGE RANGE(N'2023-10-31T23:59:59.999' po przeniesieniu danych z października.
  5. W kroku (7) podziel funkcję partycji, tworząc nową górną granicę: SPLIT RANGE (N'2024-04-30T23:59:59.999' po przeniesieniu danych z października.

Jednak optymalnym rozwiązaniem byłoby regularne uruchamianie ogólnego skryptu Transact-SQL, który uruchamia odpowiednią akcję co miesiąc bez modyfikacji. Możesz uogólnić poprzedni skrypt, aby działał na podanych parametrach (dolna granica, która musi zostać scalona, oraz nowa granica utworzona z podziałem partycji). Aby uniknąć tworzenia tabeli przejściowej co miesiąc, możesz utworzyć jedną wcześniej i ponownie użyć jej, zmieniając ograniczenie CHECK tak, aby odpowiadało partycji, którą zamieniasz. Aby uzyskać więcej informacji, zobacz , w jaki sposób można w pełni zautomatyzować sliding window.

Zagadnienia dotyczące wydajności związane z partycjonowaniem tabel

Aby uniknąć przenoszenia danych, należy wykonać operacje MERGE i SPLIT RANGE, ponieważ przenoszenie danych może spowodować znaczne obciążenie związane z wydajnością. Aby uzyskać więcej informacji, zobacz Modyfikowanie funkcji partycji. W tym celu należy użyć RANGE LEFT, a nie RANGE RIGHT, gdy utworzyć funkcję partycji.

Na poniższym diagramie opisano opcje RANGE LEFT i RANGE RIGHT:

Diagram przedstawiający opcje RANGE LEFT i RANGE RIGHT.

Podczas definiowania funkcji partycji jako RANGE LEFTokreślone wartości są górnymi granicami partycji. W przypadku używania RANGE RIGHTokreślone wartości są niższymi granicami partycji. W przypadku użycia operacji MERGE RANGE w celu usunięcia granicy z definicji funkcji partycji podstawowa implementacja usuwa również partycję zawierającą granicę. Jeśli ta partycja nie jest pusta, dane są przenoszone do partycji, która jest wynikiem operacji MERGE RANGE.

W scenariuszu okna przewijania zawsze usuwasz granicę dolnej partycji .

  • RANGE LEFT przypadku: najniższa granica partycji należy do partycji 1, która jest pusta (po przełączeniu partycji), więc MERGE RANGE nie powoduje żadnego przenoszenia danych.

  • RANGE RIGHT przypadek: najniższa granica partycji należy do partycji 2, która nie jest pusta, ponieważ partycja 1 została opróżniona przez wyłączenie. W tym przypadku MERGE RANGE prowadzi do przeniesienia danych, a dane z partycji 2 przenoszone są do partycji 1. Aby tego uniknąć, RANGE RIGHT w scenariuszu przesuwnego okna musi mieć partycję 1, która jest zawsze pusta. Oznacza to, że jeśli używasz RANGE RIGHT, powinieneś utworzyć i zachować jedną dodatkową partycję w porównaniu do przypadku RANGE LEFT.

Wnioski: Zarządzanie partycjami jest łatwiejsze, gdy używasz RANGE LEFT w partycji przesuwnej, i pozwala unikać przenoszenia danych. Jednak definiowanie granic partycji za pomocą RANGE RIGHT jest nieco łatwiejsze, ponieważ nie trzeba radzić sobie z problemami z sprawdzaniem daty i godziny.

Użycie niestandardowego skryptu oczyszczającego

W przypadkach, gdy partycjonowanie tabel nie jest możliwe, innym podejściem jest usunięcie danych z tabeli historii przy użyciu niestandardowego skryptu oczyszczania. Usuwanie danych z tabeli historii jest możliwe tylko wtedy, gdy SYSTEM_VERSIONING = OFF. Aby uniknąć niespójności danych, należy wykonać czyszczenie w oknie obsługi (gdy obciążenia modyfikujące dane nie są aktywne) lub w ramach transakcji (skutecznie blokując inne obciążenia). Ta operacja wymaga CONTROL uprawnienia do tabel bieżących i historycznych.

Aby w minimalnym stopniu blokować bieżące aplikacje i zapytania użytkowników, usuń dane w mniejszych fragmentach z opóźnieniem podczas wykonywania skryptu oczyszczania wewnątrz transakcji. Chociaż nie ma optymalnego rozmiaru dla każdego fragmentu danych do usunięcia we wszystkich scenariuszach, usunięcie ponad 10 000 wierszy w jednej transakcji może nałożyć znaczną karę.

Logika oczyszczania jest taka sama dla każdej tabeli czasowej, więc można ją zautomatyzować za pomocą ogólnej procedury składowanej, która ma być uruchamiana okresowo, dla każdej tabeli czasowej, dla której chcesz ograniczyć historię danych.

Na poniższym diagramie zilustrowano, jak należy zorganizować logikę czyszczenia dla pojedynczej tabeli, aby zmniejszyć wpływ na bieżące obciążenia robocze.

Diagram przedstawiający sposób organizowania logiki czyszczenia dla pojedynczej tabeli w celu zmniejszenia wpływu na bieżące obciążenia.

Poniżej przedstawiono pewne ogólne wytyczne dotyczące implementowania procesu. Zaplanuj uruchamianie logiki oczyszczania każdego dnia i iterowanie wszystkich tabel czasowych, które wymagają czyszczenia danych. Użyj agenta programu SQL Server lub innego narzędzia, aby zaplanować ten proces:

  • Usuń dane historyczne w każdej tabeli czasowej, zaczynając od najstarszych do najnowszych wierszy w kilku iteracji w małych fragmentach, i unikaj usuwania wszystkich wierszy w jednej transakcji, jak pokazano na poprzednim diagramie.

  • Zaimplementuj każdą iterację jako wywołanie ogólnej procedury składowanej, która usuwa część danych z tabeli historii (zobacz poniższy przykład kodu dla tej procedury).

  • Oblicz, ile wierszy należy usunąć dla pojedynczej tabeli czasowej za każdym razem, gdy wywołujesz proces. Na podstawie wyniku i liczby iteracji, które chcesz, określ dynamiczne punkty podziału dla każdego wywołania procedury.

  • Zaplanuj okres opóźnienia między iteracjami dla pojedynczej tabeli, aby zmniejszyć wpływ na aplikacje, które uzyskują dostęp do tabeli czasowej.

Procedura składowana, która usuwa dane dla pojedynczej tabeli czasowej, może wyglądać podobnie do poniższego fragmentu kodu. Dokładnie przejrzyj ten kod i dostosuj go przed zastosowaniem w środowisku.

Ten skrypt generuje trzy instrukcje uruchamiane wewnątrz transakcji:

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

W programie SQL Server 2016 (13.x) pierwsze dwa kroki muszą zostać uruchomione w oddzielnych instrukcjach EXEC lub program SQL Server generuje błąd podobny do następującego przykładu:

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;

Korzystanie z podejścia zasad przechowywania historii czasowej

Dotyczy: SQL Server 2017 (14.x) i nowsze wersje oraz Azure SQL Database.

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

Po zdefiniowaniu zasad przechowywania aparat bazy danych regularnie sprawdza, czy istnieją wiersze historyczne, które kwalifikują się do automatycznego czyszczenia danych. Identyfikacja pasujących wierszy i ich usuwanie z tabeli historii odbywa się w sposób przezroczysty, 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 okresu SYSTEM_TIME (w tych przykładach kolumna ValidTo). Jeśli okres przechowywania jest ustawiony na sześć miesięcy, na przykład wiersze tabeli kwalifikujące się do oczyszczenia spełniają następujący warunek:

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

W poprzednim przykładzie kolumna ValidTo odpowiada końcu okresu SYSTEM_TIME.

Jak skonfigurować zasady przechowywania

Przed skonfigurowaniem zasad przechowywania dla tabeli czasowej sprawdź, 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 ON, ale można ją zmienić za pomocą instrukcji ALTER DATABASE. Ta wartość jest automatycznie ustawiana na OFF po operacji przywracania do punktu w czasie (PITR). Aby włączyć czyszczenie historii czasowej dla bazy danych, uruchom poniższe polecenie. Musisz zastąpić <myDB> bazą danych, którą chcesz zmienić:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION ON;

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

Okres przechowywania można określić przy użyciu różnych jednostek czasu: DAYS, WEEKS, MONTHSi YEARS. Jeśli HISTORY_RETENTION_PERIOD zostanie pominięte, zakłada się przechowywanie INFINITE. 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));

Aby przejrzeć bieżący stan zasad przechowywania, użyj poniższego przykładu. To zapytanie łą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 silnik bazy danych usuwa przestarzałe wiersze

Proces oczyszczania zależy od układu indeksu tabeli historii. Tylko tabele historii z indeksem klastrowanym (drzewo B+ lub magazyn kolumnowy) mogą mieć skonfigurowane zasady skończonego przechowywania. 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 (drzewa B+) usuwa przestarzałe wiersze w mniejszych fragmentach (do 10 000), minimalizując presję na dziennik bazy danych i podsystem we/wy. Mimo że logika oczyszczania używa wymaganego indeksu drzewa B+, kolejność usuwania wierszy starszych niż okres przechowywania nie może być gwarantowana. Nie należy stosować żadnej zależności od kolejności oczyszczania w aplikacjach.

Zadanie oczyszczania klastrowanego magazynu kolumn powoduje usunięcie jednocześnie całych grup wierszy (zwykle zawierających 1 milion wierszy), co jest bardziej wydajne, szczególnie w przypadku generowania danych historycznych w dużym tempie.

Zrzut ekranu przedstawiający przechowywanie klastrowanego magazynu kolumn.

Kompresja danych i czyszczenie przechowywania sprawiają, że klastrowany indeks magazynu kolumnowego jest doskonałym wyborem w scenariuszach, w których obciążenie robocze 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.

Aby uzyskać więcej informacji, zobacz Zarządzanie danymi historycznymi w tabelach czasowych przy użyciu zasad przechowywania.

Powiązana zawartość