SQL Server Execution Plan Nedir? -2

Merhabalar,

İlişkisel veritabanı kavramı ile özdeşleşmiş olan SQL Server, üzerinde birden fazla tabloda işlem yapmamıza olanak sağlayarak işimizi oldukça kolaylaştırmaktadır. Bunu iki ayrı tablodan ayrı ayrı birer kolon getirerek yapabileceği gibi iki ya da daha fazla tablodaki tüm kolonları da getirerek de bir rapor oluşturmamıza olanak sağlayabilir. Burada bu işlemleri join operatörleri ile gerçekleştirip, istediğimiz sonuçları elde edebiliyoruz.

Fakat bu kolaylığı bize sağlarken elbette bazı hususlara dikkat etmemiz gerekiyor. Örneğin, her kolona gerçekten ihtiyacımız var mı ? Binlerce hatta milyonlarca satır verinin tüketeceği I/O ya da CPU değerlerini göze alarak istediğimiz gerçekten de tüm veri mi yoksa sadece bir bölümüne mi ihtiyacımız var ?

Bir önceki yazımda(Execution Plan Nedir?) SQL Server’da execution plan oluşturulurken sorgular için bir maliyet hesaplaması yapıldığını ve minimum maliyet belirlenerek sorgu sonucu getirildiğini aktarmıştık. SQL Server arka planda minimum maliyet prensibi çerçevesinde ilerlese de bazı durumlarda performans canavarı olabilmektedir. Örneğin kurulum esnasında RAM miktarı belirtilmezse sunucu kaynaklarını sonuna kadar kullanacaktır. Dolayısıyla SQL Server’ı kullanırken kontrol altında tutabilmemiz için sorgularımızı ona göre ayarlamalı mümkün oldukça küçük veri kümeleri ile işlem gerçekleştirmeliyiz.

Bu küçük verilerle işlem gerçekleştirmek istediğimizde sorguyu hazırlar ve bunu SQL’e iletiriz. O da bu isteği değerlendirir, derler ve bize en kısa sürede verileri döndürmek için bir plan belirleyip sonuçları bize yansıtır. İşte bu veriler birden fazla tablodan geldiğinde arka planda neler yapılıyor, hangi durumlarda neyi tercih ediyor bu yazımızda bunu inceleyeceğiz.

Öncelikle “ Birden fazla tablo+sorgu = Join işlemleri “ diyebileceğimiz bir yapıda, çeşitli değişkenler vardır. Bu değişkenlerden biri ya da birkaçında geçirilen uzun süreler sonucunda sorgumuz yavaş gelecektir. İşte bu Join işlemlerinde sorgu neden geç geliyor sorusunun cevabına erişebilmek için öncelikle sorguyu incelememiz ve nasıl bir yol izlediğini öğrenmemiz gereklidir. Sorgunun yavaşlığını incelemek için sorguyu anlamak, sorguyu anlamak için izlediği yolu görmek, izlediği yolu görmek için execution plan incelemek ve son olarak execution plan incelemek için ise Indexleri anlamak gerekir.

Detaylarda boğulmadan haydi biraz Indexleri inceleyip sonrasında Execution Plan’da Join operatörlerine bakalım.

SQL Server’da indexler B-Tree(Balanced Tree) yapısına uygun olarak oluşturulurlar.

Nedir bu B-Tree?

B-Tree yapısı, Root–Intermediate–Leaf Node olarak 3 katmanda incelenebilir. Üst iki dalları genel olarak non-leaf node olarak adlandırılır. Alt katmanlara erişim için ID tutulur. En altta yer alan kısım ise leaf node olarak isimlendirilir. Verinin kendisi ya da yolu burada tutulur. Non-Leaf nodelarda ise herhangi bir veri tutulmaz. Oluşturulan indexin Clustered ya da NonClustered index oluşuna göre bu nodelarda farklı bilgiler tutulmaktadır.

Clustered Index ;

