Continuous, proactive monitoring of the status of production systems, its services, and the consumption of system resources is crucial to detecting problems early. This allows you to prevent or minimize service disruptions. SAP HANA is no different from any other system in this regard. However, the collection and evaluation of statistical data at defined periodic intervals is built into the SAP HANA database. This embedded statistics server or, more recently, the statistics service of the index server, is part of the internal monitoring infrastructure of the SAP HANA database. The statistical data that is collected via internal monitoring is stored in tables within the _SYS_STATISTICS schema. The data is periodically evaluated to alert users of critical situations, such as when a disk is almost full, memory usage has exceeded a specific threshold, or a server has stopped working. This data is stored for a configurable time period which is usually set to 30 days by default. This information can be used to facilitate performance and stability monitoring of not only the SAP HANA database, but also the SAP HANA application. The statistical information collected by the SAP HANA database may be monitored using a variety of tools such as SAP Solution Manager, SAP HANA Studio, and the Database Administration (DBA) cockpit in SAP NetWeaver. In addition, more generic Operation System monitoring tools such as Topaz can be used to monitor the system from an external perspective. However, all these tools have limited insight into the memory manager within SAP HANA. The focus of this article is on how to use Design Studio to develop a custom dashboard application to report on trends and anomalies in the data collected in the _SYS_STATISTICS schema tables. The first step in this process is to prepare the data for dashboard consumption by defining calculation views in SAP HANA Studio. However, before the data can be prepared, it must be understood, so let’s review some of the key SAP HANA performance metrics that are covered in our dashboard.

SAP HANA Key Performance Indicators (KPIs)

The most important KPIs can be tracked using the System Monitor (Figure 1). This is the simplest way to monitor the operational status of multiple systems using a single click in SAP HANA Studio. From here you can see KPIs such as:

1. Memory usage
2. Alerts
3. CPU usage

Due to the prevalence of disk usage monitoring, we have excluded the disk usage metric from the dashboard. Instead, we focus on the three metrics listed above. For a more comprehensive view of system performance, we recommend adding the following KPIs to your routine monitoring:

• Input/Output (I/O) throughput
• Response time

Each system can also be monitored individually in the Overview tab of the administration editor (Figure 2). The call-outs in this illustration identify the three main metrics available in the system monitor: Database Used Memory (1), Current Alerts and Messages (2), and CPU Usage (3). For more detailed information on memory usage, access the Memory Overview by right-clicking any SAP HANA system within SAP HANA Studio (Figure 3). This view requires Monitoring privileges and SAP HANA Support Package Stack 07 (SPS 07), but provides memory usage information for each host. Unfortunately, this view has two main drawbacks:

1. It is not possible to see an overview of memory consumption across hosts.
2. It does not identify the working space memory—that is, the memory used for temporary computations.

