SQL Server Sorgu Performanslarının İzlenmesi

Bu yazıda yazdığımız SQL sorgularının performansını nasıl kontrol edebileceğimizi anlatacağız. Performans dediğimizde aklımıza hız gelir. Bu nedenle hemen herkes yazdığı sorgunun çalışma süresini kıyaslayıp performanslı olup olmadığı karar vermeye çalışır. Oysaki süre çalıştığınız ortamların özelliklerinden etkilenir. Serverın CPU, Memory özellikleri, çalıştığınız bilgisayarın donanımsal özellikleri hatta aradaki network bile süreye etki etmektedir. Yazılım geliştirme ortamları ile üretim ortamları birbirinden çok farklı olmasından dolayı süre kıyaslamaları performans testleri için doğru bilgi vermez. Bu anlattıklarımızı demo ile gösterelim.

Sorgumuz 1796 ms. sürdü. Aynı sorguyu tekrar çalıştıracağız.

Sorgu aynı olmasına rağmen ikinci kez çalıştırdığımda 1 .21 0 ms. sürdü. Hiç bir değişiklik
yapılmamasına rağmen ikinci kez çalıştırdığımızda daha hızlı sonuç getirdi. Sizin de ilk
çalıştırdığınızda yavaş, sonrakilerde hızlı çalışan sorgularınız olmuştur. Bu fark neden
kaynaklanmaktadır?
Cevabı çok basit; ilk çalıştırdığımızda veriler diskte duruyordu. SQL server verileri diskten
okuyup önce server memorisine taşıdı sonra sonucu getirdi. Sonraki çalıştırmada ise doğrudan memoriden okuyup sonucu getirdi.
Bu nedenlerden dolayı SQL sorguları performansı kıyaslamak için süreye(Duration) değil,
sorgunun okuma(read) yaparken ne kadar IO yaptığına bakacağız. Sorgu ve tablolardaki kayıt sayıları aynı kalmak şartıyla, IO ortamlardan bağımsızdır ve her koşulda aynı sonucu verir.

IO İstatistik Bilgilerinin Gösterilmesi

IO miktarını görmek için sorgumuzdan önce aşağıdaki SQL komutunu çalıştırıyoruz.
set statistics io on

Aynı sorguyu tekrar pes peşine 2 kez çalıştırdım. Süreler daha öncekilere yakın değerlerde;
1733 ms. ve 1273 ms. olarak gerçekleşti. Sorgu sonucunda yapılan IO miktarlarını da
görebiliyoruz. Bizim için önemli olan yer “logical reads” yani memoriden okumaları gösteren  kısımdır. Her iki sorguda da 32.980 olarak görünüyor. İlk sorguda “physical reads” ve “readahead reads” değerleri de var. Bunlar yukarıda bahsettiğim gibi diskten okumaları
göstermektedir.
Peki, 32980 neyi ifade eder? Okunan page miktarıdır ve aslında bu OLTP sistemler için yüksek
bir değerdir.

3 numaralı sorguda tablodaki kayıt sayıları bulurken bile daha az IO yapıldığı görülmektedir.
Tablonun tamamının okunması ise yalnızca 3 kat fazla IO ya neden olmaktadır.
Eğer sorgumuz düzgün olarak index kullanılıyor olsa aşağıdaki gibi tek haneli veya 3 haneli IO
miktarları görmemiz beklenir

Yalnızca 3 adet IO ile sorgu sonucunu getirmektedir.

Serverın Harcadığı Süre ve CPU Kullanımının Gösterilmesi

IO ile birlikte SQL server işlemi yapabilmek için CPU da tüketir. Ne kadar CPU kullandığı
aşağıdaki komut ile görebiliriz.
set statistics time on

Bu sorguyu çalıştırırken CPU süresi 1203 ms. olmuştur. Core sayısı yüksek olan serverlarda
paralel execution yapılabilmesi durumunda “CPU time” ile “Elapsed time” arasında belirgin
farklar olmaktadır. Buradaki sorgumuz basit olmasından dolayı iki değer birbirine çok yakın
olmuştur.

Primary Key Kullanım Amaçları ve Tanımlanabileceği Durumlar

Üniversite veri yapılarını dersininde hocamız Primary Key ne işe yarar diye
sorduğunda, arkadaşımız “silme” işine diye cevap vermişti. 

Veri tabanında silme
işlemi neredeyse hiç yapmayız. İşi biten kayıtı silme yerine tabloya durum veya
aktif kolonu ekleyip, bu kolonlar üzerinden güncel ve eski kayıtları takip
ediyoruz. Silme işlemi yok denecek kadar az olduğuna göre yine olayı
basitleştirmek için diyebilirim ki; primary key “update” için kullanılır.

Primary key 

Tablodaki her bir kayıtın ayrıştırılmasını, tekilleştirilmesini sağlayan
bir kısıttır. Neden kayıtları tekilleştirmek isteriz? Bence bunun en önemli nedeni,
silme ve güncelleme işleminde sadece istediğimiz kayıt üzerinde işlem
yapmaktır. Örneğin yanlışlıkla aynı insert işlemini iki veya daha fazla
calıştırdığımızı düşünelim. Bu durumda sadece tek kayıtın kalmasını, diğerlerinisilmeyi isteriz. Bunu nasıl yapacağız? Primary key ile daha doğrusu primary key
kolonu ile. Kayıtların tek olmasının daha sık işimeze yaradığı husus ise, row bazlı
lock işlemi yapabilmektir. Lock ve row lock için yayınlanmış olan
“Lock, Blocking, Deadlock Nedir” konusuna bakılabilir.


