Cache Configuration Commands and System Procedures

There are various system procedures and commands for configuring data caches.

This table lists commands and system procedures for configuring named data caches, for binding and unbinding objects to caches, and for reporting on cache bindings. It also lists procedures you can use to check the size of your database objects, and commands that control cache usage at the object, command, or session level.

Commands and procedures for configuring named data caches

Command or procedure



Creates or drops named caches, and changes the size, cache type, cache policy, or number of cache partitions.


Creates and drops I/O pools, and changes their size, wash size, and asynchronous prefetch percent limit.


Binds databases or database objects to a cache.


Unbinds specific objects or databases from a cache.


Unbinds all objects bound to a specified cache.


Reports summary information about data caches, and lists the databases and database objects that are bound to caches.


Reports on cache strategies set for a table or index, and disables or reenables prefetching or MRU strategy.


Changes the default I/O size for the log.


Provides information about the size of tables and indexes, or the amount of space used in a database.


Estimates the size of tables and indexes, given the number of rows the table will contain.


Reports the cache to which a table is bound.


Reports the cache to which an index is bound.


Reports the cache to which a database is bound.

set showplan on

Reports on I/O size and cache utilization strategies for a query.

set statistics io on

Reports number of reads performed for a query.

set prefetch [on |off]

Enables or disables prefetching for an individual session.

select... (prefetch...lru | mru)

Forces the server to use the specified I/O size or MRU replacement strategy.

Most of the parameters for sp_cacheconfig that configure the data cache are dynamic and do not require that you restart the server for them to take effect.

In addition to using the commands to configure named data caches interactively, you can also edit the configuration file located in the $SYBASE directory. However, doing so requires that you restart the server.

Related concepts
Configuring Data Caches Using the Configuration File