HANA Monitoring Handy SQL's

[Former Member]

Monitoring Memory Usage

Used Memory

The total amount of memory in use by SAP HANA is referred to as its Used Memory. This is the most precise indicator of the amount of memory that the SAP HANA database uses at any time

When used:  To understand the current used memory in HANA when HANA alerts shows usage greater than licensed memory. Understanding memory usage by components will help in troubleshooting and perform necessary memory clean up actions.

Display the current size of the Used Memory; you can use the following SQL statement

SELECT

        ROUND(SUM(TOTAL_MEMORY_USED_SIZE/1024/1024/1024),

        2) AS "Used Memory GB"

FROM SYS.M_SERVICE_MEMORY;

Display current used memory for Column Store Tables

SELECT

        ROUND(SUM(MEMORY_SIZE_IN_TOTAL)/1024/1024) AS "Column Tables MB Used"

FROM M_CS_TABLES;

Display current memory used breakdown by Schema

SELECT

        SCHEMA_NAME AS "Schema",

        ROUND(SUM(MEMORY_SIZE_IN_TOTAL) /1024/1024) AS "MB Used"

FROM M_CS_TABLES

GROUP BY SCHEMA_NAME

ORDER BY "MB Used" DESC;

Display memory usage by components

SELECT

        host,

        component,

        sum(used_memory_size) used_mem_size

FROM PUBLIC.M_SERVICE_COMPONENT_MEMORY

group by host,

        component

ORDER BY sum(used_memory_size) desc;

Database resident

Resident memory is the physical memory actually in operational use by a process.

SELECT SUM(PHYSICAL_MEMORY_SIZE/1024/1024/1024) "Database Resident" FROM M_SERVICE_MEMORY;

Find the total resident on each node and physical memory size

SELECT

        HOST,

        ROUND(USED_PHYSICAL_MEMORY/1024/1024/1024,

        2) AS "Resident GB",

        ROUND((USED_PHYSICAL_MEMORY + FREE_PHYSICAL_MEMORY)/1024/1024/1024,

        2) AS "Physical Memory GB"

FROM PUBLIC.M_HOST_RESOURCE_UTILIZATION;

Find total Resident

SELECT

        T1.HOST,

        (T1.USED_PHYSICAL_MEMORY + T2.SHARED_MEMORY_ALLOCATED_SIZE)/1024/1024/1024 "Total Resident"

FROM M_HOST_RESOURCE_UTILIZATION AS T1 JOIN (SELECT

        M_SERVICE_MEMORY.HOST,

        SUM(M_SERVICE_MEMORY.SHARED_MEMORY_ALLOCATED_SIZE) AS SHARED_MEMORY_ALLOCATED_SIZE

       FROM SYS.M_SERVICE_MEMORY

       GROUP BY M_SERVICE_MEMORY.HOST) AS T2 ON T2.HOST = T1.HOST;

Maximum peak used memory

SAP HANA database tracks the highest-ever value of Used Memory reached since the database was started. In fact, this is probably the single most significant memory indicator that you should monitor as an overall indicator of the total amount of memory required to operate the SAP HANA database over a long period of time.

SELECT

        ROUND(SUM("M")/1024/1024/1024,

       2) as "Max Peak Used Memory GB"

FROM (SELECT

        SUM(CODE_SIZE+SHARED_MEMORY_ALLOCATED_SIZE) AS "M"

       FROM SYS.M_SERVICE_MEMORY

       UNION SELECT

        SUM(INCLUSIVE_PEAK_ALLOCATION_SIZE) AS "M"

       FROM M_HEAP_MEMORY

       WHERE DEPTH = 0);

Peak used memory

SAP HANA maintains a special Used Memory indicator, called the Peak Used Memory. This is useful to keep track of the peak value (the maximum, or “high water mark”) of Used Memory over time. Here is how to read the Peak Used Memory:

SELECT

        ROUND(SUM("M")/1024/1024/1024,

       2) as "Peak Used Memory GB"

