Az előzményadatok megőrzésének kezelése rendszerverziójú időbeli táblákban

Vonatkozik a következőkre: SQL Server 2016 (13.x) és későbbi verziók Azure SQL DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

Egy rendszer-verziójú időbeli tábla minden korábbi változatot megőrzi minden sor történeti táblájában. A történettáblázat az alábbi feltételek mellett nagyobb mértékben növelheti az adatbázis méretét, mint a hagyományos táblák:

• Hosszú ideig megőrizöd a történelmi adatokat.
• Frissítés- vagy törlésintenzív adatmódosítási mintája van.

Egy nagy, folyamatosan növekvő történettáblázat problémává válhat, mind a tárolási költségek, mind az időbeli lekérdezésekre kivetett teljesítményadó miatt. Az adatmegőrzési szabályzat kidolgozása a történettáblázathoz fontos része az összes időbeli táblázat életciklusának tervezésének és kezelésének.

Tervezzen adatmegőrzési szabályzatot

Az időbeli táblázat adatmegőrzésének kezeléséhez először határozzuk meg az egyes időbeli táblák szükséges megtartási időszakát. A megtartási szabályzatod legtöbbször része az alkalmazásnak az üzleti logikájának, amely az időbeli táblákat használja. Például az adataudit és időutazási szcenáriók esetében szilárd követelmények vannak arra vonatkozóan, hogy a történelmi adatok meddig kell elérhetők legyenek online lekérdezéshez.

Miután meghatároztad az adatmegőrzési időszakodat, dolgozz ki egy tervet a történelmi adatok kezelésére. Döntse el, hogyan és hol tárolja az előzményadatokat, és hogyan törölheti a megőrzési követelményeknél régebbi előzményadatokat.

A cikkben szereplő minden megközelítés arra az oszlopra vonatkozik, amely a jelenlegi táblázat időszakának végének felel meg, ami az ValidTo alábbi példák oszlopa. Az egyes sorok időszakának vége határozza meg azt a pillanatot, amikor a sorverzió zárt, vagyis amikor az előzménytáblába kerül. Például az állapot ValidTo < DATEADD (DAY, -30, SYSUTCDATETIME()) egyezik a 30 napnál régebbi történelmi adatokkal.

Válassz az alábbi módszerek egyikét, hogy ezeken a sorokban cselekedj:

Approach	Hogyan működik?	Mikor érdemes használni?
Időbeli előzmények megőrzési szabályzata	Minden táblához beállítasz egy megtartási időszakot, és egy háttérfeladat automatikusan törli az öregedett sorokat.	A legegyszerűbb megoldás, amikor teljesen törölheted az öregedett előzményeket.
Táblák particionálása	Egy csúszóablak kikapcsolja a legrégebbi partíciót a történettáblázatból, így archiválhatod vagy kidobhatod.	Amikor a történelmi adatokat archiválni akarod eltávolítás előtt, vagy amikor partíciós eltávolítást szeretnél az időbeli lekérdezésekhez.
egyéni törlési szkript	Egy ütemezett szkript letiltja a rendszerverziót, törli az öregedett sorokat kis darabokban, majd újra engedélyezi a rendszerverziót.	Amikor a táblázatodhoz nincs elérhető megtartási szabályzat, és a partíció nem életképes.

A cikkben szereplő partíciós és egyedi tisztítási példák a Rendszer-verziós időbeli táblázat létrehozása mintáit használják.

Használj időbeli előzmény megőrzési szabályzatot

A következőkre vonatkozik: SQL Server 2017 (14.x) és újabb verziók, Azure SQL Database, Azure SQL Managed Instance, valamint SQL-adatbázis a Microsoft Fabricben.

Az időbeli előzmények megőrzését konfigurálhatod az egyes táblaszinten, így rugalmas öregedési szabályzatok létrehozását is lehetővé tedd. Az időbeli megtartás érdekében állítsd HISTORY_RETENTION_PERIOD be a tábla létrehozása vagy sémaváltás közben.

Miután meghatároztad a megtartási szabályzatot, a Database Engine egy ütemezett háttérfeladatot futtat, amely felkeresi és átláthatóan eltávolítja azokat a történelmi sorokat, amelyek időszak végi értéke régebbi, mint a megtartási időszak.

Adatmegőrzési szabályzat konfigurálása

