SQL Sorgu Optimizasyonuna Yardımcı Araçlar

Sorgu optimizasyonu yaparken, her değişiklikten sonra performans bilgisini gözlemleme işlemini tekrarlamak gerekir. Bu gözlem, yapılan değişikliğin performansı iyi mi kötü etkilediğini belirlemeye yardımcı olur.

1. Elapsed Time

Bir sorgunun çalışmak için aldığı zaman uzunluğu ‘Elapsed Time’ olarak adlandırılır. Sorgunun tamamlanma süresini birçok şey etkileyebilir. Tamamlanma süresi en iyi değerlendirme ölçüsüdür, çünkü bu değere kullanıcının sorgu çalışırken bekleyeceği zamandır. Tamamlanma süresini görebileceğimiz ilk yer SQL Server Management Studio’ da sorgu penceresidir. Aşağıdaki gibi görülür.

Tamamlanma süresini görebileceğimiz diğer seçenek SET STATISTICS komutunu kullanmaktır. Aşağıda bu komut ile çalıştırılmış sorgu ve sonuç penceresi görünüyor. 

Yukarıdaki örnekte SELECT deyiminden önce ‘SET STATISTICS TIME ON’ komutunu kullandık. Sonuç penceresinde ‘Elapsed Time’ bilgisi yanı sıra ‘CPU Time’ bilgisi ve PRINT deyiminin değeri bulunuyor. ‘SET STATISTICS TIME ON’ deyimi, her deyimin parse, compile ve execute işlemlerinin aldığı zamanı görüntüler. Bu deyim az sayıda komut çalıştırılacağı zaman faydalıdır. Eğer çok sayıda satır çalıştırılıyorsa okunması zor bir sonuç elde edilebilir.

2. CPU Time

Bir diğer önemli performans ölçümü CPU miktarıdır. Tamamlanma süresini görüntülemek için de kullandığımız ‘SET STATISTICS TIME ON’ komutu ile her deyimin CPU zamanını da görüntüleyebiliriz. Bu CPU tüketimini ölçmek için ilk yöntemdir.

SQL Server CPU tüketimini ölçmek için kullanılan @@CPU_BUSY sistem değişkenini sunar. @@CPU_BUSY sistem değişkeni SQL Server başlatıldığından itibaren olan çalışmak için harcanan süreyi döndürür. Sonuç CPU zaman artışıdır ve tüm CPU için kümülatiftir, böylece aktif tamamlanma süresini aşabilir. @@TIMETICKS değişkeni ile çarpılarak mikro saniye birimine dönüştürülebilir.

Aşağıdaki örnekte döngülerin çalışma süreleri SQL Server CPU miktarının @CPU_BUSY kullanımı ile ölçen yöntem bulunuyor. @@CPU_BUSY değişkeni SQL Server start edildiğinden itibaren serverda sorgu çalıştıran tüm kullanıcıların ve sistem sorgularının CPU miktarını barındırır. Bu yüzden bu yöntem, sistemde bir komut çalıştırsak ve arka planda başka sorgu çalışmıyorsa doğru sonuç verir.

Yukarıdaki örnekte görüldüğü gibi 1 ve 2. döngü CPU miktarını milisaniye biriminden hesapladık. Bu sorguyu farklı zamanlarda her çalıştırdığımda farklı sonuçlar alabilirim. SQL Server’ da çalışan diğer işlemler bu metoda etki eder. Bu metot ile aktif CPU kullanımı hesabı çok doğru bir sonuç vermeyebilir ancak aktivitesi az olan bağımsız sistemlerde işe yarar bir yöntemdir.

SQL Server Profiler kullanarak CPU ölçümü yapmak daha kesin bir yöntemdir. Yukarıda çalıştırdığımız sorgunun Profiler görüntüsü aşağıda görüleceği gibi CPU kolonunda Harcanan CPU miktarı bulunur. CPU değerini görüntülemek için SQL: BatchCompleted eventi kullanarak çalıştırmak gerekir. SQL Profiler sabit olarak süreleri milisaniye olarak görüntüler. ‘Tools/Options’ menüsündeki bir seçenek ile mikro saniye olarak görüntülenecek şekilde değiştirilebilir. Bu metot ile örnek sorgumuzdaki her döngünün miktarını değil sorgunun bütünüyle tamamlandığı miktarı elde ederiz.

3. I/O Usage

Diğer performans ölçüleri gibi I/O miktarını da çeşitli TSQL deyimleriyle sorgulamak mümkündür. SQL Server’ da 2 çeşit I/O yolu vardır: Logical ve Physical I/O. Logical I/O hafızadaki tampon alandan işlem gören veriden sorumludur. Physical I/O, SQL Server’ ın depolama için kullandığı fiziksel diskten direk olarak veri erişimi ile ilişkili giriş/çıkışlardır. Fiziksel I/O, işlemler daha uzun zaman aldığı için daha maliyetlidir. I/O genel olarak SQL deyiminin performansına tek başına etki eder yani maliyetlidir. Buna göre sorgu tasarlarken oluşacak result set üreten physical ve logical I/O operasyonlarının sayısını küçültmek gerekir.

TSQL deyimleriyle I/O miktarını görüntülemenin ilk yolu, ‘SET STATISTICS IO ON’ deyimini kullanarak I/O istatistiklerini açmaktır. SQL deyiminin sonucuyla birlikte bu bağlantı süresince SQL deyimine karar vermek için kullanılan I/O sayısını çıktı olarak döndürecektir.

Yukarıdaki örnek sorguyu çalıştırdığımızda Logical Read 34 Physical Read 3 olarak gerçekleşti. Şimdi aynı sorguyu ikinci kez çalıştırıp aşağıdaki sonuca bakalım:

Yukarıda çıkan sonuca göre sorgu ikinci kez çalıştığında TSQL deyiminin sonucunda Logical Read 34 Physical Read 0 olarak görünüyor. Çünkü bu sorgun verileri tutan database pageleri zaten buffer pooldadır. Performans arttırmak için yapılan testlerde tekrarlayan işlemler yapıyorsak, bu I/O miktarını buffer pooldan okuması işlemini ortadan kaldırmalıyız. Bunun için de “DBCC DROPCLEANBUFFERS” komutunu sorgularımızdan önce çalıştırmalıyız. Bu işlem sorguların SQL Server stop start işlemi yapmadan temiz buffer pool ile çalışmasına imkan sağlar.

Serap PARLAK