Bağımsız Tablolarda Purging Aktiviteleri

Ekleyen: Çağrı Tongur Monobil Bilişim ve Yazılım Hiz. Cofounder Tarih:03.02.2012 Okunma Sayısı:3007

Özellikle Datawarehouse’ların yaygınlaşmasından sonra inanılmaz büyüklükte tablolara sahip veritabanlarının sayısı azımsanmayacak kadar fazladır. Bugün orta büyüklükte bir işletmenin OLTP veritabanında bile 100GB’ın üzerinde yer alan tablolar göze çarpmaktadır. Özellikle Telekomunikasyon, bankacılık ve sigortacılık gibi sektörlerde yoğun transaction gören çok büyük tablolar bulunmaktadır.Büyük miktarda veriyi saklama anlamında çok güçlü olan Microsoft SQL Server da, çok büyük tablolar karşısında, özellikle DBA’lere yeni sorumluluklar da yüklemektedir. Bu tablolar OLTP veritabanlarında log tabloları olabilirken, transactional kayıtların bulunduğu tablolar da olabilmektedir. Ve bir gün gelecektir, bu çok büyük tabloyu yedeklememiz, arşivlememiz veya bu tablodan kayıt silmemiz gerekecektir.

Konu büyük tablolardan kayıt silme işlemini nasıl gerçekleştirmemiz üzerine olacaktır. Kayıt Silme işlemi uygulama durmuşken yani Cold da olabilir, veya uygulama ayakta iken, yani HOT da olabilir. Tabi konu çok büyük bir tablodan kayıt silme olduğu için biz Cold Purging üzerinde duracağız. Tablo büyük ve üzerinde çok transaction da varsa bu işlemi runtime’da HOT yapmak biraz maliyetli ve uzun olacaktır.

Peki, neden büyük tablolardan kayıt silme işlemini HOT yapamayız veya yapmamalıyız. HOT Purging de olasıdır fakat metodları incelendiğinde, ya disk maliyeti ya da süre karşımıza çıkacak en büyük handikaptır. Bir de bu aktivite esnasında, veritabanına ait standby koşusu varsa işler daha da içinden çıkılmaz bir hal alacaktır.

Her veritabanı ile ilgililenen profesyonel bilir ki, kimi DML Statement’ları pahalıdır, örneğin bir DELETE işlemi veya INSERT işlemi olması gerekenden çok data sayfasının değişmesine sebep olur, bir de bu esnada tüm bu işlemleri log’luyor isek, işlemin sonunda elimizde çok büyük bir Transaction log dosyası olacaktır. Bu da bir disk maliyeti demektir, süreyi de inceldiğimizde silinen veya eklenen verinin büyüklüğüne göre bu tarz DML statement’larının umduğumuzdan daha uzun sürdüğünü görürüz.

Bugün, bu tarz bir analizde kullanacağımız yöntemleri süre ve disk maliyetleri açısından inceleyip sizler için en uygun yöntem hangisi ise ona yönelmenizi sağlayacağım. En uygun yöntem dememdeki kasıt, bazen bu aktivitenin en kısa sürede bitirilmesi,  bazen işlemin fail olduğu esnalarda undo edilebilmesi bazen de en az disk miktarı gerektirecek şekilde bitirilmesidir. Sonuç kısmında, bizim için farklı yol haritaları çıkacak, sizler de bunlardan sizin için en uygunu hangisi ise onu seçebileceksiniz.

Örneğin, aynı tablo üzerinden geçen hafta sildiğiniz veri miktarı 80GB iken, bu hafta 60 GB, önümüzdeki hafta ise 140 GB veri siliyor olabilirsiniz. Verininin kullanacağı disk miktarı ve işlemin süresi baz alındığında her hafta farklı bir yöntem seçebiliriz ki sürecimiz disk yetmiyor diye fail olmasın, işin süresinden dolayı penalty’a maruz kalmayalım. Üstüne üstlük silinecek kayıtlar yeni transaction gören satırlar ise işimiz daha da zordur. 

HOT Purging

