A heap memory pool is an internal memory pool created at start-up that tasks use to dynamically allocate memory as needed. This memory pool is used by tasks that requires a lot of memory from the stack, such as tasks that use wide columns. For example, if you make a wide column or row change, the temporary buffer this task uses can be as larger as 16K, which is too big to allocate from the stack. Adaptive Server dynamically allocates and frees memory during the task’s run time. The heap memory pool dramatically reduces the pre-declared stack size for each task while also improving the efficiency of memory usage in the server. The heap memory the task uses is returned to the heap memory pool when the task is finished.
Set the heap memory with the heap memory per user configuration parameter. The syntax for heap memory per user is:
sp_configure 'heap memory per user', amount_of_memory
Heap memory is measured in bytes per user. By default, the amount of memory is set to 4096 bytes. This example specifies setting the default amount of heap memory for 10 users:
sp_configure 'heap memory per user', 10
You can also specify the amount of memory in the number of bytes per user. For example, the following example specifies that each user connection is allocated 4K bytes of heap memory:
sp_configure 'heap memory per user', 0, "4K"
At the initial Adaptive Server configuration, 1MB is set aside for heap memory. Additional heap memory is allocated for all the user connections and worker processes for which the server is configured, so the following configuration parameters affect the amount of heap memory available when the server starts:
number of user connections
number of worker processes
The global variable @@heapmemsize reports the size of the heap memory pool, in bytes.
