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.

Related concepts
Configuring Data Caches Using the Configuration File