Pazartesi , 23 Ekim 2017

SQL Server Execution Plan Bölüm 1 – Execution Plan Kavrami ve Çesitleri

Execution plan en basit ifadesiyle Query Optimizer tarafından hesaplanan ve bir sorgunun en ideal şekilde çalışması için bize önerilen optimum yoldur. Diğer bir ifadeyle bir Execution plan bize bir sorgunun çağrıldığında nasıl çalışacağını veya daha önce nasıl çalıştığını gösterir. Özellikle Veritabanı yöneticilerinin çok sık karşılaştığı performans problemlerini analiz ederken öncelikle çalışma süresi çok uzun süre alan sorgular tespit edilir ve daha sonra bu sorguların Execution planlarına bakılarak sorun tespit edilir. Execution plan konusunu daha iyi anlayabilmek için bir sorgunun SQL Servera gönderildikten sonra işleyişine göz atalım.

Öncelikle SQL Servera herhangi bir sorgu geldiği zaman istediğimiz sonucu bize vermesi için SQL Server tarafından bir dizi işlem yapılır. Kabaca bu işlemleri açıklamak gerekirse öncelikle SQL Server gönderilen sorguyu Parse eder yani yazım kurallarına uygunluğunu denetler ve daha sonra sorguya ilişkin çalışma planı(Execution plan ) Query Optimizer tarafından oluşturulur. Daha sonra Storage engine denilen SQL Server bileşenine gönderilen bu plana göre sorgu işletilerek veriye erişim sağlanır.

Query Optimizer optimum Execution planı hesaplarken minimum CPU ve I/O miktarına göre hesaplar. Bu hesaplama yapılırken objeler üzerinde daha önce oluşturulmuş istatistikler varsa onlardan da yararlanılır ve sorgu sonucunu hesaplar. Bu hesaplama işlemi sorgu için Estimated Cost(tahmini maliyet) olarak adlandırılır. Daha sonra hesaplanan Execution plan daha sonra sorgu çalıştırıldığında tekrar kullanılmak üzere plan Cache konulur.
İki farklı Execution plan vardır.

 

  1. Estimated Execution plan
  2. Actual Execution plan

Aralarındaki fark ise Estimated Execution plan Query Optimizer tarafında tarafından oluşturulabilecek tahmini planı temsil ederken, Actual Execution plan ise sorgu sonucunun çalışmasıyla elde edilen gerçek planı temsil eder. Ayrıca Estimated Execution plan ve Actual Execution plan birbirinden farklı olabilir. SQL Serverın plan Cache denilen bellek bölgesinde depolanan planlar ise Actual Execution planlardır.

Aslında SQL Serverın her defasında yeni bir Execution plan oluşturmak yerine bir kere oluşturup bunu plan Cache denilen belleğe kaydetmesi ve daha sonra aynı sorgu çalıştırıldığında tekrar kullanılması performans amaçlı yapılmıştır. Her defasında Execution plan hesaplaması SQL Server için ayrıca bir yük teşkil etmektedir. Bu yüzden SQL Server bir sorgu için plan Cache’de bir Execution plan varsa o sorgu için aksi belirtilmediği sürece yeni bir Execution plan oluşturmayacak ve var olan Execution planı kullanacaktır.

SQL Server tarafından oluşturulan Execution planlar sürekli plan Cache denilen bellek bölgesinde saklanmaz. SQL Serverın Lazy Writer adlı işlem belli aralıklar plan Cache de dahil olmak üzere Cache’de tutulan tüm verileri temizler. Bunun dışında manuel olarak yaptığımız bazı işlemler de sorgunun Recompile olmasını yani Execution planın tekrar oluşturulmasını sağlar. Fakat yukarıda da belirttiğimiz gibi Execution planın çok sık Recompile edilmesi SQL Server için yorucu işlemdir. Şimdi Execution planın tekrar oluşturulmasını sağlayan bazı işlemleri listeleyelim.

1-Sorguda referans edilen bir tablo üzerinde yapılan Schema değişliği(kolon eklenmesi çıkarılması gibi)
2-Sorgunun kullandığı indekslerden birinin silinmesi
3-Manuel olarak sp_recompile sistem Stored Procedure ’unun çağrılması
4-Aynı sorgu içinde DDL ve DML işlemlerinin beraber kullanılması gibi işlemler her defasında tekrar Execution planın hesaplanmasına yol açar.
5-Sorgunun kullandığı istatistiklerin değişmesi

SQL Serverda Plan Cache’de bulunan Execution planları görmek için dm_exec_cached_plans adlı sistem kataloğu sorgulanmalıdır.

SELECT [cp].[refcounts],[cp].[usecounts],[cp].[objtype]
,[st].[dbid],[st].[objectid],[st].1
,[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('DatabaseAdi')

Yukarıdaki sorgu ile ilgili veritabanı için plan Cache’de bulunan Execution planlar listelenecektir. Sorgu soncunda aşağıdaki gibi bir sonuçla karşılaşırız.


execution_plan_1

 

Bu ekranda objtype ilgili objenin tipini belirtirken query_plan ise sorgunun çalıştırılması ile oluşan planın göstermektedir. İstediğimiz Query planın üzerine tıklayarak planı görüntüleyebiliriz.

execution_plan_2

Yukarıdaki sorgulanan planın üzerine tıkladığımızda SQL Server bize Query Optimizer tarafından kullanılan Execution planı yukarıdaki gibi grafiksel şekilde sunacaktır. SQL Server kullandığı Execution planları 3 farklı formatta gösterebilir. Bu formatlar:

  • Graphical Plan
  • Text Plan
  • XML Plan

Her format aynı Execution planı simgelemesine rağmen arlarında bazı farklar bulunmaktadır. Örneğin graphical planların okunması ve değerlendirilmesi Text planlara göre daha kolay olmasına rağmen, Text planlar graphical planlara göre daha detaylı bilgi içermektedirler.

1. Grafiksel Plan

 

Diğer formattaki planlara göre daha kolay ve anlaşılır olmasına rağmen diğer formattaki Execution planlara nazaran daha yüzeysel bilgi içerirler. Hem Estimated Execution planlar hem de Actual Execution planlar bu formatta gösterilebilirler.

2.Text Plan

Text planların graphical planlara göre daha zor anlaşılmasına rağmen Text planlar daha detaylı bilgi içermektedir. Text Planlar da kendi içinde 3 farklı formatta gösterilmektedir.

  • SHOWPLAN_ALL: Bu formatta ilgili sorgu için Estimated Execution plan Text formatında gösterilir.
  • SHOWPLAN_TEXT: Bu formatta SHOWPLAN_ALL gibi Estimated Execution plan için kullanılmasına rağmen SHOWPLAN_ALL formatına göre daha kısıtlı bilgi içermektedir.
  • STATISTICS PROFILE: Bu format SHOWPLAN_ALL ile aynı bilgileri vermesine rağmen bu format sadece Actual Execution planları için kullanılmaktadır.

3. XML Plan

XML Planlar da Text planlara göre anlaşılması daha kolay olup çok detaylı bilgileri içerirler. SQL Server bize 2 farklı XML plan formattı sunmaktadır.

  • SHOWPLAN_XML: Estimated Execution plan için kullanılır.
  • STATISTICS_XML: Actual Execution plan için kullanılır.

 

Hakkında ismailadar

Cevapla

E-posta adresiniz yayınlanmayacak. Required fields are marked *

*


*

Şu HTML etiketlerini ve özelliklerini kullanabilirsiniz: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>