HANA SQL statements

Some useful HANA SQL statements.


/* actual execution date */
SELECT substr (CURRENT_TIMESTAMP, 1, 16) as "execution date" FROM SYS.M_DATABASE;


/* HANA version */
SELECT version as "HANA version" FROM SYS.M_DATABASE;



/* start time of HANA */
SELECT substr (start_time , 1, 16) as "Start time of HANA" FROM SYS.M_DATABASE;



/* start time of indexserver */
SELECT substr (start_time , 1, 16 ) as "Start time of Indexserver" FROM "SYS"."M_SERVICE_STATISTICS" where port = '302055';



/*  physical memory */
SELECT round (physical_memory /1024/1024/1024 , 0) as "physical memory of server" FROM "SYS"."M_SERVICE_STATISTICS" where service_name = 'indexserver';



/* disk size of schema HANADB */
SELECT round (sum (disk_size) / 1024 / 1024 / 1024 , 2 ) as "Disk size schema HANADB in GB" FROM "SYS"."M_TABLE_PERSISTENCE_STATISTICS" where schema_name = 'HANADBXY';



/* disk size of all generated FSI tables */
SELECT round (sum (disk_size) / 1024 / 1024 / 1024 , 2 ) as "Disk size FSI tables in GB" FROM "SYS"."M_TABLE_PERSISTENCE_STATISTICS" where schema_name = 'HANADBXY' and table_name like '/1FS/%';



/* size of lob files */
SELECT round (sum (physical_size) / 1024 / 1024 / 1024 , 2 )as "size lobfiles in GB" FROM "SYS"."M_TABLE_LOB_FILES";



/* memory consumption of indexserver */
SELECT round (value / 1024 / 1024 /1024 , 2) as "memory size indexserver in GB" FROM "SYS"."M_MEMORY" where port = '302055' and name = 'TOTAL_MEMORY_SIZE_IN_USE';



/* memory consumption of all column store tables */
SELECT round ( sum (estimated_max_memory_size_in_total) / 1024 / 1024 / 1024 , 2 ) as "Memory CS tables in GB" FROM "SYS"."M_CS_TABLES";



/* memory consumption of all row store tables */
SELECT round (sum (allocated_size) / 1024 / 1024 / 1024 , 2) as "Memory RS table in GB" FROM "SYS"."M_RS_MEMORY" where category = 'TABLE';



/* summe primary, secondary indcies and concat attirbutes */
select round (sum (memory_size_in_total) / 1024 / 1024 / 1024 , 2 ) as "Sum primary, secondary indices and concat in GB" from sys.m_cs_all_columns where schema_name = 'HANADBXY' and INTERNAL_ATTRIBUTE_TYPE = 'CONCAT_ATTRIBUTE'