HI friends, most of the time we used DBCC FREEPROCCACHE to clear the procedure cache, clears the specific plan from cache by specifying plan handle or SQL handle.
DBCC FREEPROCCACHE does not require any parameters.
To clear the plan cache we just need to execute below query –
Whenever we execute this command, it will results as message like –
“DBCC execution completed. If DBCC printed error messages, contact your system administrator.”
If you don’t want any information message to be appear after execution then execute the query in this way –
DBCC FREEPROCCACHE WITH NO_INFOMSGS
But when we required to clean the procedure cache for a particular database then in that case we use another DBCC command – DBCC FLUSHPROCINDB.
This command work is identical to FREEPROCCACHE only difference is that it accepts the parameters and can be executed for a single database only.
Suppose I want to clear the procedure cache for my database ‘Test_kapil’ then I can do it in this way-
SELECT name as DBname, dbid as DatabaseID FROM sys.sysdatabases WHERE name like ‘%Test_Kapil%’
First we will find out the DBID from the system table using above query.
After getting DBIS from that we will pass that DBID to command.
This is how we can also clean the procedure cache for a particular database.
Happy Learning 🙂