Clustered index bilindiği üzere tablolarımızda sadece bir adet oluşturulabilmektedir. Bunun sebebi ise oluşturulan kolona göre disk üzerinde veriyi mantıksal olarak sıralamasıdır. Bu sebepten dolayı fiziksel index olarak da bilinir. Veriler fiziksel olarak sadece bir kere sıralanabileceği için, tablo üzerinde en fazla 1 adet oluşturulabilir. Veri katmanı diyebileceğimiz ve en alt katman olan Leaf Nodelerda ise direkt olarak verinin kendisi tutulur.

Bu kısımda değinmek istediğim bir diğer kısım ise, Unique kavramıdır. Bir Clustered index oluşturulurken, bu indexin Unique olup olmaması performans anlamında farklılıklar gösterecektir. Örneğin, bir clustered index oluşturduk fakat oluştururken Unique olarak işaretlemedik. Bu durumda SQL Server bu indexler üzerinden arama yaparken doğru bir şekilde arama yapabilmek için bu alanı kendisi Unique hale getirir. Bunun için de arka planda bu index keyleri tekilleştirmek için 4 byte büyüklüğünde UNIQUIFIER key tutacaktır. Bu da her ne kadar ufak değerler olarak gözükse de büyük tabloların olduğu bir veri tabanında index boyutunu artırarak disk kapasitesine etki edebilir.

Bir clustered index oluşturulmak istenildiğinde identity şeklinde (otomatik artan) belirlenmiş kolona tanımlanması önemlidir. Çünkü bu tabloya veri eklendikçe ilgili kolondaki Id numarası da belirlemiş olduğumuz sayı çerçevesinde artacak ve bir bakıma Unique olmuş olacaktır. Tabi ki bu kolon üzerinde oluşturulan index için, Index oluşturma penceresinden Unique olarak işaretlenmiş olduğunu doğrulamayı da unutmamak gerekir.

Tüm bu sebepler dolayısıyla tablolarımıza otomatik artan(identity) bir kolon tanımlıyoruz. Bu kolon üzerinde PK olarak işaretlenmiş otomatik artan bir yapıya sahip olarak, Unique index belirlemiş ve 4 byte boyutunda ekstra bir key oluşturmamış oluyoruz. Bu da hem disk kapasitesi anlamında bize fayda sağlıyor hem de Clustered index yapısı itibariyle leaf node kısmına eriştiğimizde direkt olarak veriye ulaşıyoruz. Bu sebeple sorgularımızı Clustered index üzerinden getirecek şekilde düzenlemek bize performans kazandıracaktır.

Tabi ki Clustered Index bu kadar avantaj sağlarken dikkat edilmesi gereken bazı kısımları da mevcuttur. Örnek vermek gerekirse, Clustered Index tanımlı otomatik artan olarak işaretlenmemiş bir kolonumuz mevcut olsun. Bu kolon üzerinde Update komutunun sık sık kullanıldığını düşünelim. Eğer bu kolon sık sık update alıyorsa index üzerindeki veriler de bu update işlemine göre yeniden sıralanmalıdır. Üstelik bu sadece Clustered  Index ile kalmayıp tablo üzerindeki NonClustered Indexlerin de güncellenmesine sebep olacaktır. Çünkü NonClustered Index kendi index bilgilerinin dışında bir de Clustered Index’e erişebilmek için kendi üzerinde bir key tutar. Yani bir bakıma bağlıdır. Bu sebeple sık sık update alan kolonlara Clustered Index tanımlamak uzun vadede sağladığı faydadan ziyade ekstra iş yükü getirecektir.

Bir diğer index olan NonClustered Index yapısını inceleyelim.

NonClustered Index de Clustered Index gibi B-Tree yapısına göre şekillenmektedir. Burada asıl önemli nokta NonClustered Indexte verinin kendisi değil, yolunun tutulduğudur. Yani leaf nodelar üzerinde verinin direkt kendisi değil Clustered Index üzerinden veriyi getirmesi için Row Locator olarak Clustered key tutulur. Eğer tablo üzerinde Clustered Index tanımlı değilse (Heap) benzer şekilde bir Row Locator barındırır. Fakat bu kez Clustered Index Key yerine Row ID (RID) tutulur. Eğer diğer yazımı okuduysanız orada NonClustered Index kullanıldığında diğer kolonları getirirken Clustered Index yoksa RID Lookup yapıldığından bahsetmiştik.

