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 VeritabanıAzure SQL Yönetilen ÖrneğiSQL database in Microsoft Fabric

Sistem sürümüne sahip bir zamansal tablo, her satırın önceki tüm sürümlerini geçmiş tablosunda tutar. Geçmiş tablosu, aşağıdaki koşullarda veritabanınızı normal tablolardan daha fazla artırabilir:

  • Tarihsel verileri uzun süre saklarsınız.
  • Yoğun güncelleme veya silme içeren bir veri değiştirme örüntünüz var.

Büyük ve sürekli büyüyen bir tarih tablosu, hem depolama maliyetleri hem de zamansal sorgulara uyguladığı performans vergisi nedeniyle sorun haline gelebilir. Tarih tablosu için veri saklama politikası geliştirmek, her zaman tablosunun yaşam döngüsünü planlamak ve yönetmenin önemli bir parçasıdır.

Bir veri saklama politikası planlayın

Zamansal tablo veri tutma yönetimini yönetmek için, önce her zaman tablosu için gerekli saklama süresini belirleyin. Çoğu durumda, tutma politikanız, 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, çevrimiçi sorgulama için tarihsel verilerin ne kadar süre erişilebilir olması gerektiği konusunda kesin gereksinimlere sahiptir.

Veri saklama sürenizi belirledikten sonra, geçmiş verileri yönetmek için bir plan geliştirin. Geçmiş verilerinizi nasıl ve nerede depoladığınıza ve bekletme gereksinimlerinizden daha eski geçmiş verileri nasıl sileceğinize karar verin.

Bu makaledeki her yaklaşım, mevcut tablodaki dönemin sonuna karşılık gelen sütuna ( ValidTo aşağıdaki örneklerdeki sütun) etki eder. 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, durum ValidTo < DATEADD (DAY, -30, SYSUTCDATETIME()) 30 günden eski olan tarihsel verilerle eşleşiyor.

Bu satırlar üzerinde işlem yapmak için aşağıdaki yaklaşımlardan birini seçin:

Approach Nasıl çalışır? Ne zaman kullanılır?
Zamansal geçmiş tutma politikası Her tablo için bir saklama süresi belirlersiniz ve arka plan görevi eski satırları otomatik olarak siler. Eski kayıt geçmişini doğrudan silebildiğinizde en basit seçenek.
Tablo bölümleme Bir kaydırmalı pencere, tarih tablosunun en eski bölümünü çıkarır, böylece arşivleyebilir veya atabilirsiniz. Tarihsel verileri kaldırmadan önce arşivlemek istediğinizde veya zamansal sorgular için bölüm eleme istediğinizde.
Özel temizleme senaryosu Zamanlanmış bir betik, sistem sürümünü devre dışı bırakır, yaşlı satırları küçük parçalar halinde siler ve ardından sistem sürümünü yeniden etkinleştirir. Tablonuz için bir saklama politikası mevcut olmadığında ve bölümlendirmenin uygulanabilir olmadığında.

Bu makaledeki bölümleme ve özel temizleme örnekleri, Sistem sürümünde zamansal tablo oluştur makalesinden örnekleri kullanır.

Zamansal geçmiş tutma politikası kullanın

Şunlara uygulanır: SQL Server 2017 (14.x) ve sonraki sürümler, Azure SQL Veritabanı, Azure SQL Yönetilen Örneği ve Microsoft Fabric'te SQL database.

Bireysel tablo düzeyinde zamansal geçmiş saklama ayarları yapabilirsiniz, böylece esnek yaşlanma politikaları oluşturabilirsiniz. Zamansal tutmayı etkinleştirmek için, tablo oluştururken veya şema değişikliği sırasında HISTORY_RETENTION_PERIOD değerini ayarlayın.

