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
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;
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;
My name is Mahmoud El Mansy I'm C# Senior solution developer .