* "Optimize for ad-hoc workloads" ayarı devrede olduğunda ad-hoc sorgular için çalıştırma planının tamamı depolanmaz Plan Cache'te, yalnızca "Stub" denilen küçük bir bölümü depolanır. Böylece Plan Cache bir daha muhtemelen kullanılmayacak binlerce plan ile dolup taşmaz.
** İstisnai durumlarda yolda plan değişebilir, işte bu nedenle Estimated Execution Plan ile Actual Execution Plan farklı olabilir.
Belli bir Batch veya stored procedure için Plan Cache'teki çalıştırma planını görmek istediğimizde sys.dm_exec_query_plan isimli DMV'den faydalanabiliriz. Örneğin:
SELECT [query_plan] FROM sys.dm_exec_query_plan(
komutuyla X isimli stored procedure'ün plan handle'ını sorgulayıp çalıştırma planını aldığınızı düşünün. Eğer bunu yeterince çok denediyseniz, ilgili sorgunun planının Plan Cache'te olduğundan emin olduğunuz halde yukarıdaki sorgunun zaman zaman null değeri döndüğünü görmüşlüğünüz olmuştur. Bu yazımda bunun nedenini açıklamak istedim.
Bir stored procedure içerisinde temp table kullanılıyorsa, sorgu belli koşullara göre birkaç seçenek içeriyorsa veya "RECOMPILE" seçeneği ile çalıştırılıyorsa o zaman deferred compilation denilen olay gerçekleşiyor ve SQL Server stored procedure ilk defa çalıştırılırken sorgunun tamamı için çalıştırma planı oluşturmuyor.
sys.dm_exec_query_plan isimli DMV çalıştırma planını bir bütün olarak getiriyor. Ya hep, ya hiç. Eğer Batch'inizdeki veya stored procedure'ünüzdeki tüm bloklar için çalıştırma planı varsa*, o zaman bu DMV ile ilgili sorgunuza dair oluşturulan çalıştırma planını Plan Cache'ten edinebilirsiniz.
* SQL Server 2005'ten beri Batch içerisindeki tüm kod blokları için ayrı ayrı çalıştırma planı üretiliyor.
Peki Batch'inizde veya stored procedure'ünüzde deferred compilation nedeniyle henüz çalıştırma planı olmayan komutlar varsa, ama siz olduğu kadarını görmek istiyorsanız ne yapacaksınız? sys.dm_exec_query_plan DMV'si bu durumda null sonucunu döner, işte bu durumda sys.dm_exec_text_query_plan isimli DMV'yi kullanmalısınız.
sys.dm_exec_text_query_plan isimli DMV'yi kullanırken eğer "statement_start_offset" ve "statement_end_offset" değerlerini girmezseniz yine sys.dm_exec_query_plan DMV'si ile yaşadığınız sorunu yaşarsınız. Bu nedenle bunu atlamamanız önemli.
Örnek kullanım:
SELECT
CAST([qp].[query_plan] AS XML) AS [query_plan],
[qs].[execution_count],
...
FROM sys.dm_exec_query_stats AS [qs]
CROSS APPLY sys.dm_exec_text_query_plan ([qs].[plan_handle], [qs].[statement_start_offset], [qs].[statement_end_offset]) AS [qp]
WHERE OBJECT_NAME([qp].[objectid], [qp].[dbid]) = 'sp_adı';
Ekrem Önsoy
Microsoft SQL Server Danışmanı
www.ekremonsoy.com