Saklama politikasını tanımladıktan sonra, Database Engine, dönem sonu değeri saklama süresinden daha eski olan geçmiş satırları bulup şeffaf bir şekilde kaldıran planlanmış bir arka plan görevi çalıştırır.

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, ancak bunu ifadeyi ALTER DATABASE kullanarak değiştirebilirsiniz. Veritabanı Altyapısı da, Belirli bir zaman noktasına geri yüklemeyle ilgili dikkat edilmesi gerekenler bölümünde açıklandığı gibi, belirli bir zaman noktasına geri yükleme (PITR) işleminden sonra onu otomatik olarak OFF olarak ayarlar. Veritabanınızda zamana bağlı geçmiş saklama temizlemesini etkinleştirmek için aşağıdaki deyimi çalıştırın. Değiştirmek istediğiniz veritabanıyla değiştirin <myDB> :

ALTER DATABASE [<myDB>]
    SET TEMPORAL_HISTORY_RETENTION ON;

Important

is_temporal_history_retention_enabled OFF olsa bile zamansal tablolar için saklama süresini yapılandırabilirsiniz, ancak bu durumda Database Engine eskimiş satırlar için otomatik temizlemeyi tetiklemez.

Tablo oluşturma sırasında parametre için HISTORY_RETENTION_PERIOD bir değer belirleyerek saklama politikasını yapılandırabilirsiniz:

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

Bu politika yürürlüğe girdiğinde, dbo.WebsiteUserInfoHistory içindeki satırlar aşağıdaki koşulu sağladıklarında temizlenmeye uygun hale gelir:

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

Tutma süresini DAYS, WEEKS, MONTHS veya YEARS içinde belirtebilirsiniz. HISTORY_RETENTION_PERIOD öğesini belirtmezseniz, saklama süresi varsayılan olarak INFINITE olur. INFINITE anahtar sözcüğünü açıkça da kullanabilirsiniz.

Bazı durumlarda, tablo oluşturulduktan sonra saklama yapılandırması 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));

Important

SYSTEM_VERSIONING değerini OFF olarak ayarlamak, saklama süresi değerini korumaz. ON açıkça belirtilmeden SYSTEM_VERSIONING değerini HISTORY_RETENTION_PERIOD olarak ayarlamak, INFINITE tutulmasına neden olur.

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

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

Temizleme işlemi, geçmiş tablosunun dizin düzenine bağlıdır. Sınırlı saklama politikasını yalnızca kümelenmiş bir sıra deposu (B-ağacı) veya kümelenmiş columnstore indeksi olan geçmiş tablolarında yapılandırabilirsiniz. Bir arka plan görevi, sınırlı bir saklama süresine sahip tüm zamansal tablolar için yaşlanmış veri temizliğini gerçekleştirir.

Note

Belgelerde genellikle dizinlere başvuruda B ağacı terimi kullanılır. Rowstore dizinlerinde Veritabanı Altyapısı bir B+ ağacı uygular. Bu, sütun deposu dizinleri veya bellek için iyileştirilmiş tablolardaki dizinler için geçerli değildir. Daha fazla bilgi için SQL Server ve Azure SQL dizin mimarisi ve tasarım kılavuzuna bakın.

B-tree satır odaklı depolama indeksi

Satır deposu kümelenmiş dizin, SYSTEM_TIME döneminin sonuna karşılık gelen sütunla başlamalıdır. Böyle bir indeks yoksa, sonlu bir saklama süresi yapılandıramazsınız:

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.

Varsayılan geçmiş tablosunda zaten uyumlu bir kümelenmiş indeks var. Bu indeksi sonlu bir saklama süresine sahip bir geçmiş tablosuna düşürmeye çalışırsanız, işlem aşağıdaki hatayla başarısız olur:

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.

