The first step in developing a plan for cache usage is to provide as much memory as possible for the data cache:
Determine the maximum amount of memory you can allocate to Adaptive Server. Set 'max memory' configuration parameter to that value.
Once all the configuration parameters that use Adaptive Server memory have been configured, the difference between the 'max memory' and run value of 'total logical memory' is the memory available for additional configuration and/or for data/procedure caches. If you have sufficiently configured all the other configuration parameters, you can choose to allocate this additional memory to data caches. Most changes to the data cache are dynamic and do not require a reboot.
Note that if you allocate all the additional memory to data caches, there may not be any memory available for reconfiguration of other configuration parameters. However, if there is additional memory available in your system, 'max memory' value can be increased dynamically and other dynamic configuration parameters like 'procedure cache size', 'user connections, etc., can be increased.
Use your performance monitoring tools to establish baseline performance, and to establish your tuning goals.
Determine the size of memory you can allocate to data caches as mentioned in the above steps. Include the size of already configured cache(s), like the default data cache and any named cache(s).
Decide the data caches's size by looking at existing objects and applications. Note that addition of new caches or increase in configuration parameters that consume memory does not reduce the size of the default data cache. Once you have decided the memory available for data caches and size of each individual cache, add new caches and increase or decrease size of existing data caches.
Evaluate cache needs by analyzing I/O patterns, and evaluate pool needs by analyzing query plans and I/O statistics.
Configure the easiest choices that will gain the most performance first:
Choose a size for a tempdb cache.
Choose a size for any log caches, and tune the log I/O size.
Choose a size for the specific tables or indexes that you want to keep entirely in cache.
Add large I/O pools for index or data caches, as appropriate.
Once these sizes are determined, examine remaining I/O patterns, cache contention, and query performance. Configure caches proportional to I/O usage for objects and databases.
Keep your performance goals in mind as you configure caches:
If your major goal in configuring caches is to reduce spinlock contention, increasing the number of cache partitions for heavily-used caches may be the only step.
Moving a few high-I/O objects to separate caches also reduces the spinlock contention and improves performance.
If your major goal is to improve response time by improving cache hit ratios for particular queries or applications, creating caches for the tables and indexes used by those queries should be guided by a thorough understanding of the access methods and I/O requirements.
