Mengelola retensi data historis dalam tabel temporal versi sistem

  • Artikel

Berlaku untuk: SQL Server 2016 (13.x) dan Azure SQL DatabaseAzure SQL Managed Instance yang lebih baru

Dengan tabel temporal versi sistem, tabel riwayat mungkin meningkatkan ukuran database lebih dari tabel biasa, terutama dalam kondisi berikut:

  • Anda menyimpan data historis untuk jangka waktu yang lama
  • Anda memiliki pola modifikasi data berat atau pembaruan

Tabel riwayat yang besar dan terus berkembang dapat menjadi masalah baik karena biaya penyimpanan murni, dan memberlakukan pajak performa pada kueri temporal. Oleh karena itu, mengembangkan kebijakan retensi data untuk mengelola data dalam tabel riwayat adalah aspek penting dalam merencanakan dan mengelola siklus hidup setiap tabel temporal.

Manajemen retensi data untuk tabel riwayat

Mengelola retensi data tabel temporal dimulai dengan menentukan periode retensi yang diperlukan untuk setiap tabel temporal. Kebijakan retensi Anda, dalam kebanyakan kasus, harus dianggap sebagai bagian dari logika bisnis aplikasi menggunakan tabel temporal. Misalnya, aplikasi dalam skenario audit data dan perjalanan waktu memiliki persyaratan tegas dalam hal berapa lama data historis harus tersedia untuk kueri online.

Setelah Anda menentukan periode retensi data, langkah Anda selanjutnya adalah mengembangkan rencana untuk mengelola data historis. Anda harus memutuskan bagaimana dan di mana Anda menyimpan data historis, dan cara menghapus data historis yang lebih lama dari persyaratan retensi Anda. Pendekatan berikut untuk mengelola data historis dalam tabel riwayat temporal tersedia:

Dengan masing-masing pendekatan ini, logika untuk memigrasikan atau membersihkan data riwayat didasarkan pada kolom yang sesuai dengan akhir periode dalam tabel saat ini. Nilai akhir periode untuk setiap baris menentukan momen ketika versi baris ditutup, yaitu, ketika mendarat di tabel riwayat. Misalnya, kondisi ValidTo < DATEADD (DAYS, -30, SYSUTCDATETIME ()) menentukan bahwa data historis yang lebih lama dari satu bulan perlu dihapus atau dipindahkan dari tabel riwayat.

Catatan

Contoh dalam artikel ini menggunakan tabel temporal Buat versi sistem.

Menggunakan pendekatan pemartisian tabel

Tabel dan indeks yang dipartisi dapat membuat tabel besar lebih mudah dikelola dan dapat diskalakan. Dengan menggunakan pendekatan pemartisian tabel, Anda dapat menggunakan partisi tabel riwayat untuk menerapkan pembersihan data kustom atau pengarsipan offline berdasarkan kondisi waktu. Pemartisian tabel juga memberi Anda manfaat performa saat mengkueri tabel temporal pada subset riwayat data dengan menggunakan eliminasi partisi.

Dengan pemartisian tabel, Anda dapat menerapkan jendela geser untuk memindahkan bagian terlama dari data historis dari tabel riwayat dan menjaga ukuran konstanta bagian yang dipertahankan dalam hal usia - mempertahankan data dalam tabel riwayat sama dengan periode retensi yang diperlukan. Operasi pengalihan data dari tabel riwayat didukung sementara SYSTEM_VERSIONING itu adalah ON, yang berarti Anda dapat membersihkan sebagian data riwayat tanpa memperkenalkan jendela pemeliharaan atau memblokir beban kerja reguler Anda.

Catatan

Untuk melakukan pengalihan partisi, indeks terkluster Anda pada tabel riwayat harus diselaraskan dengan skema partisi (harus berisi ValidTo). Tabel riwayat default yang dibuat oleh sistem berisi indeks berkluster yang menyertakan ValidTo kolom dan ValidFrom , yang optimal untuk pemartisian, menyisipkan data riwayat baru, dan kueri temporal umum. Untuk informasi selengkapnya, lihat Tabel temporal.

Jendela geser memiliki dua set tugas yang perlu Anda lakukan:

  • Tugas konfigurasi partisi
  • Tugas pemeliharaan partisi berulang

