Clearing entire procedure cache from SQL Server (DBCC FREEPROCCACHE)

You may use DBCC FREEPROCCACHE to clear all elements from the plan cache using below syntax:

DBCC FREEPROCCACHE

You may also clear cache of a particular procedure using below syntax:

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

Plan handle for a specific query can be found using DMV as mentioned in below example.

Example:

Clearing entire procedure cache:

DBCC FREEPROCCACHE

Clearing plan cache of a query as  SELECT * FROM employee where employee_id = 9

Step 1: Determine plan_handle for the specific query using below code:

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 employee where employee_id %';

GO

You should get plan_handle value for the specific query. Let us assume you got an output as below:

plan_handle                                         text

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

0x060006001ECA270EC0215D0500      SELECT * FROM employee where employee_id = 9

Step 2:

Use below syntax to remove cache for the specific query as below:

DBCC FREEPROCCACHE (0x060006001ECA270EC0215D0500); -- Use the value from step 1
GO

Clearing all cache associated with a resource pool:

Below code clears all cache associated with resource pool “default”:

DBCC FREEPROCCACHE ('default');

GO

Note:

If you want to suppress the informational message (typical dbcc output as "DBCC execution completed. If DBCC printed error messages, contact your system administrator."), you may use WITH NO_INFOMSGS clause with any of the above examples.

Example:

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

Caution: Be very careful before using this command. Deleting cache will force the procedure or code to recompile. This will create a sudden drop in performance of procedures or codes (whose cache has been cleared) before it picks up again.