When setting Adaptive Server’s memory configuration, you specify each memory requirement with an absolute value, using sp_configure. You also specify the size of the procedure and default data caches in an absolute value.
There are three configuration parameters that affect the way in which memory is allocated. They are:
The configuration parameter max memory allows you to establish a maximum setting for the amount of memory you can allocate to Adaptive Server. Setting max memory to a slightly larger value than immediately necessary provides extra memory that is utilized when Adaptive Server’s memory needs are increased.
The way Adaptive Server allocates the memory specified by max memory depends on how you configure allocate max shared memory and dynamic allocation on demand.
The allocate max shared memory parameter allows you to either allocate all the memory specified by max memory at start-up or to allocate only the memory required by the total logical memory specification during start-up.
On some platforms, if the number of shared memory segments allocated to an application is more than an optimal, platform-specific number, it could result in performance degradation. If this occurs, set max memory to the maximum amount available for Adaptive Server. Set allocate max shared memory to 1 and restart the server. This ensures that all the memory for max memory will be allocated by Adaptive Server during start time with the least number of segments.
For example, if you set allocate max shared memory to 0 (the default) and max memory to 500MB, but the server configuration only requires 100MB of memory at start-up, Adaptive Server allocates the remaining 400MB only when it requires the additional memory. However, if you set allocate max shared memory to 1, Adaptive Server allocates the entire 500MB when it starts.
The advantage of allocating all the memory at start-up, by setting allocate max shared memory to 1, is that there is no performance degradation while the server is readjusting for the additional memory. However, if you do not predict memory growth properly, and max memory is set to a large value, you may be wasting physical memory. Since you cannot dynamically decrease memory configuration parameters, it is important that you take into account other memory requirements.
dynamic allocation on demand allows you to determine whether your memory resources are allocated as soon as they are requested or only as they are needed. Setting dynamic allocation on demand to 1 allocates memory changes as needed, and setting it to 0 allocates the configured memory requested in the memory configuration change at the time of the memory reconfiguration.
For example, if you set the value of dynamic allocation on demand to 1 and you change number of user connections to 1024, the total logical memory is 1024 multiplied by the amount of memory per user. If the amount of memory per user is 112K, then the memory for user connections is 112MB (1024 x 112).
This is the maximum amount of memory that the number of user connections configuration parameter is allowed to use. However, if only 500 users are connected to the server, the amount of total physical memory used by the number of user connections parameter is 56MB (500 x 112).
Now assume the value of dynamic allocation on demand is 0; when you change number of user connections to 1024, all user connection resources are configured immediately.
Optimally, you should organize Adaptive Server’s memory so that the amount of total physical memory is less than the amount of total logical memory, which is less than the max memory. This can be achieved, in part, by setting the value of dynamic allocation on demand to 1, and setting the value of allocate max shared memory to 0.
