optimize for ad hoc workloads nedir?

optimize for ad hoc workloads plan : ad hoc diye adlandırılan tek kullanımlık sql query’lerinin, plan önbelleğindeki verimliliğini arttırmak için kullanılır. Ayar “açık” (1) olarak yapılandırıldığında Database Engine ad hoc query’lerin plan’larını tam derlenmiş plan (compiled plan) yerine, küçük derlenmiş plan taslağı (compiled plan stub) olarak tutmaktadır.

Demo

Öncelikle bu özelliği kullanmadığımızda durumun ne olduğunu gözlemlemek için optimize for ad hoc workloads‘ı kapatıyoruz.

Plan belleğinde daha rahat çalışabilmek adına plan belleğini temizliyorum.

SELECT * FROM Users WHERE DisplayName='Community';
GO
SELECT * FROM Users WHERE DisplayName='Yunus UYANIK';
GO
SELECT * FROM Users WHERE DisplayName='Yunus UYANIK';
GO

Adhoc query’lerimi çalıştırıyorum.

Plan belleğine göz attığımda; Query’lerin kaç kez çalıştığı önemsenmeden, Compiled Plan oluşturulduğunu ve bu iki planın da plan belleğinde 16KB’lık yer kapladığını görüyorum.

Şimdi ise optimize for ad hoc workloads‘ı açarak neler olduğunu görelim.

Tekrar plan temizliyorum.

SELECT * FROM Users WHERE DisplayName='Community';
GO
SELECT * FROM Users WHERE DisplayName='Yunus UYANIK';
GO
SELECT * FROM Users WHERE DisplayName='Yunus UYANIK';
GO 

Ve query’lerimi tekrar çalıştırıyorum ancak DisplayName='Yunus UYANIK'; geçen query’imi iki kez çalıştırıp Database Engine’in AdHoc olmadığını düşünmesini istiyorum.

Bu kez plan belleğine baktığımda DisplayName='Community'; geçen query’im için bir Compiled Plan Stub oluştuğunu, bu planın yalnızca 448 byte olduğunu ve query_plan kolonunun NULL olduğunu görüyorum.

DisplayName='Yunus UYANIK'; geçen query’imde bu durum değişiyor. Query’i iki kez çalıştırmış olmama rağmen execution count=1 olarak gözüküyor. Database Engine bu query’i ilk kez çalıştırdığımda bir Compiled Plan Stub oluşturmuştu, query’i tekrar çalıştırdığımda Compiled Plan Stub‘ı plan önbelleğinden kaldırıp bir Compiled Plan oluşturdu ve plan boyutu 16KB oluyor. Bu sayede Database Engine plan belleğinde gereksiz bir plandan kurtuluyor ve memory’i daha verimli kullanabiliyor.

optimize for ad hoc workloads ayarı değiştirildiğinde yalnızca yeni planlar etkilenir var olan planlarda da bu değişikliğin uygulanması için plan belleğini temizlemek gerekmektedir.

Query’lerin ne durumda olduğunu görmek için sys.dm_exec_cached_plans dmv’inin cacheobjtype kolonununu gözlemleyebilirsiniz.