Cache: working with cache

In this post I will present the sp_cacheconfig procedure. Knowing it, in the next posts I will be able to show two common usages of the cache in the Sybase.

This command allows to define how the cache will be used. Yes, cache can be used also without knowing how this procedure works, as always blocks read from the disk will go into cache allowing to speed up future read operations. But having knowledge about this procedure you are able to design, how the memory in the cache will be used

The syntax of sp_cacheconfig is following:

sp_cacheconfig [cachename [, "cache_size[P | K | M | G]"] 
 [, logonly | mixed | inmemory_storage][, strict | relaxed]]
 [, "cache_partition=[1 | 2 | 4 | 8 | 16 | 32 | 64]"]
 [, instance instance_name]

As you can see, this command can be started without parameters. In such case only the definition of cache will be displayed. When you want to define the cache, then you should use at least two parameters:

• cache name - this is the name allowing you to reference this cache in future
• cache size - defining the size of the cache

The other parameters define how the cache will work, one of them is the most important in our case:

• logonly or mixed or inmemory_storage and the inmemory_storage is especially interesting. It will be extensively used in future examples. This type of cache allows to store the database in memory!

So for example to have a cache of size 100 MB of memory, that could be used by databases or to be more accurate to save database in memory following command should be used:

sp_cacheconfig inmem_db_cache, "100m", inmemory_storage

to display configuration use

sp_cacheconfig

If you would like to remove configuration - just set its size to );

sp_cacheconfig inmem_db_cache, "100m"

In next posts I will show the cache in 2 scenarios:

• additional temporal database in memory
• disk database preloaded into memory