Configuring Data Caches

The most common reason for administering data caches is to reconfigure them for performance.

See the Performance and Tuning Series: Basics > Memory Use and Performance discusses performance concepts associated with data caches.

• The SAP ASE Data Cache
  The data cache holds the data, index, and log pages that are currently in use, as well as pages used recently by SAP ASE.
• Cache Configuration Commands and System Procedures
  There are various system procedures and commands for configuring data caches.
• Viewing Information About Data Caches
  Use sp_cacheconfig to create and configure named data caches. When you first install SAP ASE, it has a single cache named default data cache.
• Configuring Data Caches
  Use an absolute value to specify the default data cache and the procedure cache for SAP ASE.
• Configuring a Cache Replacement Policy
  If a cache is dedicated to a table or an index, and has little or no buffer replacement when the system reaches a stable state, you can set the relaxed LRU (least recently used) replacement policy. This policy may improve performance for caches where there is little or no buffer replacement occurring, and for most log caches.
• Dividing a Data Cache into Memory Pools
  After you create a data cache, you can divide it into memory pools, each of which may have a different I/O size.
• Binding Objects to Caches
  Binding an object to a cache retains the object in memory and can expediate processing, keeping the bound objects in memory to avoid having any physical I/O when they are referenced.
• Getting Information About Cache Bindings
  When you include the cache name, sp_helpcache provides information about a cache and the entities that are bound to it.
• Dropping Cache Bindings
  When you drop a cache binding for an object, all pages that are currently in memory are cleared from the cache.
• Changing the Wash Area for a Memory Pool
  A portion of each pool is configured as the wash area.
• Changing the Asynchronous Prefetch Limit for a Pool
  The asynchronous prefetch limit specifies the percentage of the pool that can be used to hold pages that have been brought into the cache by asynchronous prefetch, but have not yet been used by any queries.
• Changing the Size of Memory Pools
  Use sp_poolconfig to change the size of a memory pool, to specify the cache, the new size for the pool, the I/O size of the pool you want to change, and the I/O size of the pool from which the buffers should be taken.
• Adding Cache Partitions to Reduce Spinlock
  On multiengine servers, more than one task can attempt to access the cache.
• Dropping a Memory Pool
  To completely remove a pool, set its size to 0.
• Cache Binding Effects on Memory and Query Plans
  Binding and unbinding objects may have an impact on performance.
• Configuring Data Caches Using the Configuration File
  You can add or drop named data caches and reconfigure existing caches and their memory pools by editing the configuration file that is used when you start SAP ASE.