Cache and the memory governor

The SQL Anywhere database server utilizes the cache (buffer pool) to temporarily store 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 requires 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 provide efficient execution of the workload.

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

Use the following settings, operators, and statistics to understand, and control, how the memory governor uses the cache:

  • QueryMemMaxUseful graphical plan operator   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.

  • QueryMemActiveMax server property   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.

  • QueryMemActiveEst Performance Monitor statistic   The memory governor 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.

  • query_mem_timeout database option   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 cause it to perform slowly, possibly with a low-memory execution strategy if one exists for memory-intensive physical operators in that plan.

  • QueryMemGrantWaiting server property and Performance Monitor statistic   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.

  • QueryMemGrantWaited server property and Performance Monitor statistic   The database server property and Performance Monitor statistic QueryMemGrantWaited shows the total number of times that a request had to wait before a memory request was granted.

  • QueryMemNeedsGrant graphical plan operator   In the graphical plan, QueryMemNeedsGrant shows whether the memory governor considers the request 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 portion of the query memory pool.

  • QueryMemLikelyGrant graphical plan operator   In the graphical plan, QueryMemLikelyGrant shows an estimate of how many pages are likely to be granted to the request for execution.

 See also
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1