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’}

cache_name – is the name of an existing data cache.
cache_size – specifies the size of the cache, specified by P for pages, K for kilobytes, M for megabytes, or G for gigabytes. The default is K.
instance_name – name of the instance with a cache that you are investigating.

Example 1 – Displays information about items bound to pub_cache:
```
sp_helpcache pub_cache
```
Example 2 – Shows the amount of overhead required to create an 80MB data cache:
```
sp_helpcache "80M"
```
Example 3 – Displays information about all caches and all items bound to them:
```
sp_helpcache
```
Example 4 – (Cluster Edition) displays the overhead for the cache C2 on instance “blade1” for size 10M:
```
sp_helpcache 'C2', '10M', 'instance blade1'
```

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.

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

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

Information	Values
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