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

Dotyczy do: SQL Server 2016 (13.x) i nowsze wersje Azure SQL DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

Systemowo wersjonowana tabela czasowa przechowuje każdą poprzednią wersję każdego wiersza w swojej tabeli historii. Tabela historii może zwiększyć rozmiar bazy danych bardziej niż zwykłe tabele pod następującymi warunkami:

  • Przez długi czas przechowujesz dane historyczne.
  • Stosujesz wzorzec modyfikacji danych z przewagą aktualizacji lub usunięć.

Duża, stale rosnąca tabela historii może stać się problemem, zarówno ze względu na koszty przechowywania, jak i podatek od wydajności, jaki nakłada na zapytania czasowe. Opracowanie polityki przechowywania danych dla tabeli historycznej jest ważnym elementem planowania i zarządzania cyklem życia każdej tabeli czasowej.

Zaplanuj politykę przechowywania danych

Aby zarządzać retencją danych w tabeli czasowej, najpierw określ wymagany okres retencji dla każdej tabeli czasowej. Polityka retencji powinna w większości przypadków być częścią logiki biznesowej aplikacji, która korzysta z tabel czasowych. Na przykład zastosowania w audycie danych i scenariuszach podróży w czasie mają sztywne wymagania dotyczące tego, jak długo dane historyczne muszą być dostępne do zapytań online.

Po ustaleniu okresu przechowywania danych, opracuj plan zarządzania danymi historycznymi. Zdecyduj, jak i gdzie są przechowywane dane historyczne oraz jak usunąć dane historyczne starsze niż wymagania dotyczące przechowywania.

Każde podejście opisane w tym artykule działa na kolumnie odpowiadającej końcowemu okresowi w bieżącej tabeli, czyli kolumnie ValidTo w poniższych przykładach. 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 stan ValidTo < DATEADD (DAY, -30, SYSUTCDATETIME()) ten odpowiada danym historycznym starszym niż 30 dni.

Wybierz jedno z następujących podejść do działania na tych wierszach:

Approach Jak to działa Kiedy należy go używać
Polityka przechowywania historii czasowej Ustawiasz okres przechowywania dla każdej tabeli, a zadanie w tle automatycznie usuwa stare wiersze. Najprostsza opcja, gdy można po prostu całkowicie usunąć starą historię.
Partycjonowanie tabel Przesuwane okno przełącza najstarszą partycję poza tabelę historii, więc możesz ją archiwizować lub wyrzucać. Kiedy chcesz archiwizować dane historyczne przed ich usunięciem lub chcesz eliminować partycje dla zapytań czasowych.
Niestandardowy skrypt oczyszczania Zaplanowany skrypt wyłącza wersjonowanie systemowe, usuwa stare wiersze w małych częściach, a następnie ponownie włącza wersjonowanie systemowe. Gdy polityka retencji nie jest dostępna dla twojej tabeli, a partycjonowanie nie jest możliwe.

Przykłady partycjonowania i niestandardowego czyszczenia w tym artykule wykorzystują przykłady z artykułu Create a system-versioned temporal table.

Stosuj politykę przechowywania historii czasowej

Dotyczy do: SQL Server 2017 (14.x) i nowszych wersji, Azure SQL Database, Azure SQL Managed Instance oraz bazy danych SQL w Microsoft Fabric.

Możesz skonfigurować przechowywanie historii czasowej na poziomie poszczególnych tabel, co pozwala tworzyć elastyczne polityki starzenia się. Aby włączyć retencję czasową, ustaw HISTORY_RETENTION_PERIOD podczas tworzenia tabeli lub przy zmianie schematu.

Po zdefiniowaniu polityki retencji Database Engine wykonuje zaplanowane zadanie w tle, które znajduje i transparentnie usuwa historyczne wiersze, których wartość końcowa jest starsza niż okres retencji.

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 ma domyślnie wartość ON, ale można ją zmienić za pomocą instrukcji ALTER DATABASE. Aparat bazy danych również ustawia ją automatycznie na OFF po operacji przywracania do określonego punktu w czasie (PITR), zgodnie z opisem w sekcji Zagadnienia dotyczące przywracania do określonego punktu w czasie. Aby włączyć czyszczenie historii czasowej w swojej bazie danych, uruchom następujące polecenie. Zamień <myDB> na bazę danych, którą chcesz zmienić:

