Showplan SET ifadeleri ile Execution Plan gösterimi

SQL Server’deki bir sorgunun hangi yöntemle çalıştırılacağını SQL Server’in ürettiği Execution Plan’a bakarak anlayabiliriz. SQL Server’in sunduğu gösterim planı ifadelerini (Showplan SET statements) kullanarak o anki Execution Plan hakkında daha geniş bilgi alabiliriz. Sorguya ait execution plan’ı görmek için temel olarak aşağıdaki deyimler kullanılır.
SET SHOWPLAN_ALL ON
SET SHOWPLAN_TEXT ON
SET SHOWPLAN_XML ON
SET STATISTICS XML ON
SET STATISTICS PROFILE ON
SET STATISTICS IO ON
SET STATISTICS TIME ON
SQL Server bir T-SQL sorgusu için tek bir yürütme planı (tahmini-Estimated veya gerçekleşen-Actual) oluşturur. Yukarıda verilmiş olan deyimler bu planın gösterim formatını ve detaylarını belirler. SQL server temel olarak aşağıdaki formatları sunar;

  • Grafiksel plan
  • Text formatlı plan
  • XML formatlı plan

Veritabanı yöneticileri bu formatlardan birini kullanarak yürütüme planıyla ilgili detaylara erişebilir. Grafik formatı en anlaşılık kolay formattır. Ancak yüzeysel kalıp planla ilgili bazı detayları göstermez. Bir sorgunun tahmini yürütme planını almak için editörün üst kısmındaki çubuktan “Display Estimated Execution Plan” düğmesini tıklamak yeterli olacaktır.

Sorgu çalıştıktan sonra yürütme planının da görülmesi için aynı çubuktaki “Include Actual Execution Plan” düğmesi tıklanabilir. Her iki durumda da yürütme planı aşağıdaki gibi grafik ortamında görünecektir.

Aynı bölümlere editörü sağ tıklayarak ta erişilebilir. Veya CTRL+L / CTRL+M kısayolu ile ulaşılabilir.

Text tabanlı format grafik formata göre okunması, anlaşılması biraz daha zordur ancak planla ilgili birçok detayı içerir. Üç tür text plan formatı mevcuttur;

  • SHOWPLAN_ALL : Yazılmış olan sorgu için detaylı tahmini yürütme planını yazdırır.
    SET SHOWPLAN_ALL ON
    GO
    SELECT * FROM l_location
    GO
    SET SHOWPLAN_ALL OFF

  • SHOWPLAN_TEXT : Plan için daha sınırlı bir bilgi sunar. Bu deyim de aynı şekilde sadece tahmini plan bilgisi verir.
    SET SHOWPLAN_TEXT ON
    GO
    SELECT * FROM l_location
    GO
    SET SHOWPLAN_TEXT OFF

  • STATISTICS PROFILE : SHOWPLAN_ALL ile aynı detayı sunar ondan farklı olarak gerçekleşen plan (Actual execution plan) hakkında bilgi sunar. Yani önceki deyimler sorguyu çalıştırmaz sadece yürütme planını sunar ancak bu seçenekte hem T-SQL deyimini çalıştırır hem de çalışma sonrası gerçekleşmiş

XML formatlı gösterim için SHOWPLAN_XML veya STATISTICS XML deyimleri kullanılır. İlk deyim tahmini yürütme planı sunarken ikinci deyim gerçekleşmiş olan yürütme planını sunar. XML formatı, TEXT formatına göre daha fazla detay içerir.
SET SHOWPLAN_XML ON
GO
SELECT * FROM l_location
GO
SET SHOWPLAN_XML OFF


İPUCU : XML formatlı çıktıları grafik ortamında görmek için XML çıktısı herhangi bir metin editörüne kopyalanır sqlplan uzantılı olarak kayıt edilir. Ardından dosya SQL Server Management Studio üzerinden açılır.
Sorguların performans analizinde yürütme planını daha iyi incelemek veya gerekirse ona göre yönlendirme yapmak için bu deyimlerin dışında SET STATISTICS IO ve SET STATISTICS TIME deyimleri kullanılır. Bu iki deyim sorgularla ilgili özellikle istatistiksel bilgileri elde etmek için kullanılır.
SET STATISTICS IO en çok tercih ettiğim deyimlerden biri olup sorgu sonucu için hangi tablo için kaç adet index’in tarandığı, fiziksel ve mantıksal okuma yapıldığını belirtir. Bu amaçla Table, Scan count, logical read, physical reads . isminde çıktılar üretir.
SET STATISTICS IO ON
GO
SELECT * FROM l_location
SELECT TOP 10 * FROM l_product
GO
SET STATISTICS IO OFF

