Sistem sürümüne dayalı zamana bağlı tablolarda geçmiş verilerin elde tutulmasını yönetme

Şunlar için geçerlidir: SQL Server 2016 (13.x) ve sonraki sürümler Azure SQL DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

Sistem sürümüne sahip zamana bağlı tablolarda geçmiş tablosu, özellikle aşağıdaki koşullar altında veritabanınızın boyutunu normal tablolardan daha fazla artırabilir:

• Geçmiş verileri uzun süre saklarsınız
• Bir güncelleştirmeniz var veya ağır veri değiştirme düzenini silebilirsiniz

Büyük ve sürekli büyüyen bir geçmiş tablosu, hem saf depolama maliyetleri hem de zamana bağlı sorgulamaya performans vergisi uygulama nedeniyle sorun haline gelebilir. Geçmiş tablosundaki verileri yönetmek için bir veri saklama ilkesi geliştirmek, her zamansal tablonun yaşam döngüsünü planlamanın ve yönetmenin önemli bir yönüdür.

Geçmiş tablosu için veri saklama yönetimi

Zamansal tablo veri saklamayı yönetme işlemi, her geçici tablo için gerekli saklama süresini belirlemeyle başlar. Bekletme ilkeniz çoğu durumda, zamansal tabloları kullanan uygulamanın iş mantığının bir parçası olmalıdır. Örneğin, veri denetimi ve zaman yolculuğu senaryolarındaki uygulamalar, geçmiş verilerin çevrimiçi sorgulama için ne kadar süre kullanılabilir olması gerektiğiyle ilgili kesin gereksinimlere sahiptir.

Veri saklama sürenizi belirledikten sonra geçmiş verileri yönetmek için bir plan geliştirmeniz gerekir. Geçmiş verilerinizi nasıl ve nerede depoladığınıza ve bekletme gereksinimlerinizden daha eski geçmiş verileri nasıl sileceğinize karar verin. Zamana bağlı geçmiş tablosundaki geçmiş verilerini yönetmek için aşağıdaki yaklaşımlar kullanılabilir:

• Tablo bölümleme
• Özel temizleme senaryosu
• Saklama politikası

Bu yaklaşımların her birinde, geçmiş verilerini geçirme veya temizleme mantığı, geçerli tablodaki dönem sonuna karşılık gelen sütunu temel alır. Her satır için dönem sonu değeri, satırın sürümünün kapatıldığı zaman (geçmiş tablosuna indiğinde) belirler. Örneğin, ValidTo < DATEADD (DAYS, -30, SYSUTCDATETIME ()) koşulu, bir aydan eski geçmiş verilerin kaldırılması veya geçmiş tablosundan taşınması gerektiğini belirtir.

Bu makaledeki örnekler, Sistem sürümlemeli zamana bağlı tablo oluşturma makalesinde oluşturulan örnekleri kullanır.

Tablo bölümleme yaklaşımını kullanma

bölümlenmiş tablolar ve dizinler büyük tabloları daha yönetilebilir ve ölçeklenebilir hale getirebilirsiniz. Tablo bölümleme yaklaşımıyla, bir zaman koşuluna göre özel veri temizleme veya çevrimdışı arşivleme uygulayabilirsiniz. Tablo bölümleme ayrıca bölüm eleme kullanarak veri geçmişinin bir alt kümesindeki zamana bağlı tabloları sorgularken size performans avantajları sağlar.

Tablo bölümleme ile, geçmiş tablosundaki geçmiş verilerin en eski bölümünü dışarı taşımak ve tutulan bölümün boyutunu yaş bakımından sabit tutmak için kayan bir pencere uygulayabilirsiniz. Kayan pencere, geçmiş tablosundaki verileri gerekli saklama süresine eşit tutar. SYSTEM_VERSIONING ONise geçmiş tablosundan veri değiştirme işlemi desteklenir. Bu, bakım penceresi eklemeden veya normal iş yüklerinizi engellemeden geçmiş verilerinin bir bölümünü temizleyebileceğiniz anlamına gelir.

Note

Bölüm değiştirme gerçekleştirmek için geçmiş tablosundaki kümelenmiş dizininizin bölümleme şemasıyla uyumlu olması gerekir (ValidToiçermelidir). Sistem tarafından oluşturulan varsayılan geçmiş tablosu bölümleme, yeni geçmiş verileri ekleme ve tipik zamansal sorgulama için en uygun olan ValidTo ve ValidFrom sütunlarını içeren kümelenmiş bir dizin içerir. Daha fazla bilgi için bkz. Zamana bağlı tablolar.