Untuk ilustrasi, mari kita asumsikan bahwa Anda ingin menyimpan data historis selama enam bulan dan bahwa Anda ingin menyimpan setiap bulan data dalam partisi terpisah. Selain itu, mari kita asumsikan bahwa Anda mengaktifkan penerapan versi sistem pada bulan September 2023.

Tugas konfigurasi partisi membuat konfigurasi partisi awal untuk tabel riwayat. Untuk contoh ini, Anda akan membuat partisi angka yang sama dengan ukuran jendela geser, dalam bulan, ditambah partisi kosong ekstra yang disiapkan (dijelaskan kemudian dalam artikel ini). Konfigurasi ini memastikan bahwa sistem dapat menyimpan data baru dengan benar ketika Anda memulai tugas pemeliharaan partisi berulang untuk pertama kalinya, dan menjamin bahwa Anda tidak pernah membagi partisi dengan data untuk menghindari pergerakan data yang mahal. Anda harus melakukan tugas ini menggunakan Transact-SQL menggunakan contoh skrip nanti di artikel ini.

Gambar berikut menunjukkan konfigurasi partisi awal untuk menyimpan data enam bulan.

Diagram showing initial partitioning configuration to keep six months of data.

Catatan

Lihat pertimbangan performa dengan partisi tabel nanti di artikel ini untuk implikasi performa penggunaan RANGE LEFT versus RANGE RIGHT saat mengonfigurasi partisi.

Partisi pertama dan terakhir terbuka pada batas bawah dan atas, masing-masing, untuk memastikan bahwa setiap baris baru memiliki partisi tujuan terlepas dari nilai dalam kolom partisi. Seiring berjalannya waktu, baris baru dalam tabel riwayat mendarat di partisi yang lebih tinggi. Ketika partisi keenam terisi, Anda mencapai periode retensi yang ditargetkan. Ini adalah saat untuk memulai tugas pemeliharaan partisi berulang untuk pertama kalinya (perlu dijadwalkan untuk berjalan secara berkala, sekali per bulan dalam contoh ini).

Gambar berikut mengilustrasikan tugas pemeliharaan partisi berulang (lihat langkah-langkah terperinci nanti di bagian ini).

Diagram showing the recurring partition maintenance tasks.

Langkah-langkah terperinci untuk tugas pemeliharaan partisi berulang adalah:

  1. BERALIH KELUAR: Buat tabel penahapan lalu alihkan partisi antara tabel riwayat dan tabel penahapan menggunakan pernyataan ALTER TABLE (Transact-SQL) dengan SWITCH PARTITION argumen (lihat Contoh C. Beralih partisi antar tabel).

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

    Setelah pengalihan partisi, Anda dapat secara opsional mengarsipkan data dari tabel penahapan lalu menghilangkan atau memotong tabel penahapan, agar siap untuk waktu berikutnya Anda perlu melakukan tugas pemeliharaan partisi berulang ini.

  2. MERGE RANGE: Gabungkan partisi 1 kosong dengan partisi 2 menggunakan ALTER PARTITION FUNCTION (Transact-SQL) dengan MERGE RANGE (Lihat contoh B). Dengan menghapus batas terendah menggunakan fungsi ini, Anda secara efektif menggabungkan partisi 1 kosong dengan partisi 2 sebelumnya untuk membentuk partisi 1baru . Partisi lain juga secara efektif mengubah ordinal mereka.

  3. SPLIT RANGE: Buat partisi kosong baru 7 menggunakan ALTER PARTITION FUNCTION (Transact-SQL) dengan SPLIT RANGE (Lihat contoh A). Dengan menambahkan batas atas baru menggunakan fungsi ini, Anda secara efektif membuat partisi terpisah untuk bulan mendatang.

Menggunakan Transact-SQL untuk membuat partisi pada tabel riwayat

Gunakan skrip Transact-SQL berikut untuk membuat fungsi partisi, skema partisi, dan membuat ulang indeks berkluster agar selaras dengan skema partisi, partisi. Untuk contoh ini, Anda membuat jendela geser enam bulan dengan partisi bulanan, mulai September 2023.

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;

Gunakan Transact-SQL untuk mempertahankan partisi dalam skenario jendela geser

