SQL Server High Compilations per Batch and Plan Cache Bloat
Separates single-use ad-hoc plans from frequently recompiled queries and addresses app parameterization and narrowly scoped server settings.
Operational Safety & Risk Notice
This runbook contains operational intervention steps; several are write-impacting (KILL, terminate, DROP, RESIZE). Verify in a staging environment first, and apply sequentially in production with verification.
1. Symptom & Signals
- M35 derleme/batch oranı kalıcı olarak yüksek; eşlik eden CPU baskısı olabilir.
- Plan cache'te `Adhoc` ve `usecounts = 1` planlar çok yer tutar; Query Store'da aynı SQL için çok sayıda metin/plan oluşur.
2. Business & Technical Impact
Derleme CPU'su artar, faydalı planlar cache'ten atılır; sık çalışan sorgularda kararsız gecikmeler görülür.
3. Diagnosis & Root-Cause Analysis
SELECT counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%:SQL Statistics%'
AND counter_name IN ('SQL Compilations/sec', 'Batch Requests/sec', 'SQL Re-Compilations/sec');5 dakika sonra tekrar edin: `Δcompilations / NULLIF(Δbatch_requests,0)`; sıfır paydada oran üretmeyin.
SELECT objtype, COUNT(*) AS plan_count,
SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS single_use_count,
SUM(CASE WHEN usecounts = 1 THEN CONVERT(bigint,size_in_bytes) ELSE 0 END)/1048576 AS single_use_mb
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY single_use_mb DESC;4. Remediation & Action Plan
EXEC sys.sp_configure N'show advanced options', 1;
RECONFIGURE;
EXEC sys.sp_configure N'optimize for ad hoc workloads', 1;
RECONFIGURE;Sunucu geneli değişikliktir; çalışma yükünün değişimini izleyin. Bazı planlar her çalışmada yeniden derleniyorsa bu ayar tek başına çözmez.
4. **Yapmayın:** `DBCC FREEPROCCACHE` ile tüm plan cache'i boşaltmak CPU/derleme patlaması yaratabilir; `ALTER DATABASE ... SET PARAMETERIZATION FORCED` değerini test etmeden yaymayın.
5. Verification & Health Checks
- M35 aynı pencere boyunca düşüyor; tek kullanımlık planların cache payı ve CPU basıncı azaldı.
- Query Store'da kritik sorguların çalışma süreleri ve plan kararlılığı bozulmadı.
6. Rollback & Contingency Plan
- Ayarı geri alın: `EXEC sys.sp_configure N'optimize for ad hoc workloads', 0; RECONFIGURE;`.
- Uygulama parametreleme değişikliklerini deployment geri alımıyla döndürün; ayar değişikliği cache'te önceden oluşan planları kendiliğinden temizlemez.