ALTER DATABASE [<myDB>]
    SET TEMPORAL_HISTORY_RETENTION ON;

Important

Możesz skonfigurować retencję dla tabel temporalnych, nawet jeśli is_temporal_history_retention_enabled ma wartość OFF, ale aparat bazy danych nie uruchamia w takim przypadku automatycznego czyszczenia przestarzałych wierszy.

Możesz skonfigurować politykę retencji 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
    )
);

Po wprowadzeniu tych zasad wiersze w dbo.WebsiteUserInfoHistory kwalifikują się do oczyszczenia, gdy spełniają następujący warunek:

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

Możesz określić okres retencji w DAYS, WEEKS, MONTHS, lub YEARS. Jeśli pominiesz HISTORY_RETENTION_PERIOD, domyślna retencja wynosi INFINITE. Możesz również jawnie użyć słowa kluczowego INFINITE.

W niektórych sytuacjach możesz chcieć skonfigurować retencję 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));

Important

Ustawienie SYSTEM_VERSIONING na OFF nie zachowuje wartości okresu retencyjnego. Ustawienie SYSTEM_VERSIONING na ON bez jawnego HISTORY_RETENTION_PERIOD skutkuje zachowaniem INFINITE.

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 AS T1
    OUTER APPLY (
        SELECT is_temporal_history_retention_enabled
        FROM sys.databases
        WHERE name = DB_NAME()
) AS DB
    LEFT OUTER JOIN sys.tables AS 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. Politykę ograniczonego okresu przechowywania można skonfigurować tylko dla tabel historii z klastrowanym indeksem rowstore (B-tree) lub klastrowanym indeksem magazynu kolumnowego. Zadanie działające w tle wykonuje usuwanie przestarzałych danych dla wszystkich tabel czasowych ze skończonym okresem retencji.

Note

W dokumentacji jest zwykle używany termin B-tree w odniesieniu do indeksów. W indeksach typu rowstore silnik bazy danych implementuje drzewo B+. Nie dotyczy to indeksów magazynu kolumn ani indeksów w tabelach zoptymalizowanych pod kątem pamięci. Aby uzyskać więcej informacji, zobacz architekturę i przewodnik projektowania indeksu SQL Server i Azure SQL.

Indeks składu wiersza w B-drzewie

Indeks klastrowany w pamięci wiersza musi zaczynać się od kolumny odpowiadającej końcowi okresu SYSTEM_TIME . Jeśli taki indeks nie istnieje, nie można skonfigurować skończonego okresu retencji:

Msg 13765, Level 16, State 1
Setting finite retention period failed on system-versioned temporal table
'dbo.WebsiteUserInfo' because the history table 'dbo.WebsiteUserInfoHistory'
does not contain required clustered index. Consider creating a clustered
columnstore or B-tree index starting with the column that matches end of
SYSTEM_TIME period, on the history table.

Domyślna tabela historii już ma zgodny klasterizowany indeks. Jeśli spróbujesz usunąć ten indeks w tabeli historii o skończonym okresie retencji, operacja kończy się błędem i pojawia się następujący błąd:

Msg 13766, Level 16, State 1
Cannot drop the clustered index 'WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory'
because it is being used for automatic cleanup of aged data. Consider setting HISTORY_RETENTION_PERIOD to INFINITE on the corresponding system-versioned
temporal table if you need to drop this index.

Logika czyszczenia klastrowanego indeksu rowstore usuwa stare wiersze w mniejszych fragmentach (do 10 000), minimalizując obciążenie dla logu bazy danych i podsystemu I/O. Chociaż logika czyszczenia używa wymaganego indeksu drzewa B, nie może zagwarantować kolejności usuwania wierszy starszych niż okres retencji. Nie należy stosować żadnej zależności od kolejności oczyszczania w aplikacjach.

Klastrowany indeks kolumnowy

