Any suggestions? Export (0) Print
Expand All



THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008) noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool.


DBCC FREEPROCCACHE does not clear the execution statistics for natively compiled stored procedures. The procedure cache does not contain information about natively compiled stored procedures. Any execution statistics collected from procedure executions will appear in the execution statistics DMVs: sys.dm_exec_procedure_stats (Transact-SQL) and sys.dm_exec_query_plan (Transact-SQL).

This topic applies to SQL Server. For SQL Data Warehouse and Parallel Data Warehouse, see DBCC FREEPROCCACHE (Azure SQL Data Warehouse, Parallel Data Warehouse).

Topic link icon Transact-SQL Syntax Conventions

DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ]

( { plan_handle | sql_handle | pool_name } )

plan_handle uniquely identifies a query plan for a batch that has executed and whose plan resides in the plan cache. plan_handle is varbinary(64) and can be obtained from the following dynamic management objects:

sql_handle is the SQL handle of the batch to be cleared. sql_handle is varbinary(64) and can be obtained from the following dynamic management objects:

pool_name is the name of a Resource Governor resource pool. pool_name is sysname and can be obtained by querying the sys.dm_resource_governor_resource_pools dynamic management view.

To associate a Resource Governor workload group with a resource pool, query the sys.dm_resource_governor_workload_groups dynamic management view. For information about the workload group for a session, query the sys.dm_exec_sessions dynamic management view.


Suppresses all informational messages.

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.

The following reconfigure operations also clear the procedure cache:

  • access check cache bucket count

  • access check cache quota

  • clr enabled

  • cost threshold for parallelism

  • cross db ownership chaining

  • index create memory

  • max degree of parallelism

  • max server memory

  • max text repl size

  • max worker threads

  • min memory per query

  • min server memory

  • query governor cost limit

  • query wait

  • remote query timeout

  • user options

When the WITH NO_INFOMSGS clause is not specified, DBCC FREEPROCCACHE returns:

"DBCC execution completed. If DBCC printed error messages, contact your system administrator."

Requires ALTER SERVER STATE permission on the server.

The following example clears a query plan from the plan cache by specifying the query plan handle. To ensure the example query is in the plan cache, the query is first executed. The sys.dm_exec_cached_plans and sys.dm_exec_sql_text dynamic management views are queried to return the plan handle for the query. The plan handle value from the result set is then inserted into the DBCC FREEPROCACHE statement to remove only that plan from the plan cache.

USE AdventureWorks2012;
SELECT * FROM Person.Address;
SELECT plan_handle, st.text
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'SELECT * FROM Person.Address%';

Here is the result set.

plan_handle                                         text

--------------------------------------------------  -----------------------------

0x060006001ECA270EC0215D05000000000000000000000000  SELECT * FROM Person.Address;

(1 row(s) affected)

-- Remove the specific plan from the cache.
DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000);

The following example clears all elements from the plan cache. The WITH NO_INFOMSGS clause is specified to prevent the information message from being displayed.


The following example clears all cache entries associated with a specified resource pool. The sys.dm_resource_governor_resource_pools view is first queried to obtain the value for pool_name.

SELECT * FROM sys.dm_resource_governor_resource_pools;

Community Additions

© 2016 Microsoft