Bir programcının dünyası

Sql Server‘da belli bir veritabanında en büyük tabloları bulmak için aşağıdaki sorguyu çalıştırabiliriz;

DECLARE @TabloBoyutlari TABLE (TabloAdi VARCHAR(50), SatirSayisi INT, AyrilmisAlan VARCHAR(50), KullanilanAlan VARCHAR(50), IndexBoyu VARCHAR(50), KullanilmayanAlan VARCHAR(50))

INSERT INTO @TabloBoyutlari
EXECUTE sp_MSforeachtable 'sp_spaceused [?]'

SELECT TOP 10 * FROM @TabloBoyutlari ORDER BY CONVERT(INT, REPLACE(AyrilmisAlan, ' KB', '')) DESC

Bu sorgu sayesinde ilgili veritabanındaki tabloların,

• Tablo Adı
• Satır Sayısı
• MDF dosyasında ilgili tablo için ayrılmış alanın boyutu
• MDF dosyasında ilgili tablo için ayrılmış alanın kullanılan boyutu
• MDF dosyasında ilgili tablonun indexleri için ayrılmış alan
• MDF dosyasında ilgili tablo için kullanılmayan alan

bilgilerine ulaşabiliriz.

İlgili tablo için aşağıdaki eşitlik her zaman doğrudur;

Kullanılan Alanı + Index Alanı + Kullanılmayan Alanı = Ayrılmış Alan

Benim bilgisayarımdaki AdventureWorks2008R2 örnek veritabanında yukarıdaki sorguyu çalıştırdığımda, şöyle bir sonuç alıyorum;

Bilgisayar Programcılığı dünyasında genellikle bir işi yapmanın birden fazla yolu vardır. Özellikle veritabanı tarafında bu yollar daha da fazla olabilir. Uygulama Geliştiriciler olarak yeni bir kod parçası yazacağımız zaman, genellikle bu yollardan en çok kullandığımız bir tanesini seçeriz ve onu kullanırız.

Ne var ki, seçtiğimiz yol herzaman en doğru yol olmayabilir. Sql‘de join‘li sorgulama yaparken, filtreleme için kullandığımız yöntem de bunlardan biri olabilir.

Bu yazımda join‘li sorgularda filtreleme için seçebileceğimiz iki yolu karşılaştıracağım. Karşılaştırma yapmak için kendi bilgisayarımda kurulu olan Sql Server 2008 üzerinde AdventureWorkd2008 R2 veritabanını kullandım.

Eğer birden fazla tablonun join‘ler ile ilişkilendirildiği bir sorgu yazıyorsak, sonuç kümesini filtreleme için kullanabileceğimiz iki yöntem vardır;

WHERE Filtreleme

Sonuç kümesinde olmasını/olmamasını istediğimiz kayıtları sorgunun WHERE cümlesinde tanımlarız. Şablon;

SELECT
	[TABLE1.ALANADLARI],
	[TABLE2.ALANADLARI]
FROM
	[TABLE1]
	JOIN [TABLE2] ON [TABLE1.ALAN1] = [TABLE2.ALAN1]
WHERE
	[TABLE1.ALAN2] = DEGER AND
	[TABLE2.ALAN2] = DEGER

JOIN Filtreleme

Sonuç kümesinde olmasını/olmamasını istediğimiz kayıtları sorgunun JOIN cümlelerinde tanımlarız. Şablon;

SELECT
	[TABLE1.ALANADLARI],
	[TABLE2.ALANADLARI]
FROM
	[TABLE1]
	JOIN [TABLE2] ON [TABLE1.ALAN1] = [TABLE2.ALAN1] AND [TABLE1.ALAN2] = DEGER AND [TABLE2.ALAN2] = DEGER

AdventureWorks2008 R2 veritabanında aşağıdaki iki sorguyu çalıştırdıktan sonra, performans analizini yapalım;

WHERE Filtreleme

SELECT
	*
