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?
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 community
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.
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.
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