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.

The memory governor

See also