Bir programcının dünyası

İstatistikler, verinin veritabanı nesnelerinde (tablo, index, vs) nasıl dağıldığı bilgisini içerdiği için, veritabanında en önemli nesnelerden biridir.

Veritabanı Yöneticilerinin (Database Administrators) eskimiş istatistiklerden şikayetlerini çok duydum. Yapılması gereken istatistikleri güncellemektir.

Eğer bir tablodaki indexlere ait istatistiklerin son güncelleme zamanını öğrenmek istiyorsanız, aşağıdaki sql sorgusunu çalıştırabilirsiniz;

SELECT
	NAME AS INDEX_ADI,
	STATS_DATE(OBJECT_ID, INDEX_ID) AS SON_GUNCELLEME
FROM
	SYS.INDEXES
WHERE
	OBJECT_ID = OBJECT_ID('HumanResources.Department')

Eğer tüm indexler için aynı bilgiyi almak istiyorsanız, WHERE filtresini kaldırmanız yeterli.

Benim test bilgisayarımda aldığım sonuç;

PK_Department_DepartmenID 2010-01-20 15:30:17:200
AK_Department_Name 2010-01-20 15:30:26:053

Eğer istatistiklerinizin zamanı geçmiş olduğunu düşünüyorsanız ve güncellemek istiyorsanız, aşağıdaki sql komutunu çalıştırabilirsiniz;

UPDATE STATISTICS HumanResources.Department

Bu komutu çalıştırdığınızda ilgili tablonun istatistik verilerinin güncellendiğini göreceksiniz. Eğer komutu WITH FULLSCAN komutu ile birlikte çalıştırırsanız, tablodaki tüm kayıtlar baştan taranacak ve istatistik baştan oluşturulacaktır.

UPDATE STATISTICS HumanResources.Department
WITH FULLSCAN

Dikkat : İstatistikleri güncellemek için yukarıdaki komutları, sadece istatistiklerinizin gerçekten eskimiş olduğuna inandığınızda, sorgularınız için oluşturulan query plan’ların hatalı olduğunu gördüğünüzde ve sql server’ın yoğun olmadığı zamanlarda çalıştırmanızı tavsiye ederim.

Kendi test bilgisayarımda, istatistik güncelleme komutunu çalıştırdıktan sonra, aşağıdaki sonuçları elde ettim;

PK_Department_DepartmenID 2010-01-30 21:10:06:560
AK_Department_Name 2010-01-30 21:10:06:594

Gördüğünüz gibi, ilgili tablodaki indexlerin istatistikleri güncellendi.

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.

Database’de şu scripti çalıştıralım;

CREATE TABLE T_PERSONEL
(
  ADSOYAD VARCHAR(50),
  DOGUM_TARIHI SMALLDATETIME
)

INSERT INTO T_PERSONEL VALUES ('Engin POLAT',  '1981-01-12')
INSERT INTO T_PERSONEL VALUES ('Emre ERKAN', '1979-03-14')
INSERT INTO T_PERSONEL VALUES ('Ozan ÇAĞLARGİL',  '1982-09-27')
INSERT INTO T_PERSONEL VALUES ('Fatih DURGUT',  '1978-06-23')
INSERT INTO T_PERSONEL VALUES ('Burhan İNEGÖL',  '1980-11-08')

Aşağıdaki stored procedure’i yazalım.

CREATE PROCEDURE PR_PERSONEL_LISTE
(
  @SIRALAMA INT
)
AS
BEGIN
  SELECT ADSOYAD, DOGUM_TARIHI FROM T_PERSONEL
  ORDER BY
    CASE @SIRALAMA
      WHEN 1 THEN DOGUM_TARIHI
      WHEN 2 THEN ADSOYAD
    END
END

Bu procedure’ü 1 parametresi vererek çalıştırıyoruz, ve beklediğimiz sonucu alamıyoruz.

Nerede hata yaptık? (İpucu; parametre olarak 2 vermeyi deneyin)

Yorumlarınızı bekliyorum…

Sql Server’da Identity kolon içeren tablolar ile çalışırken, yeni üretilen identity değerine ihtiyacımız olur.

@@IDENTITY, SCOPE_IDENTITY() ve IDENT_CURRENT() aynı işi farklı yöntemlerle yapar; son üretilen identity değerini döndürmek.

SELECT @@IDENTITY
Açılmış olan bağlantıda son üretilen identity değerini döndürür. @@IDENTITY tablo ve scope bakmaksızın, connection’da üretilen son identity’yi verir. Dikkat : Eğer Insert yaptığınız tablo’da Trigger varsa, yanlış identity alabilirsiniz.

SELECT SCOPE_IDENTITY()
Açılmış olan bağlantıda ve sorgunun çalıştığı scope’ta son üretilen identity’yi döndürür. Trigger kullanılan tablolarda @@IDENTITY yerine SCOPE_IDENTITY() kullanılması tavsiye edilir.

SELECT IDENT_CURRENT(tablename)
Bağlantı ve scope bakmaksızın, parametre olarak verilen tabloda üretilen son identity değerini döndürür.

Örnek;

CREATE TABLE TEST_TABLO_1
(
	ID INT NOT NULL IDENTITY (1, 1),
	ACIKLAMA VARCHAR(500) NULL,
	BILGI VARCHAR(1000) NULL
)

CREATE TABLE TEST_TABLO_2
(
	ID INT NOT NULL IDENTITY (1, 1),
	ACIKLAMA VARCHAR(500) NULL,
	EKSTRA_BILGI VARCHAR(1000) NULL
)

GO

CREATE TRIGGER TRG_TEST ON TEST_TABLO_1
FOR INSERT
AS
INSERT INTO TEST_TABLO_2
	(ACIKLAMA, EKSTRA_BILGI)
VALUES
	('Açıklama', 'Extra Bilgi')

GO

INSERT INTO TEST_TABLO_2 (ACIKLAMA, EKSTRA_BILGI) VALUES ('DENEME', 'TEST1')
INSERT INTO TEST_TABLO_2 (ACIKLAMA, EKSTRA_BILGI) VALUES ('DENEME', 'TEST2')
INSERT INTO TEST_TABLO_2 (ACIKLAMA, EKSTRA_BILGI) VALUES ('DENEME', 'TEST3')
INSERT INTO TEST_TABLO_2 (ACIKLAMA, EKSTRA_BILGI) VALUES ('DENEME', 'TEST4')

INSERT INTO TEST_TABLO_1 (ACIKLAMA, BILGI) VALUES ('DENEME', 'TEST1')

SELECT @@IDENTITY
UNION ALL
SELECT SCOPE_IDENTITY()
UNION ALL
SELECT IDENT_CURRENT('TEST_TABLO_1')

GO

DROP TRIGGER TRG_TEST
DROP TABLE TEST_TABLO_1
DROP TABLE TEST_TABLO_2

Sorgunun çıktısı;

Gördüğünüz gibi, SELECT @@IDENTITY çalışan Trigger’dan etkilenerek yanlış sonucu döndürmüştür.

TEST_TABLO_2‘ye ilk eklediğimiz kayıtta, identity olarak 5 aldık.

SELECT SCOPE_IDENTITY() ise, doğru değer (1) döndürdü.

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