Silinecek kayıt kümesi yeni transactional kayıtlar ise, silme işlemini hot yapmanın bir anlamı olmayacaktır. Fakat üzerinde durduğumuz başlığı Hot Purging olarak düşünürsek silinen kayıtlar muhakkak eski tarihlere ait ve transaction göremeyen kayıtlar olmalıdır.

Hot Purging nasıl olmalıdır ve uygulamayı kesintiye uğratmadan bu işlemi nasıl bir yol izlemeliyiz sorusunun cevabı DELETE Statement.

Daha önce bahsettiğim gibi, DELETE statement’i pahalı bir işlemdir ve Transaction Log’un Recovery Model’i FULL, Simple veya Bulk-Logged ise transaction log büyüklüğü ve işlem için geçen süre de farklılaşacak veya aynı kalacaktır.

Bahsi geçen tablo sadece Clustered Index tanımlı üzerinde hiçbir dependency bulunmayan transactional bir tablodur.

Önce her üç Recovery Model’e sahip veritabanlarımızı oluşturmamız gerekir:

CREATE DATABASE HotPurgingSimple
GO


ALTER DATABASE HotPurgingSimple
SET RECOVERY SIMPLE
GO


CREATE DATABASE HotPurgingBulkLogged
GO


ALTER DATABASE HotPurgingBulkLogged
SET RECOVERY SIMPLE
GO


CREATE DATABASE HotPurgingFull
GO

ALTER DATABASE HotPurgingFull
SET RECOVERY SIMPLE
GO

Daha sonra tablomuzu daha küçük boyutlarda -1000000 kayıt olacak şekilde- oluşturalım.

use HotPurgingSimple
GO

CREATE TABLE PurgedTable(
       ID bigint,
       OBJECTID int NOT NULL,
       RNAME nvarchar(max) NOT NULL,
       SYSID uniqueidentifier NOT NULL,
       DATE datetime NOT NULL
       CONSTRAINT PK_PurgedTable PRIMARY KEY CLUSTERED (ID ASC)
)
GO

INSERT INTO PurgedTable
select top 1000000 ROW_NUMBER() OVER (ORDER BY a.object_id) ID,
ABS(a.object_id) OBJECTID,
REPLICATE(a.name,300) RNAME,
newid() SYSID,
DATEADD(DAY,ABS(CHECKSUM(NEWID())%3650),'2000-01-01') DATE
from master.sys.all_parameters a
cross join master.sys.all_parameters b
GO


use HotPurgingBulkLogged
GO

CREATE TABLE PurgedTable(
       ID bigint,
       OBJECTID int NOT NULL,
       RNAME nvarchar(max) NOT NULL,
       SYSID uniqueidentifier NOT NULL,
       DATE datetime NOT NULL
       CONSTRAINT PK_PurgedTable PRIMARY KEY CLUSTERED (ID ASC)
)
GO

INSERT INTO PurgedTable
select * from HotPurgingSimple.dbo.PurgedTable
GO

use HotPurgingFull
GO

CREATE TABLE PurgedTable(
       ID bigint,
       OBJECTID int NOT NULL,
       RNAME nvarchar(max) NOT NULL,
       SYSID uniqueidentifier NOT NULL,
       DATE datetime NOT NULL
       CONSTRAINT PK_PurgedTable PRIMARY KEY CLUSTERED (ID ASC)
)
GO

INSERT INTO PurgedTable
select * from HotPurgingSimple.dbo.PurgedTable
GO

Veritabanlarını oluştururken dikkatinizi çekmiştir, her üç veritabanının recovery model’i de SIMPLE olarak seçilmişti. Bu adımda veritabanlarının isimlerinden de anlıyacağınız üzere Recovery Model’llerini değiştireğim.

ALTER DATABASE HotPurgingBulkLogged
SET RECOVERY BULK_LOGGED
GO

ALTER DATABASE HotPurgingFull
SET RECOVERY FULL
GO

Hot Purging işlemine başlayabiliriz. Elimizde artık üç eşit boyda tablo ve veritabanı bulunmaktadır. Bunu sorgulamak için ise aşağıdaki query’i kullanabiliriz:

