Share via

DBCC PROCCACHE (Transact-SQL)

  • Article

Displays information in a table format about the procedure cache.

Topic link iconTransact-SQL Syntax Conventions

Syntax

DBCC PROCCACHE [ WITH NO_INFOMSGS ]

Arguments

  • WITH
    Allows for options to be specified.
  • NO_INFOMSGS
    Suppresses all informational messages that have severity levels 0 through 10.

Result Sets

The following table describes the columns of the result set.

Column name Description

num proc buffs

Total number of pages used by all entries in the procedure cache.

num proc buffs used

Total number of pages used by all entries that are currently being used.

num proc buffs active

For backward compatibility only. Total number of pages used by all entries that are currently being used.

proc cache size

Total number of entries in the procedure cache.

proc cache used

Total number of entries that are currently being used.

proc cache active

For backward compatibility only. Total number of entries that are currently being used.

Remarks

The procedure cache is used to cache the compiled and executable plans to speed up the execution of batches. The entries in a procedure cache are at a batch level. The procedure cache includes the following entries:

  • Compiled plans
  • Execution plans
  • Algebrizer tree
  • Extended procedures

The SQL Server Performance Monitor uses DBCC PROCCACHE to obtain information about the procedure cache.

Permissions

Requires membership in the sysadmin fixed server role or the db_owner fixed database role.

See Also

Reference

DBCC (Transact-SQL)

Other Resources

Memory Architecture

Help and Information

Getting SQL Server 2005 Assistance