Primary key için ikinci bir hususta, diğer bir tablodan bu tabloya referans verip
iki tablo arasında integratiyi korumaktır. Yani foreign key tanımlayabilmektir.
Primary key’in kullanım amaçlarını anlattıktan sonra diyebiliriz ki;

1. Bu tabloda delete veya update yapacak mıyım?
2. Bu tabloya başka tabloları bağlayacak mıyım, referans verecek miyim?

Eğer bu iki sorunun cevabıda hayır ise tabloya primary key tanımlamak
anlamsızdır. Bunun tersi doğru değildir, yani ikisinden birinin cevabı evet olsa
bile primary key tanımlamak gerekir anlamı çıkartılamaz. O halde diyebiliriz ki,
sadece insert ve select yapılan tablolar için primary key konulmaması daha
isabetlidir.

Primary key kolonları tekil(unique) olmak zorunda olduğundan dolayı, eğer
tabloda böyle bir kolon veya bir kaç kolonun birleşimi ile elde edilebilen tekillik
sağlanamıyorsa Unique bir kolon eklenmelidir ki; buna auto incremental veya
identity alan deriz.

Lock, Blocking, Deadlock Nedir?

Veri tabanları ACID(https://en.wikipedia.org/wiki/ACID) olmalıdır. Bu özellikleri sağlayabilmek için kullanılan mekanizmalardan bir tanesi de LOCK mekanizmasıdır. Birbirininden farklı lock tiplerini bulunur; okuma, yazma gibi. Lock tiplerini birbiri ile uyumlu ve ya uyumsuzdur. Örneğin okuma birbirlerini ile uyumlu iken yazma uyumsuz olabilir. Daha açık olması için, bir kaydı aynı anda iki proses okuyabilirken, aynı anda iki proses güncelleyemez. Buradan anlaşılabileceği gibi okuma işlemlerini yani select cümlelerini de sisteme lock koyar.

Lock konusunda diğer bir husus ise, lock seviyesidir. Seviyeden kasıtı, row, page veya tablo diye özetleyebiliriz. Örnek verelim, insert işlemi sadece o kayıtı locklar, bu sırada aynı kayıta okuma gelirse lock olduğundan dolayı insert işlemi tamamlanana kadar bekler. Yine tek bir kayıtı güncellediğinizi(update) düşünelim. Bu durumda sadece o kayıt mı locklanır? Bilemeyiz, eğer güncelleme işlemi index kullanmıyorsa tüm tabloyu bile locklamış olabilir. Buradan açıkca anlaşılacağı gibi, o tablodan okuma, yazma yapacak olan tüm prosesler güncelleme işleminin bitmesini bekleyecektir.

Lock işlemini anlatırken, bekleme konusu geçti. Veritabanında Wait dediğimiz beklemeler oluşur. Bekleme sebepleri farklı farklıdır, lock sadece bunlardan bir tanesinidir. Memory, CPU,disk hatta network gibi kaynaklar kullanımlarında dolayı beklemeler oluşabilir. Örneğin 20 core bir makinede aynı anda 20 den fazla proses işlem yapmak isterse ne olur? Yine okuma hızı düşük bir disk sisteminden büyük miktarda veri okursanız ne olur? Veri tabanı sistemi, okuma işlemi bitene kadar bekleyecektir. Bu tip beklemelere IO_wait denir.
Lock ve wait konularını açıkladıktan sonra blocking’i açıklayabiliriz. Her hangi bir sebepten
ötürü, bir proses başka bir prosesi bekliyorsa, bu işleme blocking denir. Aslında sistemde lock var yanlış bir terimdir. Sistemde her zaman lock olur, tehlikeli olan lock’ın blocking’e sebep olmasıdır.

Deadlock ise, karşılıklı iki prosesin birbirini beklemesidir. Basitce örnekleştirelim, 2 kayıtlı
tabloda ilk proses 1 nolu kayıtı güncellemiş 2. kayıtı güncelleyecek, diğer proses ise 2 nolu kayıtı güncellemiş 1 nolu kayıtı güncelleyecek. Bu durumda iki proses birbirini sonsuza kadar beklemesi gerekir. Bu durum oluştuğunda veri tabanı sistemi fark eder ve içlerinden birini kurban olarak seçip sonlandırır

Özetle blocking ve deadlock’ın sebebi lock’lardır.
CPU, memory vs gibi hususlardan kaynaklanan wait ve blocking’lere developerların müdahale sanşı yoktur. Developerlardan beklenen; locking süresini münkün olduğunca minimumda tutmaktır. Bu da kapsamı münkün olduğunca dar tutmak yani transaction bloğunu küçültmek ve doğru index kullanarak değdiğimiz(lock koyduğumuz) kayıtların sayısını minimumda tutmakla olur.