Check SQL Server Plan Cache Pollution (III) in 45 Seconds, The "45 Seconds DBA Series" | Part 24
Before we dive into today's topic, if you missed my previous post you can take a look at "Check SQL Server Plan Cache Pollution (II) in 45 Seconds". 👉 If you found this deep-dive helpful, feel free to check out the ads—your support helps me keep creating high-quality SQL Server content for the community.
Check SQL Server Plan Cache Pollution (III) in 45 Seconds
Inside SQL Server Compilation & Recompilation | Part 24
Ever wondered why your CPU hits 100% despite low query volume? In this post, I'll tear apart the mechanics of Compilation Storms and show you how to detect "SOS_CACHESTORE" bottlenecks in under a minute.
🧠 TL;DR BOX
✔️ Compilation Cost: CPU cycles are wasted when SQL Server performs "Full Optimization" repeatedly. 💣
✔️ Trivial Plans: The fast-track to execution that skips cost-based optimization for simple queries. ⚡
✔️ Recompilation Triggers: Statistics invalidation and schema changes (DDL) are the primary suspects for cache instability. 📉
✔️ Spinlock Warning: HighSOS_CACHESTOREwait times mean your Plan Cache is under heavy contention. 🚀
Hi SQL SERVER Guys and Gals,
Managing a high-concurrency SQL Server environment is a game of millimeters. We often talk about "Fast Queries," but we rarely talk about the tax the engine pays before a query even starts: Compilation. When your plan cache is unstable, SQL Server enters a "Compilation Storm," turning your high-end CPUs into expensive heaters. Let's get under the hood.
🔍 DIAGNOSIS: Compilation vs. Recompilation
🧠 What it really is: Compilation is the CPU-intensive process where the Optimizer creates an execution plan. It happens in phases:
- ⚡ Trivial Plans: If a query is simple enough (e.g., a simple SELECT by Primary Key), SQL bypasses the cost-based optimizer to save CPU. It's the "Express Lane."
- 🧠 Full Optimization: For complex joins, SQL iterates through multiple "Search Works." This is where the real CPU cost lies.
- 💣 Recompilation: Triggered by schema changes (ALTER TABLE), statistics updates (crossing the RT - Recompilation Threshold), or explicit hints.
⚡ The Performance Killer: If you have high frequency recompiles, you hit SOS_CACHESTORE spinlocks. This occurs when multiple threads try to update the plan cache simultaneously, leading to massive context switching and latency.
🧪 QUERY: Detecting Compilation Storms
Use this diagnostic query to instantly identify if your server is spending too much time thinking and not enough time doing.
-- 🔍 CPU Diagnostic Query: Monitor Compilation vs Recompilation Rates
SELECT
[Counter] = counter_name,
[Value] = cntr_value,
[Status] = CASE
WHEN counter_name = 'SQL Compilations/sec' AND cntr_value > 100 THEN '⚠️ High Compilation Rate'
WHEN counter_name = 'SQL Re-Compilations/sec' AND cntr_value > 20 THEN '💣 Critical Recompilation Storm'
ELSE '✔️ Healthy'
END
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('SQL Compilations/sec', 'SQL Re-Compilations/sec')
AND object_name LIKE '%SQL Statistics%';
Source: sys.dm_os_performance_counters (Microsoft Learn)
🚀 FIX: Stabilizing the Optimizer
If you find your recompilation rates are spiking, take these immediate actions:
- 🚀 Optimize for Ad Hoc: Enable this server-level setting to reduce cache bloat from one-time queries.
- ⚡ Update Stats Strategy: Avoid
AUTO_UPDATE_STATISTICS_ASYNCif you cannot afford sudden plan changes during peak hours. - 🛠️ Plan Freezing: Use Query Store to force plans for mission-critical queries, bypassing the need for re-optimization.
🚀 My REAL Strategy
In my 25 years of tuning, I've seen "Schema Changes" take down enterprise systems. Even a small ALTER TABLE... ADD COLUMN can invalidate thousands of plans in milliseconds.
Strategy: Always perform DDL changes during low-traffic windows and use KEEPFIXED PLAN hints for queries sensitive to statistics volatility. If you see high SOS_CACHESTORE waits in sys.dm_os_wait_stats, you aren't looking at a disk problem; you are looking at a Concurrency/Compilation problem.
📊 TAKEAWAY
✔️ Monitor the ratio between Compilations and Recompilations daily.
✔️ Trivial plans are your friends; keep simple queries simple.
✔️ Use Query Store to stop the "Optimization" cycle for heavy queries.
📢 Support the Blog: Did you find this deep-dive helpful? The ads you see here are selected to reflect your interests. If a partner's offer catches your eye, give it a look! Your engagement helps me continue publishing premium SQL Server content for the community.
Biondi Luca @2026 - Sharing over 25 years of Gained Knowledge for Passion. Share if you like my posts!
Performance Tuning Knowledge Hub
Everything you need to master SQL Server, all in one place.
Comments
Post a Comment