Bu makalede MSSQL Server Optimize for Ad Hoc Workloads ve Parameterization konusuna değinmiş olacağız. SQL Server’da “Optimize for Ad Hoc Workloads”, ad hoc sorguların plan önbelleğinde fazla yer kaplamasını önlemek için kullanılan bir ayardır.
SQL Server, her çalıştırılan sorgunun *execution planını* önbelleğe alır. Eğer ad hoc (tek seferlik) sorgular çok fazla çalıştırılıyorsa, bu gereksiz execution planları önbelleği doldurur. Bellek (Memory) israfı oluşur ve performans düşer.
“Optimize for Ad Hoc Workloads” açık olduğunda, SQL Server bir sorguyu ilk çalıştırdığında sadece bir plan “stub” kaydeder. Eğer aynı sorgu ikinci kez çalıştırılırsa, SQL Server tam planı önbelleğe alır. Böylece tek seferlik sorgular gereksiz yere plan önbelleğini tüketmez.
Aşağıdaki sorgu yardımıyla Optimize for Ad Hoc Workloads özelliğini aktif edebiliriz.
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;
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.
Tekrardan kapatmak için aşağıdaki komut kullanılmaktadır.
EXEC sp_configure 'optimize for ad hoc workloads', 0;
RECONFIGURE;
Aşağıdaki komut yardımıyla mevcut durumu kontrol edebiliriz.
SELECT name, value, value_in_use
FROM sys.configurations
WHERE name = 'optimize for ad hoc workloads';
Bu çok fazla tek seferlik sorgu çalıştırıyorsak ve aynı zamanda bellek sıkıntısı yaşıyorsak bu özelliğin açılması gerekmektedir. OLTP sistemlerinde çok sık kullanılan sorgularımız varsa bu özelliğin kesinlikle kapatılması gerekmektedir. Tekrar eden sorgunuzun sayısı az ise bu özelliğin açılması gerekmektedir.
SQL Server’da Parameterization, sorguların yürütme planlarının paylaşılmasını ve tekrar kullanılmasını sağlayan bir mekanizmadır.
SQL Server’da iki tip Parameterization vardır:
SIMPLE
SQL Server, yalnızca belirli durumlarda otomatik olarak parametrize eder.
SELECT * FROM [Production].[Product] WHERE ProductID = 10;
SELECT * FROM [Production].[Product] WHERE ProductID = 20;
Burada SQL Server her iki sorgu için ayrı bir execution plan oluşturur (gereksiz bellek kullanımı önlemek için).
Eğer bu sorguları sp_executesql ile çalıştırırsan, tek bir execution plan kullanılır:
DECLARE @ID INT = 10;
EXEC sp_executesql N'SELECT * FROM [Production].[Product] WHERE ProductID = @ID', N'@ID INT', @ID;
FORCED
Tüm sorgular SQL Server tarafından otomatik olarak parametrize edilir. Aynı yapıya sahip sorguların tek bir execution plan kullanmasını sağlar. Bellek kullanımını düşürür, ancak bazen yanlış plan seçilmesine neden olabilir.
Aşağıdaki komut ile bu özellik açılabilir.
ALTER DATABASE AdventureWorks2014 SET PARAMETERIZATION FORCED;Tekrardan aktif edilmiş özelliği geri almak için aşağıdaki komut kullanılmaktadır.
ALTER DATABASE AdventureWorks2014 SET PARAMETERIZATION SIMPLE;Parameterization Ne Zaman Kullanılmalı
Eğer benzer yapıda çok fazla sorgu çalıştırıyorsanız Forced kullanılabilir. Eğer sorguların execution planı çok değişkense ve Farklı parametrelerle farklı execution planları gerekiyorsa.
Optimize for Ad Hoc Workloads, SQL Server’ın ilk çalıştırmada sadece “stub” plan kaydetmesini sağlar, böylece gereksiz bellek tüketimi azalır.
Parameterization, aynı yapıda sorguların tekrar kullanılmasını sağlar, böylece her seferinde yeni bir execution plan oluşturulmasını engeller.
En iyi performansı almak için sistemdeki sorgu tiplerini analiz edip uygun olanı seçmelisin.
Şimdi detaylı bir şekilde AdventuresWorks2014 üzerinde detaylı bir şekilde işlemlerimize değinelim.
Aynı yapıda farklı değerlerle birçok sorgu çalıştırılıyor. Varsayılan durumda SQL Server her sorgu için ayrı execution plan oluşturur.
SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = 707;
SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = 708;
SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = 709;
Her bir sorgu için ayrı execution plan saklanır. Bellek gereksiz yere dolar. Daha sonra Optimize for Ad Hoc Workloads aktif edilir.
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;
Not: Test ortamında aşağıdaki komutla plancache temizleyebiliriz. Gerçek sistem üzerinden yapılmamalıdır.
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERSKaldığımız yerden devam edecek olursak Bellek kullanımını kontrol etmek için aşağıdaki komut kullanılmaktadır.
SELECT objtype, cacheobjtype, usecounts, size_in_bytes,text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE text LIKE '%Sales.SalesOrderDetail%';
Eğer usecounts = 1 ise, yalnızca stub kaydedilmiş demektir.
Eğer usecounts > 1 ise, plan tam olarak önbelleğe alınmış demektir.
Yukarıda dikkat edersek herhangi bir önbelleğe alınma söz konusu değildir. Yukarıdaki select sorgularımızı tekrardan çalıştırdığımızda sorgular aynı olmasına rağmen farklı planlar oluşturulduğunu görüyoruz.
Aşağıdaki komut ile parameterization durumunu kontrol edebiliriz
SELECT name, is_parameterization_forced
FROM sys.databases
WHERE name = 'AdventureWorks2014';
is_parameterization_forced = 0 → Simple Mode
is_parameterization_forced = 1 → Forced Mode
Yukarıda yapmış olduğumuz ilk örnek simple parameterization yapısına örnek olarak verebiliriz. Çünkü her sorgu farklı execution plan kullanır. Eğer parametre değerleri değişkense, SQL Server yeni planlar oluşturur.
Eğer ilgili veritabanı üzerinde Forced parameterization yapısını aktif edersek neler olacağını gözlemleyelim.
ALTER DATABASE AdventureWorks2014 SET PARAMETERIZATION FORCED;
Bu yapımızda sql server’ın tüm benzer sorgular için aynı execution plan yapısını kullandığını dile getirmiştik. Forced parameterization etkin hale gelmesi için eski planların kullanımdan düşmesi gerekmektedir.
SELECT objtype, cacheobjtype, usecounts, size_in_bytes,text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE text LIKE '%Sales.SalesOrderDetail%';
Tekrar simple modda kullanmış olduğumuz select ifadelerini çalıştıralım.
Forced Mode sayesinde bu üç sorgu da tek bir execution plan kullanır. Bellek tüketimi azalır, ancak bazen yanlış plan seçilmesine neden olabilir.
Tekrardan aşağıdaki komut ile Forced moddan çıkılabilir.
ALTER DATABASE AdventureWorks2014 SET PARAMETERIZATION SIMPLE;Aşağıdaki sorgu en çok çalıştırılan sorguları görmemizi sağlar. Eğer dönen sonuçta birçok sorgunun execution count değeri 1 ise sistemde yüksek sayıda adhoc sorgu var demektir. Aşağıdaki sorgu gerçekte çalışmış ve istatistik tutulan planlar. Anlık duruma göre plan handle yapısı değişmektedir.
SELECT
qs.execution_count,
qs.plan_handle,
qs.total_worker_time,
st.text
FROM
sys.dm_exec_query_stats qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY
qs.execution_count DESC;282 bin dönen plandan 230 bine yakın sorgumuz 2. defa çalıştırılmamış. Buda Ad Hoc Workloads yapısının açılması gerektiğini göstermektedir.
Aşağıdaki sorgu ile oluşan planların sadece bir kere çalıştığını, kaç tanesinin tekrarlandığı ve plan stub olup olmadığını görmek için aşağıdaki sorgu kullanılmaktadır. Yukarıdaki ifadeye göre daha kesin bir değer vermektedir.
SELECT
cp.objtype,
COUNT(*) AS plan_count,
SUM(CASE WHEN qs.execution_count = 1 THEN 1 ELSE 0 END) AS single_use_plans,
SUM(CASE WHEN qs.execution_count > 1 THEN 1 ELSE 0 END) AS reused_plans
FROM
sys.dm_exec_cached_plans cp
LEFT JOIN
sys.dm_exec_query_stats qs ON cp.plan_handle = qs.plan_handle
WHERE
cp.cacheobjtype = 'Compiled Plan'
AND cp.objtype = 'Adhoc'
GROUP BY
cp.objtype;
- plan_count: Toplam ad hoc plan sayısı
- single_use_plans: Sadece 1 kez kullanılan planlar
- reused_plans: En az iki kez kullanılan planlar. Tekrarı gelen sorgular
Yani sadece yaklaşık %15’lik bir kesim istatistiklerde görünüyor ve o kesimin içinde de:
• Yaklaşık %73 (37,496 / 51,696) tek seferlik,
• Yaklaşık %27 (14,200 / 51,696) tekrar eden sorgular.
Peki yukarıdaki sorgumuz sonucu 285 bine yakın plan neden görünmüyor. Bunlar sistem işlemleri veya arka planda çalışan otomatik sorgular olabilir. SQL Server, bellek yönetimi gereği dm_exec_query_stats verilerini zamanla silip, ama planı bir süre daha cache’de tutabilir. Özellikle az kullanılan sorguların istatistiği daha hızlı silinir. Plan oluşturulmuş ama yürütme başlamadan expire olmuştur.
Yukarıda sebeplerden ötürü optimize for ad hoc workloads ayarını AÇIK bırakmamız, plan önbelleğini verimli kullanmak açısından oldukça mantıklı.
Aksi halde, bu yüz binlerce tek seferlik plan bellekte tam olarak yer kaplar ve gereksiz bellek tüketimi, plan cache pressure, performans düşüşü gibi sonuçlar doğar.
Sadece istatistiği olan plan handleleri görmek için aşağıdaki sorgu kullanılmaktadır. Çünkü bir sorgunun planı olup herhangi bir istatistik değerine sahip olmayabilir. Sql server o planı daha kullanmıyordur da diyebiliriz. Yukarıdaki sorgunun daha minimize edilmiş şekli.
SELECT
COUNT(*) AS matched_plans,
SUM(CASE WHEN qs.execution_count = 1 THEN 1 ELSE 0 END) AS single_use,
SUM(CASE WHEN qs.execution_count > 1 THEN 1 ELSE 0 END) AS reused
FROM
sys.dm_exec_cached_plans cp
JOIN
sys.dm_exec_query_stats qs ON cp.plan_handle = qs.plan_handle
WHERE
cp.cacheobjtype = 'Compiled Plan'
AND cp.objtype = 'Adhoc';
Bu makalede Optimize for Ad Hoc Workloads ve Parameterization kaba taslak değinmiş olduk.
Başka makalede görüşmek dileğiyle.
“İman edip dünya ve âhiret için yararlı işler yapanlara gelince, onları da nimetlerle dolu, içinde ebedî kalacakları cennetler bekliyor. Bunu Allah gerçek olarak vaad etmiştir. O azîzdir, hakîmdir.”Lokman-8-9