Zadanie czyszczenia dla clustered columnstore usuwa całe grupy wierszy jednocześnie. Każda grupa wierszy zazwyczaj zawiera milion wierszy. Ta metoda jest bardziej efektywna, zwłaszcza gdy Twoje obciążenie generuje dane historyczne w szybkim tempie.

Zrzut ekranu przedstawiający przechowywanie klastrowanego magazynu kolumn.

Kompresja danych i oczyszczanie danych zgodnie z zasadami retencji sprawiają, że klastrowany indeks columnstore jest dobrym wyborem w scenariuszach, w których Twoje obciążenie robocze szybko generuje dużą ilość danych historycznych. Ten wzorzec jest typowy dla intensywnych obciążeń transakcyjnych, które wykorzystują tabele temporalne do śledzenia zmian i audytu, analizy trendów lub pobierania danych z Internetu Rzeczy (IoT).

Czyszczenie klastrowanego indeksu magazynu kolumn działa optymalnie, gdy historyczne wiersze napływają w kolejności rosnącej (uporządkowane według kolumny końca okresu). Ten warunek zawsze występuje, gdy tylko SYSTEM_VERSIONING mechanizm wypełnia tabelę historii. Jeśli wiersze w tabeli historii nie są uporządkowane według kolumny końcowej okresu (co może się zdarzyć podczas migracji istniejących danych historycznych), utwórz ponownie klastrowany indeks pamięci kolumn na poprawnie uporządkowanym indeksie wiersza B-drzewa, aby osiągnąć optymalną wydajność.

Unikaj odbudowy klastrowanego indeksu kolumnowego w tabeli historii z ograniczonym okresem retencji, ponieważ odbudowa może zmienić kolejność grup wierszy naturalnie narzucaną przez systemowe wersjonowanie. Jeśli musisz odbudować klastrowany indeks kolumnowy w tabeli historii, utwórz go ponownie na zgodnym indeksie B-drzewa, aby zachować uporządkowanie grup wierszy niezbędne do regularnego czyszczenia danych. Stosuj to samo podejście, jeśli tworzysz tabelę temporalną z istniejącą tabelą historii, która ma klasterizowany indeks pamięci kolumn 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 the clustered columnstore index */
CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory
    ON WebsiteUserInfoHistory WITH (DROP_EXISTING = ON);

Podczas konfigurowania ograniczonego okresu przechowywania dla tabeli historii z klastrowanym indeksem magazynu kolumn nie można utworzyć w tej tabeli dodatkowych nieklastrowanych indeksów B-tree:

CREATE NONCLUSTERED INDEX IX_WebHistNCI
    ON WebsiteUserInfoHistory(UserName);

Poprzednia instrukcja kończy się następującym błędem:

Msg 13772, Level 16, State 1
Cannot create non-clustered index on a temporal history table 'WebsiteUserInfoHistory' since it has finite retention period and clustered columnstore index defined.

Wykonywanie zapytań względem tabel z zasadami przechowywania

Wszystkie zapytania w tabeli temporalnej automatycznie filtrują historyczne wiersze odpowiadające polityce ograniczonej retencji, aby uniknąć nieprzewidywalnych i niespójnych wyników. Zadanie czyszczenia usuwa stare wiersze w dowolnym momencie i w dowolnej kolejności.

Poniższy zrzut ekranu pokazuje plan zapytania dla podstawowego zapytania. Ten przykład zakładaMONTH jeden okres retencji w tabeli WebsiteUserInfo :

SELECT *
FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

Plan zapytań zawiera dodatkowy filtr w kolumnie końca okresu (ValidTo) w operatorze Clustered Index Scan (podkreślonym na poniższym obrazku) w tabeli historii.

Zrzut ekranu planu zapytań z dodatkowym filtrem retencyjnym w kolumnie ValidTo w tabeli historii.

Jeśli wykonasz zapytanie bezpośrednio względem tabeli historii, możesz zobaczyć wiersze starsze niż określony okres retencji, ale bez gwarancji powtarzalności wyników zapytania. Poniższy zrzut ekranu pokazuje plan zapytań dla zapytania w tabeli historii bez dodatkowych filtrów:

Zrzut ekranu planu zapytań przy bezpośrednim zapytaniu do tabeli historii bez filtra retencyjnego.

