MS SQL SERVER DATABASE MODLARININ INSERT İŞLEMLERİNDE PERFORMANSI

 

Bu yazımızda Microsoft SQL Server’da veritabanı modellerinin Integration Services Package yoluyla insert işleminde performanslarını ele alacağız.

Microsoft SQL Server’da veritabanı modları, verilerin nasıl saklandığı ve yönetildiği konusunda belirli davranışları belirler.

1- Full Recovery Model

Bu mod, veri kaybını en aza indirmek için ayrıntılı bir geri yükleme stratejisi sunar.Tam geri yükleme modeli, yedekleme, günlük dosyaları ve işlem günlüğü kayıtları arasında ayrıntılı bir ilişki oluşturur.Bu mod, tam yedekleme (full backup), fark yedekleme (differential backup) ve işlem günlüğü yedeklemesi (transaction log backup) gibi yedekleme türlerini destekler.

2- Simple Recovery Model

Bu mod yedekleme ve geri yükleme sürecini basit tutar.Basit geri yükleme modeli, tam yedekleme (full backup) dışında herhangi bir fark yedekleme veya işlem günlüğü yedeklemesi türünü desteklemez.İşlem günlüğü, dolması tamamlandığında otomatik olarak temizlenir.

3- Bulk-Logged Recovery Model

Bu mod, büyük hacimli veri ekleme ve değiştirme işlemlerini hızlandırır. Tam geri yükleme modeline kıyasla daha az işlem günlüğü kaydedilir, bu da yedekleme ve geri yükleme işlemlerini hızlandırır. Bu modda yapılan büyük hacimli işlemler sonrasında yedek alındığında, tam geri yükleme modeline geçilerek günlük kayıtların düzeltilmesi sağlanır.

TEST 1

SSMS programı üzerinden Database properties penceresinden recovery model olarak SIMPLE modu seçiyoruz.

Görsel-1

Database SIMPLE moddayken log dosyamızın boyutu 8,192 KB olduğunu not ederek işlemimize başlıyoruz.

Görsel-2

Daha önceden oluşturduğumuz içerisinde Integration Services Package ile başka bir veritabanından TESTDW veritabanımıza 10 Milyon satır veri çekmemize yarayan Job’ı başlatıyoruz.

Görsel-3

Job bitiminde süreyi not ediyoruz, daha sonra bu veriler ile diğer database modlarının farklarını inceleyeceğiz.

Görsel-4

SIMPLE moddayken log dosyasını Shrink için %88 bir alan bırakıldığını görüyoruz.

Görsel-5

Sunucu üzerinden log dosyasının boyutunu kontrol ettiğimizde 73,728 KB olduğunu görüyoruz.

Görsel-6

VLF Count değerini kontrol ederek TESTDW veritabanımızın VLF count sayısını not ediyoruz.

SELECT [name], s.database_id,
COUNT(l.database_id) AS 'VLF Count',
SUM(vlf_size_mb) AS 'VLF Size (MB)',
SUM(CAST(vlf_active AS INT)) AS 'Active VLF',
SUM(vlf_active*vlf_size_mb) AS 'Active VLF Size (MB)',
COUNT(l.database_id)-SUM(CAST(vlf_active AS INT)) AS 'In-active VLF',
SUM(vlf_size_mb)-SUM(vlf_active*vlf_size_mb) AS 'In-active VLF Size (MB)'
FROM sys.databases s
CROSS APPLY sys.dm_db_log_info(s.database_id) l
GROUP BY [name], s.database_id
ORDER BY 'VLF Count' DESC
GO

Görsel-7

TEST 2

SSMS programı üzerinden Database properties penceresinden recovery model olarak BULK-LOGGED
modu seçiyoruz.

Görsel-8

İşleme başlamadan önce log dosyamızı Shrink ederek alanı boşaltıyoruz.

USE [TESTDW]
GO
DBCC SHRINKFILE (N'TESTDW_log' , 0, TRUNCATEONLY)
GO

Shrink işlemi sonrasında log dosyamızın boyutunun 8,192 KB ve VLF Count sayısının 4 olduğunu not ediyoruz.

Görsel-9

Görsel-10

Test Job’ımızı başlatarak işlemimize devam ediyoruz.

Görsel-11

Job işleminin bitimine bu süreyi de not ediyoruz.

Görsel-12

BULK-LOGGED moddayken log dosyasını Shrink için %19 bir alan bırakıldığını görüyoruz.

Görsel-13

 

Sunucu üzerinden log dosyasının boyutunu kontrol ettiğimizde 73,728 KB olduğunu görüyoruz.

Görsel-14

 

VLF Count kontrol ederek TESTDW veritabanımızın VLF count sayısını not ediyoruz.

Görsel-15

TEST 3

SSMS programı üzerinden Database properties penceresinden recovery model olarak FULL modu seçiyoruz.

Görsel-16

İşleme başlamadan önce log dosyamızı Shrink ederek alanı boşaltıyoruz.

USE [TESTDW]
GO
DBCC SHRINKFILE (N'TESTDW_log' , 0, TRUNCATEONLY)
GO

Shrink işlemi sonrasında log dosyamızın boyutunun 8,192 KB ve VLF Count sayısının 4 olduğunu not ediyoruz.

Görsel-17

Görsel-18

Test Job’ımızı başlatarak işlemimize devam ediyoruz.

Görsel-19

 

Job işleminin bitimine bu süreyi de not ediyoruz.

Görsel-20

FULL moddayken log dosyasını Shrink için %33 bir alan bırakıldığını görüyoruz.

Görsel-21

Sunucu üzerinden log dosyasının boyutunu kontrol ettiğimizde 598,016 KB olduğunu görüyoruz.

Görsel-22

VLF Count kontrol ederek TESTDW veritabanımızın VLF count sayısını not ediyoruz.

Görsel-23

Test Analizi

Yapmış olduğumuz testlerin sonucunda SIMPLE ve BULK-LOGGED modlarında log dosyalarının boyutları aynı oranda büyümekte ancak Shrink için alan oranı BULK-LOGGED modda daha az kalmaktadır. FULL modda ise hem log dosyası büyümekte hemde Shrink için alan oranı az kalmaktadır. Veri yazma süresi modlara göre değişmekle ile birlikte ETL sürecinde kullanılan tool sayısına ve türüne göre bu sürelerin modların performansını belirlemesinde etkin rol oynayabilir.
Bu modlar, veritabanının işletim şeklini belirler ve yedekleme, geri yükleme ve performans açısından farklı avantajlar ve dezavantajlar sunar. Hangi modun kullanılacağı, veri tabanının iş yüküne, veri bütünlüğüne ve yedekleme/geri yükleme ihtiyaçlarına göre belirlenmelidir. Her modun belirli bir senaryoya en iyi uygun olduğu durumlar vardır.

İlerleyen yazılarımızda ETL sürecinin karmaşık olduğu bir Integration Services Package ile performans testi yaparak insert işlemindeki kazandırdığı zaman gibi faktörlere bakacağız.