Optimize for Ad Hoc Workloads: SQL Server’da Plan Cache Şişmesini Önlemek

SQL Server her sorgu için bir çalıştırma planı derler ve bunu plan cache’te saklar; aynı sorgu yeniden geldiğinde planı tekrar derlemek yerine cache’teki planı kullanır. Ancak birçok uygulama, değerleri sorgu metninin içine gömerek her seferinde biraz farklı bir sorgu gönderir. WHERE MusteriNo = 1001 ile WHERE MusteriNo = 1002 SQL Server için iki ayrı sorgudur. Sonuç: plan cache, bir daha hiç kullanılmayacak binlerce tek kullanımlık planla dolar ve yeniden kullanılabilir planlara, hatta buffer pool’a ayrılabilecek bellek boşa gider.

Bu yazıda plan cache şişmesini nasıl ölçeceğinizi, optimize for ad hoc workloads ayarının ne yaptığını ve sorunun asıl kaynağını nasıl çözeceğinizi anlatıyoruz.

Plan Cache Şişmesini Ölçmek

Önce sorunun gerçekten var olup olmadığına bakın. Aşağıdaki sorgu, plan cache’teki planları türüne göre gruplar ve yalnızca bir kez kullanılmış planların ne kadar bellek kapladığını gösterir:

SELECT objtype,
       COUNT(*)                                             AS plan_count,
       SUM(CAST(size_in_bytes AS bigint)) / 1048576         AS total_mb,
       SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END)       AS single_use_plans,
       SUM(CASE WHEN usecounts = 1 THEN CAST(size_in_bytes AS bigint) ELSE 0 END) / 1048576 AS single_use_mb
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY total_mb DESC;

Adhoc satırında tek kullanımlık planlar toplam plan cache’in önemli bir bölümünü (örneğin birkaç yüz megabayt ya da gigabaytlar) oluşturuyorsa, plan cache şişmesi yaşıyorsunuz demektir.

Optimize for Ad Hoc Workloads Ne Yapar?

Bu ayar açıkken SQL Server, bir sorgu ilk kez çalıştığında planın tamamını değil, yalnızca küçük bir plan stub saklar. Aynı sorgu ikinci kez gelirse plan yeniden derlenir ve bu kez tamamı cache’e yazılır. Böylece bir daha hiç çalışmayacak sorgular, cache’te tam plan boyutu yerine yalnızca birkaç yüz baytlık yer kaplar.

EXEC sys.sp_configure N'show advanced options', 1;
RECONFIGURE;
EXEC sys.sp_configure N'optimize for ad hoc workloads', 1;
RECONFIGURE;

Ayar yeniden başlatma gerektirmez. Mevcut planları etkilemez; yalnızca bundan sonra derlenen yeni sorgular için geçerlidir. Aynı ayar SSMS’te sunucu özelliklerinin Advanced sayfasında da bulunur:

Açmalı mısınız?

Çoğu sunucu için evet. Bedeli küçüktür: ikinci kez çalışan bir sorgu bir kez fazladan derlenir. Kazancı ise ad hoc sorguların yoğun olduğu sistemlerde yüzlerce megabayt bellektir. Bu yüzden ayar, birçok DBA’nın standart kurulum listesinde yer alır.

Asıl Çözüm: Parametrelendirme

Optimize for ad hoc workloads belirtiyi hafifletir, nedeni ortadan kaldırmaz. Kalıcı çözüm, uygulamanın parametreli sorgular göndermesidir:

-- Kötü: değer sorgu metnine gömülü, her değer yeni bir plan demek
-- SELECT * FROM dbo.Siparis WHERE MusteriNo = 1001;

-- İyi: parametreli sorgu, tek plan yeniden kullanılır
EXEC sys.sp_executesql
     N'SELECT SiparisNo, Tarih, Tutar FROM dbo.Siparis WHERE MusteriNo = @MusteriNo',
     N'@MusteriNo int',
     @MusteriNo = 1001;

ORM’lerde ve uygulama kütüphanelerinde parametreli komutları kullanmak, sorguların hem plan cache’i verimli kullanmasını hem de SQL injection’a karşı korunmasını sağlar.

Uygulamayı değiştiremiyorsanız iki seçenek daha vardır:

• Forced parameterization: ALTER DATABASE SatisDB SET PARAMETERIZATION FORCED; SQL Server, sorgulardaki sabitleri otomatik olarak parametreye çevirir. Plan cache’i ciddi şekilde rahatlatabilir; ancak bazı sorgularda parametre hassasiyeti nedeniyle daha kötü planlara yol açabilir. Önce test ortamında Query Store ile ölçün.
• Plan guide’lar ve Query Store hint’leri: Belirli sorgu kalıpları için parametrelendirmeyi hedefli olarak açmak.

Kontrol Listesi

• Plan cache’teki tek kullanımlık ad hoc planların boyutu ölçüldü.
• optimize for ad hoc workloads açık.
• En çok tek kullanımlık plan üreten sorgu kalıpları belirlendi ve uygulama ekibiyle paylaşıldı.
• Uygulama değiştirilemiyorsa forced parameterization test ortamında ölçüldü.
• FREEPROCCACHE rutin bir bakım adımı olarak kullanılmıyor.