Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.


Is there any point in setting the cache limits on a server with 24 Gb, running a database on approx. 11 Gb? I'm thinking about the -c -cl and -ch parameters. And what is the point in setting "-ca 0" to disable automatic cache resize? Is there any situation where you want to use this parameter?


Bjarne Anker

asked 23 Jan '18, 12:55

Bjarne%20Anker's gravatar image

Bjarne Anker
accept rate: 10%

edited 24 Jan '18, 09:35

Volker%20Barth's gravatar image

Volker Barth

Here's an excerpt from this Foxhound 4 Help topic:

Performance Tip: If the computer is dedicated to running this SQL Anywhere server and no other process of importance, consider disabling the automatic cache size tuning process by specifying the -ca 0 and -c options to set a fixed cache size. With a dedicated computer there may be no need for SQL Anywhere to perform extra work changing the cache size up and down.

permanent link

answered 24 Jan '18, 08:58

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%


That being said, using the defaults may be helpful to get to know how much maximum cache size the database server will use for typical workloads... So I recommend to study the according properties and/or the according console log messages, and use the results to specify the -c option value...

(24 Jan '18, 09:32) Volker Barth

Thanks for the input guys.

I've started the service with automatic cache resize (no -ca 0 parameter) and the high limit for the cache (-ch) bigger than the databasesize (12 GB for a 9 GB database). I looks very promising.


permanent link

answered 24 Jan '18, 09:38

Bjarne%20Anker's gravatar image

Bjarne Anker
accept rate: 10%

In my understanding, the default value for the maximum cache size on Win64 is 90 % of the total physical memory of the machine, which would be about 21,6 GB, so I'm not sure you have increased the maximum cache size... Note that cache is also used for temporary file space, so whether a "somewhat-more-than-dbsize" cache is optimal or not is difficult to tell without real workloads...

I would prefer to set the initial cache size because that would default to 25 % of the total RAM here, so only 6 GB. As stated, I can't tell whether that is fitting for you, but the properties can tell you so...

(25 Jan '18, 07:52) Volker Barth

FWIW Foxhound provides ongoing information related to cache usage:

  • cache size and usage: 13M, 0% of 14G

  • temporary file size, usage, fragments etc: Temp 2M 48.9% 5 322G C:\Users\Breck\AppData\Local\Temp\sqla0001.tmp

  • database-level temporary space usage, rollback log size and number of uncommitted operations

  • connection-level temporary space usage, rollback log size and number of uncommitted operations

See "Usage 2: Runaway Memory Usage" in the white paper here.

(26 Jan '18, 07:33) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 23 Jan '18, 12:55

question was seen: 1,846 times

last updated: 26 Jan '18, 07:33