3

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.

  • You can find some examples here : sqlskills.com/blogs/glenn/… – Sabin Bio Feb 24 at 6:02
  • Thank you for your response, but I'm looking forward a way to clear just ad hoc queries from cache plan not all the things that are located there...@SabinBio – Commander Feb 24 at 6:28
  • 1
    Looking at the link, at the Example 8 - here you can remove a particular plan , base on a plan handle. – Sabin Bio Feb 24 at 6:38
  • 4
    Consider turning on the 'optimize for ad hoc workloads' server configuration option so that plans for ad-hoc queries are cached only executed multiple times. – Dan Guzman Feb 24 at 10:13
  • 1
    What do you expect to gain from doing this? Those ad hoc queries will be removed from the plan cache if they're old enough and room in the cache is required for a newer plan. @DanGuzman's suggestion of 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. – alroc Feb 24 at 12:42
4

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

enter image description here

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

enter image description here

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

  • The original question was to "remove just ad hoc queries (not prepared queries) from plan cache ". Your answer removes the prepared plans as well. This answer should not have been marked as correct. – mhenry1384 Jun 19 at 14:50
  • 1
    Marking answer is with OP. I have mentioned in my answer so you can see selectively clearing the SQLCP would remove prepared and ad-hoc plans which was OK with OP so he marked it. If you have way to just remove ad-hoc queries feel free to add – Shanky Jun 19 at 15:19
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 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.