Execution Plan Nasıl İncelenmelidir Part-2

Bir sonraki bölüme geçmeden önce ilk yazıda sql query nasıl çalışır ve execution planda bulunan bir operatörün tooltip detayındaki tanımlar nelerdir bunlardan bahsettikten sonra operatörlere giriş yapma vakti geldi.

Örneklerimizde kullanacağımız iki tablo bulunmaktadır öncelikle o tabloları oluşturalım. Bir cahillik yaptık ve product tablomuzun clustered index’ini oluşturmadığımızı varsayalım.

Tablolarımızı oluşturduktan sonra şimdi operatörlere temelden başlayarak örneklerle devam edelim.

1. Table Scan

Evlerden ırak diyebileceğimiz performans katilidir. Tablo üzerinde hiçbir index bulunmadığı taktirde gerçekleşen scan türüdür. İlgili sorgu çalıştığında bütün tabloyu satır satır tarayarak sonucu elde eder. Çözüm ise belli en azından bir Clustered Index. Varsayarlım Clustered Index attık ve aşağıdaki sorguyu tekrar çalıştıralım.

SELECT Id,Title FROM dbo.Product WHERE UserId=1000

Bu sefer table scan etmedi ama Clustered Index Scan yaptı ve 900 bin satırı teker teker gezdiğinden dolayı aslında sorgunun maliyeti değişmedi.

2)Clustered Index Scan

Primary Key için atılan Clustered index üzerinden scan işlemini gerçekleştirmektedir, sorgunun koşulu index ile uyuşmadığı taktirde burada da satır satır arama yapmaktadır yani Table Scan’den pek bir farkı yoktur. Kısacası yukarıdaki sorgumuzda bize pek yardımcı olmadı. Execution planınızı incelediğinizde engine zaten size missing index uyarısı veriyor olacaktır. Yani sizin koşul olarak belirttiğiniz parametreye özel index oluşturmanız gerekmektedir.

Peki koşulumuz Primary Key üzerinden olsaydı ne olurdu? O zaman bütün tabloyu baştan gezmek zorunda kalmayıp Clustered Index Seek yapacaktı.

3)Clustered Index Seek

Clustered Index Scanden farkı Id(primary key) bazlı bir koşul çalıştığında tabloyu satır satır gezmeden direk ilgili satırları/sayfaları gezmektedir. Böylelikle maliyet gezdiği satır ile doğru orantılı olduğundan dolayı çok çok daha az olacaktır.

Peki Bir önceki sorgumuza dönecek olursak Where koşulumuzda UserId vardı ve buna yönelik bir index atıp tekrar deneyelim.

CREATE NONCLUSTERED INDEX [IDX_Product_UserId] ON [dbo].[Product]([UserId] ASC)

Görüldüğü gibi bu sefer NonClustred Index Seek yaparak sadece ilgili kayıtlara ulaşıp onları getirdi ve böylelikle maliyet çok büyük oranda fark etti. Ama bu sefer Key Lookup sorunumuz var. Şimdi index’i attıktan sonra çıkan plandaki NonClustered İndex Seek ve Key Lookup üzerinden devam edelim.

Dipnot: Net anlaşılmadıysa bu index seek/scan kavramı tam olarak anlamadım diyenler için bir kez daha özet geçeyim. Index olmayan bir tabloda veriler hiç bir sıralama olmadan karışık bir şekilde bulunmaktadır arama işlemi yapıldığında ise her satır tek tek geziliyor ve bundan dolayı maliyet ilgili kayıt bulunana kadar kaç satır gezdi ise onunla orantılıdır.

Peki Index atınca ne oluyor? İndeks verileri belirlenen koşullara göre sıralıyor ve belli bir ağaç yapısında(balance-tree) tutuyor. Bu ağaç yapısı verinin boyutuna göre değişmekle birlikte Non-Leaf ve Leaf Nodelar bulunmaktadır.

Non-Leaf(Root ve Intermediate) Node’larda veri tutulmaz sadece alt node’ların Idleri tutulur ve referanslama yapılır. Leaf Node’larda ise veriler tutulur. Bu yapı sayesinde ilgili kayıtlara ilgili referanslar üzerinden hızlı bir şekilde erişebiliyoruz.

Peki o zaman her sorgu için ayrı bir index oluşturalım bunun bize bir dezavantajı yok mu? Tabi ki de var öncelikle oluşturduğunuz her index aslında birer sanal tablodur yani veri tabanındaki tuttuğunuz veri boyutunuz artacak demektir. Ayrıca her insert işleminde ilgili kayıt index’ler için oluşturulan bu balance tree de doğru leaf’e yerleştirilmelidir. Bu ise insert işlemi için maliyet demektir. Birbirine benzer index’ler attığınızda bu index’ler birbirinin kullanımını da etkiliyor olabilir.

Mesela UserId ve CategoryId ve CompanyId için bir index oluşturalım.

CREATE NONCLUSTERED INDEX [TEST1] ON [dbo].[Product] ( [UserId] ASC, [CategoryId] ASC, [CompanyId] ASC)

Ardından sadece bu koşulları kullanan bir sorgu çalıştıralım. görüldüğü gibi TEST1 indeksini kullandı.

Şimdi ise UserId, CategoryId, CompanyId ve RoleType için TEST2 adında index atalım. Bir önce çalıştırdığımız sorguyu tekrar çalıştıralım ve bu sefer TEST2'yi kullandığını göreceğiz.

Kısacası ikinci atılan index aslında birinci atılan index’i de kapsamaktadır ve böylelikle ilk attığımız indeks boşa çıkmış durumdadır, bundan dolayı sadece fazladan insert işleminde maliyet ve fazladan yer kaplamaktadır.

