Different Ways to Flush or Clear SQL Server Cache

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.

Note

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

Syntax for SQL Server:

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

Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse:

DBCC FREEPROCCACHE [ ( COMPUTE | ALL ) ]

     [ WITH NO_INFOMSGS ]  

[;] 

Arguments

( { 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:

·         sys.dm_exec_cached_plans

·         sys.dm_exec_requests

·         sys.dm_exec_query_memory_grants

·         sys.dm_exec_query_stats

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:

·         sys.dm_exec_query_stats

·         sys.dm_exec_requests

·         sys.dm_exec_cursors

·         sys.dm_exec_xml_handles

·         sys.dm_exec_query_memory_grants

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.

WITH NO_INFOMSGS
Suppresses all informational messages.

COMPUTE
Purge the query plan cache from each Compute node. This is the default value.

ALL
Purge the query plan cache from each Compute node and from the Control node.

Permissions

Applies to: SQL Server, Parallel Data Warehouse

·         Requires ALTER SERVER STATE permission on the server.

Applies to: SQL Data Warehouse

·         Requires membership in the DB_OWNER fixed server role.

A. Clearing a query plan from the plan cache

·         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; 

GO 

SELECT * FROM Person.Address; 

GO 

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%'; 

GO 

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); 

GO 

B. Clearing all plans from the plan cache

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.

DBCC FREEPROCCACHE WITH NO_INFOMSGS; 

C. Clearing all cache entries associated with a resource pool

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; 

GO 

DBCC FREEPROCCACHE ('default'); 

GO 

D. DBCC FREEPROCCACHE Basic Syntax Examples

The following example removes all existing query plan caches from the Compute nodes. Although the context is set to UserDbSales, the Compute node query plan caches for all databases will be removed. The WITH NO_INFOMSGS clause prevents informational messages from appearing in the results.

USE UserDbSales; 

DBCC FREEPROCCACHE (COMPUTE) WITH NO_INFOMSGS;

The following example has the same results as the previous example, except that informational messages will show in the results.

USE UserDbSales; 

DBCC FREEPROCCACHE (COMPUTE); 

When informational messages are requested and the execution is successful, the query results will have one line per Compute node.

E. Granting Permission to run DBCC FREEPROCCACHE

The following example gives the login David permission to run DBCC FREEPROCCACHE.

GRANT ALTER SERVER STATE TO TestUser;

GO