Etiket Bulutu

Benchmark Convert_IMplicit Database High Availability Database Mirroring datawarehouse dimension table dmv Dynamic Data Masking Execution Execution Plans fact table Failover Cluster Node ekleme Failover Clustering FileStream generate script High Availability Implicit Instant File Initialization index Kinect Linux Live Query Statistics Log Shipping Mirroring object explorer object explorer details ODBC Driver pass performance performance tuning Plan Handle Planü Power View reporting services rol Row Level Security script sql serer 2016 sql server SQL Server 2008 SQL Server 2008 Log Shipping SQL Server 2012 SQL Server 2012 installation SQL Server 2012 Kurulumu SQL Server Backup SQL Server da Backup planı SQL Server da Maintenance Plans oluşturma SQL Server database mirroring SQL Server Disaster Recovery sql server dynamic management views SQL Server Failover Cluster SQL Server High Availability SQL Server Log Shipping SQL Server Maintenace Plans sql server performans SQLDIAG SQLDIAG Troubleshooting T24 Temenos truncate table t-sql unique index performance 1. Dünya savaşı istatistikleri 1456 451 ACID advanced analytics Advanced Data Analytics Affinity algı Alter index Alter table ALTER TABLE .. ALTER COLUMN Altın Oran Always On ALWAYSON AlwaysOnDemoTool amazon web services kinesis AMR analiz analysis service Ankara Antivirus apache kafka Arduino Article Assembly asymmetric audit Authentication Auto Growth Availability Group azure Azure Backup azure event hub partition azure event hubs azure event hubs servisi azure event hubs veri edinme Azure File Share Azure Fiyatlandırma Azure HDInsight Azure Hizmet Modelleri Azure ML Azure New Portal Azure Pricing Azure Queue azure sql database configuration azure sql database kullanımı azure sql database stream veriyi tutma azure sql database table partitioning Azure Storage azure stream analytics azure stream analytics dashboard azure stream analytics ölçeklendirilmesi azure stream analytics servisi Azure Table BA Backup backup encyrption backupset Bakım BASE bellek Best Practice BI Semantic Model Big Data Big User blocking blocking disable trigger blocking enable trigger Buffer Cache buffer pool Buffer Pool Extension bulk logged Buluta Veri Depolama Buluttaki Disk Business Analytics Conference business intelligence Büyük Veri Case Central Management Server certificate changed data capture Cloud Computing Cloud DR CLR Cluster clustered columnstore index Clustered Index Code Snippets Cold Purging collation column store column-level columnstore ColumnStore Indexes Compress ComputerNamePhysicalNetBIOS Concurrency Conditions Contained Database Contained Databases convert CONVERT_IMPLICIT Corruption Credentials cube DAC Dashboard Tasarımı data cleansing Data Compression Data Consistency Model data encryption data matching data mining Data Page data profiling data quality Data Services Data Warehouse Design Database database list Database Management Sistem database master key Database Mirroring Database Snapshot database trigger database-level Data-Ink Ratio datasets datasource DataZen date date dimension db_owner DBA DBCC dbcc dropcleanbuffers dbcc freeproccache DBMS dbo user DDL deadlock debugging DecryptByKey DecryptByPassPhrase deleted bitmap delta store Denali Denali SSAS deny database list deşifre detail index developer DIFFERENTIAL BACKUP DirectQuery Dirty Read Disaster Recovery Distribution Yapılandırma Distributor Distributor Agent dm_server_services DMF DMO DMV document db dosya bazlı şifreleme dqs dr Dynamic Management Function Dynamic Management Object Dynamic Management View ecrypt Effected Report Design Techniques Eğitim EncryptByKey EncryptByPassPhrase encryption endpoint Environment Variable error Error 5030 Error Log Estetik Raporlama Estimated Rows Eş Zamanlılkk Etkili Rapor Tasarlama Teknikleri Etkinlik ETL event Event Viewer except;intersect;sql execution Execution Plan export formats extended events Extended Stored Procedure Facets Failover Failover Cluster fast n execution plan FETCH NEXT FILELISTONLY FILLFACTOR File Table file-level FileStream Filter Pack Filtered Index First_Value Flat File fn_repl_hash_binary Focal Point foreignkey FORMAT Forwarded Record forwarded_record_count ftp task FULL BACKUP Full Recovery Full-Text Search functions Gartner Geocluster Gerçek Zamanlı Dashboard gestalt Golden Ratio görsel duyu group by Güvenlik ha Hadoop hafıza Hash HASHBYTES HEADERONLY headers footers Heap Hekaton hicri High Availability hijr Hiyerarşi Hybrid Cloud IaaS Index Index Scan In-Memory InMemory DW In-Memory DW InMemory OLTP In-Memory OLTP Internet of People Internet of Things IO IOT IoT nedir Isolation Level indeks index inmemory in-memory oltp internet of things isolation level istatistik istatistikler İş zekası İzolasyon Seviyesi Job json json support knowledge base kolon-satır bazlı kurulum küp Lag Lansman latch Lead linked server lock locking locking hints Log Backup Log Reader Agent Log Shipping login Lost-Update LQS Machine Learning Maintenance Management Studio matrix Max Text Replication Size mdx memory Memory Optimization Advisor Memory Optimized Table Memory Optimized Tables merge Merge Agent merge kullanımı Merge Publication Merge Replication merge type 1 slowly changing dimension merge type 1 slowly changing dimension örneği merge type 1 vs type 2 scd merge type 2 slowly changing dimension merge type 2 slowly changing dimension örneği merge type 3 slowly changing dimension merge type 4 slowly changing dimension message Microsoft Advanced Data Analytics Çözümleri microsoft azure Microsoft Bulut Microsoft Sanal Akademi Microsoft SQL Server Microsoft SQL Server 2014 Yenilikleri Microsoft SQL Server 2016 Mirror mirroring missing index Monitoring move Msdb multi_user multiversion concurrency control MVP MVP Roadshow MySnippet Named Pipes Natively Store Procedures Natively Stored Procedures Nesnelerin İnterneti Network Binding Order NoEngine Approaches nonclustered columnstore index Non-Repetable Read NoSQL NoSQL Approaches NoSQL Dünyası object explorer Odak Noktası ODBC Office 365 Offline OFFSET olap OLAP Backup OLE DB OLTP Online Index order attributes Otomatik Büyüme OVER PaaS PAD_INDEX page out page properties PAGE RESTORE PAGEIOLATCH paging parameters partition partitioning PASS PASS Summit PASS Summit 2014 Performance Performance Tuning performans performans tuning Phantom Read pivot Policies Policy Based Management Filtreleme Policy Management Power BI Power BI Dashboard Power BI Rest API power bi power view PowerBI PowerBI for Office 365 powerbi PowerMap PowerPivot PowerQuery powershell powershell ile sql yönetimi PowerView PowerView raporlarının web sayfalarına gömülmesi precon Primary Key primarykey Project Deployment Model Project Variable Protokol Proxy Proxy Account Publisher Purging on Independent Tables QL Server 2014 Yenilikleri Que Reader Agent Query Plan query store R Range Raporlama Raporlama Projeleri için Strateji Belirleme Raporlama Projelerine Hazırlık Read Committed Read Uncommitted RealTime Dashboard Rebuild RECONFIGURE RECONFIGURE WITH OVERRIDE Recovery model Relational Engine relationships Rename SSRS Database Repeatable Read Replication Replication Monitoring replikasyon report manager web site report parts reporting service reporting services reporting servis Resource Governor RESTORE Restore Database Restore Generate Restore Generate Script Restore transaction log rollback rs Rule of Thirds sa user SaaS sayfalama scd 3 demo scd karşılaştırma scd type 4 demo Scheduling Schema Comparison script Security segment elimination select into Self-Service BI Semantic Search Serializable Server Core SERVERPROPERTY Service services shared data sources shared datasets Shared Memory sharepoint Sharepoint 2010 ShowPlan Shrink simple recovery sing_user sliding window Slowly Changing Dimension snapshot Snapshot Agent Snapshot Publication Snapshot Replication Snippet snowflake sorting sp_configure sp_describe_first_result_set sp_server_diagnostics sp_spaceused sql SQL Agent Job SQL Azure sql bilgi yarışması SQL CLR SQL DIAG SQL DIAG Performans verisi toplama SQL endpoint SQL Login SQL Onculeri SQL Öncüleri sql script sql server SQL Server 2005 SQL Server 2008 SQL Server 2011 CTP3 SQL Server 2011 Denali SQL Server 2012 SQL Server 2012 CTP3 SQL Server 2012 RC SQL Server 2012 RC0 SQL Server 2012 ShowPlan Enhancements SQL Server 2012 T-SQL Enhancements SQL Server 2014 Sql Server 2014 Cardinality Estimator SQL Server 2014 Yenilikleri sql server 2016 SQL Server 2016 New Features SQL Server 2016 Yenilikleri sql server agent sql server assembly ekleme SQL Server Authentication sql server cast ve convert sql server clr integration sql server clr kullanımı sql server clr örnek sql server cluster SQL Server Code Name Denali SQL Server da Kullanıcı Yaratma SQL Server Database Project sql server dmv ve dmf sql server execution plan temizleme SQL Server Express Backup sql server fast n option örneği sql server fast n seçeneği SQL Server login sql server management stdio sql server merge into örnek sql server merge komutu sql server merge performnas sql server merge type 1 scd sql server merge type 2 scd sql server merge type 3 scd SQL Server Mobile Report Publisher SQL Server Network Interface SQL Server Onculeri SQL Server Öncüleri SQL Server Öncüleri Ankara SQL Server Performance sql server performans SQL Server Profiler SQL server recovery model SQL Server Reporting Services SQL Server Restore Generate Script SQL Server sa SQL Server Security SQL Server SQL DIAG sql server tarih dönüşüm işlemi sql server tarihsel veriler ile çalışma SQL Server User SQL Server yetki SQL Server yetkilendirme sql servera .net kodu ekleme SQL Serverda yetkilendirme nasıl SQL Serverda yetkilendirme nasıl yapılır sql to oracle linked server sql türkiye SQL User With Password sql yarışma SQLCMD sql'den oracle'a linked server SQLDIAG SQLDIAG Report SQLOS sqlsaturay SQLSaturday SQLSaturday #182 SQLSaturday #359 sqlsaturday #451 sqlserveronculeri ssas SSAS 2012 SSIS SSIS 2012 ssis SSMS SSMS Project SSMS Solution ssrs Stanby Database star schema STOPAT STOPBEFOREMARK STORAGE Storage Engine stored procedure stream analytics job subreports Subscriber Subscription subscriptions symmetric SYS sys.dm_db_index_physical_stats sys.dm_db_index_usage_stats sys.dm_db_missing_index_columns sys.dm_db_missing_index_details sys.dm_db_missing_index_group_stats sys.dm_db_missing_index_groups sys.server_principals sysadmin System Databases System View şifre şifreleme table table difference TableHasClustIndex TableHasIdentity TableHasPrimaryKey Tablet PC Tabular Mode Tabular Model TCP/IP TDE Tempdb time series Transaction Transactional Publication Transactional Replication Transparent Data Encryption trigger Troubleshooting TRY_CONVERT TRY_PARSE tsql t-sql T-SQL 2012 tsql mistakes Undocument union unionall Updatable ColumnStore İndex upgrade Veri ambarı veri edinme seçenekleri Veri Güvenliği Veri Hizmetleri Veri madenciliği Veri Mürekkep Oranı Veri Tabanı Yönetim Sistemleri Veri Tipi Veri Tutarlılık Modelleri Veri Yönetimi Evrimi verinin evrimi Veritabanı oluşturmak VERİTABANI YEDEKLEME STRATEJİLERİ veritabanı yedeklerinin şifrelenmesi Veritabanı Yöneticisi Veritabanı Yönetimi VeritPaq view any database Visual Studio VTYS web services Webcast Windows 7 Windows 8 Windows Authentication Windows Azure Windows Failover Clustering wmi WRITELOG xevents xp_sqlagent_enum_jobs YEDEKLEME STRATEJİLERİ Yedekli Çalışma Yetkilendirme Yiğit Aktan ysfkhvc yusuf kahveci Yüksek Erişilebilirlik Yüksek Süreklilik zip

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ı:2922


Ö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:

 

Veritabanı

Aktiviteden Önce Veritabanı Büyüklüğü

Aktiviteden Sonra Veritabanı Büyüklüğü

Süre(dk)

HotPurgingSimple_log

1128480

2660928

13

HotPurgingBulkLogged_log

1128480

2660928

11

HotPurgingFull_log

1128480

2660928

14

 

 

ş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.

 

 

~ Süre (dk)

~ Log Büyüklüğü (KB)

ColdPurgingDelete

21

4568448

ColdPurgingImportWizard

73

14208

ColdPurgingInsert

5.3

798848

ColdPurgingSelectInto

6.1

727168

 

sp_dboption '[DatabaseName]', 'select into/bulkcopy', 'ON/OFF'

Bu opsiyonu değitirmemdeki amaç SELECT INTO cümlelerinde loglanacak transaction log kaydını minimize etmektir. Süreye pozitif etki gösterirken transaction log’un büyümesini minimize eder.

 

Ö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 yaz
Turgay Sahtiyan

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

Üye Girişi

Kullanıcı Adınız

Şifreniz

Şifremi Unuttum

Arkadaşına Tavsiye Et

Tavsiye edebilmek için siteye giriş yapmalısınız