NonClustered Index için bir diğer kavram ise Include kavramıdır. Hazırladığımız sorgularda NonClustered index kullanıldığında diğer kolonlar için Key Lookup yapmasını engellemek amacıyla o kolonların da NonClustered indexe dahil edilmesidir. Bu şekilde veriye daha hızlı erişim sağlayabiliriz. Include kullanımının bir diğer avantajı ise non-leaf node kısmına etki etmemesidir. Yani non-leaf node üzerinde veri boyutunda büyüme olmadan, leaf node üzerinde çözümlenir. Leaf node üzerinde bu key bilgileri tutularak veriye erişim sağlanır. Clustered index kullanımında ise leaf nodelarda direkt veriye ulaşıldığı için sorgu sonucunu getirir. Bu sebeple Clustered Index üzerinde Include kavramı yoktur.

Indexler konusunda son olarak bir konuya daha değinmek istiyorum. Sorgularda NonClustered Index kullanılırken bazı durumlarda Clustered Index kullanımının da olduğundan ve bunu Clustered Index key üzerinden yaptığını aktarmıştık. Bu Clustered Index key değerleri eğer Unique olarak işaretlenmemişse yine benzer şekilde bunları tekilleştirmek için 4 byte boyutunda UNIQUIFIER kullanımı olacaktır.

Yukarıda da bahsetmiş olduğum üzere Execution Plan için Index kullanımı önemli bir konudur. Index için daha fazla detaya girmeden artık Execution Plan incelerken karşımıza çıkan Join operatörlerine geçiş yapabiliriz.

JOIN Operatörlerini,

1. Hash Match
2. Nested Loops Join
3. Merge Join

olmak üzere üç farklı başlık altında inceleyeceğiz.

Hash Match

Eğer iki tablo da çok büyükse ve veriler sıralı değilse performans açısından pek sağlıklı olmayacaktır. Fakat tablolardan birisi küçük ise daha performanslı çalışacaktır. Sebebi ise, büyük tablodaki verilerin daha küçük bir veri kümesi üzerinde gezerek eşleştirme yapacak olmasıdır.

Örnek vermek gerekirse küçük tablomuz 1000 satır büyük tablomuz 100.000 olsun. Daha küçük tablo olarak 1000 satırlık tabloyu seçtiğini ve hash table oluşturduğunu düşünelim.100.000 satır veri, bu 1000 satır üzerinde eşleşme arayacaktır. Fakat küçük tabloda 1000 değil de 10.000 satır olsaydı bu sefer büyük tablodaki 100.000 satır verimiz 10.000 veri üzerinde eşleşme arayacaktı. Bu da yüksek I/O kullanımı ve yavaş sorgu olarak bize geri dönecekti.

Nested Loops Join

Bu join operatöründe ise joinlediğimiz tablolar için birleştirme sütunları üzerinde index tanımlı ve iki tablo arasında veri sayısı farkı fazla ise ya da nispeten daha küçük tablolarda karşılaşırız.

Arka planda tablolarımızdan biri outer diğeri ise inner olarak belirlenir. Outer tablosu üzerinden seçilen bir veri ,inner tablosunun üzerindeki her veri için eşleşen kaydı arar. Kayıt eşleşiyorsa bu eşleşme alınır ve başka eşleşen var mı diye aramaya devam edilir. Outer tablosu üzerindeki bir veri Inner tablosu üzerinde tüm satırları taradıktan sonra işlem sonlanır. Daha sonra Outer tablosundaki diğer kayda geçer. Bu işlem outer tablosundaki tüm verilerin inner tablosunda arama yapmasıyla son bulur. Eşleşen kayıtlar alınarak bir sonraki execution plan operatörüne aktarılır.

Bu operatör için güncel statisticslerin olması oldukça büyük öneme sahiptir. Execution plan hesaplanırken tablolar üzerindeki veri sayılarının hatalı olması, Outer ve inner olarak belirlenen veri kümelerinde eşleşen kayıtları ararken performans kaybına yol açacaktır.

