Eight Different Ways to Clear the SQL Server Plan Cache

Nearly anytime you see the command DBCC FREEPROCCACHE mentioned in a blog post, magazine article or book, you usually get some sort of a scary warning about how you should not use it on a production system, or else life as we know it will end. For example, Books Online says this:

Use DBCC FREEPROCCACHE to clear the plan cache carefully. Freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache. This can cause a sudden, temporary decrease in query performance. For each cleared cachestore in the plan cache, the SQL Server error log will contain the following informational message: “SQL Server has encountered %d occurrence(s) of cachestore flush for the ‘%s’ cachestore (part of plan cache) due to ‘DBCC FREEPROCCACHE’ or ‘DBCC FREESYSTEMCACHE’ operations.” This message is logged every five minutes as long as the cache is flushed within that time interval.

I would argue that running DBCC FREEPROCCACHE does not cause that much distress with a modern processor, even on a very busy OLTP system. It will cause a pretty minor CPU spike for a few seconds on most systems as the query plans get recompiled as they are executed. It can actually be pretty useful for resetting the cached_time time for sys.dm_exec_procedure_stats so that it is the same for most of the stored procedures in your normal workload. That makes it easier to pick out your most expensive queries or stored procedures on a cumulative basis when you are looking at things like total worker time or total logical reads.

Having said all that, I want to show a few methods for clearing all or part of the plan cache that are somewhat less impactful on the system. Running DBCC FREEPROCCACHE is kind of a brute force approach, so if you are concerned about that, you can run one of the variations shown below:

-- Eight different ways to clear the plan cache
-- Glenn Berry
-- SQLskills.com
    


-- Example 1 ***********************
-- Remove all elements from the plan cache for the entire instance 
DBCC FREEPROCCACHE;


-- Example 2 ***********************
-- Flush the plan cache for the entire instance and suppress the regular completion message
-- "DBCC execution completed. If DBCC printed error messages, contact your system administrator." 
DBCC FREEPROCCACHE WITH NO_INFOMSGS;


-- Example 3 ***********************
-- Flush the ad hoc and prepared plan cache for the entire instance
DBCC FREESYSTEMCACHE ('SQL Plans');


-- Example 4 ***********************
-- Flush the ad hoc and prepared plan cache for one resource pool

-- Get Resource Pool information
SELECT name AS [Resource Pool Name], cache_memory_kb/1024.0 AS [cache_memory (MB)], 
        used_memory_kb/1024.0 AS [used_memory (MB)]
FROM sys.dm_resource_governor_resource_pools;

-- Flush the ad hoc and prepared plan cache for one resource pool
DBCC FREESYSTEMCACHE ('SQL Plans', 'LimitedIOPool');


-- Example 5 **********************
-- Flush the entire plan cache for one resource pool

-- Get Resource Pool information
SELECT name AS [Resource Pool Name], cache_memory_kb/1024.0 AS [cache_memory (MB)], 
        used_memory_kb/1024.0 AS [used_memory (MB)]
FROM sys.dm_resource_governor_resource_pools;


-- Flush the plan cache for one resource pool
DBCC FREEPROCCACHE ('LimitedIOPool');
GO


-- Example 6 **********************
-- Remove all elements from the plan cache for one database (does not work in SQL Azure) 

-- Get DBID from one database name first
DECLARE @intDBID INT;
SET @intDBID = (SELECT [dbid] 
                FROM master.dbo.sysdatabases 
                WHERE name = N'AdventureWorks2014');

-- Flush the plan cache for one database only
DBCC FLUSHPROCINDB (@intDBID);



-- Example 7 **********************
-- Clear plan cache for the current database

USE AdventureWorks2014;
GO
-- Clear plan cache for the current database
-- New in SQL Server 2016 and SQL Azure
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;



-- Example 8 **********************
-- Remove one query plan from the cache

USE AdventureWorks2014;
GO

-- Run a stored procedure or query
EXEC dbo.uspGetEmployeeManagers 9;

-- Find the plan handle for that query 
-- OPTION (RECOMPILE) keeps this query from going into the plan cache
SELECT cp.plan_handle, cp.objtype, cp.usecounts, 
DB_NAME(st.dbid) AS [DatabaseName]

[text][/text]

FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st 
WHERE OBJECT_NAME (st.objectid)

[text][/text]

LIKE N'%uspGetEmployeeManagers%' OPTION (RECOMPILE); 

-- Remove the specific query plan from the cache using the plan handle from the above query 
DBCC FREEPROCCACHE (0x050011007A2CC30E204991F30200000001000000000000000000000000000000000000000000000000000000);