Optimize for ad hoc workloads ve Parameterization

Bu makalede Optimize for ad hoc workloads’u enable ettiğimizde ve bununla beraber parameterization kullandığımızda neler olduğunu inceleyeceğiz. İki özelliği ayrı ayrı ve teker teker kullandığımızda, ne gibi durumlar ortaya çıkacağını göreceğiz.

Optimize for ad hoc workloads SQL Server 2008 ile beraber gelen bir özelliktir. SQL Server üzerinde enable edildiğinde, tek kullanımlık sorgular için, plan cache üzerinde query plan’ın tamamını oluşturmak yerine sadece küçük bir Compiled Plan Stub oluşturur. Query plan’ın tamamı sorgu ikinci kez çalıştırıldığında oluşur. Aşağıdaki gibi sql server üzerinde bu özelliği enable edebiliriz. Sorguların teker teker çalıştırılması gerekir.

sp_configure 'show advanced options',1
reconfigure

sp_configure 'optimize for ad hoc workloads',1
reconfigure

SQL Server default olarak simple parameterization kullanıyor. Fakat forced parameterization kullanacak şekilde de ayarlanabilir. Şimdi simple ya da forced parameterization ile beraber optimize for ad hoc workloads’un enable ve disable edilmesiyle neler olacağını örnekler üzerinde görelim.

1. Optimize for ad hoc workloads enable değilken ve sql server simple parameterization  ile çalışırken, Cache’i aşağıdaki sorguyla temizliyoruz. Production ortamında bunu yapmamanız gerekir.

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Sorgularımızı AdwentureWorks veritabanında çalıştıracağız. O yüzden aşağıdaki query’i çalıştırıyoruz.

USE AdventureWorks
GO

Aşağıdaki 3 sorguyu cache’i temizledikten sonra teker teker çalıştıralım.

SELECT * FROM HumanResources.Shift

SELECT * FROM HumanResources.Shift

where ModifiedDate='2002-06-01 00:00:00.000'
 
SELECT * FROM HumanResources.Shift

where ISNULL(ModifiedDate,'1/1/2003')>'1/1/1990'

Yukarıdaki sorguları çalıştırdıktan sonra, sorgularımızın query plan’larının oluşup oluşmadıklarına, oluştularsa boyutlarının büyüklüğüne bakmak için aşağıdaki sorguyu çalıştırıyoruz.

SELECT p.size_in_bytes,p.cacheobjtype,p.objtype,qp.query_plan,t.text

FROM sys.dm_exec_cached_plans p

CROSS APPLY sys.dm_exec_query_plan(p.plan_handle) qp

CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t

where t.text like '%HumanResources%

Optimize for ad hoc workloads disable ve simple parameterization enable iken aşağıdaki gibi bir sonuç alıyoruz;

Görüldüğü gibi bütün query planlar oluştu. Sadece bir sorguda farklılık olduğunu görüyoruz.

SELECT * FROM HumanResources.Shift where ModifiedDate='2002-06-01 00:00:00.000'

sorgusunun hem parametreli hali, hem de parametresiz hali için query plan oluşturulmuş. Ama where koşuluna sahip diğer bir sorgumuz için sadece parametresiz halinin query planı çıkarılırken parametreli hali için bir query plan çıkarmamış.Simple parameterization kullandığımız için sadece where= olan sorguyu parametrize edebildi. Ama karmaşık bir where koşuluna sahip olan diğer sorgumuzu parametreli hale getiremedi. Simple parameterization bazı durumlarda where= olan sorgularıda parameterize edemeyebilir.

Aşağıdaki her adımı gerçekleştirmek için gereken ayarları yaptıktan sonra yukarıdaki scriptleri tekrar tekrar çalıştırıyoruz.

2. Optimize for ad hoc workloads enable ve simple parameterization enable iken ilk çalıştırdığımızda aşağıdaki gibi bir sonuç alıyoruz. Görüldüğü gibi sadece parametreli hale getirdiği sorgu için query plan çıkardı. Geri kalanları Compiled Plan Stub şeklinde küçük bir alan ayırarak sakladı.

3. Optimize for ad hoc workloads enable ve simple parameterization enable iken ikinci kez aynı sorguları cache’i temizlemeden çalıştırdığımızda aşağıdaki gibi bir sonuç alıyoruz. Görüldüğü gibi sorguları ikinci kez çalıştırdığımızda 1. Maddede aldığımız sonucu aldık.

4. Optimize for ad hoc workloads disable ve forced parameterization enable iken aşağıdaki gibi bir sonuç alıyoruz; Görüldüğü gibi bütün query planlar oluştu. 1. Maddeden farklı olarak burda where koşulu olan iki sorgumuzunda parametreli hali içinde query plan çıkarıldığını görüyoruz.

