sp_bindcache

Binds a database, table, index, text object, or image object to a data cache.

Syntax

sp_bindcache cachename, dbname
	[, [ownername.]tablename
	[, indexname | "text only"]]

Parameters

Examples

Usage

  • A database or database object can be bound to only one cache. You can bind a database to one cache and bind individual tables, indexes, text objects, or image objects in the database to other caches. The database binding serves as the default binding for all objects in the database that have no other binding. The data cache hierarchy for a table or index is as follows:
    • If the object is bound to a cache, the object binding is used.

    • If the object is not bound to a cache, but the object’s database is bound to a cache, the database binding is used.

    • If neither the object nor its database is bound to a cache, the default data cache is used.

  • The cache and the object or database being bound to it must exist before you can execute sp_bindcache. Create a cache with sp_cacheconfig and, if the operation is not dynamic, restart the SAP ASE server before binding objects to the cache.

  • Cache bindings take effect immediately, and do not require a restart of the server. When you bind an object to a data cache:
    • Any pages for the object that are currently in memory are cleared.

    • When the object is used in queries, its pages are read into the bound cache.

  • You can bind an index to a different cache than the table it references. If you bind a clustered index to a cache, the binding affects only the root and intermediate pages of the index. It does not affect the data pages (which are, by definition, the leaf pages of the index).

  • To bind a database, you must be using the master database. To bind tables, indexes, text objects, or image objects, you must be using the database where the objects are stored.

  • To bind any system tables in a database, you must be using the database and the database must be in single-user mode. Use the command:
    sp_dboption db_name, "single user", true

    For more information, see sp_dboption.

  • You do not have to unbind objects or databases in order to bind them to a different cache. Issuing sp_bindcache on an object that is already bound drops the old binding and creates the new one.

  • sp_bindcache needs to acquire an exclusive table lock when you are binding a table or its indexes to a cache so that no pages can be read while the binding is taking place. If a user holds locks on a table, and you issue sp_bindcache on that object, the task doing the binding sleeps until the locks are released.

  • When you bind or unbind an object, all stored procedures that reference the object are recompiled the next time they are executed. When you change the binding for a database, all stored procedures that reference objects in the bound database are recompiled the next time they are executed.

  • When you drop a table, index, or database, all associated cache bindings are dropped. If you re-create the table, index, or database, you must use sp_bindcache again if you want it bound to a cache.

  • If a database or a database object is bound to a cache, and the cache is dropped, the cache bindings are marked invalid, but remain stored in the sysattributes system table(s). Warnings are printed in the error log when the SAP ASE server is restarted. If a cache of the same name is created, the bindings become valid when the SAP ASE server is restarted.

  • The following procedures provide information about the bindings for their respective objects: sp_helpdb for databases, sp_help for tables, and sp_helpindex for indexes. sp_helpcache provides information about all objects bound to a particular cache.

  • Use sp_spaceused to see the current size of tables and indexes, and sp_estspace to estimate the size of tables that you expect to grow. Use sp_cacheconfig to see information about cache size and status, and to configure and reconfigure caches.

  • 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

    • 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.

Restrictions for sp_bindcache are:
  • The master database, the system tables in master, and the indexes on the system tables in master cannot be bound to a cache. You can bind non-system tables from master, and their indexes, to caches.

  • You cannot bind a database or an object to a cache if:
    • Isolation level 0 reads are active on the table

    • The task doing the binding currently has a cursor open on the table

  • If a cache has the type log only, you can bind a syslogs table only to that cache. Use sp_cacheconfig to see a cache’s type.

Permissions

The permission checks for sp_bindcache differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must be a user with manage data cache privilege.

Disabled

With granular permissions disabled, you must be a user with sa_role.

Auditing

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

InformationValues
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

Related reference
sp_cacheconfig
sp_configure
sp_dboption
sp_estspace
sp_help
sp_helpcache
sp_helpdb
sp_helpindex
sp_poolconfig
sp_spaceused
sp_unbindcache
sp_unbindcache_all