Nie polegaj na logice biznesowej, która odczytuje tabelę historii po upływie okresu retencyjnego, bo możesz uzyskać niespójne lub nieoczekiwane wyniki. Używaj zapytań temporalnych z klauzulą FOR SYSTEM_TIME do analizy danych w tabelach czasowych.

Rozważania dotyczące przywracania do punktu w czasie

Gdy przywracasz bazę danych do określonego punktu w czasie, nowa baza danych ma wyłączoną retencję czasową na poziomie bazy danych (is_temporal_history_retention_enabled ustawiona na OFF). To zachowanie umożliwia przeanalizowanie historycznych wierszy starszych niż okres przechowywania, zanim zadanie czyszczące je usunie. Aby wznowić automatyczne czyszczenie przywróconej bazy danych, ustaw TEMPORAL_HISTORY_RETENTION z powrotem na .ON

Note

Baza danych utworzona w tierze Premium w Azure SQL Database przechowuje kopie zapasowe do 35 dni, więc możesz przywrócić ją do określonego momentu w dowolnym momencie tego okna. W przypadku tabeli czasowej z miesięcznym okresem przechowywania umożliwia to przeglądanie wierszy historycznych sprzed maksymalnie 65 dni przez wysyłanie zapytań bezpośrednio do tabeli historii w przywróconej bazie danych.

Użyj partycjonowania tabel

partycjonowane tabele i indeksy mogą zwiększyć możliwości zarządzania i skalowalności dużych tabel. Stosując podejście partycjonowania tabel, możesz wdrożyć niestandardowe czyszczenie danych lub archiwizację offline w zależności od warunków czasowych. Partycjonowanie tabel zapewnia również korzyści wydajnościowe podczas wykonywania zapytań na tabelach czasowych w podzbiorze historii danych, poprzez użycie eliminacji partycji.

Użyj partycjonowania tabeli, aby wdrożyć okno przesuwne do usuwania najstarszej części danych historycznych z tabeli historii oraz utrzymywać stały rozmiar przechowywanej części danych według wieku. Przesuwane okno utrzymuje dane w tabeli historii równe wymaganemu okresowi przechowywania. Tabela historii obsługuje przełączanie danych, gdy SYSTEM_VERSIONING jest ON, co oznacza, że możesz usunąć część danych historycznych bez wprowadzania okna serwisowego ani blokowania normalnych obciążeń roboczych.

Note

Aby przeprowadzić przełączanie partycji, klastrowany indeks w tabeli historii musi być zgodny ze schematem partycjonowania (musi zawierać ValidTo). Domyślna tabela historii zawiera klastrowany indeks obejmujący kolumny ValidTo i ValidFrom, co jest optymalne do partycjonowania, wstawiania nowych danych historycznych oraz typowych zapytań czasowych. Aby uzyskać więcej informacji, zobacz Tabele czasowe.

Przesuwne okno wymaga dwóch zestawów zadań:

  • Zadanie konfiguracji partycjonowania
  • Cykliczne zadania konserwacji partycji

Dla tej ilustracji załóżmy, że chcesz przechowywać dane historyczne przez sześć miesięcy i każdy miesiąc danych w osobnej partycji. Załóż też, że aktywowałeś wersjonowanie systemowe we wrześniu 2023.

Zadanie konfiguracji partycjonowania tworzy początkową konfigurację partycjonowania dla tabeli historii. W tym przykładzie tworzysz tyle samo partycji, ile ma przesuwane okno, w miesiącach, plus jedną dodatkową pustą partycję. Ta konfiguracja zapewnia, że system może prawidłowo przechowywać nowe dane na początku cyklicznego zadania utrzymania partycji. Gwarantuje to również, że nigdy nie dzielisz partycji zawierających dane, co pozwala uniknąć kosztownych transferów danych. Zdefiniuj funkcję partycji z , RANGE LEFT a nie RANGE RIGHT. Więcej informacji można znaleźć w artykule Aspekty wydajności przy partycjonowaniu tabel później w tym artykule.

Poniższy obraz pokazuje 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.