Sıralı depo kümelenmiş indeks için temizleme mantığı, eski satırları daha küçük parçalar halinde (10.000'e kadar) siler, veritabanı logu ve I/O alt sistemi üzerindeki baskıyı en aza indirir. Temizleme mantığı gerekli B-ağacı dizinini kullansa da, saklama süresini aşmış satırların silinme sırasını garanti edemez. Uygulamalarınızda temizleme sırasına bağımlılık yapmayın.

Kümelenmiş sütun deposu dizini

Kümelenmiş columnstore için temizleme görevi, tüm satır gruplarını aynı anda kaldırır. Her satır grubu genellikle bir milyon satır içerir. Bu yöntem, özellikle iş yükünüz yüksek hızda geçmiş veri ürettiğinde daha verimlidir.

Kümelenmiş sütun depolama koruma ekran görüntüsü.

Veri sıkıştırma ve saklama temizliği, kümelenmiş columnstore indeksini, iş yükünüzün hızla büyük miktarda geçmiş veri ürettiği durumlar için iyi bir tercih haline getirir. Bu desen, değişim takibi ve denetim, trend analizi veya Nesnelerin İnterneti (IoT) veri alımı için zamansal tablolar kullanan yoğun işlem işleme iş yükleri için tipiktir.

Kümelenmiş sütun deposu dizinindeki temizleme işlemi, tarihsel satırlar artan sırada (dönem sonu sütununa göre sıralı olarak) geldiğinde en verimli şekilde çalışır. Bu koşul, yalnızca SYSTEM_VERSIONING mekanizma tarih tablosunu doldurduğunda her zaman geçerlidir. Geçmiş tablosundaki satırlar dönem sonu sütununa göre sıralanmamışsa (bu durum mevcut geçmiş verilerini geçirdiğinizde oluşabilir), optimum performans elde etmek için uygun şekilde sıralanmış bir B-tree satır deposu dizininin üzerinde kümelenmiş sütun deposu dizinini yeniden oluşturun.

Kümelenmiş columnstore indeksini sonlu bir saklama süresi olan bir tarih tablosunda yeniden inşa etmekten kaçının, çünkü yeniden yapılandırma, sistem sürüm işleminin doğal olarak uyguladığı satır-grup sıralamasını değiştirebilir. Geçmiş tablosundaki kümelenmiş sütun deposu dizinini yeniden oluşturmanız gerekiyorsa, verilerin düzenli olarak temizlenmesi için gerekli olan satır grubu sıralamasını korumak amacıyla, bunu uygun bir B-ağacı dizini üzerinde yeniden oluşturun. Veri sırasının garanti edilmediği kümelenmiş bir sütun deposu dizinine sahip mevcut bir geçmiş tablosuyla bir zamansal tablo oluşturuyorsanız aynı yaklaşımı izleyin:

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

Kümelenmiş bir columnstore indeksi olan bir geçmiş tablosu için sonlu bir saklama süresi yapılandırdığınızda, o tabloda ek kümelenmiş olmayan B-ağaç indeksleri oluşturamazsınız:

CREATE NONCLUSTERED INDEX IX_WebHistNCI
    ON WebsiteUserInfoHistory(UserName);

Önceki ifade aşağıdaki hatayla başarısız olur:

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.

Veri saklama politikasıyla tabloları sorgulama

Zamansal tablodaki tüm sorgular, öngörülemez ve tutarsız sonuçlardan kaçınmak için sonlu saklama politikasına uyan tarihsel satırları otomatik olarak filtreler. Temizleme görevi, eski sıraları herhangi bir zamanda ve rastgele sırayla siler.

Aşağıdaki ekran görüntüsü, temel bir sorgu için sorgu planını gösteriyor. Bu örnek, MONTH tablosunda bir-WebsiteUserInfo saklama süresi olduğunu varsayar:

SELECT *
FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

Sorgulama planı, geçmiş tablosunda Kümelenmiş İndeks Tarama operatöründe (aşağıdaki görselde vurgulanmıştır) dönem sonu sütununda (ValidTo) ekstra bir filtre içermektedir.

Geçmiş tablosunun ValidTo sütununda ekstra bir tutma filtresi bulunan sorgu planının ekran görüntüsü.

Doğrudan geçmiş tablosunu sorgulatırsanız, belirtilen saklama süresinden daha eski satırlar görebilirsiniz, ancak tekrarlanabilir sorgu sonuçlarının garantisi yoktur. Aşağıdaki ekran görüntüsü, ek filtre olmadan geçmiş tablosunda bir sorgu için sorgu planını göstermektedir:

Geçmiş tablosunu doğrudan tutma filtresi olmadan sorgulama yaparken sorgulama planının ekran görüntüsü.

Tutma süresinin ötesindeki geçmiş tablosunu okuyan iş mantığına güvenmeyin, çünkü tutarsız veya beklenmedik sonuçlar alabilirsiniz. Zamansal tablolardaki verileri analiz etmek için bu maddeyle FOR SYSTEM_TIME zamansal sorgular kullanın.

Belirli bir noktaya geri yükleme konusunda dikkat edilmesi gerekenler

Bir veritabanını belirli bir zamana geri getirdiğinizde, yeni veritabanı veritabanı seviyesinde zamansal saklama devre dışı bırakılır (is_temporal_history_retention_enabled olarak ayarlanmıştır OFF). Bu davranış, temizleme görevi bunları kaldırmadan önce saklama süresinden daha eski geçmiş satırları incelemenizi sağlar. Geri yüklenen veritabanında otomatik temizlemeyi sürdürmek için TEMPORAL_HISTORY_RETENTION öğesini yeniden ON olarak ayarlayın.

Note

Azure SQL Veritabanı'de Premium seviyesinde oluşturulan bir veritabanı, yedeklemeleri 35 güne kadar saklar, böylece o pencerenin herhangi bir yerine geri getirebilirsiniz. Bir aylık tutma süresine sahip zamansal bir tablo için, 65 güne kadar eski geçmiş satırları doğrudan geri getirilen veritabanında sorgulayarak inceleme imkanı verir.

Tablo bölümlemesini kullanın

bölümlenmiş tablolar ve dizinler büyük tabloları daha yönetilebilir ve ölçeklenebilir hale getirebilirsiniz. Tablo bölümleme yöntemini kullanarak, zaman koşuluna bağlı ö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ölmesini kullanarak tarihsel verilerin en eski kısmını tarih tablosundan çıkarmak için kaydıran bir pencere uygulayın ve saklanan kısmın boyutunu yaşa göre sabit tutun. Kayan pencere, geçmiş tablosundaki verileri gerekli saklama süresi boyunca korur. Geçmiş tablosu, ONSYSTEM_VERSIONING durumundayken verilerin dışarı alınmasını destekler; bu da bakım penceresi oluşturmadan veya normal iş yüklerinizi engellemeden geçmiş verilerinin bir bölümünü temizleyebileceğiniz anlamına gelir.

Note

Bölüm anahtarlaması yapmak için, geçmiş tablosundaki kümelenmiş indeksiniz bölümleme şemasıyla hizalanmalıdır (içinde ValidToolması gerekir). Varsayılan geçmiş tablosu, ve ValidFrom sütunlarını içeren kümelenmiş bir indeks ValidTo içerir; bu, bölümleme, yeni geçmiş verileri eklemek ve tipik zamansal sorgulama için en iyisidir. Daha fazla bilgi için bkz. Zamana bağlı tablolar.

Bir kaydırmalı pencere iki görev seti gerektirir:

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

Bu örnek için, geçmiş verileri altı ay boyunca saklamak istediğinizi ve her ayın veriyi ayrı bir bölümde tutmak istediğinizi varsayın. Ayrıca, Eylül 2023'te sistem sürümü etkinleştirdiğinizi varsayabilirsiniz.

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, kaydırma penceresinin boyutuyla aynı sayıda bölme oluşturursunuz, aylar cinsinden, artı bir ekstra boş bölüm. Bu yapılandırma, sistemin tekrar eden bölüm bakım görevine başladığınızda yeni verileri doğru şekilde depolayabilmesini sağlar. Ayrıca, veri içeren bölümleri asla bölmenizi garanti eder, bu da pahalı veri hareketlerini önler. Bölümleme işlevini RANGE RIGHT yerine RANGE LEFT ile tanımlayın. Daha fazla bilgi için, bu makalenin ilerleyen bölümlerinde Tablo bölümlemeyle ilgili performans değerlendirmeleri bölümünü inceleyebilirsiniz.

Aşağıdaki resim, altı aylık veriyi korumak için ilk bölümleme yapılandırmasını göstermektedir.

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

İlk ve son bölümler, sırasıyla alt ve üst sınırlarda açıktır ; böylece her yeni satırın bir hedef bölümü olur, bölünme sütunundaki değer ne olursa olsun. Zamanla, tarih tablasında yeni sıralar daha yüksek bölümlere yerleşir. Altıncı bölüm dolduğunda, hedeflenen saklama süresine ulaşırsınız. Bu noktada, tekrar eden bölüm bakım görevini ilk kez başlatın. Bu örnekte, ayda bir kez çalışacak şekilde zamanlayın.

Aşağıdaki fotoğraf, tekrarlayan bölüm bakım görevlerini göstermektedir.

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

Yinelenen bakım görevinin her çalıştırılması aşağıdaki adımları gerçekleştirir:

  1. SWITCH OUT: Bir aşamalama tablosu oluşturun ve ardından argümanı içeren ifadeyi ALTER TABLESWITCH PARTITION kullanarak geçmiş tablosu ile aşamalama tablosu arasında bir bölme değiştirin.

    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ırlama tablosundaki verileri arşivleyebilir ve ardından bir sonraki bakım döngüsüne hazırlanmak için hazırlama tablosunu silebilir veya içeriğini boşaltabilirsiniz.

  2. MERGE RANGE: Boş bölümü, MERGE RANGE ile ALTER PARTITION FUNCTION deyimini kullanarak 1 bölüm 2 ile birleştirin. Bu fonksiyonu en düşük sınırı kaldırmak için kullandığınızda, boş bölümü 1 eski 2 bölümle 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: ALTER PARTITION FUNCTION ile 7 deyimini kullanarak yeni boş bir bölüm SPLIT RANGE oluşturun. Bu fonksiyonu kullanarak yeni bir üst sınır eklediğinizde, önümüzdeki ay için ayrı bir bölüm oluşturmuş olursunuz.

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

Aşağıdaki Transact-SQL betiklerini kullanarak bölümleme fonksiyonunu, yani bölüm şemasını oluşturun ve kümelenmiş indeksi şemaya göre bölmeye hizalamak için yeniden oluşturun. 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, Eylül 2023 bölümünü MERGE RANGE kullanarak değiştirir ve ardından Mart 2024 için SPLIT RANGE kullanarak 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 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;

Ancak en iyi çözüm, her ay düzenli olarak bir genel Transact-SQL betiklerini değişiklik yapmadan çalıştırmaktır. Önceki betikleri, sağlanan parametrelere (birleştirilmesi gereken alt sınır ve bölünme ile oluşturulan yeni sınır) göre genelleştirebilirsiniz. Her ay bir hazırlama tablosu oluşturmaktan kaçınmak için, önceden bir tane oluşturun ve denetim kısıtlamasını, dışarı aktardığınız bölümle eşleşecek şekilde değiştirerek yeniden kullanın. Daha fazla bilgi için kayan pencere senaryosunun tamamen nasıl otomatikleştirileceği konusuna bakın.

Tablo bölümleme ile ilgili performans konuları

SPLIT RANGE ve MERGE RANGE işlemlerini, veri taşınmasını önleyecek şekilde gerçekleştirin; çünkü veri taşınması önemli bir performans ek yüküne neden olabilir. Daha fazla bilgi için bkz. Bir bölüm işlevini değiştirme.

Bölümleme fonksiyonunu , RANGE LEFTolarak oluşturduğunuzda, 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. Eğer o bölüm boş değilse, MERGE RANGE veri ortaya çıkan bölüme taşınır.

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

SOL ARALIK ve SAĞ ARALIK seçeneklerini gösteren diyagram.

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 değişiminden sonra) olan bölüme 1aittir, bu MERGE RANGE yüzden veri hareketine neden olmaz.

  • RANGE RIGHT durum: En düşük bölüm sınırı, 2 bölümüne aittir; bu bölüm boş değildir çünkü dışarı aktarma yalnızca 1 bölümünü boşaltır. Bu durumda, MERGE RANGE veri taşınmasına neden olur ve veri bir bölümden 2 bölüme 1taşınır. Bu veri hareketini önlemek için, RANGE RIGHT kayan pencere senaryosunda her zaman boş olan bir bölüm 1olması gerekir. Bu gereklilik, RANGE RIGHT kullanıyorsanız RANGE LEFT durumuna kıyasla bir ek bölüm oluşturmanız ve sürdürmeniz gerektiği anlamına gelir.