FROM
	Sales.SalesOrderHeader AS SOH WITH (NOLOCK)
	JOIN Sales.SalesOrderDetail AS SOD WITH (NOLOCK) ON SOH.SalesOrderID = SOD.SalesOrderID
	JOIN Sales.SalesOrderHeaderSalesReason AS SOHSR WITH (NOLOCK) ON SOHSR.SalesOrderID = SOH.SalesOrderID
	JOIN Sales.SalesReason AS SR WITH (NOLOCK) ON SOHSR.SalesReasonID = SR.SalesReasonID
WHERE
	SOH.CustomerID > 15000 AND
	SOD.LineTotal > 2000 AND
	SR.SalesReasonID > 5

JOIN Filtreleme

SELECT
	*
FROM
	Sales.SalesOrderHeader AS SOH WITH (NOLOCK)
	JOIN Sales.SalesOrderDetail AS SOD WITH (NOLOCK) ON SOH.SalesOrderID = SOD.SalesOrderID AND SOH.CustomerID > 15000 AND SOD.LineTotal > 2000
	JOIN Sales.SalesOrderHeaderSalesReason AS SOHSR WITH (NOLOCK) ON SOHSR.SalesOrderID = SOH.SalesOrderID
	JOIN Sales.SalesReason AS SR WITH (NOLOCK) ON SOHSR.SalesReasonID = SR.SalesReasonID AND SR.SalesReasonID > 5

Her iki sorgu da çalıştıktan sonra 1321 satır geri döndürdü. Performans incelemesini üç alanda yapacağız;

• CPU Kullanımı
• Disk Kullanımı
• Hafıza Kullanımı

Bu üç performans kriteri için verileri  SYS.SYSPROCESSES DMV‘sinden alabiliriz. SYS.SYSPROCESSES için detaylı bilgiye MSDN‘deki şu makaleden ulaşabilirsiniz. Aşağıda ilgili üç performans kriteri için verileri elde edebileceğimiz sorgu var;

SELECT
	DB_NAME(SP.DBID) AS VERITABANI,
	EST.TEXT AS SORGU,
	CPU,
	PHYSICAL_IO AS DISK_OKUMA,
	MEMUSAGE AS HAFIZA_KULLANIM
FROM
	SYS.SYSPROCESSES AS SP
	CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) AS EST

Bu sorguyu çalıştırdıktan sonra benim test bilgisayarımda şu verileri elde ettim;

• VERITABANI : AdventureWorks2008R2
• CPU : 1965
• DISK_OKUMA : 211
• HAFIZA_KULLANIM : 2

• VERITABANI : AdventureWorks2008R2
• CPU : 156
• DISK_OKUMA : 0
• HAFIZA_KULLANIM : 2

Gördüğünüz gibi kaynak kullanımı açısından (özellikle Disk Okuma ve CPU Kullanımı açısından) JOIN Filtreleme, WHERE Filtrelemeden daha avantajlı.

JOIN Filtreleme tabloları eşlerken filtrelenmiş verileri kullanıyor, WHERE Filtreleme ise, önce tabloları eşleştiriyor sonra filtreliyor. Özellikle çok kayıt bulunan/bulunacak olan tablolarınıza sorgu yazarken, JOIN Filtreleme’yi kullanmanızı tavsiye ederim.

Sql Server‘da çalıştırılan her sorgu, aslında çalıştırılmadan önce derleme (compile) işlemine tabi tutulur. Bu derleme işlemi sonucunda Sql Server query plan denilen çıktıyı üretir.

Query Plan, query processor‘e (sorguyu işleyen birim), sorgunun ihtiyaç duyduğu veriler için veritabanında bulunan tablo ve index‘lere fiziksel olarak nasıl erişebileceğini söyler.

Fakat, query plan elde etmek için yapılan bu derleme işlemi, bazı sorgular için çok pahalı olabilir.

Sql Server, aynı sql sorgusunun defalarca çalıştırıldığı durumlarda, derleme işleminin yükünü azaltmak için, query plan cache denilen hafıza bölgesinde query plan‘ları önbellekler.

