The DBCC FREEPROCCACHE command clears the procedure cache for all the procedures on the server.  This command should be used with caution, however in most circumstances it will not bring a server to its knees. In order to execute in SQL 2005+, you need to have the server-level permissions of ALTER SERVER STATE.

To use it, simple execute this:
[cc lang=”sql”]

From a production standpoint, this command is sometimes used when it has been determined that execution plans have gone bad.  This is typically a rare event and can be determined by comparing the baseline duration of commonly executed procedures.  Usually if one procedure is determined to be bad a better thing to execute is sp_recompile.

From a development standpoint, this command is used in order to clear the procedure cache in order to get a baseline for the execution time of statements or stored procedures.

In SQL Server 2008, the command can now accept a SQL Handle, Plan Handle, or a Resource Governor Pool Number, allowing you to isolate what cache you are clearing.

[cc lang=”sql”]
DBCC FREEPROCCACHE (0x03000500462D954470E2FF00BE9D00000100000000000000)

Featured Articles

 Site Author

  • Thanks for visiting!