Gunakan skrip Transact-SQL berikut untuk mempertahankan partisi dalam skenario jendela geser. Untuk contoh ini, Anda mengalihkan partisi untuk September 2023 menggunakan MERGE RANGE, lalu menambahkan partisi baru untuk Maret 2024 menggunakan 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

Anda dapat sedikit memodifikasi skrip sebelumnya dan menggunakannya dalam proses pemeliharaan bulanan reguler:

  1. Pada langkah (1) buat tabel penahapan baru untuk bulan yang ingin Anda hapus (Oktober akan menjadi berikutnya dalam contoh ini).
  2. Pada langkah (3) buat dan periksa batasan yang cocok dengan bulan data yang ingin Anda hapus: ValidTo <= N'2023-10-31T23:59:59.999' untuk partisi Oktober.
  3. Pada langkah (4) SWITCH partisi 1 ke tabel penahapan yang baru dibuat.
  4. Pada langkah (6) ubah fungsi partisi dengan menggabungkan batas yang lebih rendah: MERGE RANGE(N'2023-10-31T23:59:59.999' setelah Anda memindahkan data untuk Oktober.
  5. Pada langkah (7) bagi fungsi partisi yang membuat batas atas baru: SPLIT RANGE (N'2024-04-30T23:59:59.999' setelah Anda memindahkan data untuk Oktober.

Namun, solusi optimalnya adalah menjalankan skrip Transact-SQL generik secara teratur yang mampu melakukan tindakan yang sesuai setiap bulan tanpa modifikasi skrip. Dimungkinkan untuk menggeneralisasi skrip sebelumnya untuk bertindak berdasarkan parameter yang disediakan (batas bawah yang perlu digabungkan dan batas baru yang akan dibuat oleh dengan pemisahan partisi). Untuk menghindari pembuatan tabel penahapan setiap bulan, Anda dapat membuatnya sebelumnya dan menggunakan kembali dengan mengubah batasan pemeriksaan agar sesuai dengan partisi yang akan dialihkan. Lihat halaman berikut untuk mendapatkan ide tentang bagaimana jendela geser dapat sepenuhnya otomatis menggunakan skrip Transact-SQL.

Pertimbangan performa dengan pemartisian tabel

Penting untuk melakukan MERGE operasi dan SPLIT RANGE untuk menghindari pergerakan data karena pergerakan data dapat menimbulkan overhead performa yang signifikan. Untuk informasi selengkapnya, lihat Mengubah fungsi partisi. Anda melakukannya dengan menggunakan RANGE LEFT daripada RANGE RIGHT ketika Anda membuat fungsi partisi.

Mari kita pertama-tama jelaskan RANGE LEFT arti dari opsi dan RANGE RIGHT :

Diagram showing the RANGE LEFT and RANGE RIGHT options.

Saat Anda menentukan fungsi partisi sebagai RANGE LEFT, nilai yang ditentukan adalah batas atas partisi. Saat Anda menggunakan RANGE RIGHT, nilai yang ditentukan adalah batas bawah partisi. Saat Anda menggunakan MERGE RANGE operasi untuk menghapus batas dari definisi fungsi partisi, implementasi yang mendasarinya juga menghapus partisi yang berisi batas. Jika partisi tersebut tidak kosong, data dipindahkan ke partisi yang merupakan hasil MERGE RANGE operasi.

Dalam skenario jendela geser, Anda selalu menghapus batas partisi terendah.

  • RANGE LEFT case: Batas partisi terendah milik partisi 1, yang kosong (setelah partisi beralih), jadi MERGE RANGE tidak menimbulkan pergerakan data apa pun.
  • RANGE RIGHT case: Batas partisi terendah milik partisi 2, yang tidak kosong, karena partisi 1 dikosongkan dengan beralih. Dalam hal ini, MERGE RANGE menimbulkan pergerakan data (data dari partisi 2 dipindahkan ke partisi 1). Untuk menghindari hal ini, RANGE RIGHT dalam skenario jendela geser perlu memiliki partisi 1, yang selalu kosong. Ini berarti bahwa jika Anda menggunakan RANGE RIGHT, Anda harus membuat dan memelihara satu partisi tambahan dibandingkan RANGE LEFT dengan kasus.

Kesimpulan: Menggunakan RANGE LEFT dalam partisi geser lebih mudah untuk manajemen partisi, dan menghindari pergerakan data. Namun, menentukan batas partisi dengan RANGE RIGHT sedikit lebih sederhana karena Anda tidak perlu berurusan dengan masalah pemeriksaan waktu tanggalwaktu.

Menggunakan pendekatan skrip pembersihan kustom

Dalam kasus ketika pemartisian tabel tidak layak, pendekatan lain adalah menghapus data dari tabel riwayat menggunakan skrip pembersihan kustom. Menghapus data dari tabel riwayat hanya dimungkinkan saat SYSTEM_VERSIONING = OFF. Untuk menghindari inkonsistensi data, lakukan pembersihan baik selama jendela pemeliharaan (ketika beban kerja yang memodifikasi data tidak aktif) atau dalam transaksi (secara efektif memblokir beban kerja lain). Operasi ini memerlukan CONTROL izin pada tabel saat ini dan riwayat.

Untuk memblokir aplikasi reguler dan kueri pengguna secara minimal, hapus data dalam gugus yang lebih kecil dengan penundaan saat melakukan skrip pembersihan di dalam transaksi. Meskipun tidak ada ukuran optimal untuk setiap potongan data yang akan dihapus untuk semua skenario, menghapus lebih dari 10.000 baris dalam satu transaksi mungkin memberlakukan efek yang signifikan.

Logika pembersihan sama untuk setiap tabel temporal, sehingga dapat diotomatisasi melalui prosedur tersimpan generik yang Anda jadwalkan untuk dijalankan secara berkala, untuk setiap tabel temporal yang ingin Anda batasi riwayat datanya.

Diagram berikut menggambarkan bagaimana logika pembersihan Anda harus diatur untuk satu tabel untuk mengurangi dampak pada beban kerja yang sedang berjalan.

Diagram showing how your cleanup logic should be organized for a single table to reduce impact on the running workloads.

Berikut adalah beberapa pedoman tingkat tinggi untuk menerapkan proses. Jadwalkan logika pembersihan untuk dijalankan setiap hari dan iterasi di semua tabel temporal yang memerlukan pembersihan data. Gunakan SQL Server Agent atau alat yang berbeda untuk menjadwalkan proses ini:

  • Hapus data historis di setiap tabel temporal, mulai dari baris terlama hingga terbaru dalam beberapa iterasi dalam gugus kecil, dan hindari menghapus semua baris dalam satu transaksi, seperti yang ditunjukkan pada diagram sebelumnya.
  • Terapkan setiap iterasi sebagai pemanggilan prosedur tersimpan generik yang menghapus sebagian data dari tabel riwayat (lihat contoh kode berikut untuk prosedur ini).
  • Hitung berapa banyak baris yang perlu Anda hapus untuk tabel temporal individual setiap kali Anda memanggil proses. Berdasarkan itu dan jumlah iterasi yang ingin Anda miliki, tentukan titik pemisahan dinamis untuk setiap pemanggilan prosedur.
  • Rencanakan untuk memiliki periode penundaan antara iterasi untuk satu tabel, untuk mengurangi efek pada aplikasi yang mengakses tabel temporal.

Prosedur tersimpan yang menghapus data untuk satu tabel temporal mungkin terlihat seperti dalam cuplikan kode berikut (tinjau kode ini dengan hati-hati dan sesuaikan sebelum diterapkan di lingkungan Anda):

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;

/*Generate 3 statements that run inside a transaction:
  (1) SET SYSTEM_VERSIONING = OFF,
  (2) DELETE FROM history_table,
  (3) SET SYSTEM_VERSIONING = ON
  On SQL Server 2016, it is critical that (1) and (2) run in separate EXEC statements, or SQL Server generates the following error:
  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>'.
*/

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;

Menggunakan pendekatan kebijakan penyimpanan riwayat temporal

Berlaku untuk: SQL Server 2017 (14.x) dan versi yang lebih baru, dan Azure SQL Database.

Retensi riwayat temporal dapat dikonfigurasi pada tingkat tabel individu, yang memungkinkan pengguna untuk membuat kebijakan rentang waktu yang fleksibel. Menerapkan retensi temporal cukup sederhana: hanya memerlukan satu parameter untuk diset selama pembuatan tabel atau perubahan skema.

Setelah Anda menentukan kebijakan penyimpanan, Mesin Database mulai memeriksa secara teratur jika ada baris historis yang memenuhi syarat untuk pembersihan data otomatis. Identifikasi baris yang cocok dan penghapusannya dari tabel riwayat terjadi secara transparan, di proses di latar belakang yang dijadwalkan dan dijalankan oleh sistem. Kondisi usia untuk baris tabel riwayat dicentang berdasarkan kolom yang mewakili akhir SYSTEM_TIME periode. Jika periode retensi, misalnya, diatur ke enam bulan, baris tabel yang memenuhi syarat untuk pembersihan memenuhi kondisi berikut:

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

Dalam contoh sebelumnya, ValidTo kolom sesuai dengan akhir SYSTEM_TIME periode.

Cara mengonfigurasi kebijakan penyimpanan

Sebelum Anda mengonfigurasi kebijakan penyimpanan untuk tabel temporal, periksa terlebih dahulu apakah retensi historis temporal diaktifkan di tingkat database:

SELECT is_temporal_history_retention_enabled, name
FROM sys.databases;

Bendera is_temporal_history_retention_enabled database diatur ke ON secara default, tetapi pengguna dapat mengubahnya dengan ALTER DATABASE pernyataan . Nilai ini secara otomatis diatur ke OFF setelah operasi pemulihan titik waktu (PITR). Untuk mengaktifkan pembersihan retensi riwayat temporal untuk database Anda, jalankan pernyataan berikut:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION ON;

Kebijakan penyimpanan dikonfigurasi selama pembuatan tabel dengan menentukan nilai untuk HISTORY_RETENTION_PERIOD parameter:

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

Anda dapat menentukan periode retensi dengan menggunakan unit waktu yang berbeda: DAYS, , WEEKSMONTHS, dan YEARS. Jika HISTORY_RETENTION_PERIOD dihilangkan, INFINITE retensi diasumsikan. Anda juga dapat menggunakan INFINITE kata kunci secara eksplisit.

Dalam beberapa skenario, Anda mungkin ingin mengonfigurasi retensi setelah pembuatan tabel, atau mengubah nilai yang dikonfigurasi sebelumnya. Dalam hal ini, gunakan ALTER TABLE pernyataan:

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

Untuk mengulas status kebijakan penyimpanan saat ini, gunakan kueri berikut yang bergabung dengan bendera pengaktifan retensi temporal di tingkat database dengan periode retensi untuk tabel individu:

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;

Cara SQL Database menghapus baris berumur

Proses pembersihan tergantung pada tata letak indeks tabel riwayat. Hanya tabel riwayat dengan indeks berkluster (pohon B+ atau penyimpan kolom) yang dapat memiliki kebijakan penyimpanan terbatas yang dikonfigurasi. Proses di latar belakang dibuat untuk melakukan pembersihan data lama untuk semua tabel temporal dengan periode retensi terbatas. Logika pembersihan untuk indeks berkluster rowstore (pohon B+) menghapus baris berumur dalam gugus yang lebih kecil (hingga 10K) meminimalkan tekanan pada log database dan subsistem I/O. Meskipun logika pembersihan menggunakan indeks pohon B+ yang diperlukan, urutan penghapusan untuk baris yang lebih lama dari periode retensi tidak dapat dijamin dengan kuat. Oleh karena itu, jangan mengambil dependensi apa pun pada urutan pembersihan dalam aplikasi Anda.

Tugas pembersihan untuk penyimpan kolom berkluster menghapus seluruh grup baris sekaligus (biasanya berisi masing-masing 1 juta baris), yang sangat efisien, terutama ketika data riwayat dihasilkan dengan kecepatan tinggi.

Screenshot of clustered columnstore retention.

Kompresi data yang sangat baik dan penghapusan retensi yang efisien membuat indeks penyimpan kolom berkluster sebuah pilihan sempurna untuk skenario ketika beban kerja Anda dengan cepat menghasilkan data riwayat dalam jumlah tinggi. Pola tersebut biasanya untuk beban kerja pemrosesan transaksional intensif yang menggunakan tabel temporal untuk pelacakan perubahan dan audit, analisis tren, atau konsumsi data IoT.

Untuk informasi selengkapnya, lihat Mengelola data historis dalam Tabel Temporal dengan kebijakan penyimpanan.

Konten terkait