FROM (SELECT

        SUM(CODE_SIZE+SHARED_MEMORY_ALLOCATED_SIZE) AS "M"

       FROM SYS.M_SERVICE_MEMORY

       UNION SELECT

        SUM(INCLUSIVE_PEAK_ALLOCATION_SIZE) AS "M"

       FROM M_HEAP_MEMORY_RESET  

       WHERE DEPTH = 0);

Memory usage in server

free –g | awk '/Mem:/ {print "Physical Memory: " $2 " GB."} /cache:/ {print "Resident: " $3 " GB."}'

Memory Cleanup: Forcing Garbage collector from Server

Login to Hana server -> open HDBAdmin.sh and navigate to Services -> Console

Select the node where the garbage collection to be triggered. Execute the below command

mm gc –f

The garbage collector will be triggered, and free up the memory. This will not unload the tables.

Resetting Monitoring Views

When Used: when testing a report or need to monitor the peak of memory usage by a SQL, monitor IO, memory objects throughput and statistics about garbage collection jobs. The below will allow to reset these statistics.

Memory allocator statistics

M_HEAP_MEMORY view contains information about memory consumption of various components in the system.

ALTER SYSTEM RESET MONITORING VIEW SYS.M_HEAP_MEMORY_RESET;

M_CONTEXT_MEMORY view contains information about memory consumption grouped by connections and/or users.

ALTER SYSTEM RESET MONITORING VIEW SYS.M_CONTEXT_MEMORY_RESET;

File access statistics

M_VOLUME_IO_STATISTICS_RESET view shows information about basic I/O operations on I/O subsystems (that is, paths).

ALTER SYSTEM RESET MONITORING VIEW SYS.M_VOLUME_IO_STATISTICS_RESET;

Memory object statistics

M_MEMORY_OBJECTS_RESET view provides information about the number and size of resources currently in the resource container and about the throughput of the resource container.

ALTER SYSTEM RESET MONITORING VIEW SYS.M_MEMORY_OBJECTS_RESET;

Garbage collection/history manager statistics

M_GARBAGE_COLLECTION_STATISTICS_RESET view shows various statistics about garbage collection jobs.

ALTERSYSTEMRESET MONITORING VIEW SYS.M_GARBAGE_COLLECTION_STATISTICS_RESET;

Schema/Tables Monitoring

Find Tables loaded into memory & delta records

When used: To see what tables are loaded to memory at any given time; If a report is running slow see if the table is loaded to memory though the tables goes on lazy loading it is a best practice to have the table loaded to memory.

SELECT

        LOADED,

       TABLE_NAME,

        RECORD_COUNT,

        RAW_RECORD_COUNT_IN_DELTA ,

        MEMORY_SIZE_IN_TOTAL,

        MEMORY_SIZE_IN_MAIN,

        MEMORY_SIZE_IN_DELTA

from M_CS_TABLES

where schema_name = 'SCHEMA'

order by RAW_RECORD_COUNT_IN_DELTA Desc

To drill down further and see what columns is not loaded /loaded please use below

Select top 100 LOADED,

HOST,

TABLE_NAME,

COLUMN_NAME,

MEMORY_SIZE_IN_TOTAL

from PUBLIC.M_CS_COLUMNS

WHERE SCHEMA_NAME = 'SCHEMA'

AND LOADED <> 'TRUE'

MERGE DELTA

See if there is delta to be merged. RAW_RECORD_COUNT_IN_DELTA will provide the delta count.

SELECT

        LOADED,

       TABLE_NAME,

        RECORD_COUNT,

        RAW_RECORD_COUNT_IN_DELTA ,

        MEMORY_SIZE_IN_TOTAL,

        MEMORY_SIZE_IN_MAIN,

        MEMORY_SIZE_IN_DELTA

from M_CS_TABLES

where schema_name = 'SCHEMA'

order by RAW_RECORD_COUNT_IN_DELTA Desc

Forcing delta Merge

