2

Kimberly Tripp's blog post here made me think that running DBCC FREESYSTEMCACHE('SQL Plans') clears just single-used ad-hoc and prepared query plans. But that seems incorrect. It clears all. Which is probaly not a good option to go if you have high average use count of prepared plans.

Is there any option to clear JUST ad-hoc plans? Not the prepared ones?

2

Try this:

DECLARE @plan_handle varbinary(64)

DECLARE db_cursor CURSOR FOR 
SELECT plan_handle
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc' 

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
  • I'll give it a try, later. Many thanks. I'll probably add usecounts = 1 to the filter. – jerik1 Jun 21 '19 at 16:44
1

I had come up with this a while back. It's not far off from the above code, just plopped into a stored procedure with some filters on it.

ALTER PROCEDURE [dbo].[clear_single_plans]
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;

IF OBJECT_ID('tempdb..#plan_handles')
IS NOT NULL
BEGIN
    DROP TABLE #plan_handles
END

CREATE TABLE #plan_handles(plan_handle VARCHAR(1000));

DECLARE @plan_handle_command VARCHAR(1000) = '';

INSERT #plan_handles ( plan_handle )
SELECT DISTINCT 'DBCC FREEPROCCACHE (' 
                 + CONVERT(VARCHAR(128), decp.plan_handle, 1) 
                 + ');'
FROM sys.dm_exec_cached_plans AS decp
JOIN sys.dm_exec_query_stats AS deqs
    ON decp.plan_handle = deqs.plan_handle
WHERE decp.usecounts = 1
AND   decp.objtype = 'Adhoc'
AND   deqs.last_execution_time < DATEADD(HOUR, -1, GETDATE());

DECLARE plan_cursor CURSOR 
FORWARD_ONLY LOCAL  
FOR  
SELECT plan_handle 
FROM #plan_handles;  

OPEN plan_cursor;  
FETCH NEXT FROM plan_cursor 
    INTO @plan_handle_command
WHILE @@FETCH_STATUS = 0  
BEGIN   

 PRINT @plan_handle_command;  
 EXEC(@plan_handle_command);  

FETCH NEXT FROM plan_cursor 
    INTO @plan_handle_command;  
END;  

CLOSE plan_cursor;  
DEALLOCATE plan_cursor;  
END;

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.