DBCC FREEPROCCACHE (Transact-SQL)

Removes all elements from the procedure cache.

Transact-SQL Syntax Conventions

DBCC FREEPROCCACHE [ WITH NO_INFOMSGS ]

• WITH NO_INFOMSGS
  Suppresses all informational messages.

DBCC FREEPROCCACHE returns:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache causes, for example, an ad hoc SQL statement to be recompiled instead of reused from the cache. This can cause a sudden, temporary decrease in query performance. In SQL Server 2005 Service Pack 2, for each cleared cachstore in the plan cache, the SQL Server error log will contain the following informational message: "SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations." This message is logged every five minutes as long as the cache is flushed within that time interval.

Requires ALTER SERVER STATE permission on the server.

DBCC (Transact-SQL)

Getting SQL Server 2005 Assistance