Mielőtt konfigurálja az időalapú táblák adatmegőrzési szabályzatát, ellenőrizze, hogy engedélyezve van-e az időbeli előzménymegőrzés az adatbázis szintjén:

SELECT is_temporal_history_retention_enabled,
       name
FROM sys.databases;

Az adatbázisjelző is_temporal_history_retention_enabled alapértelmezett értéke ON, de ezt a ALTER DATABASE utasítással módosíthatod. Az adatbázismotor egy adott időpontra történő visszaállítási (PITR) művelet után is automatikusan OFF értékre állítja, a Az adott időpontra történő visszaállítás szempontjai című szakaszban leírtak szerint. Az adatbázis időelőzmény-megőrzésének törlését engedélyezheti az alábbi utasítás futtatásával. Cseréld <myDB> le azt az adatbázist, amit módosítani szeretnél:

ALTER DATABASE [<myDB>]
    SET TEMPORAL_HISTORY_RETENTION ON;

Important

Beállíthatja a temporális táblák megőrzési idejét akkor is, ha a(z) is_temporal_history_retention_enabled értéke OFF, de ebben az esetben az Adatbázismotor nem indítja el az elavult sorok automatikus törlését.

A megtartási szabályzatot beállíthatod a táblázat létrehozása során, ha megadsz egy értéket a HISTORY_RETENTION_PERIOD paraméterhez:

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

Ezzel a szabályzattal a sorok dbo.WebsiteUserInfoHistory akkor jogosultak lesznek takarításra, ha teljesítik a következő feltételt:

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

Megadhatod a megtartási időt , DAYSWEEKS, MONTHS, vagy YEARS. Ha kihagyod a(z) HISTORY_RETENTION_PERIOD elemet, a megőrzési idő alapértelmezés szerint INFINITE. A INFINITE kulcsszót explicit módon is használhatja.

Bizonyos esetekben érdemes lehet a megtartást a tábla létrehozása után konfigurálni, vagy módosítani a korábban konfigurált értéket. Ebben az esetben használja a ALTER TABLE utasítást:

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

Important

A(z) SYSTEM_VERSIONING érték OFF értékre állítása nem őrzi meg a megőrzési időtartam értékét. A(z) SYSTEM_VERSIONINGON értékre állítása explicit INFINITE megadása nélkül a(z) HISTORY_RETENTION_PERIOD megőrzését eredményezi.

A megőrzési szabályzat aktuális állapotának áttekintéséhez használja az alábbi mintát. Ez a lekérdezés az adatbázis szintjén összekapcsolja az ideiglenes adatmegőrzés engedélyezésének jelzőt az egyes táblák megőrzési időszakaival:

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;

Hogyan törli az adatbázismotor az elöregedett sorokat?

A törlési folyamat az előzménytábla indexelrendezésétől függ. Korlátozott megőrzési szabályzatot csak olyan előzménytáblákon lehet konfigurálni, amelyek fürtözött sortárolós (B-fa) vagy fürtözött oszloptárolós indexszel rendelkeznek. Egy háttérfeladat végzi az öregedett adattörlést minden időbeli táblán, amelynek megtartási ideje véges.

Note

A dokumentáció általában a B-fa kifejezést használja az indexekre hivatkozva. A sorkataszterekben az adatbázismotor egy B+ fát implementál. Ez nem vonatkozik az oszlopcentrikus indexekre vagy a memóriaoptimalizált táblák indexére. További információ: SQL Server és Azure SQL index architektúrája és tervezési útmutatója.

B-tree soralapú tárolású index

A soros tárolású klaszterezett indexnek azzal az oszloppal kell kezdődnie, amely a SYSTEM_TIME időszak végének felel meg. Ha ilyen index nem létezik, nem lehet véges megtartási időszakot konfigurálni:

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.

Az alapértelmezett történettáblázat már rendelkezik egy kompatibilis klaszterezett indextel. Ha megpróbálod ezt az indexet egy véges megtartási időszakú történettáblázatba helyezni, a művelet a következő hibával kudarcot vall:

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.

A sortároló klaszterelt index tisztítási logikája kisebb darabokban törli az öregedett sorokat (akár 10 000-ig), csökkentve a nyomást az adatbázis naplójára és az I/O alrendszerre. Bár a tisztítási logika a szükséges B-fa indexet használja, nem tudja garantálni a megőrzési időszaknál régebbi sorok törlési sorrendjét. Ne függjön az alkalmazásokban a takarítási sorrendre.

