## 3.1.3  Memory Information from SQL Commands
There are a number of ways to analyze memory usage based on pre-defined and modifiable SQL queries.
The Statement Library of SAP HANA database explorer provides a set of tabular views to display the memory consumption of loaded tables based on pre-defined SQL queries:
- The view Component Memory Usage shows the aggregated memory consumption in bytes for different heap memory service components, services, and ports. Order by the used memory size column and find the largest consumers. The following heap memory service components are available: 1) Column Store Tables, 2) Row Store Tables, 3) Statement Execution & Intermediate Results, 4) Monitoring & Statistical Data, 5) Caches, 6) System, 7) Code Size, 8) Stack Size.
- The view Schema Size of Loaded Tables displays the aggregated memory consumption of loaded tables in MB for different database schemas. The aggregation comprises both column store and row store tables. Order by the schema size column and find the largest consumers.
- The view Used Memory by Tables shows two values: the total memory consumption of all column store tables in MB and the total memory consumption of all row store tables in MB.
##  Note
You can find this and other information in the Memory Analysis app of the SAP HANA cockpit. For more information, see Analyze Memory Statistics in the SAP HANA Administration Guide .
## SQL Statement Collection
SAP Note 1969700 - SQL Statement Collection for SAP HANA contains several commands that are useful to analyze memory-related issues. Based on your needs you can configure restrictions and parameters in the sections marked with /* Modification section */ .
Some of the memory-related statements which you may find useful are described here:
- HANA_Memory_Overview
- HANA_Memory_TopConsumers* - available in two versions depending on the release. The output of the statement is the same in both cases:
- HANA_Memory_TopConsumers_History (available in older versions of the statement collection)
- HANA_Memory_TopConsumers_TimeSlices (supersedes 'Consumers History' in versions SPS 04 and above)
- HANA_Tables_LargestTables
- HANA_Memory_SharedMemory
## HANA_Memory_Overview
This query gives an overview of current memory information showing key values in the following columns:
- NAME: Description
- TOTAL_GB: Total size (GB)
- DETAIL_GB: Size on detail level (GB) - this is shown at two levels with a breakdown at a second level - see the following table:
| NAME                                 | TOTAL_GB   | DETAIL_GB                   | DETAIL2_GB                      |
|--------------------------------------|------------|-----------------------------|---------------------------------|
| User-defined global allocation limit | not set    |                             |                                 |
| License memory limit                 | 256        |                             |                                 |
| License usage                        | 143        | 124 (2014/03/01-2014/03/31) |                                 |
|                                      |            | 143 (2014/04/01-2014/04/30) |                                 |
|                                      |            | 113 (2014/05/01-2014/05/09) |                                 |
| Physical memory                      | 256        | 256 (vhbshk0sdb)            |                                 |
| HANAinstance memory (allocated)      | 113        | 113 (vhbshk0sdb)            |                                 |
| HANAinstance memory (used)           | 85         | 85 (vhbshk0sdb)             |                                 |
| HANAshared memory                    | 23         | 23 (vhbshk0sdb)             |                                 |
| HANAheap memory (used)               | 52         | 52 (vhbshk0sdb)             | 10 (Pool/NameIdMapping/ RoDict) |
## HANA_Memory_TopConsumers_History / HANA_Memory_TopConsumers_TimeSlices
The output shows the areas in history with the highest memory requirements (column store and row store tables, heap, code, stack). The following columns contain the key values:
- SAMPLE_TIME: Timestamp
- KEY_FIGURE: Memory key figure
- VALUE_TOTAL: Total value of memory key figure
- DETAIL_&lt;n>=: Top &lt;n>= detail area related to memory key figure
- VALUE_&lt;n>=: Memory key figure value of top &lt;n>= detail area
- PCT_&lt;n>=: Fraction of overall value occupied by top &lt;n>= detail area (%)
VALUE_TOT
| SAMPLE_TIME     | KEY_FIGURE      |      AL | DETAIL_1                                                    |   VALUE_1 |   PCT_1 |
|-----------------|-----------------|---------|-------------------------------------------------------------|-----------|---------|
| 2014/04/22(TUE) | Space used (GB) |  408.55 | Pool/PersistenceManager/ PersistentSpace(0)/DefaultLPA/Page |     85.94 |      21 |
| 2014/04/21(MON) | Space used (GB) |  382.82 | Pool/PersistenceManager/ PersistentSpace(0)/DefaultLPA/Page |     90.44 |      24 |
| 2014/04/20(SUN) | Space used (GB) |  437.76 | Pool/PersistenceManager/ PersistentSpace(0)/DefaultLPA/Page |    126.2  |      29 |
| 2014/04/19(SAT) | Space used (GB) |  513.7  | Pool/PersistenceManager/ PersistentSpace(0)/DefaultLPA/Page |    133.29 |      26 |
| 2014/04/18(FRI) | Space used (GB) |  445.53 | Pool/PersistenceManager/ PersistentSpace(0)/DefaultLPA/Page |    112.35 |      25 |
| 2014/04/17(THU) | Space used (GB) |  363.3  | Pool/RowEngine/QueryExecution                               |     72.54 |      20 |
| 2014/04/16(WED) | Space used (GB) |  487.59 | Pool/RowEngine/QueryExecution                               |    104.39 |      21 |
| 2014/04/15(TUE) | Space used (GB) |  588.56 | Pool/PersistenceManager/ PersistentSpace(0)/DefaultLPA/Page |    168.21 |      29 |
| 2014/04/14(MON) | Space used (GB) | 3484.97 | Pool/JoinEvaluator/ JERequestedAttributes/Results           |   2795.2  |      80 |
| 2014/04/13(SUN) | Space used (GB) |  572.9  | Pool/PersistenceManager/ PersistentSpace(0)/DefaultLPA/Page |    175.6  |      31 |
| 2014/04/12(SAT) | Space used (GB) |  477.98 | Pool/PersistenceManager/ PersistentSpace(0)/DefaultLPA/Page |    111.76 |      23 |
| 2014/04/11(FRI) | Space used (GB) |  491.72 | Pool/PersistenceManager/ PersistentSpace(0)/DefaultLPA/Page |    119.74 |      24 |
| 2014/04/10(THU) | Space used (GB) |  463.47 | Pool/PersistenceManager/ PersistentSpace(0)/DefaultLPA/Page |    100.82 |      22 |
## HANA_Tables_LargestTables
This query provides an overview of current memory allocation by tables. The following list explains the columns displayed in the table:
- OWNER: Name of the table owner
- TABLE_NAME: Name of the table
- S: Table store ('R' for row store, 'C' for column store)
- HOST: Host name ('various' in case of partitions on multiple hosts)
- U: 'X' if at least one unique index exists for the table
- POS: Position of table in top list
- COLS: Number of table columns
- RECORDS: Number of table records
- SUm_DISK_GB: Total size on disk (in GB, table + indexes + LOB segments)
- SUm_MEm_GB: Total potential maximum size in memory (in GB, table + indexes + LOB segments)
| OWNER   | TABLE_NAME       | S   | HOST     | U   |   POS |   COLS |    RECORDS |   SUM_DIS K_GB |   SUM_ME M_GB |
|---------|------------------|-----|----------|-----|-------|--------|------------|----------------|---------------|
| SAPSR3  | /BIC/AZOCEUO0500 | C   | various  | X   |     1 |     16 |  877829360 |          63.9  |         76.15 |
| SAPSR3  | /BIC/AZOCZZO0400 | C   | various  | X   |     2 |     33 |  965035392 |          63.45 |         70.1  |
| SAPSR3  | RSMONMESS        | R   | erslha33 | X   |     3 |     19 |  170801504 |          27.92 |         54.21 |
| SAPSR3  | /BIC/AZFIGLO1300 | C   | various  | X   |     4 |     60 |  652633189 |          47.2  |         53.23 |
| SAPSR3  | /BIC/AZSCXXO4400 | C   | various  | X   |     5 |     26 | 1251448665 |          47.78 |         53.04 |
| SAPSR3  | /BIC/AZOCEUO0800 | C   | various  | X   |     6 |     17 |  911830438 |          37.86 |         52.42 |
| SAPSR3  | /BIC/AZOCZZO2000 | C   | various  | X   |     7 |     34 | 1200422292 |          46.5  |         50.08 |
| SAPSR3  | RSWR_DATA        | R   | erslha33 | X   |     8 |     10 |      20471 |          36.88 |         36.88 |
| SAPSR3  | RSRWBSTORE       | C   | erslha33 | X   |     9 |      5 |   14483956 |          36.18 |         36.2  |
| SAPSR3  | /BIC/AZMIEUO0200 | C   | various  | X   |    10 |     52 |  403915330 |          28.58 |         33.05 |
| SAPSR3  | /BIC/AZSCXXO2900 | C   | various  | X   |    11 |    275 |  183029330 |          30.82 |         29.26 |
| SAPSR3  | /BIC/AZSCXXO4700 | C   | various  | X   |    12 |     42 |  648103462 |          27.38 |         28.83 |
| SAPSR3  | /BIC/FZRREUC16B  | C   | erslha35 |     |    13 |    122 |  258261262 |          26.43 |         24.99 |
| SAPSR3  | /BIC/AZOCEUO9000 | C   | various  | X   |    14 |     16 |  251896248 |          20.53 |         23.71 |
| SAPSR3  | RSBMNODES        | R   | erslha33 | X   |    15 |     12 |  130344869 |          13.67 |         20.25 |
| SAPSR3  | /BIC/AZSCXXO1400 | C   | various  | X   |    16 |    279 |  164509638 |          18.49 |         19.82 |
| SAPSR3  | /BIC/AZOCEUO0300 | C   | various  | X   |    17 |     27 |  577787981 |          17.95 |         19.6  |
| OWNER   | TABLE_NAME       | S   | HOST     | U   |   POS |   COLS |    RECORDS |   SUM_DIS K_GB |   SUM_ME M_GB |
|---------|------------------|-----|----------|-----|-------|--------|------------|----------------|---------------|
| SAPSR3  | EDI40            | R   | erslha33 | X   |    18 |      7 |    5733625 |          18.26 |         18.4  |
| SAPSR3  | /BIC/FZOCZZC20   | C   | various  |     |    19 |     34 | 1427403108 |          18.97 |         17.8  |
| SAPSR3  | /BIC/AZSCXXO2600 | C   | various  | X   |    20 |    306 |   95251083 |          16.65 |         16.97 |
| SAPSR3  | /BIC/AZSCXXO0800 | C   | various  | X   |    21 |    266 |  120598787 |          18.76 |         15.62 |
| SAPSR3  | /BIC/AZSPXXO0200 | C   | various  | X   |    22 |     48 |  270975902 |          12.63 |         15.3  |
| SAPSR3  | /BIC/AZOCZZO5000 | C   | various  | X   |    23 |     33 |  215732874 |          14.16 |         14.92 |
| SAPSR3  | /BIC/AZSPXXO0300 | C   | various  | X   |    24 |     56 |  275036362 |          13.09 |         14.77 |
| SAPSR3  | /BIC/AZOCEUO0600 | C   | various  | X   |    25 |     16 |  663581081 |          12.29 |         14.34 |
| SAPSR3  | /BIC/AZOCZZO0700 | C   | various  | X   |    26 |     41 |  350819182 |          15    |         14.32 |
| SAPSR3  | /BIC/FZRREUC16D  | C   | erslha40 |     |    27 |    122 |  146620284 |          15.39 |         14.06 |
| SAPSR3  | /BIC/AZMDEUO0800 | C   | various  | X   |    28 |    246 |  151145647 |          15.39 |         14    |
| SAPSR3  | /BIC/AZMIEUO1300 | C   | various  | X   |    29 |     16 |  406548712 |          11.52 |         13.35 |
| SAPSR3  | /BIC/AZRREUO0100 | C   | various  | X   |    30 |    167 |  202422848 |          15.36 |         13.24 |
## HANA_Memory_SharedMemory
This query shows currently used and allocated shared memory per host and service. The following list explains the columns displayed in the table:
- HOST: Host name
- PORT: Port name
- SERVICE_NAME: Service name
- SHm_ALLOC_GB: Allocated shared memory (GB)
- SHm_USED_GB: Used shared memory (GB)
- SHm_USED_PCT: Percentage of allocated memory used
| HOST     |   PORT | SERVICE_NAME     |   SHM_ALLOC_GB |   SHM_USED_GB |   SHM_USED_PCT |
|----------|--------|------------------|----------------|---------------|----------------|
| MyHost01 |  31001 | nameserver       |           0.13 |          0.02 |          18.64 |
| MyHost01 |  31002 | preprocessor     |           0    |          0    |           0    |
| MyHost01 |  31003 | indexserver      |          24.5  |         11.21 |          45.76 |
| MyHost01 |  31005 | statisticsserver |           0.14 |          0.03 |          22.98 |
| HOST     |   PORT | SERVICE_NAME   |   SHM_ALLOC_GB |   SHM_USED_GB |   SHM_USED_PCT |
|----------|--------|----------------|----------------|---------------|----------------|
| MyHost01 |  31006 | webdispatcher  |           0    |          0    |           0    |
| MyHost01 |  31007 | xsengine       |           0.14 |          0.03 |          22.96 |
| MyHost01 |  31010 | compileserver  |           0    |          0    |           0    |
## Related Information
SAP Note 1969700
Memory Analysis Using the SQL Statement Collection for Analysis and Health Checks [page 8]