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.

Next

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

Dirty reads are active on the object.
A cursor is open on the object

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.