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 to that value.
After you have set all the parameters that use Adaptive Server memory, the difference between max memory and the run value of total logical memory is the memory available for additional configuration and for data and procedure caches. If you have sufficiently configured all the other configuration parameters, you can allocate this additional memory to data caches. Most changes to the data cache are dynamic and do not require a restart.
If you allocate all the additional memory to data caches, there may not be any memory available to reconfigure other configuration parameters. However, if there is additional memory available, you can dynamically increase max memory and other dynamic configuration parameters like procedure cache size, user connections, and so on.
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 caches, like the default data cache and any named caches.
Determine data cache size by looking at existing objects and applications. Adding new caches or increasing configuration parameters that consume memory does not reduce the size of the default data cache. When you have decided what memory is available for data caches and the 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.
After you determine these sizes, examine remaining I/O patterns, cache contention, and query performance. Configure caches proportional to I/O usage for objects and databases.
Keep performance goals in mind as you configure caches:
If the major goal 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 spinlock contention and improves performance.
If the 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 access methods and I/O requirements.