DBCC FREEPROCCACHE in SQL SERVER

DBCC FREEPROCCACHE is a command used in Microsoft SQL Server to clear the procedure cache, which stores the execution plans of SQL queries. By clearing the cache, SQL Server will remove the stored execution plans and force recompilation of the queries when they are executed again.

Syntax

DBCC FREEPROCCACHE

Use of this command

Ø  Clears Execution Plans: It removes all cached query plans from memory.

Ø  Forces Recompilation: The next time a query is executed, SQL Server will need to generate a new execution plan.

Ø  Performance Impact: It can temporarily degrade performance because query recompilation is an expensive process.

Ø  Use with Caution: This command should generally be used in testing or maintenance scenarios and not during peak production times unless absolutely necessary.

DBCC FREEPROCCACHE (plan_handle);

This is used to clear a specific plan.