optimize for ad hoc workloads - Performans ve Bellek Uzerindeki Etkisi
Hepimizin bildiği gibi bellek kullanımı Sql Server için çok önemlidir, Sql Server sorguların execution planlarınıda bellekte tutmaktadır. Planlar bellek üzerinde ciddi anlamda yer kaplamaktadır ki bu planları incelediğimizde bir çok sorgunun sadece 1 kez kullanıldığına şahit olmaktayız, bu tür sorgular için Ad-Hoc query denilmektedir. Sql Server 2008 ile birlikte gelen “optimize for ad hoc workloads” sistem parametresi sayesinde Sql Server’ın ad-hoc sorguları için çok daha küçük boyutta execution plan kayıtları tutulmasını sağlamaktadır. Tabiki bu parametrenin enable edilmeden önce sistemlerin çok iyi incelenip gerçekten Ad-Hoc sorguların bellekte ne kadar yer kapladığının kontrol edilmesinde fayda olacaktır.
Default olarak bu ayar Disable olmakla birlikte aşağıdaki script ile bu ayarın o anki değerini kontrol edebiliriz.
select value_in_use
from sys.configurations
where name='optimize for ad hoc workloads'
GO
Bu ayarı değiştirmeden önce ad-hoc bir kaç sorgu oluşturup bellekte ne kadar yer kapladığını görmek daha sonra da bu ayarı Enabled edip aynı sorguların bellekte ne kadar yer kapladığını bularak sistemize olan etkisini görebiliriz.
Bu örneğe başladan önce Test Sistemimizdeki execution plan önbelleğini temizlemek daha kolay bir şekilde değerleri görmemizi sağlayacağından dolayı aşağıdaki script’ler ile bu işlemleri gerçekleştirebiliriz.
DBCC DROPCLEANBUFFERS; --Buffer Pool temizlemek için kullanılır.
DBCC FREEPROCCACHE; -- Plan Önbelleğinin temizlemek için kullanılır.
GO
Bu test işlemi için yine AdventureWorks2012 veritabanımızı kullanıyoruz.
Declare@sql nvarchar(4000),
@SalesOrderIdVarchar(20)
set@SalesOrderId= 45296
set@sql ='select * from Sales.SalesOrderHeader sh JOIN Sales.SalesOrderDetail sod on sh.SalesOrderID = sod.SalesOrderID
WHERE sh.SalesOrderID = '+ @SalesOrderId
EXECUTE (@SQL)
Yukarıdaki gibi bir Ad-Hoc Query kullanımı sonunda Sql Server tarafından bu sorgu için bir plan oluşturuldu ve bu planda Sql Server’ın plan önbelleğine atılmış oldu. Şimdi aşağıdaki script’ler aracılığı ile bu planın bellekte ne kadar yer kapladığını görmek için sistem dmv lerinden yararlanıyoruz.
SELECT count(*) as Count
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
where st.text LIKE '%SalesOrderID%'
and st.text NOT LIKE '%dm_exec_query_stats%';
GO
SELECT Sum(size_in_bytes) as Bytes
FROM sys.dm_exec_query_stats qs
JOIN sys.dm_Exec_cached_plans cp on cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
where st.text LIKE '%SalesOrderID%'
and st.text NOT LIKE '%dm_exec_query_stats%';
GO
Şimdi ise “optimize for ad hoc workloads” parameteresini enable ederek aynı sorgunun ne kadar alan kapladığını görelim.
Öncellikle bu ayarı enable etmek için aşağıdaki script’lerden yardım alıyoruz.
exec sp_configure 'advanced', 1;
RECONFIGURE
execsp_configure'optimize for ad hoc workloads', 1;
RECONFIGURE
GO
Şuanki değerin ne olduğunu kontrol etmek için tekrar yukarıda kullanıdğımız script kullanarak kontrolunu sağlayabiliriz.
Tekrar testimize başlamadanönce sistemizdeki tüm planları silmek için aşağıdaki script’i kullanıyoruz.
DBCC DROPCLEANBUFFERS; --Buffer Pool temizlemek için kullanılır.
DBCC FREEPROCCACHE; -- Plan Önbelleğinin temizlemek için kullanılır.
GO
ve tekrar aynı Ad-Hoc sorgumuzu çalıştırıp sistemde ne kadar alan apladığını kontrol ediyoruz.
Declare@sql nvarchar(4000),
@SalesOrderIdVarchar(20)
set@SalesOrderId= 45296
set@sql ='select * from Sales.SalesOrderHeader sh JOIN Sales.SalesOrderDetail sod on sh.SalesOrderID = sod.SalesOrderID
WHERE sh.SalesOrderID = '+ @SalesOrderId
EXECUTE (@SQL)
Görüldüğü üzere aynı sorgu için bu kez sadece 272 byte’lık bir plan oluşturulmuş oldu unutmamak gerekirki aynı sorgu 2.kez çalıştırıldığında tekrardan tüm plan oluşturulacağından dolayı bellekte ilk çalıştırdığımız kadar yer kaplayacaktır.
Gelecek makalede görüşmek üzere,
Esenle kalın Sql Server da kalın.
Hayri ÖZLER