After identifying the queries for which we want to create a plan guide, we could just leave it there and the old plans will age out naturally or we could clean them out ourselves. The command to clean out the procedure cache is…
DBCC FREEPROCCACHE (plan_handle)
I would be very careful using this command because if you omit the plan handle, you flush every plan from the cache and this would be disastrous on a very busy system. From then on every query submitted needs to be compiled from scratch which is likely to drive your CPU through the roof.
The plan handle is obtained from this query… (Red Gate SQL Backup Query Plan example)
SELECT *
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
WHERE objtype = 'Adhoc'
AND usecounts = 1
AND text LIKE 'SELECT TOP 1 a.type%backupset%'
If you have lots of plans to flush, it would take a while doing each one individually so instead let’s build a query string to execute.
One problem here (if you try this out before reading on) is that the plan handle is varbinary(64) and you can’t build a query string with mis-matched data types. Converting varbinary to varchar can be a challenge, especially if you search the internet looking for answers. Some of the answers are really scary. However, SQL Server has a built in function to do this for you… sys.fn_sqlvarbasetostr. Use this query to double check that your plan handle in dm_exec_cached_plans matches the one produced by the function and then when you’re happy use the next step to flush your plans.
SELECT sys.fn_sqlvarbasetostr(cp.plan_handle)
, *
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle)
WHERE objtype = 'Adhoc'
AND usecounts = 1
AND text LIKE 'SELECT TOP 1 a.type%backupset%'
ORDER BY [text]
Now, this would be one of the few times I recommend the use of a cursor but this will build your query string and execute it for you. If you want to exec one or two manually, you can comment out the EXEC and uncomment the PRINT and use the result
DECLARE @plan_handle NVARCHAR(max), @sql VARCHAR(500)
DECLARE cached_plans CURSOR
FOR
SELECT sys.fn_sqlvarbasetostr(cp.plan_handle)
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
WHERE objtype = 'Adhoc'
AND usecounts = 1
AND text LIKE 'SELECT TOP 1 a.type%backupset%'
OPEN cached_plans
FETCH NEXT FROM cached_plans INTO @plan_handle
WHILE ( @@fetch_status <> -1 )
BEGIN
IF ( @@fetch_status <> -2 )
BEGIN
SELECT @sql = 'DBCC FREEPROCCACHE (' + @plan_handle + ') WITH NO_INFOMSGS'
EXEC (@sql)
--PRINT (@sql)
END
FETCH NEXT FROM cached_plans INTO @plan_handle
END
DEALLOCATE cached_plans
GO