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	Function
sp_cacheconfig	Creates or drops named caches, and changes the size, cache type, cache policy, or number of cache partitions.
sp_poolconfig	Creates and drops I/O pools, and changes their size, wash size, and asynchronous prefetch percent limit.
sp_bindcache	Binds databases or database objects to a cache.
sp_unbindcache	Unbinds specific objects or databases from a cache.
sp_unbindcache_all	Unbinds all objects bound to a specified cache.
sp_helpcache	Reports summary information about data caches, and lists the databases and database objects that are bound to caches.
sp_cachestrategy	Reports on cache strategies set for a table or index, and disables or reenables prefetching or MRU strategy.
sp_logiosize	Changes the default I/O size for the log.
sp_spaceused	Provides information about the size of tables and indexes, or the amount of space used in a database.
sp_estspace	Estimates the size of tables and indexes, given the number of rows the table will contain.
sp_help	Reports the cache to which a table is bound.
sp_helpindex	Reports the cache to which an index is bound.
sp_helpdb	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.