{"id":1075,"date":"2016-03-26T10:31:01","date_gmt":"2016-03-26T17:31:01","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/glenn\/?p=1075"},"modified":"2026-07-08T08:42:03","modified_gmt":"2026-07-08T15:42:03","slug":"eight-different-ways-to-clear-the-sql-server-plan-cache","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/glenn\/eight-different-ways-to-clear-the-sql-server-plan-cache\/","title":{"rendered":"Eight Different Ways to Clear the SQL Server Plan Cache"},"content":{"rendered":"

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<\/a>:<\/p>\n

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 \u2018%s\u2019 cachestore (part of plan cache) due to \u2018DBCC FREEPROCCACHE\u2019 or \u2018DBCC FREESYSTEMCACHE\u2019 operations.” This message is logged every five minutes as long as the cache is flushed within that time interval.<\/p><\/blockquote>\n

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<\/a> 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.<\/p>\n

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:<\/p>\n

-- Eight different ways to clear the plan cache<\/span>\r\n-- Glenn Berry<\/span>\r\n-- SQLskills.com<\/span>\r\n    \r\n\r\n\r\n-- Example 1 ***********************<\/span>\r\n-- Remove all elements from the plan cache for the entire instance <\/span>\r\nDBCC<\/span> FREEPROCCACHE;\r\n\r\n\r\n-- Example 2 ***********************<\/span>\r\n-- Flush the plan cache for the entire instance and suppress the regular completion message<\/span>\r\n-- \"DBCC execution completed. If DBCC printed error messages, contact your system administrator.\" <\/span>\r\nDBCC<\/span> FREEPROCCACHE WITH<\/span> NO_INFOMSGS;\r\n\r\n\r\n-- Example 3 ***********************<\/span>\r\n-- Flush the ad hoc and prepared plan cache for the entire instance<\/span>\r\nDBCC<\/span> FREESYSTEMCACHE ('SQL Plans'<\/span>);\r\n\r\n\r\n-- Example 4 ***********************<\/span>\r\n-- Flush the ad hoc and prepared plan cache for one resource pool<\/span>\r\n\r\n-- Get Resource Pool information<\/span>\r\nSELECT<\/span> name AS<\/span> [Resource Pool Name], cache_memory_kb\/1024.0 AS<\/span> [cache_memory (MB)], \r\n        used_memory_kb\/1024.0 AS<\/span> [used_memory (MB)]\r\nFROM<\/span> sys.dm_resource_governor_resource_pools;\r\n\r\n-- Flush the ad hoc and prepared plan cache for one resource pool<\/span>\r\nDBCC<\/span> FREESYSTEMCACHE ('SQL Plans'<\/span>, 'LimitedIOPool'<\/span>);\r\n\r\n\r\n-- Example 5 **********************<\/span>\r\n-- Flush the entire plan cache for one resource pool<\/span>\r\n\r\n-- Get Resource Pool information<\/span>\r\nSELECT<\/span> name AS<\/span> [Resource Pool Name], cache_memory_kb\/1024.0 AS<\/span> [cache_memory (MB)], \r\n        used_memory_kb\/1024.0 AS<\/span> [used_memory (MB)]\r\nFROM<\/span> sys.dm_resource_governor_resource_pools;\r\n\r\n\r\n-- Flush the plan cache for one resource pool<\/span>\r\nDBCC<\/span> FREEPROCCACHE ('LimitedIOPool'<\/span>);\r\nGO<\/span>\r\n\r\n\r\n-- Example 6 **********************<\/span>\r\n-- Remove all elements from the plan cache for one database (does not work in SQL Azure) <\/span>\r\n\r\n-- Get DBID from one database name first<\/span>\r\nDECLARE<\/span> @intDBID INT<\/span>;\r\nSET<\/span> @intDBID = (SELECT<\/span> [dbid] \r\n                FROM<\/span> master.dbo.sysdatabases \r\n                WHERE<\/span> name = N'AdventureWorks2014'<\/span>);\r\n\r\n-- Flush the plan cache for one database only<\/span>\r\nDBCC<\/span> FLUSHPROCINDB (@intDBID);\r\n\r\n\r\n\r\n-- Example 7 **********************<\/span>\r\n-- Clear plan cache for the current database<\/span>\r\n\r\nUSE<\/span> AdventureWorks2014;\r\nGO<\/span>\r\n-- Clear plan cache for the current database<\/span>\r\n-- New in SQL Server 2016 and SQL Azure<\/span>\r\nALTER<\/span> DATABASE<\/span> SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;\r\n\r\n\r\n\r\n-- Example 8 **********************<\/span>\r\n-- Remove one query plan from the cache<\/span>\r\n\r\nUSE<\/span> AdventureWorks2014;\r\nGO<\/span>\r\n\r\n-- Run a stored procedure or query<\/span>\r\nEXEC<\/span> dbo.uspGetEmployeeManagers 9;\r\n\r\n-- Find the plan handle for that query <\/span>\r\n-- OPTION (RECOMPILE) keeps this query from going into the plan cache<\/span>\r\nSELECT<\/span> cp.plan_handle, cp.objtype, cp.usecounts, \r\nDB_NAME(st.dbid) AS<\/span> [DatabaseName]<\/pre>\n
[text][\/text]<\/p>\n
FROM<\/span> sys.dm_exec_cached_plans AS<\/span> cp CROSS<\/span> APPLY sys.dm_exec_sql_text(plan_handle) AS<\/span> st \r\nWHERE OBJECT_NAME (st.objectid)<\/span><\/pre>\n
[text][\/text]<\/p>\n
LIKE<\/span> N'%uspGetEmployeeManagers%'<\/span> OPTION<\/span> (RECOMPILE); \r\n\r\n-- Remove the specific query plan from the cache using the plan handle from the above query<\/span> \r\nDBCC<\/span> FREEPROCCACHE (0x050011007A2CC30E204991F30200000001000000000000000000000000000000000000000000000000000000);<\/pre>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,156,246],"tags":[281],"class_list":["post-1075","post","type-post","status-publish","format-standard","hentry","category-sql-server-2012","category-sql-server-2014","category-sql-server-2016","tag-sql-server-plan-cache"],"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1075","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/comments?post=1075"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1075\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1075"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1075"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1075"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}