The monMemoryUsage monitoring table provides information about server and kernel memory pools, including metrics about their sizes, usage patterns, and availablility.
The columns for monMemoryUsage are:
Name | Datatype | Attribute | Description |
---|---|---|---|
Flags | int | Status flags that describe the memory pool. | |
ConfigNum | int | Configuration number for the primary configuration option controlling the size of the memory pool. | |
TotalSize | bigint | Total size, in bytes, of the memory pool. | |
UsedSize | bigint | Currently used size, in bytes, of the memory pool. | |
FreeSize |
bigint | Amount of free memory, in bytes, in the pool. | |
NumAllocs | bigint | Counter | Total number of allocations requested. |
NumFrees | bigint | Counter | Total number of frees performed. "Frees” are the opposite of allocations. Adaptive Server allocates memory fragments, and the number of allocations is tracked by NumAllocs. When the task is finished, the memory fragment is freed (that is, returned to the memory pool). NumFrees tracks the total number of these free operations |
NumSleeps | bigint | Counter | Total number of sleeps encountered while allocating memory fragments. |
PoolOwnerKPID | int | Kernel process ID (KPID) of task that owns this fragment of the memory pool. | |
MemoryPoolName | varchar(32) | Null, Parameter | Name of the memory pool. |
PoolType | varchar(30) | Null, Parameter | Type of memory pool. One of:
|
ConfigOption | varchar(255) | Null | Name of the primary configuration option controlling the size of the memory pool. |
NumSearches | bigint | Counter, Null | Total number of free fragments examined before satisfying memory allocation requests from this memory pool. |
NumRetries | bigint | Counter, Null | Number of retries performed for all free fragments. |
ItemSize | int | Null | Size of an individual item (applies to object pool). |
MinNumItems | int | Null | Minimum number of items in this pool. |
MaxNumItems | int | Null | Maximum number of items in this pool. |
NumUsedItems | int | Null | Number of used items in this pool. |
NumItemsUsedHWM | int | Null | High-water mark for the number of items used in this pool. |
MinUsedItemSize | bigint | Null | Size, in bytes, of smallest used item. |
AvgUsedItemSize | bigint | Null | Average size, in bytes, of used items. |
MaxUsedItemSize | bigint | Null | Size, in bytes, of largest used item. |
NumUsedItemsMinSize | int | Null | Number of minimum-sized used items in this pool. |
NumUsedItemsMaxSize | int | Null | Number of maximum-sized used items in this pool. |
NumFreeItems | Null | Number of free items in this pool. | |
MinFreeItemSize | bigint | Null | Size, in bytes, of smallest item that is free. |
AvgFreeItemSize | bigint | Null | Average size, in bytes, of free items. |
MaxFreeItemSize | bigint | Null | Size, in bytes, of largest item that is free. |
NumFreeItemsMinSize | int | Null | Number of minimum-sized free items in this pool. |
NumFreeItemsMaxSize | int | Null | Number of maximum-sized free items in this pool. |
NumBlocks | int | Null | Number of blocks of memory used for this pool. |
MemSize1 | int | Null | Memory pool specific request size 1, in bytes. |
NumUsedItemsSize1 | int | Null | Number of used items in this pool of size MemSize1 . |
NumFreeItemsSize1 | int | Null | Number of free items in this pool of size MemSize1 . |
MemSize2 | int | Null | Memory pool specific request size 2, in bytes. |
NumUsedItemsSize2 | int | Null | Number of used items in this pool of size MemSize2. |
NumFreeItemsSize2 | int | Null | Number of free items in this pool of size MemSize2. |
Not all output from all monMemoryUsage columns applies, or is relevant to, all memory pools, and depending on the type of memory pool, you may need to select the relevant columns. Typically, columns return a value of NULL if they do not apply to a specific memory pool.
select PoolType = convert(varchar(10), PoolType), MemoryPoolName = convert(varchar(30), MemoryPoolName), ConfigOption = convert(varchar(30), ConfigOption) from monMemoryUsage order by 1, 2 PoolType MemoryPoolName ConfigOption -------- ----------------------- ---------------------------- Block Compression compression memory size Block Global Block Pool NULL Block Kernel Resource Memory kernel resource memory Block Proc Cache Header procedure cache size Block Pss Heap Memory heap memory per user Block RTMS Block Heap messaging memory Fragment CPINFO memory pool compression info pool size Fragment Column Default Pool column default cache size Fragment Data Cache Frag NULL Fragment Data Change Frag NULL Fragment Data Transfer Utility transfer utility memory size [...]
select MemoryPoolName = convert(varchar(30), MemoryPoolName), TotalSize, UsedSize, FreeSize, NumAllocs, NumFrees from monMemoryUsage order by 1
select MemoryPoolName = convert(varchar(30), MemoryPoolName), NumUsedItems, NumItemsUsedHWM, MinUsedItemSize, AvgUsedItemSize, MaxUsedItemSize from monMemoryUsage where PoolType = "Fragment" MemoryPoolName NumUsedItems NumItemsUsedHWM MinUsedItemSize AvgUsedItemSize MaxUsedItemSize -------------- ------------ --------------- --------------- --------------- --------------- Pss Frag Pool 983048 0 16960 0 0 Pss Frag Pool 1376267 0 17040 0 0 Pss Frag Pool 2293778 0 16960 0 0