Problem
SQL Server’s plan cache is designed to store execution plans for reuse. However, it can become bloated with single-use ad hoc plans and we will look at how to clear the plan cache in SQL Server. These one-time, throwaway execution plans get created when a query runs without parameters or reuse logic. They’re like fast food wrappers: used once and tossed, except SQL Server doesn’t toss them, it hoards them.
Over time, this clutter grows. Memory usage goes up, the plan cache gets bloated, and performance slowly grinds down. You won’t see errors or alerts, just a slow system. Tempdb spills start creeping in and users wondering why reports take forever now. It hits hardest in environments where dynamic SQL is used heavily, report builders are used, or tools like Power BI keep firing unique queries. Most DBAs don’t notice it right away—it’s silent, gradual, and sneaky. But by the time you dig into the plan cache, you’re staring at a mountain of single-use plans, wondering how it got this bad.
Solution
This tip provides a detailed walk-through of identifying plan cache bloat using the DMV sys.dm_exec_cached_plans, quantifying memory impact, and surgically clearing just the problematic cache (not a full nuke). It also shows how to mitigate this issue by some configuration changes at the SQL Server level, and some smart query design.
Understanding Plan Cache in SQL Server
The plan cache is supposed to help SQL Server stores execution plans so it doesn’t have to compile the same query repeatedly. That saves CPU and keeps things snappy. But here’s the catch: when your cache gets flooded with single-use ad hoc plans, the whole point of caching goes out the window.
Instead of boosting performance, SQL Server starts wasting memory on junk plans that’ll never be reused. It’s like stacking up receipts you’re never going to look at again—pointless clutter that eats up space and slows everything else down.
Types of Plans in Cache
- Compiled: Fully compiled plans, typically from stored procedures.
- Prepared: Plans created for parameterized queries.
- Ad Hoc: Auto-generated plans from literal queries.
Ad hoc plans are the main culprits in plan cache bloat, particularly when applications send dynamic SQL with varying literals instead of using parameterized queries.
Identifying Plan Cache Bloat
If you suspect plan cache bloat, this DMV query will help surface the junk:
-- MSSQLTips.com (T-SQL)
SELECT usecounts,
size_in_bytes,
objtype,
cacheobjtype,
text
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
WHERE cp.cacheobjtype = 'Compiled Plan'
AND cp.objtype = 'Adhoc'
AND cp.usecounts = 1
ORDER BY size_in_bytes DESC;This pulls out all the ad hoc plans used only once—no reuse, no benefit, just memory hogs.
What to Watch For
- usecounts = 1: This means the plan was created and never reused.
- objtype = ‘Adhoc’: If these dominate the cache, there are a lot of random, unparameterized SQL.
- High memory usage: Look at the size_in_bytes. Even small plans add up when there are thousands of them.
If the top rows are full of usecounts = 1 and Adhoc, and the total memory they’re using is non-trivial. Yes, you have classic plan cache bloat.
Measuring Memory Impact
If you want to see how much memory these cached plans are actually eating, run this:
-- MSSQLTips.com (T-SQL)
SELECT type AS CacheStoreType,
name,
pages_kb,
entries_count
FROM sys.dm_os_memory_cache_counters
WHERE pages_kb > 0;This shows how much memory (in KB) each part of the cache is using. Focus on the following:
- CACHESTORE_SQLCP: This is where ad hoc query plans live. If this is bloated, you likely have too many single-use queries sitting around.
- CACHESTORE_OBJCP: This holds stored procedure plans. Ideally, we want most of our reuse coming from here.
- CACHESTORE_PHDR: This tracks SQL Server’s guesswork around parameterization. If it’s big, forced parameterization might be overdoing it.
If CACHESTORE_SQLCP(Adhoc) is the biggest one here, the server’s wasting memory on one-time ad hoc plans instead of caching useful stuff.
Simulating Plan Cache Bloat with Ad Hoc Queries
Step 1: Create Test Table and Insert Data
To simulate plan cache bloat, first create a large table with a million rows using a tally table approach and populate it with random data. This helps mimic a production-like workload.
-- MSSQLTips.com (T-SQL)
DROP TABLE IF EXISTS Orders;
CREATE TABLE Orders (
OrderID INT PRIMARY KEY IDENTITY,
CustomerType VARCHAR(10),
Amount DECIMAL(10, 2),
CreatedDate DATETIME
);
WITH Tally AS (
SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
)
INSERT INTO Orders (CustomerType, Amount, CreatedDate)
SELECT
CASE WHEN ABS(CHECKSUM(NEWID())) % 2 = 0 THEN 'Retail' ELSE 'VIP' END,
ABS(CHECKSUM(NEWID())) % 10000,
DATEADD(DAY, -ABS(CHECKSUM(NEWID())) % 1000, GETDATE())
FROM Tally;
Step 2: Generate Multiple Ad Hoc Queries
Execute a loop that fires 100 unique ad hoc queries, each with different literal values. This ensures SQL Server generates 100 different execution plans, all stored as separate ad hoc entries in the plan cache. These plans are not reused and serve no long-term benefit, which is precisely how plan cache bloat begins. This setup allows DBAs to visualize how seemingly harmless, dynamic queries can silently clutter memory and impact performance without generating any errors.
-- MSSQLTips.com (T-SQL)
DECLARE @i INT = 1;
WHILE @i <= 100
BEGIN
EXEC('SELECT * FROM Orders WHERE OrderID = ' + CAST(@i AS VARCHAR));
SET @i += 1;
ENDAfter running the above query in a loop for 100 times, let’s inspect individual ad hoc plans cached by SQL Server for queries like ‘SELECT * FROM Orders WHERE OrderID =’.
Finding Plan Usage Data
The below query shows how many times each plan was used (usecounts), its memory size (size_kb), and the original query text. Even minor differences in spacing or literals can cause SQL Server to cache a new plan, leading to unnecessary memory bloat. This DMV helps identify those redundant plans, especially in dynamic SQL scenarios where queries aren’t parameterized. It’s a valuable diagnostic tool for spotting inefficiencies in plan reuse and confirming if plan cache bloat is occurring in real workloads.
-- MSSQLTips.com (T-SQL)
-- View individual cached plans matching ad hoc SELECTs on Orders.OrderID
SELECT
cp.plan_handle,
cp.usecounts,
cp.size_in_bytes / 1024 AS size_kb,
cp.objtype,
cp.cacheobjtype,
st.text AS query_text
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE cp.cacheobjtype = 'Compiled Plan'
AND st.text LIKE '%SELECT * FROM Orders WHERE OrderID%'
ORDER BY cp.size_in_bytes DESC;
Step 3: Quantify Total Memory Wasted on Single-Use Ad Hoc Plans
Even though each ad hoc plan is small, their cumulative memory usage can be substantial in large workloads. Use the query below to measure how much total memory (in KB) these single-use plans are consuming.
-- MSSQLTips.com (T-SQL)
-- Estimate total memory used by all single-use ad hoc plans
SELECT SUM(size_in_bytes)/1024 AS total_kb
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc' AND usecounts = 1;It gives a quick snapshot of how much RAM is being wasted on non-reusable plans. If this number is in the hundreds of MB or more, it’s a strong signal that plan cache bloat is affecting SQL Server performance.
Step 4: Inspect Plan Cache Growth
Once confirmed that single-use ad hoc plans exist, it’s important to see how much memory they’re actually consuming. This step helps to dig into the individual size of each cached ad hoc plan that was used only once and never again.
Even though each plan might be small on its own, SQL Server can accumulate thousands of these throwaway entries, eating up a large chunk of memory without providing any reuse benefit. The query below lets you inspect those one-time plans, ranked by memory size, along with the original SQL text that caused them.
-- MSSQLTips.com (T-SQL)
SELECT size_in_bytes / 1024 AS size_kb, text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
WHERE objtype = 'Adhoc'
AND usecounts = 1
ORDER BY size_in_bytes DESC;
This confirms that SQL Server cached many unique, non-reused plans for the same query, and this consumes space in memory. Though individual ad hoc plans take a nominal size, a high number of such plans cause large and unnecessary consumption of RAM. And so, this can cause plan cache bloat in environments with a large number of unique queries.
How to Clean Up Plan Cache Safely
Manual Cleanup Everything
Use the command below to clear the plan cache. This will release any memory being used by plan caches before the last restart.
Note: Use manual cleanup of all SQL plans with caution in production, as this flushes compiled plans.
-- MSSQLTips.com (T-SQL)
DBCC FREESYSTEMCACHE ('SQL Plans');After executing the cleanup command DBCC FREESYSTEMCACHE (‘SQL Plans’), if we rerun the DMV query to check the plan cache for the previous ad hoc query SELECT * FROM Orders WHERE OrderID = XX, we’ll notice that the plan no longer exists in cache. This confirms that the associated execution plans were successfully flushed from memory.
Targeted Cleanup Strategy
Identify the largest ad hoc plans via the DMV query and schedule off-hours cleanup, if required.
Here is the output from our query.
If we want to remove a specific plan use the following and replace the plan_handle from above in the query below.
-- MSSQLTips.com (T-SQL)
DBCC FREESYSTEMCACHE (enter plan_handle here);You can also use the query below to review top bloat sources.
-- MSSQLTips.com (T-SQL)
SELECT TOP 50 cp.usecounts,
cp.size_in_bytes / 1024 AS size_kb,
st.text AS query_text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE cp.objtype = 'Adhoc'
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;Preventing Plan Cache Bloat
Enable Stub Plan Caching
This setting enables stub plan caching in SQL Server. When it’s turned on, SQL Server does not store the full execution plan the first time an ad hoc query runs. Instead, it saves a small “stub” plan (~300 bytes). If the same query is executed again, only then will it compile and store the full execution plan.
-- MSSQLTips.com (T-SQL)
sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;Consider Forced Parameterization
When you enable PARAMETERIZATION FORCED, SQL Server automatically converts all eligible queries into parameterized form, even complex ones. That means:
- SELECT * FROM Orders WHERE OrderID = 101;
- SELECT * FROM Orders WHERE OrderID = 102;
Both will use the same parameterized plan.
-- MSSQLTips.com (T-SQL)
ALTER DATABASE [DB_NAME] SET PARAMETERIZATION FORCED;Refactor Query Logic
Proper query refactoring is essential to prevent plan cache bloat. By shifting from dynamic literal-heavy queries to parameterized ones or stored procedures, you significantly increase plan reuse.
1. Use Parameterized Queries
Instead of this (which generates a new plan every time):
-- MSSQLTips.com (T-SQL)
EXEC('SELECT * FROM Orders WHERE OrderID = 1001');Use this:
-- MSSQLTips.com (T-SQL)
DECLARE @OrderID INT = 1001;
SELECT * FROM Orders WHERE OrderID = @OrderID;This reuses the same execution plan across different parameter values.
2. Use Stored Procedures
Encapsulate queries into procedures to ensure plan reuse:
-- MSSQLTips.com (T-SQL)
CREATE PROCEDURE usp_GetOrderById
@OrderID INT
AS
BEGIN
SELECT * FROM Orders WHERE OrderID = @OrderID;
ENDAnd call it like this: EXEC usp_GetOrderById @OrderID = 1001;
This locks in a reusable compiled plan, reducing bloat.
3. Avoid Dynamic SQL with Literals
Dynamic SQL with hard-coded values forces a new plan each time:
-- MSSQLTips.com (T-SQL)
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT * FROM Orders WHERE OrderID = ' + CAST(1001 AS VARCHAR);
EXEC(@sql);Refactor such queries using sp_executesql and parameters:
-- MSSQLTips.com (T-SQL)
DECLARE @sql NVARCHAR(MAX);
DECLARE @OrderID INT = 1001;
SET @sql = 'SELECT * FROM Orders WHERE OrderID = @OrderID';
EXEC sp_executesql @sql, N'@OrderID INT', @OrderID;4. Follow Consistent Query Templates
Ensure the application code uses uniform query structure:
“SELECT * FROM Orders WHERE OrderID = @OrderID” instead of varying field orders or spacing.
Avoid inline literals even if cached behavior looks harmless.
Following these practices ensures stable performance and optimal plan reuse.
Summary
Plan cache bloat is a subtle but damaging issue in SQL Server environments that rely on ad hoc queries. By monitoring DMVs, enabling lightweight plan settings, and refactoring inefficient patterns, DBAs can prevent memory waste and improve workload stability.
Next Steps
- Review and test cleanup process in Dev/Test before applying to Production.
- Read more about Optimize for Ad Hoc Workloads on MSSQLTips.
- Check out Analyzing the SQL Server Plan Cache for advanced understanding.
- Different Ways to Flush or Clear SQL Server Cache
- Monitor sys.dm_exec_cached_plans weekly for new bloat patterns.
- External References
