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.

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

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.

Displays information about items bound to pub_cache:

sp_helpcache pub_cache

Shows the amount of overhead required to create an 80MB data cache:

sp_helpcache "80M"

Displays information about all caches and all items bound to them:

sp_helpcache

For cluster environments – displays the overhead for the cache C2 on instance “blade1” for size 10M:

sp_helpcache 'C2', '10M', 'instance blade1'

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.
For cluster environments – 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:

Event	Audit option	Command or access audited	Information in extrainfo
38	exec_procedure	Execution of a procedure	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