Bir önceki başlık olan Hash Match operatörü için gösterilmiş olan sorguda, birleştirme kolonlarına Clustered Index tanımlayalım ve sonucu görelim.

Görüldüğü üzere bu kez aynı sorgu için Nested Loops operatörü kullanılarak sonuç getirildi. Bu sorgu için Table_1 üzerinde 1000 kayıt Table_2 için 100.000 kayıt vardı. İki tablo arasındaki veri büyüklüğü oldukça fazla görünüyor.

Peki iki veri seti arasında bu kadar fark olmasaydı ? Table_1 üzerinde 90000 kayıt olsaydı ne olacaktı ?

Merge Join

Birleştirilecek tablolar için, birleştirme koşulunda belirtilen kolonlar için index tanımlı olması (sıralanmış olması) gerekir. Bu sıralı olma durumu Merge Join için önemlidir. Veriler sıralı olduğundan dolayı bir tablodan veri alıp diğerinde arayarak değil, bu iki sıralı tabloyu aynı anda eşleştirme yapmak için gezerek ilgili verileri belirler. Böylece diğer join operatörleri gibi veri için tek tek diğer tabloyu gezmez. Bu sebeple Join operatörleri arasında en performanslısı diyebiliriz. Tabi ki diğer bütün operatörlerde olduğu gibi bu operatör için de index yapınıza ve tablodaki veri büyüklüğünüze göre SQL Server kendisi karar verecektir.

Nested Loops Join işlemi için de benzer şekilde bir örnek yapmıştık. Birleştirme kolonları olarak verdiğimiz sütunlara index tanımlamıştık ve SQL Server 1000-100.000 kayıt olan iki tablo için Nested Loops kullanmıştı.

Tüm koşullar aynı iken 1.tablomuza 89.000 kayıt daha ekliyorum ve 90.000-100.000(Table_1-Table_2) kayıt varken nasıl bir yol izleyeceğine bakalım.

Görüldüğü üzere tablomuzdaki veri sayısı farkı azaldığında ve birleştirme kolonlarımız sıralı olduğunda SQL Server aynı sorgu için Merge Join kullandı. Buradan hareketle SQL Server veri boyutuna göre Nested Loops ya da Merge Join kullanımına kendisi karar verecektir.

Merge Join birleştirme operatöründe bir diğer önemli detay ise sıralama işlemidir. Eğer veriler sıralı ise sonuç oldukça hızlı gelecektir. Fakat iki farklı veri setinden birisi sıralı değilse ve SQL Server Merge Join tercih edecekse, sıralama işlemi yapılacağından dolayı maliyeti yükselebilir.

NOT : SQL Server join işlemlerinde tabloların büyüklüğüne, oluşturulmuş indexlere, verilerin sıralı olmasına, iki tablo arasındaki veri büyüklüğü gibi farklara bakarak kullanacağı operatöre kendisi karar verir. Fakat biz sorgumuzu belirli bir operatöre göre çalıştırmak istediğimizde sorgunun sonuna OPTION(operatör_ismi) ekleyerek istediğimiz join operatörüne göre sonucu getirmesini sağlayabiliriz.

Örnek olarak Merge Join kullanılan son sorgu için Hash Match kullanarak sorgu sonucunu getirmesini isteyelim.

Birden fazla tablodan veri ihtiyacımız olduğunda daha kompleks sorgular hazırladığımız için sorgularımız daha yavaş gelebilmektedir. Uzun süren bu sorguları elimizde bir kahve ile beklemek yerine neden bu kadar uzun sürdüğünü görmek ve anlamak amacıyla Execution plan operatörlerini birlikte incelemiş olduk.

Son olarak, SQL Server işini en pratik ve hızlı bir şekilde zaten yapmaktadır. Yeter ki biz onu rayından çıkarmayalım. Sorgularımıza WHERE koşulu ekleyerek veri kümesini daraltalım ve mümkün oldukça indexler üzerinden arama yapmasını sağlayalım.😊

Execution Plan yazısının devamı şeklinde yazdığım bu yazının da sonuna geldik.

Faydalı bilgiler bulmanız umuduyla,

Hoşça kalın.