SELECT size,name
FROM HotPurgingSimple.sys.sysfiles
WHERE name LIKE '%log'
UNION ALL
SELECT size,name
FROM HotPurgingBulkLogged.sys.sysfiles
WHERE name LIKE '%log'
UNION ALL
SELECT size,name
FROM HotPurgingFull.sys.sysfiles
WHERE name LIKE '%log'

USE HotPurgingSimple
GO
EXEC sp_spaceused N'dbo.PurgedTable'
GO
USE HotPurgingBulkLogged
GO
EXEC sp_spaceused N'dbo.PurgedTable'
GO
USE HotPurgingFull
GO
EXEC sp_spaceused N'dbo.PurgedTable'
GO

Hot purging işlemini DELETE cümlesi yardımıyla yapacağız ve artık süre ölçümünü de göz önüne alacağız.

use HotPurgingSimple
go
delete from PurgedTable whereID <=600000
go
use HotPurgingBulkLogged
go
delete from PurgedTable whereID <=600000
go
use HotPurgingFull
go
delete from PurgedTable whereID <=600000
go

Tablo çok fazla transaction görüyor ise Cold Purging konusunda anlatacağımız metodları uygulayıp, tabloyu rename etmemiz imkansız gibi görünecektir. Bu işlem çok kısa bir kesinti yaratarak, tabloların rename edilmesi ile ilgilidir. Fakat bahsettiğim gibi, uygulamayı kesintiye uğratacağı için hot purging aktivitesi sayılmaz.

İşlemin neticesi:

şeklinde olacaktır.

Burada göründüğü gibi süreler birbirine çok yakındır. Transaction Log büyüme miktarı ise aynıdır. Süre ve log büyüklülerinin bu kadar yakın olmasının sebebi test yaptığım sunucu üzerinde işlem yoğunluğunun az olmasıdır.

Cold Purging

Purging işlemini uygulamayı stop ederek yapacağımız bu adımda, sistemi etkileyen tüm faktörleri göz önüne almalıyız. Bu faktörler kimi zaman tabloyu ilgilendiren agent üzerinde koşan bir job olurken, kimi zaman da alınan transaction Log Backup olabilir. Tüm bu etkenleri minimize ettikten sonra, bize kalan silme işlemi için en doğru yöntem hangisidir ona karar vermektir.

Cold Purging işlemini aşağıdaki 4 metod ile gerçekleştirebiliriz:

• Tablo üzerinden DELETE cümlesi ile veriler silinir.
• Kalmasını istediğimiz verileri farklı bir tabloya SELECT .. INTO metodu ile taşıyıp daha sonra yeni oluşan tabloyu rename edilir.
• Visual Studio Export/Import Wizard’ını kullanıp yeni bir tablo oluşturulur ve oluşan tablo rename edilir.
• Tablo oluşturulur, daha sonra veri insert cümlesi ile taşınır.

Bu işlemleri test etmek için aşağıdaki veritabanlarını kullanacağız:

CREATE DATABASE ColdPurgingImportWizard
GO
ALTER DATABASE ColdPurgingImportWizard
SET RECOVERY SIMPLE
GO
CREATE DATABASE ColdPurgingInsert
GO
ALTER DATABASE ColdPurgingInsert
SET RECOVERY SIMPLE
GO
CREATE DATABASE ColdPurgingSelectInto
GO
ALTER DATABASE ColdPurgingSelectInto
SET RECOVERY SIMPLE
GO
CREATE DATABASE ColdPurgingDelete
GO
ALTER DATABASE ColdPurgingDelete
SET RECOVERY SIMPLE
GO

Daha sonra test için kullancağımız tabloyu oluştururuz:

use ColdPurgingImportWizard
GO

CREATE TABLE PurgedTable(
       ID bigint,
       OBJECTID int NOT NULL,
       RNAME nvarchar(max) NOT NULL,
       SYSID uniqueidentifier NOT NULL,
       DATE datetime NOT NULL,
       CONSTRAINT PK_PurgedTable PRIMARY KEY CLUSTERED (ID ASC)
)
GO
INSERT INTO PurgedTable
select top 3000000 ROW_NUMBER() OVER (ORDER BY a.object_id)ID,
ABS(a.object_id) OBJECTID,
REPLICATE(a.name,350) RNAME,
newid() SYSID,
DATEADD(DAY,ABS(CHECKSUM(NEWID())%3650),'2000-01-01') DATE
from master.sys.all_parameters a
cross join master.sys.all_parameters b
GO

