Bekletme ilkesiyle Zamana bağlı tablolardaki geçmiş verileri yönetme

Şunlar için geçerlidir: Azure SQL Veritabanı Azure SQL Yönetilen Örneği

Zamana bağlı tablolar, özellikle geçmiş verileri daha uzun süre saklamanız durumunda veritabanı boyutunu normal tablolardan daha fazla artırabilir. Bu nedenle, geçmiş veriler için bekletme ilkesi, her zamansal tablonun yaşam döngüsünü planlamanın ve yönetmenin önemli bir yönüdür. Azure SQL Veritabanındaki ve Azure SQL Yönetilen Örneği zamana bağlı tablolar, bu görevi gerçekleştirmenize yardımcı olan kullanımı kolay saklama mekanizmasıyla birlikte gelir.

Zamana bağlı 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. Geçici saklama uygulamak basittir: Tablo oluşturma veya şema değişikliği sırasında yalnızca bir parametrenin ayarlanmasını gerektirir.

Bekletme ilkesini tanımladıktan sonra Veritabanı ve Azure SQL Yönetilen Örneği Azure SQL, 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 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 göre denetlenir. Örneğin saklama süresi altı aya ayarlandıysa, temizleme için uygun tablo satırları aşağıdaki koşulu karşılar:

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

Yukarıdaki örnekte , ValidTo sütununun SYSTEM_TIME dönemin sonuna karşılık geldiğini varsaydık.

Bekletme ilkesini yapılandırma

Geçici bir tablo için bekletme ilkesini yapılandırmadan önce, ö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 kullanıcılar ALTER DATABASE deyimiyle değiştirebilir. Ayrıca belirli bir noktaya geri yükleme işleminden sonra otomatik olarak KAPALI olarak ayarlanır. Veritabanınızda zamana bağlı geçmiş saklama temizlemeyi etkinleştirmek için aşağıdaki deyimi yürütebilirsiniz:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

Önemli

is_temporal_history_retention_enabled KAPALI olsa bile zamana bağlı tablolar için bekletmeyi yapılandırabilirsiniz, ancak bu durumda eski satırlar için otomatik temizleme tetiklenmez.

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

veritabanı ve Azure SQL Yönetilen Örneği Azure SQL farklı zaman birimleri kullanarak bekletme süresini belirtmenize olanak tanır: GÜN, HAFTA, AY VE YIL. HISTORY_RETENTION_PERIOD atlanırsa, SONSUZ saklama 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));

Önemli

SYSTEM_VERSIONING KAPALI olarak ayarlanması saklama süresi değerini korumaz . SYSTEM_VERSIONING HISTORY_RETENTION_PERIOD belirtilmadan AÇIK olarak ayarlanması SONSUZ saklama süresine neden olur.

Bekletme ilkesinin geçerli durumunu gözden geçirmek için, tek tek tabloların bekletme süreleri ile veritabanı düzeyinde geçici bekletme etkinleştirme bayrağını birleştiren aşağıdaki sorguyu kullanın:

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

Eski satırlar nasıl silinir?

