sys.dm_exec_cached_plans - Usage & Examples

sys.dm_exec_cached_plans provides a window into SQL Server's plan cache, part of the buffer pool that stores compiled execution plans to avoid recompilation overhead. The plan cache operates under memory pressure using a cost-based aging algorithm. Each cached plan has an associated cost and age, with expensive plans staying longer than cheap ones.

The query optimizer compiles plans based on initial parameter values (parameter sniffing), storing them with specific cardinality estimates. When memory pressure occurs, SQL Server's Resource Monitor background process triggers plan eviction using a clock algorithm that considers both cost and recent usage. Plans with higher costs get multiple chances before eviction.

SQL Server 2016 introduced Query Store as a persistent alternative to the volatile plan cache, but sys.dm_exec_cached_plans remains the real-time view of active cached plans. The plan cache size is automatically managed as part of the buffer pool, typically consuming 10-20% of available memory on dedicated SQL Server instances.

SQL Server 2019 added intelligent query processing features that can trigger additional plan recompilations, increasing plan cache churn. SQL Server 2022 introduced parameter-sensitive plan optimization, creating multiple cached plans for the same query with different parameter ranges, potentially increasing plan cache usage.

The objtype column distinguishes between different plan types: compiled plans (Proc), prepared statements (Prepared), and ad hoc queries (Adhoc). Ad hoc plan proliferation commonly causes plan cache bloat, as each unique query text creates a separate cache entry.

-- Plan cache memory consumption by plan type
SELECT 
    objtype AS PlanType,
    COUNT(*) AS PlanCount,
    SUM(size_in_bytes) / 1024 / 1024 AS SizeMB,
    AVG(usecounts) AS AvgUseCount
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY SizeMB DESC;

-- Single-use plans consuming cache memory
SELECT 
    COUNT(*) AS SingleUsePlans,
    SUM(size_in_bytes) / 1024 / 1024 AS WastedMB,
    (SELECT SUM(size_in_bytes) FROM sys.dm_exec_cached_plans) / 1024 / 1024 AS TotalCacheMB
FROM sys.dm_exec_cached_plans
WHERE usecounts = 1 AND objtype = 'Adhoc';

-- Largest plans in cache by memory consumption
SELECT TOP 20
    cp.size_in_bytes / 1024 AS SizeKB,
    cp.usecounts,
    cp.objtype,
    SUBSTRING(st.text, 1, 200) AS QueryText
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE st.text IS NOT NULL
ORDER BY cp.size_in_bytes DESC;

-- Plan cache turnover analysis
SELECT 
    cacheobjtype,
    objtype,
    COUNT(*) AS PlanCount,
    SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS SingleUse,
    AVG(CAST(usecounts AS FLOAT)) AS AvgReuse
FROM sys.dm_exec_cached_plans
GROUP BY cacheobjtype, objtype
ORDER BY PlanCount DESC;

-- Plans with high recompilation indicators
SELECT TOP 10
    cp.usecounts,
    cp.size_in_bytes,
    qs.execution_count,
    qs.plan_generation_num,
    SUBSTRING(st.text, 1, 100) AS QueryText
FROM sys.dm_exec_cached_plans cp
INNER JOIN sys.dm_exec_query_stats qs ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE qs.plan_generation_num > 1
ORDER BY qs.plan_generation_num DESC;

Clear single-use ad hoc plans to reclaim cache memory

-- WARNING: Test in development first. This removes cached plans.
-- Run during low activity periods.
DECLARE @SingleUseMB INT;

SELECT @SingleUseMB = SUM(size_in_bytes) / 1024 / 1024 
FROM sys.dm_exec_cached_plans 
WHERE usecounts = 1 AND objtype = 'Adhoc';

PRINT 'Clearing ' + CAST(@SingleUseMB AS VARCHAR(10)) + ' MB of single-use plans';

-- Remove only single-use ad hoc plans
DBCC FREESYSTEMCACHE('SQL Plans') WITH MARK_IN_USE_FOR_REMOVAL;

Expected impact: Immediate memory reclamation but temporary performance impact as plans recompile.

Enable optimize for ad hoc workloads to reduce plan cache bloat

-- Stores plan stub instead of full plan for single-use queries
-- Safe to enable on production, takes effect immediately
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;

Expected impact: Reduces plan cache memory consumption by 80-90% for ad hoc workloads with minimal CPU overhead.

Force plan parameterization for specific databases

-- Forces simple parameterization for repeated query patterns
-- Use cautiously as it can change query plans
ALTER DATABASE [YourDatabase] 
SET PARAMETERIZATION FORCED;

Expected impact: Reduces plan proliferation but may cause parameter sniffing issues. Monitor query performance after implementation.

Configure "optimize for ad hoc workloads" on servers handling significant ad hoc query volumes. This server-level setting stores lightweight plan stubs instead of full execution plans for single-use queries, reducing memory consumption by 80-90%.

Implement forced parameterization at the database level for applications generating similar query patterns with different literal values. Applications should use parameterized queries or stored procedures to maximize plan reuse and minimize cache churn.

Monitor plan cache effectiveness using performance counters: SQL Server Plan Cache - Cache Hit Ratio should exceed 95%, and Cache Object Counts should remain stable. Set up alerts when single-use plan memory exceeds 1GB or when total plan cache size approaches 2GB on systems with limited memory.

Establish regular plan cache maintenance during low-activity periods. Consider clearing single-use ad hoc plans weekly or when memory pressure occurs. Use Query Store for critical queries requiring persistent plan management independent of plan cache volatility.

Configure appropriate max server memory settings to ensure plan cache receives adequate memory allocation. On dedicated SQL Server instances, reserve 10-15% of total system memory for the operating system and other processes, allowing the plan cache to auto-size within SQL Server's memory allocation.