The memory governor

The SQL Anywhere database server utilizes the cache, also called the buffer pool, to temporarily store (buffer) images of database pages in memory. These pages are typically table pages and index pages,
although there are several other types of physical pages stored in a SQL Anywhere database. In addition to these pages, the
database server utilizes the cache for two other pools of memory. One of these pools is the virtual memory used for database
server data structures, such as those that represent connections, statements, and cursors. The second pool consists of cache
pages that are used as virtual storage for query memory.

Query execution algorithms, such as hash join and sorting, require memory to operate efficiently. SQL Anywhere uses a memory governor to decide how much query memory each statement can use for query execution. The memory governor is responsible for allocating
a pool of query memory to statements to give efficient execution of the workload. The QueryMemPages database server property
shows the number of pages in the query memory pool that are available for distribution. The pool size is set to be a proportion
of the maximum cache size for the server; that is, the cache size's upper bound, which can be controlled by the -ch server option. The QueryMemPercentOfCache
database server property gives the proportion of maximum cache size that can be query memory, which is 50%.

The memory governor grants individual statements a selected number of pages that the statement can then use for memory-intensive
query processing algorithms. Memory in the query memory pool is still available for other purposes (such as buffering table
or index pages) until the query processing algorithm uses the pages. Memory-intensive query processing algorithms that use
query memory include all hash-based operators, such as hash distinct, hash group by, and hash join, and sorting and window
operators.

When a statement begins executing, the memory governor uses the optimizer's estimates to determine how much memory would be
useful to the statement. This estimate appears in the graphical plan as QueryMemMaxUseful. Query memory for the statement
is allocated across the particular memory-intensive operators used in the access plan for that request. Parallel memory-intensive
operators beneath an Exchange operator each receive their own allocation of query memory. Simple requests do not benefit from
large amounts of memory, but requests that use hash-based operators or sorting can operate more efficiently if there is enough
memory to hold all the needed rows in memory.

Increasing the database server multiprogramming level requires the database server to reserve some amount of query memory
for each additional concurrent task, or request, reducing the amount available to any particular request. Also, the memory
governor limits the number of memory-intensive requests that can execute concurrently. This maximum value is selected based
on the performance characteristics of the computer running the database server, and the limit is shown with the server property
QueryMemActiveMax. The memory governor also maintains a running estimate of the number of concurrent memory intensive requests,
and this estimate is available as the database server property and Performance Monitor statistic QueryMemActiveEst. The memory
governor uses this running average to decide how to assign memory from the query memory pool. If few memory-intensive requests
have been executing, then more memory is assigned to each one. If many have been executing, each one is assigned less to share
the query memory more evenly, taking into account the estimated number of query memory pages useful to each request.

If a memory-intensive statement begins executing and there are already the maximum number of concurrent memory-intensive requests
executing, then incoming statements wait for one of the existing requests to release its allocated memory. The query_mem_timeout
database option controls how long the incoming request waits for a memory grant. With the default setting of -1, the request
waits for a database server-defined period of time. If no memory grant is available after waiting, then the statement's access
plan is executed with a small amount of memory, which could lead it to perform slowly, possibly with a low-memory execution
strategy if one exists for memory-intensive physical operators in that plan. The database server property and Performance
Monitor statistic QueryMemGrantWaiting shows the current number of requests that are waiting for a memory request to be granted,
and QueryMemGrantWaited shows the total number of times that a request had to wait before a memory request was granted.

In the graphical plan, the value QueryMemNeedsGrant shows whether the memory governor considers this to be a simple request
(no memory grant needed) or memory intensive (a memory grant is needed). If the memory governor classifies a request as not
needing a memory grant, then the request begins executing immediately. Otherwise, the request asks to use a proportion of
the query memory pool. The graphical plan value QueryMemLikelyGrant shows an estimate of how many pages are likely to be granted
to the request for execution.