Fürtözött oszlopalapú tárolású index

A klaszterezett columnstore tisztítási feladata egyszerre eltávolít egész sorcsoportokat . Minden sorcsoport általában egymillió sorból áll. Ez a módszer hatékonyabb, különösen, ha a munkaterhelés nagy tempóban generál történelmi adatokat.

Az adattömörítés és a megőrzés tisztítása miatt a klaszterezett columnstore index jó választás olyan helyzetekben, amikor a munkaterhelés gyorsan nagy mennyiségű történelmi adatot generál. Ez a minta jellemző intenzív tranzakciós feldolgozási feladatokra, amelyek időbeli táblázatokat használnak változáskövetéshez és auditáláshoz, trendelemzéshez vagy az Internet of Things (IoT) adatfelvételhez.

A klaszterezett oszloptároló index tisztítása optimálisan működik, ha a történelmi sorok növekvő sorrendben érkeznek (az időszak végi oszlop szerint sorrendben). Ez a feltétel mindig akkor fordul elő, ha csak a SYSTEM_VERSIONING mechanizmus tölti fel a történettáblázatot. Ha a történettáblázat sorai nem az időszak végi oszlop szerint vannak sorrendben (ami előfordulhat meglévő történelmi adatok migrálásakor), akkor hozd létre újra a klaszterezett columnstore indexet egy megfelelően rendezett B-fa sortároló index fölött, hogy elérd az optimális teljesítményt.

Kerüld a klaszterezett oszloptároló index újraépítését egy véges megtartási időszakú történettáblán, mert az újraépítés megváltoztathatja a rendszerverziós művelet természetes sorcsoport-sorát. Ha újra kell építened a klaszterezett oszloptároló indexet a történettáblázaton, hozd létre újra egy kompatibilis B-fa index fölött, hogy megőrizze a sorcsoport-sorsorrendet, amely szükséges a rendszeres adattisztításhoz. Ugyanezt a megközelítést alkalmazzuk, ha időbeli táblát hozol létre egy meglévő történeti táblával, amelynek klaszterezett oszloptároló indexe van, garantált adatsorrend nélkül:

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

Ha egy véges megőrzési időszakot állítasz be egy klaszterezett oszloptárolós indexszel rendelkező történettáblához, nem hozhatsz létre további nem klaszterezett B-fa indexeket azon a táblán:

CREATE NONCLUSTERED INDEX IX_WebHistNCI
    ON WebsiteUserInfoHistory(UserName);

Az előző állítás a következő hibával hibázik:

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.

Táblák lekérdezése adatmegőrzési szabályzattal

Az időbeli táblán minden lekérdezés automatikusan kiszűri azokat a történelmi sorokat, amelyek megfelelnek a véges megtartási szabályzatnak, hogy elkerüljék a kiszámíthatatlan és következetlen eredményeket. A takarítási feladat bármely időben és tetszőleges sorrendben törli az öregedett sorokat.

A következő képernyőkép egy alapvető lekérdezési tervet mutatja. Ez a példa egyMONTH megtartási időszakot feltételez a WebsiteUserInfo táblázaton:

SELECT *
FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

A lekérdezési terv egy további szűrőt tartalmaz a history tábla Clustered Index Scan operátorában az időszak végi oszlopon (ValidTo), amelyet a következő kép kiemel.

Ha közvetlenül a történettáblázatot kérdezzük, előfordulhat, hogy olyan sorok vannak, amelyek régebbi a megadott megtartási időszaknál, de nem garantálják az ismételhető lekérdezési eredményeket. A következő képernyőkép a lekérdezési tervet mutatja a történettáblázatban extra szűrők nélkül:

Ne támaszkodj üzleti logikára, amely a megtartási időszakon túl olvasza a történettáblázatot, mert következetlen vagy váratlan eredményeket kaphatsz. Használj időbeli lekérdezéseket a FOR SYSTEM_TIME záradékhoz az időbeli táblák adatainak elemzéséhez.

Időponthoz kötött visszaállítási szempontok

