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.