SQL Server - “optimize for ad hoc workloads” Parametresi ile Memory’i Daha Randımanlı Kullanmak

SQL Server - “optimize for ad hoc workloads” Parametresi ile Memory’i Daha Randımanlı Kullanmak
Daha önceki bir çok yazımda da belirttiğim gibi, SQL Server çalıştırdığı query’ler için oluşturduğu query planları daha sonra kullanmak için Plan Cache’de yani memory de saklar. Daha sonraki kullanımlarda tekrar query plan oluşturmakla zaman kaybetmez, daha önce plan cache’e kaydettiği plan’ı kullanır ve buda performans artışı olarak bize geri döner.
Hem procedureler gibi parameterize edilebilen sorgular için hemde ad hoc gibi yani parameterize edilemeyen sorgular için query plan’lar oluşturulur ve plan cache’de saklanır. Procedure’ler için oluşturulmuş query plan’lar bize çok fazla problem çıkarmayacaktır. Çünkü bu planlar, sorguların parametrik olmalarından dolayı binlerce kez kullanılacaktır. Ama Ad Hoc sorgular içerdikleri parametreler ile cache’lenir ve aynı sorgu farklı bir parametre ile geldiğinde yeni bir plan cache oluşturulur. Dolayısıyla çok büyük ihtimalle ad hoc query’ler için oluşturulmuş query plan’lar sadece bir kez kullanılacaktır. Buda bize memory’de gereksiz yere yer işgali olarak geri dönecektir.

Bugünkü yazımda Ad Hoc için oluşturulan query plan’larının daha az yer kaplamasını nasıl sağlayacağımızı ve dolayısıyla memory’nin daha randımanlı nasıl kullanabileceğini görüyor olacağız.

Ufak bir örnekle Ad Hoc - Procedure kullanımının farkını açıklamaya çalışayım. Örneklerimde DBCC FREEPROCCACHE ve sys.dm_exec_cached_plans komutlarını kullanacağım için daha önce bu komutlar üzerine yazdığım makaleleri okumanızı tavsiye ederim.

Örneğimizde AdventureWorks’teki bir sp’yi 3 kez, birde Ad Hoc Query’i farklı where statement ile 2 kez çalıştıracağım.
1 --Her satır ayrı ayrı çalıştıralacaktır.
2 --use AdventureWorks
3 --SP yi 3 kez çalıştır
4 --exec dbo.uspGetEmployeeManagers 11;
5 --exec dbo.uspGetEmployeeManagers 11;
6 --exec dbo.uspGetEmployeeManagers 11;
7 --Ad Hoc sorguyu farklı parametre ile 2 kez çalıştır.
8 --select * from Person.Address where City='Bothell';
9 --select * from Person.Address where City='Portland';

Şimdi cache’len query plan’larını sorgulayalım.
1
select st.text,*
2
from sys.dm_exec_cached_plans cp
3
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
4
where (st.text like '%uspGetEmployeeManagers%'
5
or st.text like '%select * from Person.Address%')
6
and st.text not like '%select st.text%'

Gördüğünüz gibi SP için bir query plan oluşturuldu ve 3 kez aynı plan kullanıldı. Ad Hoc query’ler için ise, her bir query için bir plan oluşturuldu.

İşte bu şekilde Ad Hoc query’lerin her biri için plan oluşturulmakta ve bir çoğu 1 den fazla kullanılmamakta. Bizim amacımız bu 1 kez kullanılan plan’ların cache’de kapladığı boyutu düşürerek memory’nin diğer objeler için kullanılmasını sağlamak.

Bu amacımıza erişmek için kullanacağımız parametre “optimize for ad hoc workloads” server parametresi. Önce bir örnek yapıp daha sonra bu parametrenin ne işe yaradığını açıklayalım.

Örneğimizde Person.Address tablosuna 5 değişik where statement’ı ile select çekeceğiz.

01
--Her satır ayrı ayrı çalıştıralacaktır.

02
use AdventureWorks

03
--Once Plan Cache'i Temizleyelim.

04
DBCC FREEPROCCACHE

05
--Ad Hoc sorguyu farklı parametre ile 5 kez çalıştır.

06
select * from Person.Address where City='Bothell';

07
select * from Person.Address where City='Portland';

08
select * from Person.Address where City='Orlando';

09
select * from Person.Address where City='Montreal';

10
select * from Person.Address where City='Calgary';

Cache’lenmiş planlara bakalım.

Beklediğimiz gibi 5 ad hoc sorgusuda ayrı ayrı cache’lendi. Asıl acı olan belki bir daha hiç kullanılmayacak bu plan’ların her birinin memory’de 24 KB yer kaplaması. Bu boyut gözünüze küçük gözükebilir “24 KB mı kurtaracak benim memory’i” diyebilirsiniz. Production ortamında bu gereksiz planların 1-2 GB’a kadar yer kapladığına gözüyle şahit olan biri olarak şunu söyleyebilirim ki, 24 KB diyip geçmemek lazım :)

Bir kez kullanılan bu ad hoc query’lerin daha az yer kaplaması için “optimize for ad hoc workloads” parametresini 1 yapalım.
1

sp_configure 'optimize for ad hoc workloads',1

2

reconfigure

Şimdi select cümlelerinin olduğu sorgu bloğunu tekrar çalıştıralım.

01
--Her satır ayrı ayrı çalıştıralacaktır.
2
use AdventureWorks
03
--Once Plan Cache'i Temizleyelim.

04
DBCC FREEPROCCACHE
05
--Ad Hoc sorguyu farklı parametre ile 5 kez çalıştır.
06
select * from Person.Address where City='Bothell';
07
select * from Person.Address where City='Portland';
08
select * from Person.Address where City='Orlando';
09
select * from Person.Address where City='Montreal';
10
select * from Person.Address where City='Calgary';
Cache’lenen planlara bakalım.

Az önce 24 KB yer kaplamakta olan planlar şimdi sadece 320 byte yer kaplamakta. Yani nerdeyse 100’de 1.

Peki ama nasıl oluyor bu.
Orlando ve Montreal sorgularını 1 kez daha çalıştıralım.
1
select * from Person.Address where City='Orlando';
2
select * from Person.Address where City='Montreal';

Gördüğünüz gibi 2 defa çalıştırdığım sorguların planları “Compiled Plan” halini dönüşüp 24KB yer kaplar hale geldiler. Diğer planlarım ise “Compiled Plan Stub” tipindeler.
İşin özü şu. Bu parametre aktif hale getirildikten sonra çalıştırılan sorgular için oluşturulan query plan’ların sadece belli bir kısmı cache’lenmekte. Bu kısımda, bir sonraki aynı sorgunun çalıştırılmasında eşleştirebilmek için gerekli olan kısım. Bu kadar basit :)