We use Administration console to monitor the SAP HANA system.
Go to Landscape tab page to check if all the services that belong to your database are running.
(preprocessor, name server, index server for each host and one statistics server)
The Services tab page contains information about the status of database services.
Running services are shown with green icon.
Also, information about resource usage and possible bottlenecks is displayed.
Using the context menu, you can restart services.
When you use Stop or Kill command, the services is stopped and automatically restarted again.
As all the services are started automatically when they are stopped, there is no need to start single services manually.
The configuration tab page displays the list of all hosts belonging to specific HANA database as well as current services.
It is also possible to reconfigure the services.
Monitoring view of High availability and System tolerance
The Overview tab under General Information shows status of secondary system replication (with SPS05 and higher versions)
This information is only available and applicable if you are operating a secondary instance of your database (for example, in a high availability scenario). If this is the case, then content from the primary or productive instance of your database is replicated to the secondary instance.
The status of the Secondary System Replication is given by the following schema (select column REPLICATION_STATUS of the view M_SERVICE_REPLICATION):
green – REPLICATION_STATUS ACTIVE for all rows
yellow – REPLICATION_STATUS not ACTIVE, but no ERROR
rot – REPLICATION_STATUS at least one ERROR
User Interface for Table Redistribution
In the administration editor, choose Landscape -> Configuration -> Redistribution
These options are available for following scenarios:
- You are planning to remove a host from your system
- You have added a new host to your system
- You want to optimize current table distribution
- You want to optimize table partitioning
Disk Full Handling
If the storage on which database files are stored becomes full, the database gets suspended and an event is triggered.
This event is displayed on the overview tab page of the administration editor in addition to the bar view of disk usage.
Additional storage space has to be provided or unused files should be deleted and the event has to be marked as handled before the database can resume its work.
Marking the event can be done using the link on the overviw tab.
Typically SAP HANA studio collects data to be displayed using SQL statements.
When the database is not yet started, no SQL connection is available.
During that time (for instance during database startup), SAP HANA studio now collects information about the SAP HANA database using the sapstartsrv connection.
This enables the user to analyze problems which might occur during the start/stop phase of the SAP HANA database and to read diagnosis files even when the database is stopped. This diagnosis mode is opened automatically, when the database is started/stopped using SAP HANA studio.
On the Performance tab page, information about running threads and performance KPIs is displayed. The Threads tab page displays a list of all threads and their status – filters can be applied. The Load tab page provides a graphical view of specific performance KPIs (like CPU usage, memory consumption, etc.).
Fine Grained Monitoring
The Load sub tab under Performance tab can provide data on 1 sec/10 sec level (X scale)
There are different KPIs for analyzing the parameter of host and index server. The following KPI are available for host:
CPU used by index server
Memory used for all processes (physical and swap)
Disk used %
Disk size (size of host hard disk)
Following KPIs are available for index server:
Heap memory used
size and limit of heap memory used
size and limit of index server
Integrated Process monitor
DSO activation is a BW operation. It is integrated in the Job Progress monitor as the operations are potentially long running.
The session tab under Performance allows you to monitor all sessions in current landscape such as identifying of inactive sessions, investigating of blocking situations and viewing memory consumption by session.
You can cancel a session by right-clicking the session and choosing Cancel Session.
The Threads display on Performance tab can visualize blocking situations. A blocked thread is marked with a warning icon and the tooltip provides detailed information.
As for most views in SAP HANA studio one can add further columns to the view –e.g. the transaction ID, which might be interesting in case of a blocking situation.
It is possible that currently no thread is active in the session causing the blocking situation. Therefore you might not see the blocking transaction ID in the Thread view and need to check all open transactions which are displayed in System Information -> Open Transactions
You can cancel operations by selecting from context menu in the Threads tab under Performance.
In the Thread view it is now possible to end the operation of a specific thread. As several threads run together in one session and in one transaction, the operations of all other threads belonging to that session/transaction will be ended as well.
The SQL Plan Cache and Expensive Statements Trace tabs both provide information on long running SQL statements. SQL Plan Cache shows which frequently run statements are expensive.
The Volumes tab page provides more detailed information about the storage usage
on your server. The display can be switched between ‚grouped by service‘ and ‚grouped by storage‘.
Grouped by service: For each service the size of the data and log area is displayed as well as the directory in which they are located.
Grouped by storage: The storage path, total storage size and used storage size are displayed
as well as the size occupied on the corresponding storage by data, log or trace.
In below window you can see the details of Log storage:
Formatting: The log segment is being formatted and not yet used
Preallocated: The log segment has been preallocated but never used
Writing: The log segment is currently being written
Closed: The log segment is closed, not backed up and is still required for restart.
Truncated – The log segment is not required for restart, but has not been backed up.
BackedUp – The log segment has been backed up, but is still required for restart.
Free – The log segment has been backed up, it is not required for restart and can be reused.
In case you are using log_mode = legacy, you must execute a data backup in order to release log files for reuse (log segments will stay in the state Closed or BackedUp until the backup is done).
After a data backup, empty log files can be freed explicitly after backup using the sql_reclaim_log SQL command. This command can also be used in other log modes after a savepoint to release empty log files freed by the savepoint.
You can also see the values since the last reset in the dependent view M_LOG_SEGMENTS _RESET
To reset the values, use the SQL command: ALTER SYSTEM RESET MONITORING VIEW SYS.M_LOG_SEGMENTS_RESET or the management console command: monitor reset -n M_LOG_SEGMENTS_RESET
This monitor view is based on the following monitors: M_LOG_SEGMENTS_
Volume display – volume I/O statistics
Distinguish between read and write times
System Information contains list of useful SQL statements.
e.g. to get information about expensive SQL statements, memory usage, SQL locks, merge statistics.
List of Log and Trace Files
In case of problems with the database, log and trace files can be checked for errors.
These diagnosis files are available in the studio on the tab page Diagnosis Files of the Administration editor. To display a diagnosis file, choose Open in the context menu of the list or just double-click the entry of the respective log file.
Database traces including a SQL trace and the performance trace can be activated by choosing Configure Trace… When no SQL connection is available, operating system user <sid>adm
is required to access the log files.
In the Diagnosis Files view it is now also possible to show the <n> first lines of a file. It is still possible to display the last <n> lines or to display the entire file.
The number of lines is configurable, the max. limit is 100.000 lines. For large files (more than 100.000 lines) showing the entire file is not possible, instead a download option is provided.
You can activate SQL trace and specify a file name for the trace file. You can adapt the flush interval if required. During tracing, the messages of a connection are buffered.
You can view the details of the various database operations by choosing Visualize Plan in the context menu of SQL Plan Cache in Performance tab-
A detailed graphic is displayed. This graphic is a very powerful tool for studying performance of queries on SAP HANA databases. You can explore the graphic further, for example, you can expand, collapse, or rearrange nodes on the screen.
You can also save the graphic as an image or XML file, for example, so you can submit it as part of a support query.