Using DBCC FREEPROCCACHE

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”]
DBCC FREEPROCCACHE
[/cc]

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)
[/cc]