Creating named data caches and memory pools, and binding databases and database objects to the caches, can dramatically hurt or improve Adaptive Server performance. For example:
A cache that is poorly used hurts performance.
If you allocate 25% of your data cache to a database that services a very small percentage of the query activity on your server, I/O increases in other caches.
A pool that is unused hurts performance.
If you add a 16K pool, but none of your queries use it, you have taken space away from the 2K pool. The 2K pool’s cache hit ratio is reduced, and I/O is increased.
A pool that is overused hurts performance.
If you configure a small 16K pool, and virtually all of your queries use it, I/O rates are increased. The 2K cache will be under-used, while pages are rapidly cycled through the 16K pool. The cache hit ratio in the 16K pool will be very poor.
When you balance your pool utilization within a cache, performance can increase dramatically.
Both 16K and 2K queries experience improved cache hit ratios. The large number of pages often used by queries that perform 16K I/O do not flush 2K pages from disk. Queries using 16K will perform approximately one-eighth the number of I/Os required by 2K I/O.
When tuning named caches, always measure current performance, make your configuration changes, and measure the effects of the changes with similar workload.
