You may use DBCC FREEPROCCACHE to clear all elements from the plan cache using below syntax:
You may also clear cache of a particular procedure using below syntax:
Plan handle for a specific query can be found using DMV as mentioned in below example.
Example:
Clearing entire procedure cache:
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
-------------------------------------------------- -----------------------------
0x060006001ECA 270EC0215D0500 SELECT * FROM employee where employee_id = 9
Step 2:
Use below syntax to remove cache for the specific query as below:
GO
Clearing all cache associated with a resource pool:
Below code clears all cache associated with resource pool “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:
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.
Comments