Sonuç: Kaydırmalı bölümde kullandığınızda bölüm yönetimi daha kolaydır RANGE LEFT ve veri hareketini ö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 bir temizleme betiği kullanın

Tablonuzda bir saklama politikası mevcut değilse ve tablo bölümlendirmesi uygulanabilir değilse, özel bir temizleme betiği kullanarak verileri geçmiş tablosundan silebilirsiniz. Bu süreç ancak SYSTEM_VERSIONING = OFF olduğunda mümkündür. Veri tutarsızlığını önlemek için, temizlik işlemi ya bakım penceresi sırasında (veri değiştiren iş yükleri aktif olmadığı zaman) ya da bir işlem içinde (diğer iş yüklerini etkili şekilde engelleyerek) gerçekleştirin. Bu işlem geçerli ve geçmiş tablolarda CONTROL izni gerektirir.

Temizleme mantığı her zaman tablosu için aynıdır, yani bunu genel bir depolanmış prosedürle otomatikleştirebilirsiniz. SQL Server Agent’ı veya farklı bir aracı kullanarak bu yordamı, veri geçmişini sınırlamak istediğiniz her zamansal tablo üzerinde yineleme yapacak şekilde her gün çalışacak biçimde zamanlayın.

Aşağıdaki diyagram, tek bir tablo için temizleme mantığını nasıl organize edeceğinizi ve çalışan iş yüklerine etkisini azaltacağınızı göstermektedir.

