Check SQL Server Plan Cache Pollution (II) in 45 Seconds, The "45 Seconds DBA Series" | Part 23
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 (I). 👉 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 (II) in 45 Seconds
The "45 Seconds DBA Series" | Part 23
You fixed ad-hoc queries, but your server is still screaming? In this post, I’ll expose why even perfect parameterization can lead to "reuse toxicity" and massive CPU spikes.
✔️ Parameterized Pollution: Cache pollution isn't just "too many plans"; it's also "the wrong plan reused everywhere." 💣
✔️ Reuse Toxicity: A plan optimized for a single row is forced onto a million-row result set, killing performance. 📉
✔️ Plan Instability: Dramatic swings betweenmin_worker_timeandmax_worker_timeindicate Parameter Sniffing warfare. ⚔️
✔️ Resolution: Leverage Query Store,query_hashidentification, and SQL 2022 PSP Optimization. 🚀
Hi SQL SERVER Guys and Gals,
In the previous part of this series, we cleared out the "junk" plans. But here is the hard truth: clean code doesn't guarantee a clean cache. Even when your application is 100% parameterized, SQL Server can still suffer from a different kind of pollution: Logical Toxicity. This happens when the engine reuses a suboptimal execution plan because it "sniffed" a non-representative parameter during compilation.
🔍 DIAGNOSIS: The Hidden War Inside the Cache
⚡ Parameter Sniffing: When a procedure or query is first executed, SQL Server looks at the parameter values to build the most efficient plan. If that first execution is for a "rare" value, it creates a plan that is poisonous for the "common" values used later.
💣 Cache Reuse Toxicity: This isn't about memory bloat; it's about CPU bloat. You have one plan in the cache (good for memory), but it’s the *wrong* plan for 90% of your users.
- query_hash: Identifies queries that are logically identical.
- query_plan_hash: Identifies the exact physical execution plan being used.
If you see one query_hash mapped to high max_worker_time while min_worker_time is low, you are witnessing Plan Instability.
🧪 QUERY: Detecting Toxic Plan Reuse
Run this query to find queries that are performing inconsistently. Large gaps between Min and Max CPU time are a smoking gun for Parameter Sniffing.
-- 🔍 WOW Query: Identify Plan Instability & Toxic Reuse
SELECT TOP 20
qs.query_hash,
qs.execution_count,
CAST(qs.min_worker_time / 1000.0 AS DECIMAL(10,2)) AS MinCPUMs,
CAST(qs.max_worker_time / 1000.0 AS DECIMAL(10,2)) AS MaxCPUMs,
CAST((qs.total_worker_time / qs.execution_count) / 1000.0 AS DECIMAL(10,2)) AS AvgCPUMs,
st.text AS QueryText,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.execution_count > 5
AND (qs.max_worker_time / (qs.min_worker_time + 1)) > 10 -- Max is 10x higher than Min
ORDER BY qs.max_worker_time DESC;
Source: sys.dm_exec_query_stats (Microsoft Learn)
🚀 FIX: Stabilization Strategies
Fixing toxic reuse requires moving beyond just "parameterizing." You need to force stability:
- 🧠 Query Store: Use it to find the "Regressed Queries" and force the last known good plan.
- ⚡ PSP Optimization: If you are on SQL Server 2022, the engine now natively handles multiple plans for different parameter sizes (Parameter Sensitive Plan optimization).
- 🔧 Recompile Hint: Use
OPTION (RECOMPILE)only for highly volatile queries where the cost of compilation is lower than the cost of a bad plan. - 🚀 Plan Guides: Force a specific join or index usage to prevent the optimizer from getting "creative."
🚀 My REAL Strategy
In 25 years of tuning, I've learned that Parameter Sniffing is the #1 cause of "emergency" CPU spikes. Don't just clear the cache with DBCC FREEPROCCACHE—that's a band-aid that often makes things worse because it triggers a compilation storm.
Instead, look at Query Store. If you see a query with a massive "Duration" jump but the same "Plan ID," you have toxic reuse. If you see multiple Plan IDs for the same Query ID, you have instability. Use Forced Plans to stabilize production while you rewrite the logic.
📊 TAKEAWAY: Performance is Consistency
✔️ High execution count + high Max CPU = Toxic Reuse.
The goal isn't just to save space in the plan cache; it's to ensure that when a query runs, it runs with a plan that fits the data it is processing. If your Max worker time is significantly higher than your Min, your cache isn't just polluted—it's compromised.
📢 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!
🧠 Master SQL Server Performance Hub
Expand your knowledge with my latest deep-dives and performance guides.
Performance "45 Seconds"
Indexing & Tuning
Execution Plans
Don't forget to check my April 2026 Recap for a complete monthly summary.
Comments
Post a Comment