Clearing SQL Server’s Bloated Ad-Hoc Plan Cache

---

SQL Server’s plan cache stores compiled execution plans so they can be reused without recompilation. That is the intended behavior. The problem is that workloads using dynamically generated queries, ORMs, or chatty applications produce thousands of plans that will never be executed a second time. These single-use ad-hoc plans accumulate in the plan cache under objtype = 'Adhoc', consuming memory that would be better used for buffer pool pages or genuinely reusable plans.

This article covers how to measure the ad-hoc bloat, how to clear it and see exactly how much memory was reclaimed, and how to prevent it from accumulating again with a single server configuration change.

1 Why Ad-Hoc Plans Accumulate Beginner

Every time SQL Server executes a query it has not seen before, it compiles an execution plan and stores it in the plan cache. The expectation is that the same query will run again and reuse the cached plan, avoiding the CPU cost of recompilation. For parameterized stored procedures and prepared statements this works as intended.

The problem arises with applications that generate queries with literal values embedded in the SQL text rather than using parameters. An ORM generating SELECT * FROM Orders WHERE OrderID = 1042 and then SELECT * FROM Orders WHERE OrderID = 1043 produces two distinct query texts and two distinct plan cache entries, even though the plans are structurally identical. With thousands of such queries, the plan cache fills with entries that will never be reused.

The consequences are real. Plan cache memory pressure causes SQL Server to evict potentially useful plans, including stored procedure plans that are frequently reused. This leads to increased CPU from recompilation, unpredictable query performance, and memory that is unavailable for the buffer pool.

2 Measure, Clear, and Confirm Reclaimed Memory Beginner

This script measures ad-hoc plan cache size before and after clearing, and reports exactly how much memory was reclaimed. Run the before snapshot, execute the clear, wait briefly for the cache to stabilize, then run the after snapshot.

-- Step 1: Measure current ad-hoc plan cache usage before clearing
DECLARE @beforeAdHocMB  DECIMAL(14,2);
DECLARE @beforeTotalMB  DECIMAL(14,2);

SELECT @beforeAdHocMB =
    SUM(CAST(size_in_bytes AS BIGINT)) / 1048576.0
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc';

SELECT @beforeTotalMB =
    SUM(CAST(size_in_bytes AS BIGINT)) / 1048576.0
FROM sys.dm_exec_cached_plans;

PRINT CONCAT('Before: Ad-hoc plans = ', @beforeAdHocMB,
             ' MB  |  Total cache = ', @beforeTotalMB, ' MB');

-- Step 2: Clear all SQL plans (ad-hoc and prepared)
-- See Section 3 for a surgical approach that targets only ad-hoc plans
DBCC FREESYSTEMCACHE('SQL Plans');

-- Brief pause to let the cache stabilize before measuring
WAITFOR DELAY '00:00:05';

-- Step 3: Measure again after clearing
DECLARE @afterAdHocMB   DECIMAL(14,2);
DECLARE @afterTotalMB   DECIMAL(14,2);
DECLARE @reclaimedMB    DECIMAL(14,2);

SELECT @afterAdHocMB =
    SUM(CAST(size_in_bytes AS BIGINT)) / 1048576.0
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc';

SELECT @afterTotalMB =
    SUM(CAST(size_in_bytes AS BIGINT)) / 1048576.0
FROM sys.dm_exec_cached_plans;

SET @reclaimedMB = @beforeAdHocMB - @afterAdHocMB;

PRINT CONCAT('After:  Ad-hoc plans = ', @afterAdHocMB,
             ' MB  |  Total cache = ', @afterTotalMB, ' MB');
PRINT CONCAT('Reclaimed from ad-hoc plans: ', @reclaimedMB, ' MB');

3 Surgical Removal: Clear Only Ad-Hoc Plans Intermediate

To remove only the single-use ad-hoc plans without touching prepared statements or stored procedure plans, iterate through the plan handles where objtype = 'Adhoc' and call DBCC FREEPROCCACHE per plan handle. This is slower than the single-command approach but avoids clearing genuinely reusable plans.

-- Remove only single-use ad-hoc plans (usecounts = 1)
-- Leaves prepared statements and stored procedure plans untouched
DECLARE @plan_handle VARBINARY(64);
DECLARE @removed     INT = 0;

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT plan_handle
    FROM sys.dm_exec_cached_plans
    WHERE objtype    = 'Adhoc'
    AND   usecounts  = 1;       -- only plans that have never been reused

