Cache configuration commands

The following table lists commands 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.

Command

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 a table is bound to.

sp_helpindex

Reports the cache an index is bound to.

sp_helpdb

Reports the cache a database is bound to.

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 reboot the server for them to take effect. See Table 19-1 for a description of static and dynamic actions.

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, this requires that you reboot the server. See “Configuring data caches with the configuration file” for more information.