use ColdPurgingInsert
GO
CREATE TABLE PurgedTable(
       ID bigint,
       OBJECTID int NOT NULL,
       RNAME nvarchar(max) NOT NULL,
       SYSID uniqueidentifier NOT NULL,
       DATE datetime NOT NULL
       CONSTRAINT PK_PurgedTable PRIMARY KEY CLUSTERED (ID ASC)
)
GO
INSERT INTO PurgedTable
select * from ColdPurgingImportWizard.dbo.PurgedTable
GO
use ColdPurgingSelectInto
GO

CREATE TABLE PurgedTable(
       ID bigint,
       OBJECTID int NOT NULL,
       RNAME nvarchar(max) NOT NULL,
       SYSID uniqueidentifier NOT NULL,
       DATE datetime NOT NULL
       CONSTRAINT PK_PurgedTable PRIMARY KEY CLUSTERED (ID ASC)
)
GO
INSERT INTO PurgedTable
select * from ColdPurgingImportWizard.dbo.PurgedTable
GO
use ColdPurgingDelete
GO
CREATE TABLE PurgedTable(
       ID bigint,
       OBJECTID int NOT NULL,
       RNAME nvarchar(max) NOT NULL,
       SYSID uniqueidentifier NOT NULL,
       DATE datetime NOT NULL
       CONSTRAINT PK_PurgedTable PRIMARY KEY CLUSTERED (ID ASC)
)
GO
INSERT INTO PurgedTable
select * from ColdPurgingImportWizard.dbo.PurgedTable
GO

Tabloları oluşturduktan sonra test neticeleri almak için tüm veritabanlarının log dosyalarını 1MB olarak shrink ediyorum ve RECOVERY MODEL’ini FULL olarak set ediyorum.

ALTER DATABASE ColdPurgingImportWizard
SET RECOVERY FULL
GO

ALTER DATABASE ColdPurgingInsert
SET RECOVERY FULL
GO

ALTER DATABASE ColdPurgingSelectInto
SET RECOVERY FULL
GO

ALTER DATABASE ColdPurgingDelete
SET RECOVERY FULL
GO

Sırasıyla Purging için gerekli script’leri ve gereken prosedürleri yazıyorum.

Öncelikle, Export Import Wizard ile veriyi PurgedTableTemp isimli bir tabloya kopyalıyorum ve çıkan süreleri Profiler ile izleyip, daha sonra toplamda kullanacağım için not ediyorum. Import Wizard kullanırken aşağıdaki query’iyi belirtebileceğim Query opsiyonunu seçiyorum.

select * from PurgedTable where ID >1000000

Daha sonra ise aşağıdaki script ile oluşan PurgedTableTemp tablosunu rename edip, import süresine burada elde ettiğim süreyi ekliyorum.

SET STATISTICS IO ON;
GO
SET STATISTICS TIME ON
GO
use ColdPurgingImportWizard
GO
sp_RENAME 'PurgedTable','PurgedTableOld'
go
sp_RENAME 'PurgedTableTemp','PurgedTable'
go
sp_rename N'PurgedTableOld.PK_PurgedTable',N'PK_PurgedTableOld',N'INDEX';
go
ALTER TABLE PurgedTable ADD CONSTRAINT PK_PurgedTable
PRIMARY KEY CLUSTERED
(
ID ASC
)
go

İkinci adımda ise Insert cümlesi ile tabloyu kopyalıyorum ve süreleri not ediyorum:

