Execution Plan Nasıl İncelenmelidir Part-1

Herkesin en az bir kez sorduğu “ Abi bu sorgu niye geç geliyor?” sorusunun cevabını arayacağız bugün. Son yıllarda NoSql veritabanları ne kadar popüler olsa da RDBMS’lerden tam olarak kopamıyoruz. Bu yazımda bir sorguyu çalıştırdığımızda execution plan nasıl incelenir buna değineceğiz. Öncelikle arka planda nasıl çalıştığı ardından operatörlerin tooltip’lerindeki parametreler nelerdir, operatörler nedir ve maliyetleri nasıl azaltılır ve en son ise system log üzerinden performansa yönelik nasıl bilgiler edinebiliriz üzerine değinip sonlandıracağım.

Yazıyı 2 yada 3 parçaya böleceğim. Bu yazıda daha çok temel bilgiler vereceğim, diğerlerinde ise biraz daha pratik ağırlıklı gerçekten belki bir sorunuzun cevabını bulabileceğiniz bilgileri paylaşmaya çalışacağım.

O zaman yavaştan başlayalım.

SQL Query nasıl çalışır?

Sql Server Engine gelen sorguyu işleyip ilgili veriyi döneceği bu süreçte 4 işlem gerçekleştirir bunlar sırası ile;

• Parsing
• Algebrizing
• Optimizing
• Execution

İlk üç aşama SQL Server Relational Engine tarafından, son aşama ise Sql Server Storage Engine tarafından gerçekleştirilir.

1)Parsing

Sorgu Sql server relational engine tarafından ağaç yapısında parçalara bölünür ve syntax hatası var mı sorgu içerisinde kullanılan tablolar var mı diye kontrol eder.

2)Algebrizer

Bu ağaç yapısındaki parçaları alır içlerindeki database objelerine olan referansları bulur ve bunlar üzerinden ilgili sorguları çıkarır yani parse edilen query’den parçalanmış ağaç yapısı çıkmıştı bunları syntax kontrolünden geçirdikten sonra veritabanındaki referanslarını bulup engine için anlamlı query tree’lere çevirir.

3)Query Optimizer

İşte en önemli kısım, çıkan sorguların minimum kaynak tüketecek şekilde ve minimum maliyetli planlanmasını sağlar. Bu çıkarılan plana cost-based-execution plan denir. Bu aşamada bu planları çıkarırken kullanabileceği indexlere, tablolardaki verilere, engine istatistiklerine vb. göre karar verip her bir gerçekleştireceği işlemin maliyetini hesaplayıp genel ortalamayı belirler ve ardından minimum maliyetteki planı seçer. Bu seçilen plana Execution Plan denir.

Bu çıkan plan Memory Storage’a kaydedilir. Yeni bir sorgu geldiğinde memory’e kaydedilen sorgulara bakılır eğer uygun bir plan bulunmazsa query optimizer aşamasında yeni bir plan hazırlanır. Planların cache’lenmesinin sebebi yeni bir execution plan çıkarmanın maliyetinin çok yüksek olmasından dolayıdır. Ve son aşamada ise sorgumuz çalışır ve sonucu getirir.

Execution plan sayesinde sorgu neden yavaş çalışıyor hangi aşamada çok fazla memory, cpu kullanıyor gibi sorulara cevap vermemize yarıyor. Şimdi bu çıkarımları nasıl yapacağız sorularımıza planı inceleyerek nasıl cevap vereceğiz yavaştan giriş yapalım.

2 tip execution plan bulunmaktadır:

1)Estimated Execution Plan:

Sorgu parçalara ayrıldıktan sonra hiç çalıştırılmadan yapılan tahmini plandır.

2)Actual Execution Plan:

Sorgu çalıştırıldıktan sonra o aşamada izlenen, gerçekleşen adımların gösterildiği plandır.

Actual Execution plan daha doğru, yararlı olmasından dolayı çoğunlukla önerilirken, çok kompleks ve çok uzun sürecek sorgularda Estimated Execution planı öneriyorlar.

Execution planı sağdan sola doğru incelersek oluşan tree query’lerin alt kademelerinden başlamış oluruz. Zaten işlemler arasındaki oklardan bu anlaşılıyor. İşlemler arasındaki oklar sadece planın akışını göstermiyor aynı zamanda aktarılan verinin boyutunu okun kalınlığı ile bize gösteriyor.

Peki planda belirtilen cost yüzdesi bizim için yeterli bir bilgimi tabi ki değil. Her bir işlemin üzerine gittiğinizde gerçekleşen işlemin neler yaptığını görebilirsiniz.

