How to clear ad hoc queries from plan cache?

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.

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.

StackExchange Database Administrators Q#230584, answer score: 7