How to clear ad hoc queries from plan cache?

Question

As the title implies, I'm going to remove just ad hoc queries (not prepared queries) from plan cache in sql server 2014/2016 because it occupies more than 50% percent of my main memory. Do you have any suggestion?

Many Thanks.

Answer 1

DECLARE @plan_handle varbinary(64)

DECLARE db_cursor CURSOR FOR 
SELECT plan_handle
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc' -- and usecounts = 1 -- optional: just delete the ones that are used only once

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @plan_handle  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    DBCC FREEPROCCACHE (@plan_handle);  
    FETCH NEXT FROM db_cursor INTO @plan_handle 
END 

CLOSE db_cursor  
DEALLOCATE db_cursor 

Answer 2

So you just want to clear the Ad-hoc query plans and still dont want to clear the whole procedure cache. What you are asking is there in Clearing Your Ad-hoc SQL Plans While Keeping Your SP Plans Intact

The blog asks you to run

DBCC FREESYSTEMCACHE('SQL Plans')

As per the blog

The procedure cache actually consists of 4 distinct cache stores that hold different types of plans. Those cache stores are:

• CACHESTORE_OBJCP - these are "Object Plans" - stored procedures, functions and triggers. Generally, the good stuff.
• CACHESTORE_SQLCP - these are "SQL Plans" - ad-hoc SQL statements (including parameterized ones) and prepared statements. This is the stuff that we are after.
• CACHESTORE_PHDR - so-called "Bound Trees" for views, constraints and defaults. Irrelevant for the problem discussed.
• CACHESTORE_XPROC - not really execution plans but rather pointers to the entry points of your extended SPs.

So you can see selectively clearing the SQLCP would remove prepared and ad-hoc plans. I tested this on my system.

Ran query

select objtype, 
count(*) as number_of_plans, 
sum(cast(size_in_bytes as bigint))/1024/1024 as size_in_MBs,
avg(usecounts) as avg_use_count
from sys.dm_exec_cached_plans
--where objtype='adhoc'
group by objtype

And the output was

You can see the the picture has 1264 ad-hoc plans and 69 prepared statements.

Now I selectively clear the SQLCP using DBCC FREESYSTEMCACHE('SQL Plans') and rerun the query again which gave me below output

Now you can see the ad-hoc and prepared plans are 2 and 6 respectively. While other are NOT affected.