sybanurag

Source: Sybooks

Below Data Cache configuration can improve the performance:

• Configure named data caches to be large enough to hold critical tables and indexes. This keeps other server activity from contending for cache space and speeds queries using these tables, since the needed pages are always found in cache. Can configure these caches to use the relaxed LRU replacement policy, reducing the cache overhead.
• To increase concurrency, bind a hot table to one cache and the indexes on the table to other caches.

• Create a named data cache large enough to hold the hot pages of a table where a high percentage of the queries reference only a portion of the table.

For example, if a table contains data for a year, but 75% of the queries reference data from the most recent month (about 8% of the table), configuring a cache of about 10% of the table size provides room to keep the most frequently used pages in cache and leaves some space for the less frequently used pages

• Assign tables or databases used in decision-support systems (DSS) to specific caches with large I/O configured.
This keeps DSS applications from contending for cache space with OLTP applications. DSS applications typically access large numbers of sequential pages, and OLTP applications typically access relatively few random pages.

• Bind tempdb to its own cache to keep it from contending with other user processes. Proper sizing of the tempdb cache can keep most tempdb activity in memory for many applications. If this cache is large enough, tempdb
activity can avoid performing I/O.

• Bind text pages to named caches to improve the performance on text access.
• Bind a database’s log to a cache, again reducing contention for cache space and access to the cache.