Dynamically Decreasing Memory Configuration Parameters

If you reset memory configuration parameters to a lower value, in-use memory is not dynamically released.

In this figure, because dynamic allocation on demand is set to 1, memory is now used only when there is an event that triggers a need for additional memory. In this example, such an event is a request for additional user connections, when a client attempts to log in to SAP ASE.

dynamic allocation on demand set to 1 with no new user connections
Graphic of three stratified images that describe how Adaptive Server’s logical memory is affected by dynamically adding users. In the first image, there are 50 user connections. The second image adds 50 more user connections, increasing the logical memory accordingly. In the third image, the number of user connections is decreased by 50 again, returning logical memory to the value in the first image.

You may decrease number of user connections to a number that is greater than or equal to the number of user connections actually allocated, because, with dynamic allocation on demand set to 1, and without an actual increase in user connection request, no additional memory is required from the server.

The figure below assumes that each of the additional 50 user connections is actually used. You cannot decrease number of user connections because the memory is in use. You can use sp_configure to specify a change to memory configuration parameters, but this change does not take place until you restart the server.

dynamic allocation on demand set to 1, with new user connections logged on
Graphic of three stratified images that describe how Adaptive Server’s logical memory is affected by dynamically adding users with dynamic allocation set to 1. In the first image, there are 50 user connections. The second image adds 50 more user connections, increasing the logical memory accordingly. In the third image, the 50 additional users are still connected to the server, so you cannot dynamically reduce the number of user connections below this total number.

When dynamic allocation on demand is set to 0, all configured memory requirements are immediately allocated. You cannot dynamically decrease memory configuration.

dynamic allocation on demand set to 0
Graphic of three stratified images that describe how Adaptive Server’s logical memory is affected by dynamically adding users with dynamic allocation set to 1. In the first image, there are 50 user connections. The second image adds 50 more user connections, increasing the logical memory accordingly. In the third image, the 50 additional users are still connected to the server, and you cannot dynamically reduce the number of user connections below this total number with dynamic alloacation on demand set to 0. You can only reduce the memory configuration parameters by restarting the server.
Note: In theory, when dynamic allocation on demand is set to 0, there should be no difference between total logical and physical memory. However, there are some discrepancies in the way that SAP ASE estimates memory needs, and the way in which memory is actually required for usage. For this reason, you may see a difference between the two during runtime.

In the previous examples, users can change the memory configuration parameter values to any smaller, valid value. This change does not take place dynamically, but it disallows new memory use. For example, if you have configured number of user connections to allow for 100 user connections and then change that value to 50 user connections (which occurs in the situations above), you can decrease the number of user connections value back to 50. This change does not affect the memory used by SAP ASE until after the server is restarted, but it prevents any new users from logging in to the server.