These cache recommendations can improve performance on both single and multiprocessor servers:
Adaptive Server writes log pages according to the size of the logical page size. Larger log pages potentially reduce the rate of commit-sharing writes for log pages.
Commit-sharing occurs when, instead of performing many individual commits, Adaptive Server waits until it can perform a batch of commits at one time. Per-process user log caches are sized according to the logical page size and the user log cache size configuration parameter. The default size of the user log cache is one logical page.
For transactions generating many log records, the time required to flush the user log cache is slightly higher for larger logical page sizes. However, because the log-cache sizes are also larger, Adaptive Server does not need to perform as many log-cache flushes to the log page for long transactions.
See the Utilities Guide for specific information.
Create a named cache for tempdb and configure the cache for 16K I/O for use by select into queries and sorts.
Create a named cache for the logs for your high-use databases. Configure pools in this cache to match the log I/O size set with sp_logiosize.
If a table or its index is small and constantly in use, create a cache for just that object or for a few objects.
For caches with cache hit ratios of more than 95%, configure relaxed LRU cache replacement policy if you are using multiple engines.
Keep cache sizes and pool sizes proportional to the cache utilization objects and queries:
If 75% of the work on your server is performed in one database, that database should be allocated approximately 75% of the data cache, in a cache created specifically for the database, in caches created for its busiest tables and indexes, or in the default data cache.
If approximately 50% of the work in your database can use large I/O, configure about 50% of the cache in a 16K memory pool.
It is better to view the cache as a shared resource than to try to micromanage the caching needs of every table and index.
Start cache analysis and testing at the database level, concentrating on particular tables and objects with high I/O needs or high application priorities and those with special uses, such as tempdb and transaction logs.
On SMP servers, use multiple caches to avoid contention for the cache spinlock:
Use a separate cache for the transaction log for busy databases, and use separate caches for some of the tables and indexes that are accessed frequently.
If spinlock contention is greater than 10% on a cache, split it into multiple caches or use cache partitions.
Use sp_sysmon periodically during high-usage periods to check for cache contention.
Set relaxed LRU cache policy on caches with cache hit ratios of more than 95%, such as those configured to hold an entire table or index.