SELECT * FROM HumanResources.Shift where ModifiedDate='2002-06-01 00:00:00.000'

sorgusunun ve

SELECT * FROM HumanResources.Shift where ISNULL(ModifiedDate,'1/1/2003')>'1/1/1990'

sorgusunun hem parametreli hali, hem de parametresiz hali için query plan oluşturulmuş. Forced parameterization kullandığımız için parametrize edilebilecek tüm sorgularımız için davranış şekli bu oldu.

5. Optimize for ad hoc workloads enable ve forced parameterization enable iken ilk çalıştırdığımızda aşağıdaki gibi bir sonuç alıyoruz. Görüldüğü gibi, 2. Maddeden farklı olarak where koşuluna sahip tüm sorgularımızın parametreli halleri için query plan oluşturuldu.

6. Optimize for ad hoc workloads enable ve forced parameterization enable iken ikinci kez aynı sorguları cache’i temizlemeden çalıştırdığımızda aşağıdaki gibi bir sonuç alıyoruz. Aldığımız sonucun 4.madde de aldığımız sonuçla aynı olduğunu görüyoruz.

Örnekler SQL Server 2012 Enterprise Edition ile gerçekleştirilmiştir. SQL Server 2008 R2 Enterprise Edition ve Standart Edition ve SQL Server 2008 Standart Edition ile gerçekleştirdiğimde de aynı sonuçları aldım. Optimize for ad hoc workloads’un amacı single use kullanılan sorguların ilk çalıştırıldığı anda query planlarını oluşturmayıp, memory’de gereksiz yer kaplamamasıdır. Optimize for ad hoc workloads ile beraber forced parameterization’ı enable ettiğimizde sorgu parametrize olabiliyorsa her halükarda ilk çalıştırıldığında query plan oluşturulacağı için ikisini bir arada kullanmanın çok mantıklı olmayacağını düşünüyorum. Forced parameterization kullanmak yerine, uygulamalarınızdan gelen sorguları parametreli hale getirmeniz daha faydalı olacaktır.

Execution Plan Nedir

Execution plan sorgunun nasıl çalışacağını(index mi kullanacak yoksa tabloyu tamamen tarayacak mı? Ne çeşit bir join yapacak? nested loops, merge joins, hash joins vb) ve sorgudaki her bölümün sorguya maliyet oranını belirleyen(örneğin index seek yani index üzerinde ihtiyacı olan kaydı tarama yapmadan direk bulması gibi. Örneğin bu index seek işleminin sorguya maliyetide %1 olabilir) yol haritasıdır. SQL Server’ a gönderdiğiniz bir TSQL sorgusu yavaş çalıştığında ilk bakılacak yer sorgunun execution plan’ıdır. Estimated execution plan ve actual execution plan olarak iki çeşidir vardır. Estimated execution plan sorguyu çalıştırmadan, çalıştırıldığı takdirde kullanacağı execution plan’ı tahmin eder. Actual execution plan ise sorgu çalıştırıldıktan sonra gerçekten kullandığı execution plan’ı ifade eder.  Execution plan’ı anlamanın en iyi yolu bir örnek üzerinden gitmek olacaktır. SSMS’i açıyoruz ve AdventureWorks2014 veritabanında Person.Adress Tablosuna  tıkladıktan sonra new query deyip aşağıdaki sorguyu yazıyoruz.

SELECT * FROM Person.Address

Daha sonra aşağıdaki resimdeki işaretli yere tıklıyoruz.

Yukarıda gördüğünüz gibi sorgu çalışmadı fakat çalıştırdığı takdirde Clustered Index Scan yapacağınız ve bu işlemin sorguya maliyetinin %100 olduğunu tahmin etti.

Aşağıda ok ile işaretli yere tıklayarak F5’e basalım ve sorgumuzu çalıştıralım.

Gördüğünüz gibi sorgu çalıştı kayıtlar geldi ve Execution plan’ı oluşturdu.

Yukarıdaki resimdeki altı çizili Execution Plan sekmesine tıklayıp actual execution plan’ı görebiliriz.

Bizim sorgumuzda aşağıda gördüğünüz gibi estimated ve actual execution plan aynı sonucu verdi.

“SQL Server’da Index Kavramı ve Performansa Etkisi“, “SQL Server’da İstatistik Kavramı ve Performansa Etkisi” ve “Execution Planı Yorumlayarak Performans İyileştirmesi Yapmak” isimli makalelerimde ilginizi çekebilir. Ayrıca “SQL Server’da Join Türleri” isimli makaleme de göz atmak isteyebilirsiniz.