Binds a database, table, index, text object, or image object to a data cache.
sp_bindcache cachename, dbname [, [ownername.]tablename [, indexname | "text only"]]
is the name of an active data cache.
is the name of the database to be bound to the cache or the name of the database containing the table, index, text or image object to be bound to the cache.
is the name of the table’s owner. If the table is owned by “dbo”, the owner name is optional.
is the name of the table to be bound to the cache, or the name of the table whose index, text object, or image object is to be bound to the cache.
is the name of the index to be bound to the cache.
binds text or image objects to a cache. When this parameter is used, you cannot give an index name at the same time.
Binds the titles table to the cache named pub_cache:
sp_bindcache pub_cache, pubs2, titles
Binds the clustered index titles.title_id_cix to the pub_ix_cache:
sp_bindcache pub_ix_cache, pubs2, titles, title_id_cix
Binds pubs2 to the tempdb_cache:
sp_bindcache tempdb_cache, pubs2
Binds the pubs2 transaction log, syslogs, to the cache named logcache:
sp_bindcache logcache, pubs2, syslogs
Binds the image chain for the au_pix table to the cache named pub_cache:
sp_bindcache pub_cache, pubs2, au_pix, "text only"
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 Adaptive 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 Adaptive Server is restarted. If a cache of the same name is created, the bindings become valid when Adaptive 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.
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.
Only a system administrator can execute sp_bindcache.
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 |
|
System procedures sp_cacheconfig, sp_configure, sp_help, sp_helpcache, sp_helpdb, sp_helpindex, sp_poolconfig, sp_unbindcache, sp_unbindcache_all