Amikor egy adatbázist egy adott időpontra állít vissza, az új adatbázis időbeli megőrzése adatbázisszinten le van tiltva (is_temporal_history_retention_enabled értéke OFF). Ez a viselkedés lehetővé teszi, hogy a megtartási időszaknál régebbi történelmi sorokat vizsgáld, mielőtt a takarítási feladat eltávolítja őket. Az automatikus tisztítás folytatásához a visszaállított adatbázison állítsd vissza a(z) TEMPORAL_HISTORY_RETENTION értékét erre: ON.

Note

Az Azure SQL Database Premium szintjén létrehozott adatbázis akár 35 napig is megőrzi a biztonsági mentést, így bármikor visszaállíthatod az adott ablak bármely pontját. Egy egy hónapos időbeli tábla esetén lehetővé teszi, hogy akár 65 napos történelmi sorokat is megvizsgáld azzal, hogy közvetlenül a visszaállított adatbázison kérdezzük a történettáblázatot.

Táblaparticionálás használata

particionált táblák és indexek a nagy táblák kezelhetőbbé és méretezhetőbbé teszik. A táblázatos partíciós megközelítéssel egyedi adattisztítást vagy offline archiválást valósíthatsz meg egy időfeltétel alapján. A táblaparticionálás teljesítménybeli előnyöket is biztosít, ha időleges táblákat kérdez le az adatelőzmények egy részhalmazán a partícióeliminálás használatával.

Használd a táblázat partícióját egy csúszóablak megvalósításához, hogy eltávolítsuk a történelmi adatok legrégebbi részét a történeti táblából, és a megtartott rész mérete életkor szerint állandó maradjon. A csúszóablak az előzménytáblában a szükséges megőrzési időszaknak megfelelő mennyiségű adatot tart fenn. A történeti tábla támogatja az adatok kicserélését, amíg SYSTEM_VERSIONING van ON, ami azt jelenti, hogy a történetadatok egy részét anélkül tisztíthatod, hogy karbantartási ablakot jelentene vagy blokkolnád a szokásos terheléseket.

Note

A partícióváltáshoz a klaszterelt indexnek a történettáblázatban igazítva kell lennie a partíciós sémával (tartalmaznia ValidTokell ). Az alapértelmezett történettáblázat egy klaszterezett indexet tartalmaz, amely tartalmazza az ValidTo és ValidFrom oszlopokat, ami optimális a partícióhoz, új történeti adatok beillesztéséhez és tipikus időbeli lekérdezésekhez. További információ: Temporális táblák.

A tolóablak két feladatkészletet igényel:

• Particionálási konfigurációs feladat
• Ismétlődő partíciókarbantartási feladatok

Ehhez a példához tegyük fel, hogy hat hónapig szeretnél történelmi adatokat tárolni, és minden hónap adatát külön partícióban szeretnéd tárolni. Tegyük fel, hogy 2023 szeptemberében aktiváltad a rendszerverziót.

A particionálási konfigurációs feladat létrehozza az előzmények táblájának kezdeti particionálási konfigurációját. Ebben a példában ugyanannyi partíciót hozunk létre, mint a csúszóablak mérete, hónapokban, plusz egy extra üres partíciót. Ez a konfiguráció biztosítja, hogy a rendszer helyesen tudja tárolni az új adatokat, amikor először megkezded az ismétlődő partíciókarbantartási feladatot. Azt is garantálja, hogy soha ne oszd fel azokat a partíciókat, amelyek adatokat tartalmaznak, így elkerülhető a drága adatmozgás. Definiáljuk a partíciós függvényt RANGE LEFT-val RANGE RIGHT helyett. További információért lásd: Teljesítményszempontok a táblázatpartícióval később ebben a cikkben.

A következő kép mutatja az eredeti partíciós konfigurációt, amely hat hónapnyi adat megtartásához szolgál.

Az első és az utolsó partíció nyitott rendre az alsó, illetve a felső határon, annak biztosítása érdekében, hogy minden új sorhoz legyen hozzárendelt célpartíció, függetlenül a particionáló oszlopban szereplő értéktől. Idővel új sorok a történelmi táblán magasabb falfalakba kerülnek. Amikor a hatodik partíció megtelik, akkor éred el a beállított adatmegőrzési időszakot. Ekkor kezdjük el először az ismétlődő partíció karbantartási feladatát. Ütemezd úgy, hogy rendszeresen fusson, ebben a példában havonta egyszer.