For the most flexibility in system monitoring capabilities, migrate to the new statistics server implementation which is available as of SAP HANA SPS 07 (refer to https://www.saphana.com/docs/DOC-4403 for enabling instructions). This new implementation replaces the separate statistics server with internal SQL Script procedure calls from the index server. As a result, performance is improved and memory consumption is reduced. Now let’s take a detailed look at each of these three KPIs that are included in your Design Studio dashboard.

Memory Usage

Given that SAP HANA is an in-memory database, it is crucial to attentively monitor and manage memory consumption. Therefore, the SAP HANA database pre-assigns and manages its own memory pool and offers an assortment of memory usage gauges to facilitate monitoring. Memory usage of all systems can be viewed within the System Monitor. The memory usage of an individual system or service can be accessed within the Overview and Landscape tabs, respectively. Memory in SAP HANA is consumed for numerous purposes:

• The operating systems and support files
• Proprietary code and stack of program files
• System-defined column and row store tables
• User-defined column and row store tables
• Working space where computations occur, temporary results are stored, and shared-user memory consumption occurs

SAP HANA audits memory from the perspective of the host. The most pertinent aspects include:

• Physical memory (Figure 4) – The maximum amount of physical (system) memory that is available on the host
• Allocated memory – The memory pool reserved by SAP HANA from the operating system
• Used memory – The amount of memory from this pool that is actually used by the SAP HANA database

The physical memory on most SAP HANA hosts ranges from 256GB to 2TB. This is implemented to run the Linux OS, SAP HANA, and any additional program that runs on the host. SQL statements may be used to obtain or edit memory information. There is a set of predefined SQL statements provided by SAP that are available for use. For column tables, SAP HANA sometimes unloads columns that are infrequently used out of memory to free up space. Used memory serves two main purposes (Figure 5):

• Program code and stack
• Working space and data tables (heap and shared memory)

The program code area houses the SAP HANA database while it is active. Various components of SAP HANA can share a common program code. The stack is required to complete actual computations. From a business perspective, the most important measure of memory usage is the consumption of user-column and row-store tables. This is the component over which a company wields the greatest control. Secondary only to user table usage is the consumption of free memory for temporary computations, over which the company also has control. Temporary usage can be controlled in many ways, most notably by capping expensive statements (this prevents index server crashes and preserves the stability of the system) and by limiting the use of joins in calculation views on large tables. Figure 6 shows the results of a calculation view created in a 7.5TB SAP HANA scale-out system. This view breaks down the total memory used by the SAP HANA database by code and stacks, system tables, row-store tables, column store tables and temporary computations. This memory is subject to the allocation limit on each node in the system. The calculation view can also show memory consumption by host in the 7.5TB scale-out SAP HANA system (Figure 7). This system has 15 nodes which are 512GB each. Note the results here only show the memory allocated to the index server port on each node. Displaying memory usage in this way facilitates observation of over-allocation for temporary computations (notice the temporary computations in Figure 6 exceeds 2X the memory used by tables) and memory leaks (notice the high usage by system tables on the first host in Figure 7). Once observed, steps should be taken to identify and remediate the root cause before system stability is compromised.

Alerts

As previously covered, the statistics server is continuously collecting and evaluating information about status, performance, and resource usage from all components of the SAP HANA database. In addition, it performs routine checks on the collected data and issues alerts when configurable threshold values are exceeded. The priority of the alert indicates the severity of the problem and depends on the nature of the check and configured threshold values. For example, if 90 percent of available disk space is used, then a low priority alert is issued; if 98 percent is used, then a high priority alert is issued. There are five possible alert severity levels:

1. Informational
2. Low
3. Medium
4. High
5. Very high

Custom alerts can be created to assist in monitoring system performance in the administration editor under the Alerts tab. We recommend creating an administrative email account to isolate system monitoring information. Alert notifications can be sent to recipients based on alert type and severity. The periodicity of each alert can also be scheduled. By default, alerts are executed every six hours. Current alerts are summarized on the Overview tab of the administration editor and displayed in detail on the Alerts tab (Figure 8). The alerts are stored indefinitely in the statistics table schema, so there is no purging of history, making this a good source of data for trending. Figure 9 shows the last 30 days of distinct alerts by date. The alerts are color-coded by severity. What is most interesting is that the severity of alerts triggered over the last five days suggests that a recent update to SAP HANA revision 74.04 may have resolved the high priority alerts that had been triggered on 24 of the previous 25 days.

Another view of the alert statistics (Figure 10) shows the distribution of severity ratings over all time, which indicates that informational alerts represent almost half of all alerts triggered.

CPU Usage

CPU usage of all systems can be reviewed within the System Monitor. The CPU usage of an individual system can be found on the Overview tab (Figure 2), and of individual services on the Landscape tab (Figure 11). CPU usage is displayed as a percentage of the maximum available and is color-coded in red, yellow, or green according to the thresholds that are set. In most cases, the CPU usage is lower than 10 percent, so idle time is not reflected in the graph in order to make the usage statistics more legible. Figure 12 shows a graph illustrating the CPU usage by the system, users, and Wait Input/Output (WIO) as a percentage of the total CPU time.

I/O Throughput

Although SAP HANA is an in-memory database, I/O still maintains a critical role as it pertains to the performance of the system. From the perspective of the end user, an application or the system as a whole may experience slower speeds or become unresponsive if I/O performance issues exist. In certain cases, data is read from or written to disk; one example is the transaction commit. This is typically accomplished asynchronously but may also be done through synchronous I/O in particular circumstances. Important data structures may be locked even during asynchronous I/O. I/O throughput can be viewed by accessing the administration editor in SAP HANA Studio and then navigating to the Volumes tab. The Details for Data Volumes of Service section displays specifics about the selected index server, statistics server, and XS engine. There are additional name server details that can be displayed, including the following options:

• Volume I/O Statistics – Shows general file I/O statistics for specific paths such as storage, volume, and service. Aggregated I/O statistics are also displayed. The data is unrelated to the I/O buffer size such as the read/write requests, data volume or throughput, and the total I/O time and speed in MBs.
• Volume I/O Performance Statistics – Shares specific file I/O statistics for certain paths such as storage, volume, or service. I/O statistics are related to the I/O buffer size; an example is the number of read/write requests, processing durations, and bottlenecks within the system. These figures can be of interest when analyzing database issues.

Monitoring I/O throughput can help identify hardware problems with the SAP HANA application, such as bad network switches or cables. Figure 13 shows an example of an I/O throughput by server date. Notice the dip in write I/O throughput. I/O throughput is captured for data, logs, data backups, and log backups. Figure 14 shows the throughput for logs written-to and read-from disk volumes in the SAP HANA application.

Response Time

SAP HANA acceleration on transactions and reports is heavily reliant on the acceleration factor, which mainly depends on the percentage of database read time to overall response time (Figure 15). If the SAP HANA database reduces the database read time to a negligible amount, the acceleration factor would be 2 for transactions with a read time of 50 percent of the overall response time. Higher acceleration can be achieved for transactions and reports with a higher ratio of database read time to overall response time. The EarlyWatch Alert report can be accessed to identify reports and transactions with a high contribution of database read time to the overall response time and a significant total of database read time. As a result, it is possible to identify potential optimization candidates for SAP HANA Accelerators. The database response time can be measured directly within the SAP HANA database, whether or not there is any SAP NetWeaver application installed on it. The statistics server records the last execution time for all SQL plans while also keeping a running total of total execution time and count of executions. The statistics enable the calculation of an average SQL plan execution time, which is loosely comparable to database response time. Figure 16 shows an average execution time by host and Figure 17 shows the average execution time according to the last execution date. Note it is also possible to view average execution time by user. Note the highest average execution time in Figure 16 is on the first node, which also happens to be the master node. This illustrates the dependency on the master node in scale-out systems and suggests the master node should not be overloaded. Rather, it should be reserved for temporary computations, to the extent possible.

Developing the Design Studio Application

We have designed our dashboard to include six tabs. The first tab is an overview tab providing a quick view of the status of all five metrics chosen. The remaining tabs provide detailed views on each metric complete with some interactive features. Let’s dive into how we designed and built the dashboard, starting with how we connect to the calculation views in SAP HANA.

Pulling Data from SAP HANA

When SAP BusinessObjects Business Intelligence (BI) is used as the platform for Design Studio, this means that both SAP BW and SAP HANA data sources can be used for applications as well as queries that are universe-based. A universe data source can be created by selecting a universe connection that the administrator has set up and also by constructing queries using the Editing Query Specification dialog box. When creating an application using analytic and calculation views from SAP HANA, it is possible to save the application in the SAP HANA repository. With this option, run time of the application is being done strictly by the browser so Java is not required (BI launch pad). During runtime, there will be no use of Business Intelligence Consumer Services (BICS). Instead, Firefly connects to the data. To use this feature, you must be using SAP HANA 1.0SP7 or newer. Any SDK that is created can also be uploaded to and downloaded from the SAP HANA repository. The prerequisites for linking Design Studio to SAP HANA are:

• At least SAP HANA SPS 07
• SAPUI5_1 Delivery unit installed on the SAP HANA system
• HCO_INA_SERVICE Delivery unit installed on the SAP HANA system
• SAP HANA Studio installed on a local machine belonging to an application designer

Design Studio for SAP HANA can be downloaded from the SAP Marketplace; it is named Design Studio HANA 1.3. You can then launch SAP HANA Studio and log in to the system where the delivery unit is to be deployed:

• From SAP HANA Studio, navigate to File > Import > SAP HANA Content > Delivery Unit (Figure 18).

• Then select the appropriate SAP HANA system and client. This is the target system that will be linked to Design Studio.
• The final step is to open the HCOBIAAS.tgz file that was downloaded as part of the SAP BusinessObjects Design Studio for SAP HANA delivery unit. Ensure that the Overwrite inactive versions and Activate objects check boxes are selected, and click the Next button. Now you are ready to begin using the Design Studio applications in unison with SAP HANA.

Design Interface

Over the last two years, Design Studio has become a complete platform for BI dashboard and application development. Design Studio, with its ease of use, has emerged as a far superior alternative to BEx Web App Designer. In particular, Design Studio does not necessitate programming skills in order to be implemented. ABAP coding is necessary to accomplish simple tasks in BEx Web App Designer. However, this is not to say that Design Studio does not offer options for programmers; it does include the option of using a Java Script subset. Furthermore, Design Studio can seamlessly integrate with SAP BusinessObjects Analysis, edition for Microsoft Office. This is not a capability that BEx Web App Designer can offer. In addition to helping users build more dynamic applications, it also enables power users to generate purpose-built OLAP analysis and planning applications. Design Studio also presents company and SAP-delivered content while offering augmented support for mobile devices and SAP HANA data sources. Design Studio offers a variety of graphical options that can be implemented to represent the HANA performance data as desired. The data behind these graphs is based on the associated HANA tables and analytic or calculation views. The Multidimensional Reporting property for these views should be set to True in order to be compatible with Design Studio. Design Studio (ensure that version 1.3 or later is installed) can then be launched and connected to an SAP HANA system by following these steps. Figure 19 Figure 20 3. Define the appropriate data source by navigating to Application > Add Data Source… (Figure 21). This is the SAP HANA view that will be accessed to pull data from SAP HANA into Design Studio. Select the data source and click the OK button.

4. Once the application has been created, it can then be launched by navigating to the Execute drop-down options and selecting Execute on SAP HANA (Figure 22).

Figure 23 shows the Memory Usage tab of your completed performance dashboard as it appears in Design Studio.

Figure 24 shows the Overview tab of your completed performance dashboard as it appears when launched.