Pierwsza i ostatnia partycja są otwarte odpowiednio od dołu i od góry, aby zapewnić, że każdy nowy wiersz trafi do partycji docelowej niezależnie od wartości w kolumnie partycjonowania. Z czasem nowe rzędy na tablicy historycznej trafiają na wyższe przegrody. Gdy szósta partycja się zapełni, osiągasz docelowy okres retencji. W tym momencie rozpocznij cykliczne zadanie utrzymania partycji po raz pierwszy. Ustaw je tak, aby uruchamiało się cyklicznie, w tym przykładzie raz w miesiącu.

Poniższy obraz ilustruje powtarzające się zadania utrzymania partycji.

Diagram przedstawiający cykliczne zadania konserwacji partycji.

Każde uruchomienie cyklicznego zadania konserwacji wykonuje następujące kroki:

  1. SWITCH OUT: Utwórz tabelę przejściową, a następnie przełącz partycję między tabelą historii a tabelą przejściową za pomocą instrukcji ALTER TABLE z argumentem SWITCH PARTITION.

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

    Po przełączeniu partycji możesz opcjonalnie zarchiwizować dane z tabeli przejściowej, a następnie usunąć lub opróżnić tę tabelę, aby przygotować ją do kolejnego cyklu konserwacji.

  2. MERGE RANGE: Połącz pustą partycję 1 z partycją 2 za pomocą instrukcji ALTER PARTITION FUNCTION z MERGE RANGE. Gdy użyjesz tej funkcji do usunięcia najniższej granicy, w praktyce łączysz pustą partycję 1 z poprzednią 2 , tworząc nową partycję 1. Inne partycje również skutecznie zmieniają ich reguły.

  3. SPLIT RANGE: Utwórz nową pustą partycję 7, za pomocą instrukcji ALTER PARTITION FUNCTION z SPLIT RANGE. Gdy użyjesz tej funkcji do dodania nowej górnej granicy, w praktyce tworzysz osobną partycję na nadchodzący miesiąc.

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

Użyj następującego skryptu Transact-SQL, aby utworzyć funkcję partycjonowania, schemat partycjonowania i ponownie utworzyć indeks klastrowany tak, aby był zgodny ze schematem partycjonowania. 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 zastępujesz partycję z września 2023 za pomocą MERGE RANGE, a następnie dodajesz nową partycję dla marca 2024 za 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 to switch 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 to switch 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;

Jednak optymalnym rozwiązaniem jest regularne uruchamianie uniwersalnego skryptu Transact-SQL co miesiąc bez żadnych modyfikacji. Możesz uogólnić poprzedni skrypt, aby działał na podanych parametrach (dolna granica, która musi się połączyć, oraz nowa granica utworzona przez podział partycji). Aby uniknąć tworzenia tabeli pomostowej co miesiąc, utwórz ją wcześniej i wykorzystuj ponownie, zmieniając ograniczenie CHECK tak, aby odpowiadało partycji, którą przełączasz. Aby uzyskać więcej informacji, zobacz jak w pełni zautomatyzować scenariusz okna przesuwnego.

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

Wykonuj operacje MERGE RANGE and SPLIT RANGE w sposób unikający ruchu danych, ponieważ ruch danych może powodować znaczne obciążenie wydajności. Aby uzyskać więcej informacji, zobacz Modyfikowanie funkcji partycji.

Gdy tworzysz funkcję partycji jako RANGE LEFT, określone wartości to górne granice 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, MERGE RANGE przenosi dane na powstałą partycję.

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

Diagram przedstawiający opcje RANGE LEFT i RANGE RIGHT.