A következő kép az ismétlődő partíció karbantartási feladatokat mutatja be.

Az ismétlődő karbantartási feladat minden futása a következő lépéseket végzi:

1. SWITCH OUT: Hozz létre egy staging táblát, majd kapcsolj át egy partíciót az előzménytábla és a staging tábla között a(z) ALTER TABLE utasítás SWITCH PARTITION argumentumával.

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

   A partícióváltást követően opcionálisan archiválhatja az átmeneti táblából származó adatokat, majd az átmeneti táblát either eldobhatja, vagy csonkolhatja, hogy előkészítse a következő karbantartási ciklusra.

2. MERGE RANGE: Egyesítse az üres partíciót 1 a(z) 2 partícióval a(z) ALTER PARTITION FUNCTION utasítás MERGE RANGE paraméterrel való használatával. Ha ezt a függvényt használjuk a legalacsonyabb határ eltávolítására, akkor az üres partíciót 1 az előző partícióval 2 egyesíted, hogy új partíciót 1hozzanak létre. A többi partíció is hatékonyan változtatja a sorszámait.

3. SPLIT RANGE: Hozz létre egy új üres partíciót 7 a következő ALTER PARTITION FUNCTION mondattal .SPLIT RANGE Ha ezt a funkciót használod új felső határ hozzáadására, gyakorlatilag külön partíciót hozol létre a következő hónapra.

Partíciók létrehozása az előzménytáblában a Transact-SQL használatával

A következő Transact-SQL-szkript használatával hozza létre a partíciós függvényt, a particionálási sémát, és hozza létre újra a fürtözött indexet úgy, hogy annak particionálása igazodjon a sémához. Ebben a példában egy hat hónapos tolóablakot hoz létre havi partíciókkal, 2023 szeptemberétől kezdve.

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;

Partíciók karbantartása a Transact-SQL használatával a tolóablakos forgatókönyvben

A következő Transact-SQL szkripttel tarthatja karban a partíciókat a tolóablakos forgatókönyvben. Ebben a példában a 2023 szeptemberi partíciót cseréled le , használatával MERGE RANGE, majd hozzáadsz egy új partíciót 2024 márciusára a SPLIT RANGEhasználatával.

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;

Az optimális megoldás azonban az, ha havonta rendszeresen futtatunk egy általános Transact-SQL szkriptet, módosítás nélkül. Az előző szkriptet általánosíthatod úgy, hogy a megadott paraméterek alapján működjön (az egyesítendő alsó határ és a partíció felosztásával létrehozott új határ). Annak elkerülése érdekében, hogy minden hónapban új átmeneti táblát kelljen létrehoznia, hozzon létre előre egyet, és használja fel újra úgy, hogy a CHECK megszorítást ahhoz a partícióhoz igazítja, amelyet kikapcsol. További információ: hogyan automatizálhatja teljes mértékben a csúszóablak-forgatókönyvet.

A táblaparticionálás teljesítményével kapcsolatos szempontok

A SPLIT RANGE és MERGE RANGE műveletet úgy végezze el, hogy elkerülje az adatmozgatást, mert az adatmozgatás jelentős teljesítménybeli többletterhelést okozhat. További információ: Partíciófüggvény módosítása.

Amikor a partíciós függvényt létrehozzuk , RANGE LEFTa megadott értékek a partíciók felső határai. Ha a RANGE RIGHTfunkciót használja, a megadott értékek a partíciók alsó határai. Ha a MERGE RANGE művelettel eltávolít egy határt a partíciófüggvény definíciójából, az alapul szolgáló implementáció a határt tartalmazó partíciót is eltávolítja. Ha az a partíció nem üres, MERGE RANGE az adatokat áthelyezi a kapott partícióra.

Az alábbi diagram a RANGE LEFT és a RANGE RIGHT beállításait ismerteti:

Egy tolóablakos forgatókönyvben mindig eltávolítja a legkisebb partícióhatárt.

• RANGE LEFT eset: A legalacsonyabb partíciós határ a partícióhoz 1tartozik, amely üres (a partíció kikapcsolása után), így MERGE RANGE nem okoz adatmozgást.

