DBCC PROCCACHE (Transact-SQL)

 

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Displays information in a table format about the procedure cache.

Topic link icon Transact-SQL Syntax Conventions

  
DBCC PROCCACHE [ WITH NO_INFOMSGS ]  

WITH
Allows for options to be specified.

NO_INFOMSGS
Suppresses all informational messages that have severity levels 0 through 10.

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.

The following table describes the columns of the result set.

Column nameDescription
num proc buffsTotal number of pages used by all entries in the procedure cache.
num proc buffs usedTotal number of pages used by all entries that are currently being used.
num proc buffs activeFor backward compatibility only. Total number of pages used by all entries that are currently being used.
proc cache sizeTotal number of entries in the procedure cache.
proc cache usedTotal number of entries that are currently being used.
proc cache activeFor backward compatibility only. Total number of entries that are currently being used.

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

DBCC (Transact-SQL)

Community Additions

ADD
Show: