SQLAnywhere 16 - How do I explicitly add table into cache?

[former_member644256]

I am trying to optimize performance and while I cannot adjust some expensive queries at this time I can fine-tune the SQLA16 Server configuration (64-bit). I have a lot of RAM available and it could help to cache some of the larger views & tables. I do not see any available procedure e.g.:

sp_helpcache

Are there any options for SQLA16 to manually manage cache content ? The optimizer seems to hold the cache size to 3.6GB regardles of what I tried so far.

Thank you.

[chris_keating]

You have not defined how you have configured your engine. Unless you define the cache at startup, initial cache size will be the default which is derived as described in the -c option. And it may not grow until there is a cache demand that exceeds the current cache size.

Generally, the cache contains database pages that are relevant to the workload using a LRU algorithm to replace pages. If you have expensive queries that run better after being executed one or more times, that may indicate that the query is benefitting from being in cache. Can you cause the engine to preload the cache? The database performs cache warming. If you have a server that has reached a performance steady state, you can capture that state using cache warming. To force pages into cache, you can use the sp_read_db_pages system procedure or the ALTER DATABASE cache warming options.

[chris_keating]

I erroneously answered based on v17 functionality. Prior to these features, running primary queries at database startup i.e., a database start event that issues a select * from <table> for each table/view that you are hoping to be primed into the cache.