Kayan pencerede gerçekleştirmeniz gereken iki görev kümesi vardır:

• Bölüm yapılandırma işlemi
• Yinelenen bölümleme bakım görevleri

Çizimde, geçmiş verileri altı ay boyunca tutmak istediğinizi ve her ayki verileri ayrı bir bölümde tutmak istediğinizi varsayalım. Ayrıca, Eylül 2023'te sistem sürümü oluşturma işlemini etkinleştirmiş olduğunuzu varsayalım.

Bölümleme yapılandırma görevi, geçmiş tablosu için ilk bölümleme yapılandırmasını oluşturur. Bu örnekte, kayan pencerenin boyutuyla aylar içinde aynı sayıda bölüm ve önceden hazırlanmış fazladan boş bir bölüm oluşturursunuz (bu makalenin ilerleyen bölümlerinde açıklanmıştır). Bu yapılandırma, yinelenen bölüm bakım görevini ilk kez başlattığınızda sistemin yeni verileri doğru bir şekilde depolayabilmesini sağlar ve pahalı veri hareketlerinden kaçınmak için bölümleri hiçbir zaman verilerle bölmeyebilirsiniz. Bu makalenin devamında yer alan örnek betiği kullanarak bu görevi Transact-SQL ile gerçekleştirmeniz gerekir.

Aşağıdaki resimde altı aylık verileri tutmak için ilk bölümleme yapılandırması gösterilmektedir.

altı aylık verileri tutmak için ilk bölümleme yapılandırmasını gösteren

Note

Bölümleme yapılandırırken RANGE LEFT ve RANGE RIGHT kullanmanın performans üzerindeki etkileri için, bu makalenin devamında tablo bölümlemeyle ilgili performans konuları konusuna bakın.

İlk ve son bölümler, bölümleme sütunundaki değerden bağımsız olarak her yeni satırın bir hedef bölüme sahip olmasını sağlamak için sırasıyla alt ve üst sınırlarda açıktır. Zaman geçtikçe, geçmiş tablosundaki yeni satırlar daha yüksek bölmelere yerleştirilir. Altıncı disk bölümü dolduğunda, hedeflenen saklama süresine ulaşırsınız. Bu, yinelenen bölüm bakım görevini ilk kez başlatmanın zamanıdır. Bu örnekte ayda bir kez düzenli aralıklarla çalışacak şekilde zamanlanması gerekir.

Aşağıdaki resimde tekrarlanan bölüm bakım işlerini gösterilmektedir (bu bölümün devamında yer alan ayrıntılı adımlara bakın).

Yinelenen bölüm bakım görevlerini gösteren

Yinelenen bölüm bakım görevleri için ayrıntılı adımlar şunlardır:

1. SWITCH OUT: Hazırlık tablosu oluşturun ve ardından bağımsız değişkeniyle SWITCH PARTITION ALTER TABLE ifadesini kullanarak geçmiş tablosu ile hazırlık tablosu arasında bir bölümü değiştirin (bkz. örnek C: Tablolar arasında bölümleri değiştirme).

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

   Bölüm değiştirme işleminden sonra, isteğe bağlı olarak hazırlık tablosundaki verileri arşivleyebilir ve ardından bu yinelenen bölüm bakım görevini tekrar gerçekleştirmeniz gerektiğinde hazırlık tablosunu kaldırabilir veya kesebilirsiniz.

2. MERGE RANGE: 1 kullanarak boş 2 bölümünü bölümü ile MERGE RANGE ile birleştirin (Bkz. B örneğini). Bu işlevi kullanarak en düşük sınırı kaldırarak boş bölüm 1 eski bölüm 2 etkili bir şekilde birleştirerek yeni bir bölüm 1oluşturursunuz. Diğer bölümler de dizinlerini etkili bir şekilde değiştirir.