Özetle index atarken her sorguya özel atılmamalı mümkün olduğu kadar az ve ortak indeksler kullanılması daha iyi diyebiliriz.

4)NonClustered Index Seek/Scan

Bir önceki maddelerde Clustered Index Seek/Scan işlemlerini gördük peki NonClustered index’in farkını açıklayarak başlayalım. Clustered indexler her tablo için sadece bir adet oluşturulur ve Primary Key üzerinden fiziksel dizilimini yukarıda dipnotta açıkladığım şekilde oluşturur. NonClustered indexler ise bir tablo için bir çok oluşturulabilir(max sayı 999). NonClustered index aslında bir sanal tablo gibi hayal edebilirsiniz burada PK bazlı değil sizin belirttiğiniz columnlar’a göre indeksleme yaparak oluşturur. Aradaki farkı belirttikten sonra devam edebiliriz.

NonClustered olarak oluşturduğumuz index’lerin sorgumuzdaki koşula uyması sonucunda NonClustered index seek yapar. Mevcuttaki sorgumuzun koşullarını index tam olarak sağlayamaz ise index üzerinde scan eder yani belli oranda küçülttüğümüz tablo üzerinde satır satır gezer. Aynı zamanda oluşturulan index sorgunun koşullarını karşıladığı taktirde bile index’in belirlenen sütunları sorgudaki output list’ini tam karşılamıyor ise bu indeksi tercih etmez. Peki tek nefeste ne anlatmaya çalıştığımı bir kaç örnekle pekiştirelim.

Öncelikle index scan için şöyle bir örnek verebilirim;
En son Product tablosuna UserId için bir indeks atmıştık. Peki yeni bir sorgumuz olsun ve UserId, CategoryId ve Status üzerinden koşul oluştursun. Böyle bir durumda Execution Plan’ı incelediğimizde En son attığımız index’i kullandığını görürüz ama mevcuttaki index tam olarak koşulumuzu karşılamamaktadır. Bundan dolayı index üzerinde UserId için direk ilgili kayıtlara ulaşsa bile bu daraltılan data arasında CategoryId ve Status için satır satır aramaktadır.

Başka bir örnek ise;

UserId için bir index’imiz mevcut ve şöyle bir sorgu çalıştıralım,

SELECT UserId, CategoryId, Status FROM Product Where UserId=100

bu senaryoda mevcuttaki index’i kullanıp buna ekstradan execution planda Key Lookup yaptığını göreceğiz. Sebebi ise index üzerinde ilgili page leaf’lere gidecek ama CategoryId ve Status bilgisini buradan bulunmadığından dolayı Key Lookup işlemini gerçekleştirerek Id üzerinden Clustered Index’i kullanarak tablodan CategoryId ve Status bilgisini getirecek. Bunu önlemek için mevcuttaki index’imizi aşağıdaki şekilde değiştirmemiz yeterli olacaktır.

CREATE NONCLUSTERED INDEX [IDX_Product_UserId] ON [dbo].[Product]([UserId] ASC) INCLUDE( CategoryId,Status)

Ama bir başka sorguda SELECT bölümünde CategoryId, Status, UserId dışında başka bir bilgi istenirse tekrardan key lookup yapması gerekecek bundan dolayı bu tarz ortak kullanılacak indekslerde INCLUDE yapılacak sütunları doğru bir şekilde seçip ortaklaştırmamızda fayda var.

Dipnot2: İyi güzel de bu include tam olarak ne oluyor? Include oluşturduğumuz indexlerde nonkey value’lar diyebiliriz. Include ile belirttiğimiz column’lar sadece leaf node’larda bulunurlar yani istenilen veriye ulaşılırken kullanılmaz, ulaşıldıktan sonra ilgili index key’leri ile birlikte bu belirtilen columnlarda output olarak verilir.

5)Key Lookup

Aslında yukarıdaki örneği vermemin sebebi Key Lookup örneğini de aradan çıkarmaktı. Tekrar neden kullanıldığını açıklamak gerekir ise; ilgili sorguya uyan index kullanıldığında ve bu index sorguda istenilen sütunları barındırmıyor ise KeyLlookup işlemi gerçekleştirir. Key Lookup şöyle bir yardımda bulunur, index seek sonucu bulunan kayıtların Primary Key’leri sayesinde tabloda o kayıtlara ulaşıp istenilen sütunların datalarını getirmektedir. Execution plana bakıldığında maliyetin büyük bir çoğunluğu bu aşamada harcanmakta olacaktır.

Birde bu işleme bağlı olarak nested loops gerçekleştirilmektedir ileride değineceğim.

Bu maliyetten kurtulmanın yolu ise kullanılan index’imizde doğru sütunları include etmektir.

6)RID Lookup

Varsayalım bir cahillik yaptık ve oluşturduğumuz tablo ile birlikte Clustered Index’imizi oluşturmadık. Daha demin oluşturduğumuz senaryoyu tekrar düşünelim bir sorgumuz çalışıyor, NonClustered index’i kullanıyor ama sonuç olarak belirttiğimiz sütundan index’te yok bundan dolayı Key Lookup yapacak ama bir bakıyoruz clustered yani PK üzerinden oluşturulan index tablosu yok. Peki Sql Engine ne yapıyor o sırada hemen PK üzerinden sanal bir tablo oluşturuyor ve Key Lookup yapıyor. Tahmin edeceğiniz üzere maliyeti çok çok fazla ama karşılaşma şansınız çok zor.

Serinin bir sonraki yazısına buradan devam edebilirsiniz.