Temizleme işlemi, geçmiş tablosunun dizin düzenine bağlıdır. Yalnızca kümelenmiş dizine (B ağacı veya columnstore) sahip geçmiş tablolarında sınırlı saklama ilkesinin yapılandırıldığını fark etmek önemlidir. Sınırlı 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. Rowstore (B-tree) kümelenmiş dizini için temizleme mantığı, daha küçük öbeklerdeki eski satırları siler (10 bin'e kadar), veritabanı günlüğü ve GÇ alt sistemi üzerindeki baskıyı en aza indirir. Temizleme mantığı gerekli B ağacı dizinini kullansa da, saklama süresinden eski satırlar için silme sırası kesin olarak garanti edilemez. Bu nedenle , uygulamalarınızda temizleme sırasına herhangi bir bağımlılık almayın.

Kümelenmiş columnstore için temizleme görevi, tüm satır gruplarını bir kerede kaldırır (genellikle her biri 1 milyon satır içerir), bu da özellikle geçmiş verileri yüksek hızda oluşturulduğunda çok verimlidir.

Kümelenmiş columnstore saklama

Mükemmel veri sıkıştırma ve verimli saklama temizleme, kümelenmiş columnstore dizinini iş yükünüz hızlı bir şekilde yüksek miktarda geçmiş veri oluşturduğu senaryolar için mükemmel bir seçim haline getirir. Bu düzen, değişiklik izleme ve denetim, eğilim analizi veya IoT veri alımı için zamana bağlı tabloları kullanan yoğun işlemsel işleme iş yükleri için tipiktir.

Dizinle ilgili dikkat edilmesi gerekenler

Rowstore kümelenmiş dizini olan tablolar için temizleme görevi, dizinin SYSTEM_TIME dönemin sonuna karşılık gelen sütunla başlamasını gerektirir. Böyle bir dizin yoksa, sınırlı bir saklama süresi yapılandıramazsınız:

Msg 13765, Düzey 16, Durum 1

'temporalstagetestdb.dbo.WebsiteUserInfoHistory' geçmiş tablosu gerekli kümelenmiş dizini içermediğinden, sistem sürümü oluşturulmuş 'temporalstagetestdb.dbo.WebsiteUserInfoInfo' geçici tablosunda sonlu saklama süresi ayarlanamadı. Geçmiş tablosundaki SYSTEM_TIME döneminin sonuyla eşleşen sütundan başlayarak kümelenmiş bir columnstore veya B ağacı dizini oluşturmayı göz önünde bulundurun.

Azure SQL Veritabanı ve Azure SQL Yönetilen Örneği tarafından oluşturulan varsayılan geçmiş tablosunda bekletme ilkesiyle uyumlu kümelenmiş dizin bulunduğunu fark etmek önemlidir. Kalıcı saklama süresi olan bir tabloda bu dizini kaldırmaya çalışırsanız, işlem aşağıdaki hatayla başarısız olur:

Msg 13766, Düzey 16, Durum 1

Eski verilerin otomatik olarak temizlenmesi için kullanıldığından kümelenmiş 'WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory' dizini bırakılamıyor. Bu dizini bırakmanız gerekiyorsa ilgili sistem sürümüne sahip zamana bağlı tabloda HISTORY_RETENTION_PERIOD SONSUZ olarak ayarlamayı göz önünde bulundurun.

Geçmiş satırları artan düzende (dönem sonu sütununa göre sıralanmış) eklenirse kümelenmiş columnstore dizininde temizleme en iyi şekilde çalışır; bu durum, geçmiş tablosunun yalnızca SYSTEM_VERSIONIOING mekanizması tarafından doldurulması durumunda her zaman geçerlidir. Geçmiş tablosundaki satırlar dönem sonu sütununa göre sıralanmamışsa (mevcut geçmiş verilerini geçirdiyseniz bu durum geçerli olabilir), en iyi performansı elde etmek için B ağacı satır deposu dizininin üzerinde düzgün şekilde sıralanmış kümelenmiş columnstore dizinini yeniden oluşturmanız gerekir.

Geçmiş tablosunda kümelenmiş columnstore dizinini sonlu saklama süresiyle yeniden oluşturmaktan kaçının, çünkü sistem sürümü oluşturma işlemi tarafından doğal olarak uygulanan satır gruplarında sıralamayı değiştirebilir. Geçmiş tablosunda kümelenmiş columnstore dizinini yeniden oluşturmanız gerekiyorsa, bunu yapmak için uyumlu B ağacı dizininin üzerinde yeniden oluşturun ve düzenli veri temizleme için gereken satır gruplarında sıralamayı sağlayın. Garantili veri sırası olmadan kümelenmiş sütun dizini olan mevcut geçmiş tablosuyla zamana bağlı tablo oluşturursanız da aynı yaklaşım benimsenmelidir:

/*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 clustered columnstore index*/
CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory ON WebsiteUserInfoHistory
WITH (DROP_EXISTING = ON);

Kümelenmiş columnstore diziniyle geçmiş tablosu için sonlu saklama süresi yapılandırıldığında, bu tabloda ek kümelenmemiş B ağacı dizinleri oluşturamazsınız:

CREATE NONCLUSTERED INDEX IX_WebHistNCI ON WebsiteUserInfoHistory ([UserName])

Yukarıdaki deyimi yürütme girişimi aşağıdaki hatayla başarısız oluyor:

Msg 13772, Düzey 16, Durum 1

'WebsiteUserInfoHistory' geçici geçmiş tablosunda sınırlı saklama süresi ve kümelenmiş columnstore dizini tanımlandığından kümelenmemiş dizin oluşturulamıyor.

Bekletme ilkesiyle tabloları sorgulama

Eski satırlar temizleme görevi tarafından herhangi bir zamanda ve rastgele sırada silinebileceğinden, zamansal tablodaki tüm sorgular, öngörülemeyen ve tutarsız sonuçlardan kaçınmak için, sonlu bekletme ilkesiyle eşleşen geçmiş satırları otomatik olarak filtreler.

Aşağıdaki resimde basit bir sorgunun sorgu planı gösterilmektedir:

SELECT * FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

Sorgu planı, geçmiş tablosundaki Kümelenmiş Dizin Taraması işlecinde (vurgulanmış) dönem sonu sütununa (ValidTo) uygulanan ek filtre içerir. Bu örnekte WebsiteUserInfo tablosunda bir AY saklama süresinin ayarlandığı varsayılır.

Bekletme sorgusu filtresi

Ancak, sorgu geçmişi tablosunu doğrudan kullanıyorsanız, belirtilen saklama süresinden daha eski olan ancak yinelenebilir sorgu sonuçları garantisi olmayan satırlar görebilirsiniz. Aşağıdaki resimde, ek filtreler uygulanmadan geçmiş tablosundaki sorgu için sorgu yürütme planı gösterilmektedir:

Bekletme filtresi olmadan geçmişi sorgulama

Tutarsız veya beklenmeyen sonuçlar alabileceğinizden, iş mantığınızı saklama süresinin ötesinde okuma geçmişi tablosuna güvenmeyin. Zamana bağlı tablolardaki verileri analiz etmek için FOR SYSTEM_TIME yan tümcesiyle zamana bağlı sorgular kullanmanızı öneririz.

Belirli bir noktaya geri yükleme konusunda dikkat edilmesi gerekenler

Mevcut veritabanını belirli bir noktaya geri yükleyerek yeni veritabanı oluşturduğunuzda, veritabanı düzeyinde geçici saklama devre dışı bırakılır. (is_temporal_history_retention_enabled bayrağı KAPALI olarak ayarlanır). Bu işlevsellik, eski satırları sorgulamadan önce eski satırların kaldırılması konusunda endişelenmeden geri yükleme sırasında tüm geçmiş satırları incelemenize olanak tanır. Bunu, yapılandırılmış saklama süresinin ötesindeki geçmiş verileri incelemek için kullanabilirsiniz.

Bir geçici tabloda bir AY saklama süresi belirtildiğini varsayalım. Veritabanınız Premium Hizmet katmanında oluşturulduysa, geçmişte 35 güne kadar veritabanı durumuyla veritabanı kopyası oluşturabilirsiniz. Bu, geçmiş tablosunu doğrudan sorgulayarak 65 güne kadar olan geçmiş satırları çözümlemenize olanak tanır.

Zamana bağlı saklama temizlemeyi etkinleştirmek istiyorsanız, belirli bir noktaya geri yüklemeden sonra aşağıdaki Transact-SQL deyimini çalıştırın:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

Sonraki adımlar

Uygulamalarınızda zamana bağlı tabloları kullanmayı öğrenmek için Zamana Bağlı Tablolarla Çalışmaya Başlama'ya göz atın.

Zamana bağlı tablolar hakkında ayrıntılı bilgi için Zamana bağlı tablolar bölümünü gözden geçirin.