SET STATISTICS IO ON;
GO
SET STATISTICS TIME ON
GO
use ColdPurgingInsert
go
CREATE TABLE PurgedTableTemp(
       ID bigint NOT NULL,
       OBJECTID int NOT NULL,
       RNAME nvarchar(max) NOT NULL,
       SYSID uniqueidentifier NOT NULL,
       DATE datetime NOT NULL
)
GO
insert into PurgedTableTemp select * from PurgedTable WITH (TABLOCK) where ID >1000000
go
sp_RENAME 'PurgedTable','PurgedTableOld'
go
sp_RENAME 'PurgedTableTemp','PurgedTable'
go
sp_rename N'PurgedTableOld.PK_PurgedTable',N'PK_PurgedTableOld',N'INDEX';
go
ALTER TABLE PurgedTable ADD CONSTRAINT PK_PurgedTable
PRIMARY KEY CLUSTERED
(
ID ASC
)
Go

Bir sonraki adımda SELECT .. INTO .. FROM cümlesi ile veriyi taşıyorum ve çıkan süreleri not ediyorum:

SET STATISTICS IO ON;
GO
SET STATISTICS TIME ON
GO
use ColdPurgingSelectInto
go
sp_dboption 'ColdPurgingSelectInto','select into/bulkcopy','ON'
GO
select * into PurgedTableTemp from PurgedTable WITH (TABLOCK) where ID >1000000
go
sp_RENAME 'PurgedTable','PurgedTableOld'
go
sp_RENAME 'PurgedTableTemp','PurgedTable'
go
sp_rename N'PurgedTableOld.PK_PurgedTable',N'PK_PurgedTableOld',N'INDEX';
go
ALTER TABLE PurgedTable ADD CONSTRAINT PK_PurgedTable
PRIMARY KEY CLUSTERED
(
ID ASC
)
go

En son adımda ise tablo üzerinde bulunan veriyi DELETE cümlesi ile siliyorum:

SET STATISTICS IO ON;
GO
SET STATISTICS TIME ON
GO
use ColdPurgingDelete
go
delete from PurgedTable where ID <=1000000
go

Artık netice almanın zamanı gelmiştir, log dosyalarının büyüklüklerini öğrenmek için bu sorgudan faydalanıyorum:

SELECT size,name
FROM ColdPurgingImportWizard.sys.sysfiles
WHERE name LIKE'%log'
UNION ALL
SELECT size,name
FROM ColdPurgingInsert.sys.sysfiles
WHERE name LIKE'%log'
UNION ALL
SELECT size,name
FROM ColdPurgingSelectInto.sys.sysfiles
WHERE name LIKE'%log'
UNION ALL
SELECT size,name
FROM ColdPurgingDelete.sys.sysfiles
WHERE name LIKE '%log'

Log büyüklükleri ve süreler ise aşağıdaki gibidir. Bu test farklı zamanlarda tekrarlandığında da yaklaşık neticeler aynıdır.

ÖZET

Daha büyük tablolarda, Microsoft SQL Server’in gösteceği internal davranış daha farklıdır tabi, konunun başında da bahsettiğim gibi, purge edilecek tablonun daha büyük olduğu ve silinecek kayıt kümesinin verinin çoğunluğunu oluşturduğu durumlarda, silinmeyecek veriyi farklı bir tabloya taşıyıp, oluşacak tabloyu rename etmek daha efektiftir. Seçilecek yöntem bu işin istenen en kısa sürede bitirilmesi durumunda farklıdır, en az disk gereksinimi durumunda farklıdır. Tüm etkenleri analiz edip doğru karar vermek için her defasında farklı yöntem kullanabiliriz.

Tüm sonuçları göz önüne getirdiğimizde, purging aktivitesine etki edecek diğer faktörler de düşünülmelidir. Tabloda tanımlı veri tiplerinden, üzerinde yer alan indekslere kadar tüm done ve süreç iyi analiz edilmelidir. Mesela Log Shipping ile kurulu bir Standby veritabanına sahip isek, transaction log backup’ını ve transferini erteleyebiliriz.

Yorum yazabilmek için üye olmanız ya da üye girişi yapmanız gerekmektedir.

Turgay Sahtiyan

03.02.2012 16:07

Çağrı bey öncelikle aramıza hoşgeldiniz. :) Çok detaylı bir yazı elinize sağlık. Belki bu konunun devamı olarak bir sonraki makalede partitioning switch ile arşivlemeyi anlatırsınız :)