Adhoc Queries generate lots of execution plans ,these plans
take size in the sql buffer .The great number of these
plans can cause pressure on the system leads to a RAM bottleneck.
Identify the adhoc sql cache size

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
group by objtype

clean up only adhoc sql cache

dbcc freesystemcache ('SQL Plans')

This script frees the unused Adhoc plans from cache

DECLARE @ObjType varchar(20)= 'Adhoc';
DECLARE @p_SQL varchar(512)='';
DECLARE @plan_handle varbinary(60);
declare @count int=0;
--Cursor for all Adhoc cashed plans and number of usage <=2
DECLARE planCach CURSOR FOR
SELECT plan_handle
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) p
where
( cp.objtype = N'Adhoc' or p.objectid is null)
and usecounts<=2
OPEN planCach
FETCH NEXT FROM planCach INTO @plan_handle
WHILE @@FETCH_STATUS = 0
BEGIN
print @plan_handle
DBCC FREEPROCCACHE (@plan_handle)
set @count=@count+1
print(@count)
FETCH NEXT FROM planCach INTO @plan_handle
END;
CLOSE planCach;
DEALLOCATE planCach;