Getting Information About Cache Bindings

When you include the cache name, sp_helpcache provides information about a cache and the entities that are bound to it.

For example:
sp_helpcache pubs_cache
Cache Name           Config Size     Run Size       Overhead
-------------------- -------------   ----------     ----------
pubs_cache            10.00 Mb       10.00 Mb        0.77 Mb

------------------ Cache Binding Information: ------------------

Cache Name      Entity Name           Type   Index Name  Status
----------      -----------           ----   ----------  ------
pubs_cache      pubs2.dbo.titles      index  titleind    V
pubs_cache      pubs2.dbo.au_pix      index  tau_pix     V
pubs_cache      pubs2.dbo.titles      table              V
pubs_cache      pubs2.fred.sales_east table              V

If you use sp_helpcache without a cache name, it prints information about all the configured caches on SAP ASE and all the objects that are bound to them.

sp_helpcache performs string matching on the cache name, using %cachename%. For example, “pubs” matches both “pubs_cache” and “pubs_log”.

The Status column reports whether a cache binding is valid (“V”) or invalid (“I”). If a database or object is bound to a cache, and the cache is deleted, binding information is retained in the system tables, but the cache binding is marked as invalid. All objects with invalid bindings use the default data cache. If you subsequently create another cache with the same name, the binding becomes valid when the cache is activated. The status of all user-defined caches must be “mixed cache” or “log only”.