OPEN cur;
FETCH NEXT FROM cur INTO @plan_handle;

WHILE @@FETCH_STATUS = 0
BEGIN
    DBCC FREEPROCCACHE(@plan_handle);
    SET @removed = @removed + 1;
    FETCH NEXT FROM cur INTO @plan_handle;
END

CLOSE cur;
DEALLOCATE cur;

PRINT CONCAT('Removed ', @removed, ' single-use ad-hoc plans.');

4 Long-Term Prevention: Optimize for Ad Hoc Workloads Beginner

Clearing the cache treats the symptom. The root cause is that SQL Server caches full plans for queries it has never seen before. The optimize for ad hoc workloads server configuration changes this behavior: on the first execution of a new ad-hoc query, SQL Server stores only a small plan stub rather than the full compiled plan. The full plan is stored on the second execution, when there is evidence the query will be reused. Queries that run only once never pollute the cache with a full plan.

-- Check current setting (1 = enabled, 0 = disabled)
SELECT name, value_in_use
FROM sys.configurations
WHERE name = 'optimize for ad hoc workloads';

-- Enable it
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;

-- Verify
SELECT name, value_in_use
FROM sys.configurations
WHERE name = 'optimize for ad hoc workloads';
-- value_in_use should now be 1

5 When to Use This Beginner

• Investigating memory pressure. The before and after measurement in Section 2 quantifies exactly how much memory ad-hoc plans are consuming. If the reclaimed amount is significant relative to the server’s total buffer pool allocation, ad-hoc plan bloat is a real contributor to memory pressure.
• Plan cache disproportionately large. If the plan cache is consuming several gigabytes but query reuse rates from sys.dm_exec_cached_plans show most plans have a usecount of 1, bloat is the cause.
• Establishing a clean baseline before enabling optimization. Enabling optimize for ad hoc workloads prevents future accumulation but does not remove existing bloat. Clearing first and then enabling the setting provides a clean starting point.

6 Ready-to-Schedule Agent Job Script Beginner

The scripts in Sections 2 and 3 are designed for manual execution during an investigation. For a scheduled SQL Agent job that runs nightly or weekly, a threshold guard is more appropriate. This version checks whether single-use plan cache exceeds a meaningful size before clearing. If the threshold is not met, it logs a message and exits without touching the cache. This makes it safe to schedule without manual review on every run.

-- Threshold-guarded plan cache cleanup
-- Safe to schedule as a SQL Agent job: only clears when single-use plans
-- exceed the @ThresholdMB threshold. Logs a message either way.
-- Covers both Adhoc and Prepared single-use plans (usecounts = 1).

DECLARE
    @MB           DECIMAL(19,3),
    @Count        BIGINT,
    @StrMB        NVARCHAR(20),
    @ThresholdMB  DECIMAL(19,3) = 10;   -- change this threshold as needed

SELECT
    @MB    = SUM(CAST(
                CASE WHEN usecounts = 1 AND objtype IN ('Adhoc', 'Prepared')
                     THEN size_in_bytes ELSE 0
                END AS DECIMAL(12,2))) / 1024 / 1024,
    @Count = SUM(
                CASE WHEN usecounts = 1 AND objtype IN ('Adhoc', 'Prepared')
                     THEN 1 ELSE 0
                END)
FROM sys.dm_exec_cached_plans;

SET @StrMB = CONVERT(NVARCHAR(20), @MB);

IF @MB > @ThresholdMB
BEGIN
    DBCC FREESYSTEMCACHE('SQL Plans');
    RAISERROR('%s MB was allocated to single-use plan cache. Single-use plans have been cleared.',
              10, 1, @StrMB) WITH NOWAIT;
END
ELSE
BEGIN
    RAISERROR('Only %s MB allocated to single-use plan cache. Threshold not met, no action taken.',
              10, 1, @StrMB) WITH NOWAIT;
    -- Severity 10 = informational message, not an error. Safe in Agent jobs.
END
GO

References

• Microsoft Docs: sys.dm_exec_cached_plans
• Microsoft Docs: DBCC FREEPROCCACHE
• Microsoft Docs: optimize for ad hoc workloads
• SQLYARD: SQL Server DMV Reference Cheat Sheet
• SQLYARD: When SQL Server Is Slow: Triage Playbook