Share via


DBCC FREEPROCCACHE (Transact-SQL)

Removes all elements from the procedure cache.

Topic link iconTransact-SQL Syntax Conventions

Syntax

DBCC FREEPROCCACHE [ WITH NO_INFOMSGS ]

Arguments

  • WITH NO_INFOMSGS
    Suppresses all informational messages.

Result Sets

DBCC FREEPROCCACHE returns:

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

Remarks

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.

Permissions

Requires ALTER SERVER STATE permission on the server.

See Also

Reference

DBCC (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

12 December 2006

New content:
  • Added information about the error message logged in Service Pack 2 when this command is executed.