Mahmoud ElMansy: knowledge meant to be free

Clean Adhoc plan cache

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;