Determining the Procedure Cache Size

Use procedure cache size to specify, or view, the size of your procedure cache in 2K pages, regardless of the server’s logical page size.

For example:

sp_configure "procedure cache size"

Parameter Name        Default  Memory Used  Config Value  Run Value
Unit              Type
--------------------  -------  -----------  ------------  ---------
---------------  ---------
procedure cache size     7000        15254          7000       7000
memory pages(2k)  dynamic

The amount of memory used for the procedure cache is appoximately 32.5KB (that is, 15254 2K pages plus overhead). To set the procedure cache to a different size, issue:

sp_configure "procedure cache size", new_size

This example resets the procedure cache size to 10000 2K pages (20MB):

sp_configure "procedure cache size", 10000