UPDATE SCHEMA.COLUMN_STATISTICS MERGE DELTA INDEX;

Smart merge

UPDATE <table_name> MERGE DELTA INDEX WITH PARAMETERS ('SMART_MERGE'='ON')

Find Auto Merge On

select TABLE_NAME, AUTO_MERGE_ON from SYS.TABLES

Find Compression

When used: To see the uncompressed size and the compression ratio in HANA for the loaded tables.

SELECT top 100 "SCHEMA_NAME",

sum("DISTINCT_COUNT") RECORD_COUNT,

sum("MEMORY_SIZE_IN_TOTAL") COMPRESSED_SIZE,

sum("UNCOMPRESSED_SIZE") UNCOMPRESSED_SIZE,

(sum("UNCOMPRESSED_SIZE")/sum("MEMORY_SIZE_IN_TOTAL")) as COMPRESSION_RATIO,

100*(sum("UNCOMPRESSED_SIZE")/sum("MEMORY_SIZE_IN_TOTAL")) as COMPRESSION_PERCENTAGE

FROM "SYS"."M_CS_ALL_COLUMNS"

GROUP BY "SCHEMA_NAME"

having sum("UNCOMPRESSED_SIZE") >0

ORDER BY UNCOMPRESSED_SIZE DESC ;

To go on a detail level and identify what type of compression is applied on each column and the ratio please use below

select

        COLUMN_NAME,

        LOADED,

        COMPRESSION_TYPE,

        MEMORY_SIZE_IN_TOTAL,

        UNCOMPRESSED_SIZE,

        COMPRESSION_RATIO_IN_PERCENTAGE as COMPRESSION_FACTOR

from M_CS_COLUMNS

where schema_name = 'SCHEMA'

Forcing compression on a table

update SCHEMA.COLUMN_STATISTICS  with parameters ('OPTIMIZE_COMPRESSION' = 'TRUE');

Find which node is active

to find which node your session is connected to

SELECT

        HOST,

        PORT,

        CONNECTION_ID

FROM M_CONNECTIONS

WHERE OWN = 'TRUE';

Expensive Statements

Ensure the expensive statement trace is ON

When used: To troubleshoot a report failure or a sql failure and understand why it failed. Also to monitor the expensive sqls executed in HANA. Identify the ways for performance optimization.

Find expensive statements for errors

SELECT

       "HOST",

        "PORT",

        "CONNECTION_ID",

        "TRANSACTION_ID",

        "STATEMENT_ID",

        "DB_USER",

        "APP_USER",

        "START_TIME",

        "DURATION_MICROSEC",

        "OBJECT_NAME",

        "OPERATION",

        "RECORDS",

        "STATEMENT_STRING",

        "PARAMETERS",

        "ERROR_CODE",

        "ERROR_TEXT",

        "LOCK_WAIT_COUNT",

        "LOCK_WAIT_DURATION",

        "ALLOC_MEM_SIZE_ROWSTORE",

        "ALLOC_MEM_SIZE_COLSTORE",

        "MEMORY_SIZE",

        "REUSED_MEMORY_SIZE",

        "CPU_TIME"

FROM  "PUBLIC"."M_EXPENSIVE_STATEMENTS"

WHERE ERROR_CODE > 0

ORDER BY START_TIME DESC;

Finding expensive statements executed by User

SELECT

       "HOST",

        "PORT",

        "CONNECTION_ID",

        "TRANSACTION_ID",

        "STATEMENT_ID",

        "DB_USER",

        "APP_USER",

        "START_TIME",

        "DURATION_MICROSEC",

        "OBJECT_NAME",

        "OPERATION",

        "RECORDS",

        "STATEMENT_STRING",

        "PARAMETERS",

        "ERROR_CODE",

        "ERROR_TEXT",

        "LOCK_WAIT_COUNT",

        "LOCK_WAIT_DURATION",

        "ALLOC_MEM_SIZE_ROWSTORE",

        "ALLOC_MEM_SIZE_COLSTORE",

        "MEMORY_SIZE",

        "REUSED_MEMORY_SIZE",

        "CPU_TIME"