İlgili operatörlerin tooltip’lerinde önemli olarak gördüğüm parametrelerine değinip execution plan kapsamında gerçekleşebilecek operatörler neler onlara giriş yapacağım bir sonraki yazımda devam edip son olarak sistem logları üzerinden bir kaç tüyo vereceğim.

1)Header:

İlgili operatörün gerçekleştirdiği işlemi belirtmektedir. Örneğimizde Clustered Index Scan işlemini gerçekleştirdiğini belirtiyor. Buradaki index tipi (clustered/nonclustered) ve scan/seek konularına zaten değineceğiz yazının devamında.

2)Logical Operation:

Kavramsal olarak hangi işlemin yapılması gerektiğini tanımlar.

3)PhysicalOperation:

Logical Operation da belirlenen işlemi uygular.

Bu iki kavramı tanım ile açıklamak zor olduğundan dolayı bir örnek ile tekrar açıklayacağım.

SELECT * FROM User AS U

INNER JOIN Address AS A ON A.UserId=U.Id

WHERE U.Status=1 AND A.Status=1

Sorgumuzu çalıştırdığımızda query optimizer aşamasında Inner Join için aşağıdaki tooltips’te olduğu gibi bir operatör gerçekleştirmektedir. Burada işlem Inner Join bunun fiziksel karşılığı ise Hash Match. Belirtilen komut Inner Join gerçekleştirilen işlem Hash Match’tir.

4)Actual Execution Mode:

Optimizer tarafından hangi execution modu kullanacağına karar verilir. Execution mod iki parçadan oluşur. Bunlar;

Row mode; satır satır işlem gerçekleştirirken BatchMode belli bir grup satır üzerinden işlem gerçekleştirir. Bu bahsettiğim batch ise Columnstore’lar sayesinde elde ediliyor bunu konudan çok sapmamak için başka bir yazıda anlatabilirim. Peki ne zaman hangisini tercih ediyor?

Batch mode bazı işlemleri desteklemektedir (scan,filter, hash vb.). Query Optimizer eğer çok büyük veriler üzerinde gruplama, birleştirme gibi işlemler gerçekleştirecekse Batch Mode’u tercih eder(tabi columstore’a yönelik indexleriniz varsa) bunun dışındaki senaryolarda Row Mode’u tercih eder.

5)Estimated Execution Mode:

Actual execution’dan farkı tahmini kullanılacak modu belirtir.

6)Storage:

İşlemin gerçekleşeceği storage(In-Memory) belirler RowStore veya ColumStore. Tabi bu yine execution mode’a göre belirlenmektedir. Row mode ise RowStore, batch mode ise ColumsStore üzerinde çalışacaktır.

7)Number of Rows Read:

Okunan satır sayısını belirtmektedir.

8)Actual Number of Rows/Batches:

İlgili işlemin gerçekleşmesi için okunan satır/batch sayısı.

Peki number of rows read ile Actual Number of rows arasındaki farkı örnek üzerinden konuşursak daha net anlaşılır. İki farklı işlemimiz var bir tanesi index scan yapıyor koşulumuz ise Id ve Status bazlı olsun scan işleminde 1000 satır olan tabloda koşula uyanları bulması için 1000 satırı da okumak zorunda(number of rows read) sonuç olarak ise bu 1000 satır içerisinden 300 satırı(actual number of rows) dönüyor.

Bir diğer işlemimizde ise index seek yapıyor sorgumuzun parametrelerine göre indexlenmiş tabloda direk doğru kayıtları okuduğundan dolayı her iki değerde 300'ü göstermektedir.

9)Estimated Operator Cost:

İşlem için harcanan maliyeti gösterir ve bunun bütün işlemler arasındaki yüzdelik dilimini gösterir.

10)Estimated I/O, CPU,Subtree:

İşlem için harcanan maliyetin i/o, cpu, subtree bazındaki maliyeti gösterir.

11)Ordered:

Satırların işlem sırasında sıralanıp sıralanmadığını gösterir. Bunun önemi bir sonraki işlemin aggregate veya hash match türü bir işlem ise bu parametreye dikkat etmemiz gerekir.

12)NodeId:

Execution Plan’daki işlemlerin gerçekleşme sırasını belirler. Execution plan okunurken sağdan sola doğru okunsa da numara sıralaması soldan sağa doğru verilir.

Bu yazıda sizleri çok bunaltmak istemedim, bundan dolayı ufak bir giriş yapıp bir sonraki yazıda operatörleri anlatmaya başlayacağım. Part-2'ye buradan devam edebilirsiniz.