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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityAs 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.
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
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.
'optimize for ad hoc workloads'
server configuration option so that plans for ad-hoc queries are cached only executed multiple times.optimize for ad hoc workloads
is the correct approach to use; only minimal space in the cache will be used for a single-use ad hoc query plan.