Query plan cache, önbellekleyeceği sorguları basit bir hash tablo‘da saklar. Hash tablo’nun iki alanı vardır, birinde sql sorgusunun kendisini, diğerinde derleme sonucu ortaya çıkan query plan‘ı saklar.

Sql Server, yeni bir sorgu çalıştıracağı zaman, ilk önce query plan cache‘te sorgunun query plan‘ı var mı diye bakar. Eğer bulursa, daha önce önbelleklenmiş bu query plan‘ı kullanır. Bulamazsa, ilk önce sorguyu yazım denetimine tabi tutar, sonra sorguyu derler ve oluşan query plan‘ı bu listeye ekler.

Query plan cache‘in getirdiği performans artışını ölçmek için, öncelikle Sql Server’daki query plan cache’i boşaltacağız;

DBCC FREEPROCCACHE

Dikkat : Bu komutu kullandığınızda, Sql Server üzerinde bulunan tüm query plan cache silinir. Veritabanı veya belli bir sorgu için temizleme mümkün değildir. Bu komutun Canlı Veritabanında (Production Database) kullanılmaması önerilir.

Şimdi Sql Server’ın sorguyu inceleme ve derleme işlemi için ne kadar vakit harcadığını bulmamız lazım. Yapmamız gereken, sorguyu çalıştırmadan önce aşağıdaki komutu çalıştırmak;

SET STATISTICS TIME ON

Şimdi sorgumuzu çalıştırabiliriz. Ben Sql Server 2008 için hazırlanmış örnek veritabanında (AdventureWorks 2008R2) aşağıdaki sorguyu çalıştırıyorum;

SELECT * FROM HumanResources.Employee WHERE BusinessEntityId IN (1, 2);

Sorgu sonucu;

SQL Server parse and compile time:
	CPU time = 0 ms, elapsed time = 12 ms.

(2 row(s) affected)

SQL Server Execution Times:
	CPU time = 0 ms,  elapsed time = 1 ms.

Gördüğünüz gibi, sorgunun parse ve compile işlemine tabi tutulması 12 ms. sürdü. Sorgunun çalıştırılması ise 1 ms. sürdü.

Aynı sorguyu tekrar çalıştırırsak, elde edeceğimiz sonuç;

SQL Server parse and compile time:
	CPU time = 0 ms, elapsed time = 1 ms.

(2 row(s) affected)

SQL Server Execution Times:
	CPU time = 0 ms,  elapsed time = 1 ms.

Query plan cache sayesinde, parse ve compile işlemi 12 ms. yerine 1 ms. sürdü.

Not : Sql Server’ın istatistik toplarken ulaşabileceği en düşük kesinlik süresi 1 ms.’dir. 1 ms.’den kısa süren işler için bile Sql Server 1 ms. raporlar.

Özellikle katalog tablolarının CSV çıktılarına sıklıkla ihtiyaç duyarız. Aşağıdaki sql script’ini kullanarak, siz de tablolarınızdan CSV çıktı alabilirsiniz.

Öncelikle tablomuzun orjinal haline bakalım;

SELECT Name
FROM HumanResources.Shift WITH (NOLOCK)

Şimdi öyle bir sql sorgusu yazacağız ki, çıktımız şu şekilde olacak;

Day, Evening, Night

SELECT
	SUBSTRING((
		SELECT ', ' + Name
		FROM HumanResources.Shift WITH (NOLOCK)
		FOR XML PATH('')
	), 3, 8000) AS CSV

Sorguyu FOR XML anahtar kelimeleri ile çalıştırdığımıza ve PATH olarak ” (boş string) verdiğimize dikkat edin.

SELECT ', ' + Name
FROM HumanResources.Shift WITH (NOLOCK)
FOR XML PATH('')

Böylece aşağıdaki sonucu elde etmiş olduk;

Son olarak, sql sorgusunu SUBSTRING fonksiyonu içerisine koyduk, böylece baştaki virgül ve boşluk karakterlerinden (“, “) kurtulmuş olduk.