Bu makalede MSSQL Server Execution Plan kavramını detaylı bir şekilde ele almaya çalışacağız. Execution plan sorgunun nasıl çalışacağına karar veren bir yapı olarak karşımıza çıkmaktadır. Herhangi bir sorgunun performanslı çalışıp çalışmadığını görmek için Execution plan hayati bir önem taşımaktadır. SQL Server’ın ilgili sorguyu çalıştırdığında nasıl bir yol izlediğini bilmek bize sorunla ilgili detaylı bilgi verecektir. Kısacası execution plan bir sorgunun bir data setine erişmek için kullandığı yol haritasıdır. Execution plan yapısını ve çeşitlerini açıklamadan önce bir sorgunun nasıl çalıştığını nasıl bir yaşam döngüsünden geçtiğini ele alalım.
Sql server sorgu ilk geldiğinde sorgu ilk olarak Relational Engine tarafından karşılanır. Gelen sorgu için ilk olarak query parse işlemini yapar kodda yazım hataları var mı diye kontrol edilmektedir. Bunu çalıştırmadan önce kendinizde bulabilir veya görebilirsiniz. Yani query parse bölümünde ilk kısmında T-sql kurallarına göre herhangi bir yazım hatası var mı diye kontrol işlemi yapmaktadır. Sorguda bulunan stored procedure, functions, tablo gibi ifadelerin var olup olmadığıyla ilgilenmez. Query parse işleminin ilk adımında bu kontrol yapıldıktan sonra parse işleminin ikinci aşamasında yukarıdaki bahsettiğim stored procedure, table, functions gibi sql server nesneleri var mı bu kontrolü yapmaktadır. Bu kontrol’ü yapan birim Algebrizer olarak adlandırılan kısım tarafından yapılmaktadır. Diğer bir ifadeyle Name Resolution olarak adlandırılmaktadır. İsim çözümlenmesi denilmektedir.
Yukarıda yapılan parse işleminde sonra Query Optimizer kısmı gelmektedir. Query Optimizer yapısı parse işleminde ayrılan sorgunun maliyete göre elde etmiş olduğu birden fazla execution plan yapılarını tutmaktadır. Bu execution planlar içerisinde birbirinden farklı maliyetli sorgular olabilir.
Burada execution planın önemli bileşenlerinden biri olan statistics yapısı ile maliyet hesabını yapmaktadır. İlgili sorgunun maliyeti için sql server I/O – CPU yani veri okuma yazma oranına bakıp maliyeti hesaplamaktadır. Bu maliyet hesaplaması için sql server verinin dağılımının bulunduğu istatistiklerden faydalanmaktadır. Database engine sorgudaki ilgili veriye erişmek için doğrudan veriye erişmez. Verinin istatistik değerlerine bakarak maliyet hesaplamaktadır. Bu yapı Estimated Cost olarak karşımıza çıkmaktadır.
Query Optimizer’ın seçmiş olduğunu yolu doğru seçebilmesi için kolon ve indexs istatistiklerin güncel olması gerekmektedir. Çünkü performans anlamında doğru olan execution plan seçilmesi için gerekli olan bir yapıdır.
Query Optimizer yeni gelen bir sorgunun execution plan yapılarını oluşturması sorgunun çalıştırmasından daha uzun olmaktadır. Burada Query Optimizer maliyeti düşük olan bazı planların execution planını oluşturur. Tüm yolları oluşturmaz.
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.
Query Optimizer bileşeni tarafından Execution Plan hesaplanmasının da bir maliyeti olduğu için genellikle tablo oluşturulması veya Stored Procedure oluşturulması gibi DDL işlemleri için Execution plan hesaplaması yapılmaz.
Minimum execution plan seçildikten sorgu sonucunun getirilmesi Storage Engine tarafından gerçekleşmektedir.
Sürekli gelen sorgular için database engine yapısının tekrardan execution plan oluşturması performans anlamında sıkıntı yaratacağı için önceden oluşturulan sorgu tekrardan çalıştırılacağı zaman aynı execution plan’ı kullanabilmesi için Plan Cache denilen bölgede tutulmaktadır.
Yukarıdaki açıklamalardan sonra Execution Plan kısmında kaldığımız yerden devam edelim. Kısacası Execution Plan, Query Optimizer tarafından oluşturulan sorgunun minimum seviyede en az maliyetle nasıl çalıştığını veren bir yol aynı zamanda performans problemlerinde bir referans aracı ve sorgunun genel çalışma şeklini gördüğümüz bir yapı olarak karşımıza çıkmaktadır.
Sql Server’da Execution Plan 2 çeşittir. Bunlar Estimated Execution Plan ve Actual Execution Plan olarak ayrılmaktadır.
Estimated Execution Plan: Sorgunun kendisine ulaşmadan istatistiklerden faydalanarak tahmini çalışma planının getirilmesidir.
Display Estimated Execution Plan SSMS arayüzünde aşağıdaki resimde görüldüğü gibi aktif edilir. Ya da Ctrl+L kombinasyonuyla aktif edilmektedir. Bu planlar SQL Server Query Optimizer tarafından sorgu çalıştırılmadan oluşturulduğu için gerçekte kullanılan Execution planlardan farklı olabilirler. Aşağıda kırmızı çerçeve içerisine alınmış bölümden de aktif edilebilir.
Ya da SSMS arayüzün de Query bölümünde Display Estimated Execution Plan yapımız aktif edilebilir.
Tahmini execution plan olduğu için Messages bölümü görülmektedir.
Include Actual Execution Plan: Sorgunun çalıştırılması sonucu elde edilen gerçek değerlerdir. Plan cache bölümünde tutulan planlardır. Plan cache üzerinde duran planlar zamanla Lazy Writer denilen bir kavramla plan cache ve cache’de bulunan tüm verileri siler. Lazy Writer ilgili bölümün yönetilmesini sağlamaktadır.
Ctrl+M kombinasyonuyla yapılmaktadır.
Dikkat ederseniz gerçek sistem üzerinde çalıştığı için Result ekranında sonuç görülmektedir.
Yukarıdaki iki execution plan yapısı tamamen birbirinden farklı olabilir.
Not: Sorgu üzerinde yapacağımız bir değişiklik sql server execution planın değişmesine sebebiyet vermektedir.
Not: Sorgunun kullanmış olduğu istatistiklerin değişmesi tekrardan execution plan hesaplanmasına yol açmaktadır.
Not: Mevcut olan indexslerden birinin silinmesi tekrardan execution plan oluşmasına sebebiyet vermektedir.
Not: Manuel olarak sp_recompile sistem Stored Procedure ’unun çağrılması yeniden derlenmesine sebebiyet vermektedir.
Aşağıdaki kod bloğu sayesinde Plan cache üzerinde bulunan database’in execution planlarını görebiliriz.
SELECT [cp].[refcounts],[cp].[usecounts],[cp].[objtype]
,[st].[dbid],[st].[objectid],[st].[text]
,[qp].[query_plan]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE [st].[dbid] = db_id('Database_Name')
usecounts ilgili planının kullanım sayısını vermektedir. query_plan kısmından sorgumuza ulaşıp gerekli düzenlemeler yapılabilir.
Query_plan kısmına tıkladığımızda sorgunun execution plan yapısını görebiliriz.
Not: Sql server da execution planları görmek için sysadmin, db_owner ve db_creator yetkisinin olması gerekmektedir. Yada herhangi bir kullanıcıya daha spesifik yetkiler verilebilir. İlgili kullanıcıya veritabanı altında showplan yetkisi verilebilir.
Aşağıdaki ekran resminde Show Execution Plan XML dediğimizde bize XML ifadesinde belirtilmektedir.
Yukarıda execution plan yapımızın detaylı bir şekilde XML kısmı aşağıda görülmektedir. Burada execution plandan daha detaylı bir şekilde sonucumuzu görebiliriz.
Not: Save Execution Plan As.. kısmına tıkladığımızda execution planımızı herhangi bir klasör altına alabiliriz.
Sql server’da sorgularımız için oluşan execution planları bir tabloymuş gibi görmek istersek aşağıdaki komutları kullanarak sorgu için hangi planların olduğunu görmüş oluruz.
set showplan_all on-- Estimated execution plan yapılarında kullanılır.
set statistics profile on-- Actuel execution plan yapılarında kullanılır.
SELECT *
FROM [AdventureWorks2014].[Person].[Address]
İlerleyen bölümlerde execution plan yapısını daha detaylı bir şekilde makaleler olmuş olacaktır. Bu makalede execution plan kavramını detaylı bir şekilde görmüş olduk.
Başka makalede görüşmek dileğiyle..
“Allah’tan kulları içinde ancak ilim sahibi olanlar korkar.” Fâtır sûresi – 28