FROM  "PUBLIC"."M_EXPENSIVE_STATEMENTS"

WHERE STATEMENT_STRING LIKE '%NAIRV%'

CONNECTIONS

Find running connections

SELECT "HOST", "PORT", "CONNECTION_ID", "TRANSACTION_ID", "START_TIME", "IDLE_TIME", "CONNECTION_STATUS", "CLIENT_HOST", "CLIENT_IP", "CLIENT_PID", "USER_NAME", "CONNECTION_TYPE", "OWN", "IS_HISTORY_SAVED", "MEMORY_SIZE_PER_CONNECTION", "AUTO_COMMIT", "LAST_ACTION", "CURRENT_STATEMENT_ID", "CURRENT_OPERATOR_NAME", "FETCHED_RECORD_COUNT", "AFFECTED_RECORD_COUNT", "SENT_MESSAGE_SIZE", "SENT_MESSAGE_COUNT", "RECEIVED_MESSAGE_SIZE", "RECEIVED_MESSAGE_COUNT", "CREATOR_THREAD_ID", "CREATED_BY", "IS_ENCRYPTED", "END_TIME", "PARENT_CONNECTION_ID", "CLIENT_DISTRIBUTION_MODE", "LOGICAL_CONNECTION_ID", "CURRENT_SCHEMA_NAME", "CURRENT_THREAD_ID"

FROM "PUBLIC"."M_CONNECTIONS"

WHERE  CONNECTION_STATUS = 'RUNNING'

ORDER BY "START_TIME" DESC

Resetting Connections

Find the connection

SELECT CONNECTION_ID, IDLE_TIME

FROM M_CONNECTIONS

WHERE CONNECTION_STATUS = 'IDLE' AND CONNECTION_TYPE = 'Remote'

  ORDER BY IDLE_TIME DESC

Disconnect Session

ALTER SYSTEM DISCONNECT SESSION '203927';

ALTER SYSTEM CANCEL SESSION '237048';

Find owners of objects

SELECT * FROM "PUBLIC"."OWNERSHIP" WHERE SCHEMA='SCHEMA'

Find Granted Privileges for Users

SELECT * FROM PUBLIC.GRANTED_PRIVILEGES

WHERE GRANTEE_TYPE = 'USER' AND GRANTOR = 'NAIRV'

PASSWORD Policy

Disable password policy on a user, this is used when you don’t want the policy to be applied on a user. This will set to lifetime.

ALTER USER USER DISABLE PASSWORD LIFETIME

Audit Policy

Configure

Enable global auditing

alter system alter configuration ('global.ini',

       'SYSTEM')

set ('auditingconfiguration',

       'global_auditing_state' ) = 'true' with reconfigure;

Set the auditing file type

alter system alter configuration ('global.ini','SYSTEM')

set ('auditingconfiguration'

,'default_audit_trail_type' ) = 'CSVTEXTFILE'

with reconfigure;

aduit target path

alter system alter configuration ('global.ini','SYSTEM')

set ('auditingconfiguration'

,'default_audit_trail_path' ) = 'path'

with reconfigure;

Find the policy implemented

Select * from public.audit_policies;

To enable/ disable global auditing

-- change the configuration for setting the audit

alter system alter configuration ('global.ini',

       'SYSTEM')

set ('auditingconfiguration',

       'global_auditing_state' ) = 'true' with reconfigure;

Add audit policy

CREATE AUDIT POLICY Audit_EDW_DM_DROPTABLE_H00 AUDITING SUCCESSFUL DROP TABLE LEVEL CRITICAL;

Policy enable/disable

ALTER AUDIT POLICY Audit_EDW_DM_DROPTABLE_H00 ENABLE;

CHECK AFL PAL FUNCTIONS ARE INSTALLED

SELECT * FROM SYS.AFL_FUNCTIONS WHERE PACKAGE_NAME='PAL';