Bu örnekta aşağıdaki gibi bir I/O çıktısı üretildi.
(99 row(s) affected)
Table ‘l_location’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(10 row(s) affected)
Table ‘l_product’. Scan count 1, logical reads 4, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Her iki tablo 1 kere taranmış durumda. l_location tablosu üzerinde mantıksal okuma(logical reads) 6 iken l_product tablosunda 4 görünmektedir. Mantıksal okuma SQL Server’in diskten değil doğrudan cache’den(tampon alandan) okuduğu page sayısıdır. Eğer bellekte sözkonusu page’ler yoksa SQL Server o page’lere erişmek için fiziksel okuma (physical reads) yapar. Burada görüldüğü gibi l_location tablosu için doğrudan cache’den okunmuştur. Fiziksel erişimler her zaman daha maliyetlidir. Bir tablo ilk olarak istenildiğinde fiziksel okunma olur SQL Server belleğin durumuna göre tabloyu tampon bölgeye alır ikinci okumayı cache’den gerçekleştirir. Nitekim yukarıdaki örneği ikinci kez çalıştıracak olursak physical reads 0 değeri oluşacaktır.
Daha önceki sorgulara ait işletim planlarını temizlemek için DBCC DROPCLEANBUFFERS veya DBCC FreeProcCache komutu kullanılır. Bu komutlar çalıştırıldıktan sonra sorgular yeniden compile edilir. Cache’lenmiş nesneler ve onlara ait sorgular master.dbo.syscacheobjects tablosunda tutulmaktadır. DROPCLEANBUFFERS komutu dirty page hariç tüm data cache’i siler. Tablo ve SQL sorguları tampon alandan temizlenmiş olur. Bu komut çalıştırıldıktan sonra tablolar kullanılmadan önce diskten cache’e taşınır. FREEPROCCACHE komutu prosedürlere ait cache’yi silmek için kullanılır, stored procedure’lerin de yeniden derlenmesini sağlar. Tabi bu komutları çalıştırmadan önce CHECKPOINT komutu aracılığıyla veritabanı üzerindeki dirty page’leri diske taşımak daha doğru olacaktır.
SQL Server 2005 veya 2008’deki cachedeki sorguları aşağıdaki sorgu ile kolayca görebiliriz.

SELECT [cp].[refcounts]
, [cp].[usecounts]
, [cp].[objtype]
, [st].[dbid]
, [st].[objectid]
, [st].
, [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 ;

SET STATISTICS TIME Sorgu ile ilgili istatistiksel değerleri almanın diğer yolu da sorguyla ilgili süreleri öğrenmektir.
SET STATISTICS TIME ON
GO
SELECT TOP 10 * FROM l_product
GO
SET STATISTICS TIME OFF

Bu komutları çalıştırdığımızda aşağıdaki sonucu elde ettik.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
(10 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 11 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

Sonuç ekranında Compile Time (Derleme) ve Execution Time (İşleme) olmak üzere iki zaman bilgisi verilmiştir. Her iki zaman da CPU Time (millisecond) birimindedir. Bu değer o işlem için geçen CPU zamanını belirtmektedir. Önceki deyimde ne kadar IO kaynağı harcandığını bu deyimde de parse, compile, execute için ne kadar CPU kaynağı harcandığını görebilmekteyiz.
Bu makaledeki deyimleri aktifleştirmek için sorgu editörünü sağ tıklayıp “Query Options” menüsünü de kullanabiliriz.

Görüldüğü gibi SET deyimleri özellikle performans iyileştirme durumlarında bizim için yönlendirici olabilmektedir.

Bir cevap yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir

Time limit is exhausted. Please reload CAPTCHA.