Tek bir tablo için temizlik mantığını nasıl organize edeceğinizi gösteren diyagram, böylece çalışma yüklerine etkisini azaltabilirsiniz.

İşte sürecin uygulanması için bazı üst düzey yönergeler:

  • Tüm zamansal tablolardaki tarihsel verileri birkaç yinelemede küçük parçalar hâlinde silin. En eski sıralardan başlayıp en yenisine geç. Önceki diyagramın gösterdiği gibi, tek bir işlemde tüm satırları silmekten kaçının. Her senaryoda tek bir chunk boyutu işe yaramasa da, tek bir işlemde 10.000'den fazla satırın silinmesi önemli bir ceza getirebilir.

  • Her yinelemeyi, geçmiş tablosundan bir verinin bir kısmını kaldıran genel bir depolanmış prosedürün çağrısı olarak uygulayın.

  • İşlemi her çağırdığınızda tek bir zamana bağlı tablo için silmeniz gereken satır sayısını hesaplayın. Sonuç ve istediğiniz yineleme sayısına dayanarak, her işlem çağrısı için dinamik bölünme noktalarını belirleyin.

  • Tek bir tablo için yinelemeler arasında bir gecikme planlayın, böylece zamansal tabloya erişen uygulamalar üzerindeki etkiyi azaltın.

Aşağıdaki saklanan prosedür, tek bir zamansal tablo için verileri siler. Katalog görünümlerinden geçmiş tablosunu ve dönem sonu sütununu keşfeder ve ardından bir işlem içinde üç ifadeyi çalıştırır: SET SYSTEM_VERSIONING = OFF, DELETE FROM <history_table>, ve SET SYSTEM_VERSIONING = ON. Bu kodu dikkatlice inceleyin ve ortamınızda uygulamadan önce ayarlayın.

SQL Server 2016'da (13.x), ilk iki adım ayrı EXECUTE 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 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;

İlgili içerik

  • Last updated on