sp_helpcache

Displays information about the objects that are bound to a data cache or the amount of overhead required for a specified cache size.

Syntax

sp_helpcache {cache_name | "cache_size[P | K | M | G]" ,
	‘instance instance_name’}

Parameters

Examples

Usage

There are additional considerations when using sp_helpcache:
  • To see the size, status, and I/O size of all data caches on the server, use sp_cacheconfig.

  • When you configure data caches with sp_cacheconfig, all the memory that you specify is made available to the data cache. Overhead for managing the cache is taken from the default data cache. The sp_helpcache displays the amount of memory required for a cache of the specified size.

  • (Cluster Edition) If you do not specify an instance_name, sp_helpcache displays information for all caches.

  • To bind objects to a cache, use sp_bindcache. To unbind a specific object from a cache, use sp_unbindcache. To unbind all objects that are bound to a specific cache, use sp_unbindcache_all.

  • The procedure sp_cacheconfig configures data caches. The procedure sp_poolconfig configures memory pools within data caches.

  • sp_helpcache computes overhead accurately up to 74GB.

  • Although you can still use sp_bindcache on a system tempdb, the binding of the system tempdb is now non-dynamic. Until you restart the server, the changes do not take effect, and sp_helpcache reports a status of “P” for pending, unless you have explicitly bound the system tempdb to the default data cache, in which case the status as “V” for valid, because by default the system tempdb is already bound to the default datacache.

Permissions

Any user can execute sp_helpcache. Permission checks do not differ based on the granular permissions settings.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

Related reference
sp_bindcache
sp_cacheconfig
sp_poolconfig
sp_unbindcache
sp_unbindcache_all