W scenariuszu okna przesuwnego zawsze usuwasz granicę najniższej partycji.

  • RANGE LEFT przypadek: Najniższa granica partycji należy do partycji 1, która jest pusta (po operacji SWITCH OUT 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ż zamiana opróżnia tylko partycję 1. W tym przypadku MERGE RANGE powoduje przenoszenie danych, przenosząc je z partycji 2 do partycji 1. Aby uniknąć tego przemieszczania danych, RANGE RIGHT w scenariuszu okna przesuwnego musi być partycja 1, która jest zawsze pusta. Ten wymóg oznacza, że jeśli używasz RANGE RIGHT, powinieneś utworzyć i utrzymywać jedną dodatkową partycję w porównaniu do RANGE LEFT przypadku.

Podsumowanie: Zarządzanie partycjami jest łatwiejsze, gdy w partycjonowaniu przesuwnym używa się RANGE LEFT, a także pozwala uniknąć 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żyj niestandardowego skryptu do czyszczenia

Gdy polityka retencji nie jest dostępna dla Twojej tabeli, a partycjonowanie tabel nie jest wykonalne, możesz usunąć dane z tabeli historii, używając niestandardowego skryptu czyszczenia. Proces ten jest możliwy tylko wtedy, gdy SYSTEM_VERSIONING = OFF. Aby uniknąć niespójności danych, wykonuj czyszczenie albo w oknie konserwacyjnym (gdy obciążenia modyfikujące dane nie są aktywne), albo w trakcie transakcji (skutecznie blokując inne obciążenia). Ta operacja wymaga CONTROL uprawnienia do tabel bieżących i historycznych.

Logika czyszczenia jest taka sama dla każdej tabeli czasowej, więc można ją zautomatyzować za pomocą ogólnej procedury przechowywanej. Użyj SQL Server Agent lub innego narzędzia, aby zaplanować tę procedurę na codzienne uruchomienie, iterując każdą tabelę czasową, dla której chcesz ograniczyć historię danych.

Poniższy schemat pokazuje, jak zorganizować logikę czyszczenia w przypadku jednej tabeli, aby ograniczyć wpływ na bieżące obciążenia robocze.

Diagram pokazujący, jak zorganizować logikę czyszczenia dla jednej tabeli, aby zmniejszyć wpływ na bieżące obciążenia.

Oto kilka ogólnopoziomowych wytycznych dotyczących wdrażania tego procesu:

  • Usuń dane historyczne w każdej tabeli czasowej w kilku iteracjach małych fragmentów. Zacznij od najstarszych rzędów i przejdź do najnowszych. Unikaj usuwania wszystkich wierszy w jednej transakcji, jak pokazuje poprzedni diagram. Chociaż żadna pojedyncza wielkość porcji nie sprawdza się we wszystkich scenariuszach, usunięcie ponad 10 000 wierszy w jednej transakcji może znacząco obniżyć wydajność.

  • Implementuj każdą iterację jako wywołanie ogólnej procedury przechowywanej, która usuwa część danych z tabeli historii.

  • 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 opóźnienie między iteracjami dla jednej tabeli, aby zmniejszyć wpływ na aplikacje korzystające z tabeli czasowej.

Poniższa procedura przechowywana usuwa dane dla pojedynczej tabeli czasowej. Odkrywa tabelę historii oraz kolumnę końca okresu z widoków katalogowych, a następnie wykonuje trzy instrukcje wewnątrz transakcji: SET SYSTEM_VERSIONING = OFF, DELETE FROM <history_table>, oraz SET SYSTEM_VERSIONING = ON. Dokładnie przejrzyj ten kod i dostosuj go przed zastosowaniem go w swoim środowisku.

W programie SQL Server 2016 (13.x) pierwsze dwa kroki muszą zostać uruchomione w oddzielnych instrukcjach EXECUTE 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 AS NVARCHAR (MAX) = '';
DECLARE @deleteHistoryDataScript AS NVARCHAR (MAX) = '';
DECLARE @enableVersioningScript AS NVARCHAR (MAX) = '';
DECLARE @historyTableName AS SYSNAME;
DECLARE @historyTableSchema AS SYSNAME;
DECLARE @periodColumnName AS 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 AS t1
         INNER JOIN sys.tables AS t2
             ON t1.history_table_id = t2.object_id
         INNER JOIN sys.schemas AS s1
             ON t1.schema_id = s1.schema_id
         INNER JOIN sys.schemas AS s2
             ON t2.schema_id = s2.schema_id
         INNER JOIN sys.periods AS p
             ON p.object_id = t1.object_id
         INNER JOIN sys.columns AS 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;
    EXECUTE (@disableVersioningScript);
    EXECUTE (@deleteHistoryDataScript);
    EXECUTE (@enableVersioningScript);
COMMIT TRANSACTION;

Treści powiązane

  • Last updated on