• RANGE RIGHT eset: A legalacsonyabb partíciós határ a partícióhoz 2tartozik, amely nem üres, mert a kiváltás csak a partíciót 1üríti ki . Ebben az esetben MERGE RANGE adatmozgást okoz, az adatok egyik partícióról 2 a másikra 1történő áthelyezését okozzák. Az adatmozgatás elkerülése érdekében a csúszóablakos forgatókönyvben a RANGE RIGHT partíciójának 1 értékűnek kell lennie, amely mindig üres. Ez a követelmény azt jelenti, hogy ha használod RANGE RIGHT, akkor egy extra partíciót kell létrehoznod és fenntartanod az esethez képest RANGE LEFT .

Következtetés: A partíciókezelés könnyebb, ha csúsztató partícióban használod RANGE LEFT , és elkerüli az adatmozgást. A partícióhatárok RANGE RIGHT definiálása azonban kissé egyszerűbb, mivel nem kell foglalkoznia a dátum- és időellenőrzéssel.

Használj egyedi tisztító szkriptet.

Ha a tábládhoz nincs megtartási szabályzat, és a táblázat partíciója sem működik, akkor törölheted az adatokat a történettáblázatból egy egyedi tisztító szkripttel. Ez a folyamat csak akkor lehetséges, ha SYSTEM_VERSIONING = OFF. Az adatok inkonzisztenciájának elkerülése érdekében végezze a tisztítást akár karbantartási ablak alatt (amikor az adatokat módosító munkaterhelések nem aktívak), vagy egy tranzakción belül (ami gyakorlatilag blokkolja más terheléseket). Ehhez a művelethez CONTROL engedély szükséges az aktuális és az előzménytáblákhoz.

A tisztítási logika minden időbeli táblánál ugyanaz, így automatizálhatod egy általános tárolt eljárással. Használja az SQL Server Agentet vagy egy másik eszközt az eljárás napi futásának ütemezésére úgy, hogy az végigiteráljon minden olyan temporális táblán, amelynél korlátozni szeretné az adatelőzmények megőrzését.

Az alábbi diagram bemutatja, hogyan szervezheted meg a takarítási logikát egyetlen táblára, hogy csökkentsd a futó munkaterhelésre gyakorolt hatást.

Íme néhány magas szintű irányelv a folyamat megvalósítására:

• Töröld a történelmi adatokat minden időbeli táblából több kis rész iterációjában. Kezdjük a legrégebbi soroktól, és haladjunk a legfrissebbre. Kerüld el az összes sor törlését egyetlen tranzakcióban, ahogy az előző diagram is mutatja. Bár egyetlen chunk méret nem működik minden helyzetben, egy tranzakcióban több mint 10 000 sor törlése jelentős büntetést jelenthet.

• Valósítsd meg minden iterációt egy általános tárolt eljárás meghívásaként, amely eltávolít egy adatrészt a történettáblázatból.

• Számítsa ki, hogy hány sort kell törölnie az egyes időtáblákhoz minden alkalommal, amikor meghívja a folyamatot. Az eredmény és a kívánt iterációk száma alapján határozd meg dinamikus elosztási pontokat minden eljárás meghívásához.

• Tervezzen egy iterációk közötti késést egyetlen tábla számára, hogy csökkentse az időbeli táblához hozzáférő alkalmazások hatását.

A következő tárolt eljárás törli az adatokat egyetlen időbeli tábláról. Felfedezi a történettáblázatot és az időszak végi oszlopát a katalógusnézetekből, majd három utasítást futtat egy tranzakción belül: SET SYSTEM_VERSIONING = OFF, DELETE FROM <history_table>, és SET SYSTEM_VERSIONING = ON. Nézd át alaposan ezt a kódot, és módosítsd, mielőtt alkalmaznád a környezetedben.

Az SQL Server 2016 -ban (13.x) az első két lépésnek külön EXECUTE utasításokban kell futnia, vagy az SQL Server az alábbi példához hasonló hibát okoz:

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;

Kapcsolódó tartalom

• Historikus táblák
• Kezdje el használni a rendszerverziójú temporális táblákat
• Temporális táblarendszer konzisztenciájának ellenőrzése
• partíció temporális táblákkal
• Időbeli táblázatokkal kapcsolatos megfontolások és korlátozások
• Temporális tábla biztonság
• rendszerverziójú időtáblák memóriaoptimalizált táblákkal
• Időbeli tábla metaadatnézetei és függvényei