Binding Objects to Caches

Binding an object to a cache retains the object in memory and can expediate processing, keeping the bound objects in memory to avoid having any physical I/O when they are referenced.

sp_bindcache assigns a database, table, index, text object, or image object to a cache..Before you can bind an entity to a cache:
  • The named cache must exist, and its status must be “Active.”

  • The database or database object must exist.

  • To bind tables, indexes, or objects, you must be using the database where they are stored.

  • To bind system tables, including the transaction log table syslogs, the database must be in single-user mode.

  • To bind a database, you must be using the master database.

  • To bind a database, user table, index, text object, or image object to a cache, the type of cache must be “Mixed.” Only the syslogs table can be bound to a cache of “Log Only” type.

  • You must own the object or be the database owner or the system administrator.

Binding objects to caches is dynamic; you need not restart the server.

The syntax for binding objects to caches is:
sp_bindcache cache_name, dbname [,[owner.]tablename
[, indexname | "text only" ] ]
This example binds the titles table to the pubs_cache:
sp_bindcache pubs_cache, pubs2, titles
To bind an index on titles, add the index name as the third parameter:
sp_bindcache pubs_cache, pubs2, titles, titleind
The owner name is not needed in the examples above because the objects in the pubs2 database are owned by “dbo.” To specify a table owned by any other user, add the owner name. You must enclose the entire parameter in quotation marks, since the period is a special character:
sp_bindcache pubs_cache, pubs2, "fred.sales_east"
This example binds the transaction log, syslogs, to the pubs_log cache:
sp_bindcache pubs_log, pubs2, syslogs
The database must be in single-user mode before you can bind any system tables, including the transaction log, syslogs, to a cache. Use sp_dboption from master, and a use database command, and run checkpoint:
sp_dboption pubs2, single, true
text and image columns for a table are stored in a separate data structure in the database. To bind this object to a cache, add the “text-only” parameter:
sp_bindcache pubs_cache, pubs2, au_pix, "text only"
This example, executed from master, binds the tempdb database to a cache:
sp_bindcache tempdb_cache, tempdb

You can rebind objects without dropping existing bindings.

Binding caches includes restrictions. You cannot bind or unbind a database object when:

In addition, SAP ASE must lock the object while the binding or unbinding takes place, so the procedure may have a slow response time, because it waits for locks to be released.