3. SPLIT RANGE: 7 ile ALTER PARTITION FUNCTION kullanarak yeni bir boş bölüm SPLIT RANGE oluşturun (Örnek A'ya bakın). Bu işlevi kullanarak yeni bir üst sınır ekleyerek, yaklaşan ay için etkili bir şekilde ayrı bir bölüm oluşturacaksınız.

Geçmiş tablosunda bölümler oluşturmak için Transact-SQL kullanma

Parça işlevini, parça şemasını oluşturmak ve kümelenmiş dizini parça şemasıyla hizalanacak şekilde yeniden oluşturmak için aşağıdaki Transact-SQL betiğini kullanın. Bu örnekte, Eylül 2023'ün başından itibaren aylık bölümler içeren altı aylık bir kayan pencere oluşturursunuz.

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;

Kayan pencere senaryosunda bölümleri korumak için Transact-SQL kullanma

Kayan pencere senaryosunda bölümleri korumak için aşağıdaki Transact-SQL betiğini kullanın. Bu örnekte, MERGE RANGEkullanarak Eylül 2023'e ait bölümü değiştirir ve ardından SPLIT RANGEkullanarak Mart 2024 için yeni bir bölüm eklersiniz.

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

Önceki betiği biraz değiştirebilir ve normal aylık bakım sürecinde kullanabilirsiniz:

1. (1) adımında, kaldırmak istediğiniz ay için yeni bir hazırlama tablosu oluşturun (Bu örnekte bir sonraki adım Ekim olacaktır).
2. (3) adımında, kaldırmak istediğiniz veri ayıyla eşleşen kısıtlamayı oluşturun ve denetleyin: Ekim bölümü için ValidTo <= N'2023-10-31T23:59:59.999'.
3. Adım (4)'te, SWITCH'ı yeni oluşturulan hazırlama tablosuna bölümlendirin 1.
4. (6) adımında, Ekim verilerini düzenledikten sonra alt sınırı birleştirerek bölme fonksiyonunu değiştirin: MERGE RANGE(N'2023-10-31T23:59:59.999'.
5. (7) adımında bölüm işlevini bölerek yeni bir üst sınır oluşturacaksınız: SPLIT RANGE (N'2024-04-30T23:59:59.999' Ekim için verileri dışarı taşıdığınızda.

Ancak en uygun çözüm, her ay değişiklik yapmadan uygun eylemi çalıştıran genel bir Transact-SQL betiğini düzenli olarak çalıştırmaktır. Sağladığınız parametreler (birleştirilmesi gereken alt sınır ve bölüm bölme ile oluşturulan yeni sınır) üzerinde işlem yapacak şekilde önceki betiği genelleştirebilirsiniz. Her ay geçici bir tablo oluşturmaktan kaçınmak için, değiştirdiğiniz bölümle eşleşecek şekilde denetim kısıtlamasını değiştirerek önceden bir tablo oluşturabilir ve tekrar kullanabilirsiniz. Daha fazla bilgi için bkz. kayan pencerenin tamamen nasıl otomatikleştirilebileceği.

Tablo bölümleme ile ilgili performans konuları

Veri taşımanın önemli performans yüküne neden olabileceğinden, veri taşımayı önlemek için MERGE ve SPLIT RANGE işlemlerini gerçekleştirmeniz gerekir. Daha fazla bilgi için bkz. Bir bölüm işlevini değiştirme. RANGE LEFToluştururken RANGE RIGHT yerine kullanarak yaparsınız.

Aşağıdaki diyagramda RANGE LEFT ve RANGE RIGHT seçenekleri açıklanmaktadır:

Bölüm işlevini RANGE LEFTolarak tanımladığınızda, belirtilen değerler bölümlerin üst sınırlarıdır. RANGE RIGHTkullandığınızda, belirtilen değerler bölümlerin alt sınırlarıdır. Bölüm işlevi tanımından bir sınırı kaldırmak için MERGE RANGE işlemini kullandığınızda, temel alınan uygulama sınırı içeren bölümü de kaldırır. Bu bölüm boş değilse, veriler MERGE RANGE işlemin sonucu olan bölüme taşınır.

Kayan pencere senaryosunda, her zaman en düşük bölüm sınırını kaldırırsınız.

• RANGE LEFT durum: En düşük bölüm sınırı boş olan bölüm 1aittir (bölüm geçişi bittikten sonra), bu nedenle MERGE RANGE herhangi bir veri taşımasına neden olmaz.

• RANGE RIGHT durum: Bölüm 2 geçiş yaparak boşaltıldığından, en düşük bölüm sınırı boş olmayan bölüm 1aittir. Bu durumda, MERGE RANGE veri taşımaya neden olur (bölüm 2 verileri bölüm 1bölümüne taşınır). Bunu önlemek için, kayan pencere senaryosundaki RANGE RIGHT her zaman boş olan 1bölüme sahip olması gerekir. Bu, RANGE RIGHTkullanıyorsanız, RANGE LEFT durumuna kıyasla bir ek bölüm oluşturup korumanız gerektiği anlamına gelir.

Sonuç: Kayan bölümde RANGE LEFT kullandığınızda bölüm yönetimi daha kolaydır ve veri taşımayı önler. Ancak, RANGE RIGHT ile bölüm sınırları tanımlamak biraz daha kolaydır çünkü tarih ve saat denetimi sorunlarıyla ilgilenmeniz gerekmez.

Özel temizleme betiği yaklaşımını kullan

Tablo bölümlemenin uygun olmadığı durumlarda, bir diğer yaklaşım da özel temizleme betiği kullanarak geçmiş tablosundan verileri silmektir. Geçmiş tablosundan veri silmek, ancak SYSTEM_VERSIONING = OFFolduğunda mümkündür. Veri tutarsızlığını önlemek için, bir bakım penceresi sırasında (verileri değiştiren iş yükleri etkin olmadığında) veya bir işlem içinde (diğer iş yüklerini etkili bir şekilde engelleyen) temizleme gerçekleştirin. Bu işlem geçerli ve geçmiş tablolarda CONTROL izni gerektirir.

Normal uygulamaları ve kullanıcı sorgularını en az düzeyde engellemek için, temizleme betiğini bir işlem içinde gerçekleştirirken gecikmeli olarak daha küçük öbeklerdeki verileri silin. Tüm senaryolar için silinecek her veri öbeği için en uygun boyut olmasa da, tek bir işlemde 10.000'den fazla satırı silmek önemli bir cezaya neden olabilir.

Temizleme mantığı her zamansal tablo için aynıdır, bu nedenle veri geçmişini sınırlamak istediğiniz her zamansal tablo için düzenli aralıklarla çalışacak şekilde zamanladığınız genel bir saklı yordam aracılığıyla otomatikleştirilebilir.

Aşağıdaki diyagramda, çalışan iş yükleri üzerindeki etkiyi azaltmak için temizleme mantığınızın tek bir tablo için nasıl düzenlenmesi gerektiği gösterilmektedir.

İşlemi uygulamaya yönelik bazı üst düzey yönergeler aşağıdadır. Temizleme mantığını her gün çalışacak şekilde zamanlayın ve veri temizlemeye ihtiyaç duyan tüm zamansal tablolar üzerinde yineleme yapın. Bu işlemi zamanlamak için SQL Server Agent'ı veya farklı bir aracı kullanın:

• Her zamansal tablodaki geçmiş verileri, küçük parçalar halinde birkaç yinelemedeki en eski satırlardan en son satırlara kadar silin ve önceki diyagramda gösterildiği gibi tek bir işlemdeki tüm satırları silmekten kaçının.

• Her yinelemeyi, geçmiş tablosundan verilerin bir bölümünü kaldıran genel bir saklı yordamın çağrısı olarak uygulayın (bu yordam için aşağıdaki kod örneğine bakın).

• İşlemi her çağırdığınızda tek bir zamana bağlı tablo için silmeniz gereken satır sayısını hesaplayın. Elde etmek istediğiniz sonucu ve yineleme sayısını temel alarak, her yordam çağrısı için dinamik bölme noktalarını belirleyin.

• Zamana bağlı tabloya erişen uygulamalar üzerindeki etkisini azaltmak için tek bir tablo için yinelemeler arasında bir gecikme süresi olmasını planlayın.

Tek bir zamana bağlı tablonun verilerini silecek saklı yordam aşağıdaki kod parçacığı gibi görünebilir. Bu kodu dikkatle gözden geçirin ve ortamınızda uygulamadan önce ayarlayın.

Bu betik, bir işlem içinde işlem gören üç ifade oluşturur.

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

SQL Server 2016'da (13.x), ilk iki adım ayrı EXEC deyimlerinde çalıştırılmalıdır veya SQL Server aşağıdaki örneğe benzer bir hata oluşturur:

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;

Zamana bağlı geçmiş saklama ilkesi yaklaşımını kullanma

Şunlar için geçerlidir: SQL Server 2017 (14.x) ve sonraki sürümleri ve Azure SQL Veritabanı.

Zamansal geçmiş saklama, kullanıcıların esnek eskime ilkeleri oluşturmasına olanak tanıyan tek tek tablo düzeyinde yapılandırılabilir. Zamansal saklama, tablo oluşturma veya şema değişikliği sırasında yalnızca bir parametre ayarlamanızı gerektirir.

Bekletme ilkesini tanımladıktan sonra Veritabanı Altyapısı, otomatik veri temizleme için uygun geçmiş satırlar olup olmadığını düzenli olarak denetlemeye başlar. Eşleşen satırların tanımlanması ve bunların geçmiş tablosundan kaldırılması, sistem tarafından zamanlanan ve çalıştırılan bir arka plan görevinde saydam bir şekilde gerçekleşir. Geçmiş tablosu satırlarının yaş koşulu, SYSTEM_TIME döneminin sonunu temsil eden sütuna (bu örneklerde ValidTo sütunu) göre denetlenir. Bekletme süresi altı aya ayarlanırsa, örneğin, temizleme için uygun tablo satırları aşağıdaki koşulu karşılar:

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

Yukarıdaki örnekte, ValidTo sütunu SYSTEM_TIME döneminin sonuna karşılık gelir.

Saklama ilkesini nasıl yapılandırabilirsiniz

Geçici tablo için bekletme ilkesini yapılandırmadan önce, zamansal geçmiş saklamanın veritabanı düzeyinde etkinleştirilip etkinleştirilmediğini denetleyin:

SELECT is_temporal_history_retention_enabled, name
FROM sys.databases;

Veritabanı bayrağı is_temporal_history_retention_enabled varsayılan olarak ON olarak ayarlanır, ancak bunu ALTER DATABASE deyimiyle değiştirebilirsiniz. Bu değer, anlık geri yükleme (PITR) işleminden sonra otomatik olarak OFF olarak ayarlanır. Veritabanınızda zamana bağlı geçmiş saklama temizlemesini etkinleştirmek için aşağıdaki deyimi çalıştırın. <myDB>'ı değiştirmek istediğiniz veritabanıyla değiştirin.

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION ON;

Bekletme ilkesi, tablo oluşturma sırasında HISTORY_RETENTION_PERIOD parametresi için değer belirtilerek yapılandırılır:

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

Bekletme süresini farklı zaman birimleri kullanarak belirtebilirsiniz: DAYS, WEEKS, MONTHSve YEARS. HISTORY_RETENTION_PERIOD atlanırsa, INFINITE'in saklandığı varsayılır. INFINITE anahtar sözcüğünü açıkça da kullanabilirsiniz.

Bazı senaryolarda, tablo oluşturulduktan sonra bekletmeyi yapılandırmak veya önceden yapılandırılmış değeri değiştirmek isteyebilirsiniz. Bu durumda, ALTER TABLE deyimini kullanın:

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

Bekletme ilkesinin geçerli durumunu gözden geçirmek için aşağıdaki örneği kullanın. Bu sorgu, veritabanı düzeyinde zamansal bekletme etkinleştirme bayrağını tek tek tablolar için bekletme süreleri ile birleştirir:

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;

Veritabanı Altyapısı eski satırları nasıl siler?

Temizleme işlemi, geçmiş tablosunun dizin düzenine bağlıdır. Yalnızca kümelenmiş dizine sahip geçmiş tablolarında (B+ ağacı veya columnstore) sınırlı bir bekletme ilkesi yapılandırılabilir. Sonlu saklama süresi olan tüm zamana bağlı tablolar için eski veri temizleme gerçekleştirmek için bir arka plan görevi oluşturulur. Satır deposu (B+ ağacı) kümelenmiş dizini için temizleme mantığı, yaşlanmış satırları daha küçük parçalar halinde (10.000'e kadar) siler, böylece veritabanı günlüğü ve Girdi/Çıktı alt sistemi üzerindeki baskıyı en aza indirir. Temizleme mantığı gerekli B+ ağaç dizinini kullansa da, bekletme süresinden daha eski satırların silme sırası garanti altına alınamaz. Uygulamalarınızda temizleme sırasına bağımlılık yapmayın.

Kümelenmiş sütun deposu için yapılan temizleme görevi, tüm satır gruplarını aynı anda kaldırır (genellikle her biri 1 milyon satır içerir) ve özellikle tarihsel veriler yüksek hızda üretildiğinde daha verimli olur.

Veri sıkıştırma ve veri saklama temizliği, iş yükünüz hızla yüksek miktarda geçmiş veri oluşturduğunda kümelenmiş sütun deposu dizininin senaryolar için mükemmel bir seçim olmasını sağlar. Bu düzen, değişiklik izleme ve denetim, eğilim analizi veya IoT veri alımı için zamansal tablolar kullanan yoğun işlemsel işleme iş yükleri için tipiktir.

Daha fazla bilgi için bkz. Zamana Bağlı Tablolarda bekletme ilkesiyle geçmiş verileri yönetme.

İlgili içerik

• Zamansal tablolar
• Sistem sürümüne sahip zamana bağlı tabloları kullanmaya başlama
• Zamansal tablo sistemi tutarlılık denetimleri
• Zamansal tablolarla bölümlendirme
• Zaman tablosuyla ilgili önemli noktalar ve sınırlamalar
• Zaman tablosu güvenliği
• Sistem sürümüne sahip zamana bağlı tablolar ile bellek için iyileştirilmiş tablolar
• Zamansal tablo meta veri görünümleri ve işlevleri