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.
